From: | Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> |
---|---|
To: | Josh Berkus <josh(at)agliodbs(dot)com> |
Cc: | pgsql-performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Forcing filter/join order? |
Date: | 2004-02-19 03:14:52 |
Message-ID: | 20040218185530.A38717@megazone.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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)
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2004-02-19 04:26:10 | Re: Forcing filter/join order? |
Previous Message | Josh Berkus | 2004-02-19 01:18:22 | Re: Forcing filter/join order? |