X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 86AF8D1E0DB for ; Thu, 19 Feb 2004 03:14:52 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 87715-09 for ; Wed, 18 Feb 2004 23:14:51 -0400 (AST) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id 7188FD1E0DE for ; Wed, 18 Feb 2004 23:14:47 -0400 (AST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 172EE357B5; Wed, 18 Feb 2004 19:14:52 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 15AE3357B2; Wed, 18 Feb 2004 19:14:52 -0800 (PST) Date: Wed, 18 Feb 2004 19:14:52 -0800 (PST) From: Stephan Szabo To: Josh Berkus Cc: pgsql-performance Subject: Re: Forcing filter/join order? In-Reply-To: <200402181718.22687.josh@agliodbs.com> Message-ID: <20040218185530.A38717@megazone.bigpanda.com> References: <200402181610.31978.josh@agliodbs.com> <200402181630.43503.josh@agliodbs.com> <20040218165410.G36125@megazone.bigpanda.com> <200402181718.22687.josh@agliodbs.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/216 X-Sequence-Number: 5740 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 bvalue 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)