Re: Forcing filter/join order?

On Wed, 18 Feb 2004, Josh Berkus wrote:

> Stephan,
>
> > Can you give more information?  I know that I'm not exactly certain what
> > the situation is from the above and the original query/explain piece.
> >
>
> Believe me, if I posted the query it wouldn't help.    Heck, I'd have trouble
> following it without my notes.
>
> a simplifed version:
>
> SELECT events.*, cases.case_name
> FROM events LEFT OUTER JOIN cases ON events.case_id = cases.case_id
> WHERE (event_date >= '2004-03-05' OR (event_date + duration) <= '2004-02-18')
>     AND events.status <> 0;
>
> ... this is to get me all vaild events which overlap with the range
> '2004-02-18' to '2004-03-05'.
>
> I had thought, in 7.4, that adding an index on (event_date, (event_date +
> duration)) would improve the execution of this query.   It doesn't,
> presumably because the multi-column index can't be used for both ascending
> and descending sorts at the same time, and event_date >= '2004-03-05' isn't
> selective enough.

I don't think the direction issue is the problem in the above.  I think
the problem is that given a condition like:
  a>value or b<othervalue
an index on (a,b) doesn't appear to be considered probably since the
b<othervalue wouldn't be indexable by that index and you can't use the
a>value alone since that'd do the wrong thing.

Testing on a two column table, I see behavior like the following (with
seqscan off)

sszabo=# create table q2(a int, b int);
CREATE TABLE
sszabo=# create index q2ind on q2(a,b);
CREATE INDEX
sszabo=# set enable_seqscan=off;
SET
sszabo=# explain select * from q2 where a>3 and b<5;
                            QUERY PLAN
-------------------------------------------------------------------
 Index Scan using q2ind on q2  (cost=0.00..42.79 rows=112 width=8)
   Index Cond: ((a > 3) AND (b < 5))
(2 rows)

sszabo=# explain select * from q2 where a>3 or b<5;
                             QUERY PLAN
--------------------------------------------------------------------
 Seq Scan on q2  (cost=100000000.00..100000025.00 rows=556 width=8)
   Filter: ((a > 3) OR (b < 5))
(2 rows)

sszabo=# create index q2ind2 on q2(b);
CREATE INDEX
sszabo=# explain select * from q2 where a>3 or b<5;
                                QUERY PLAN
---------------------------------------------------------------------------
 Index Scan using q2ind, q2ind2 on q2  (cost=0.00..92.68 rows=556 width=8)
   Index Cond: ((a > 3) OR (b < 5))
(2 rows)