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 D224CD1E15F for ; Thu, 19 Feb 2004 04:26:30 +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 16357-03 for ; Thu, 19 Feb 2004 00:26:30 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id D5161D1E164 for ; Thu, 19 Feb 2004 00:26:23 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i1J4QAtr028697; Wed, 18 Feb 2004 23:26:10 -0500 (EST) To: josh@agliodbs.com Cc: Stephan Szabo , pgsql-performance Subject: Re: Forcing filter/join order? 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> Comments: In-reply-to Josh Berkus message dated "Wed, 18 Feb 2004 17:18:22 -0800" Date: Wed, 18 Feb 2004 23:26:10 -0500 Message-ID: <28696.1077164770@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/217 X-Sequence-Number: 5741 Josh Berkus writes: > 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'. Did you mean events that *don't* overlap with the range? Seems like what you say you want should be expressed as event_date <= 'end-date' AND (event_date + duration) >= 'start-date' This assumes duration is never negative of course. I think you could make this btree-indexable by negating the second clause. Imagine create index evi on events (event_date, (-(event_date+duration))) and then transforming the query to event_date <= 'end-date' AND -(event_date + duration) <= -'start-date' but that doesn't quite work because there's no unary minus for date or timestamp types. Is this too ugly for you? create index evi on events (event_date, ('ref-date'-event_date-duration)) event_date <= 'end-date' AND ('ref-date'-event_date-duration) <= 'ref-date'-'start-date' where 'ref-date' is any convenient fixed reference date, say 1-1-2000. Now, what this will look like to the planner is a one-sided two-column restriction, and I'm not certain that the planner will assign a sufficiently small selectivity estimate. But in theory it could work. regards, tom lane