From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Forcing filter/join order?
Date: Wed, 18 Feb 2004 19:14:52 -0800 (PST)
Message-ID: <20040218185530.A38717@megazone.bigpanda.com> (raw)
In-Reply-To: <200402181718.22687.josh@agliodbs.com>
References: <200402181610.31978.josh@agliodbs.com>
<200402181630.43503.josh@agliodbs.com>
<20040218165410.G36125@megazone.bigpanda.com>
<200402181718.22687.josh@agliodbs.com>
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sszabo@megazone.bigpanda.com, josh@agliodbs.com
Subject: Re: Forcing filter/join order?
In-Reply-To: <20040218185530.A38717@megazone.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox