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 E07D5D1E0C7 for ; Thu, 19 Feb 2004 04:50:36 +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 17714-08 for ; Thu, 19 Feb 2004 00:50:41 -0400 (AST) Received: from davinci.ethosmedia.com (server228.ethosmedia.com [209.128.84.228]) by svr1.postgresql.org (Postfix) with ESMTP id 125C2D1B456 for ; Thu, 19 Feb 2004 00:50:35 -0400 (AST) Received: from [63.195.55.98] (HELO 192.168.1.29) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 4454626; Wed, 18 Feb 2004 20:51:46 -0800 From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: Tom Lane Subject: Re: Forcing filter/join order? Date: Wed, 18 Feb 2004 20:49:49 -0800 User-Agent: KMail/1.5.4 Cc: Stephan Szabo , pgsql-performance References: <200402181610.31978.josh@agliodbs.com> <200402181718.22687.josh@agliodbs.com> <28696.1077164770@sss.pgh.pa.us> In-Reply-To: <28696.1077164770@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200402182049.49111.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/218 X-Sequence-Number: 5742 Tom, First off, you are correct, I swapped the dates when typing the simplified query into e-mail. > 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. Interesting idea. I'll try it just to see if it works when I have a chance. In the meantime, for production, I'll stick with the hackish solution I was using under 7.2. Knowing that events are never more than one month long for this application, I can do: "WHERE event.event_date >= (begin_date - '1 month) AND event.event_date <= end_date" ... which works because I have a child table which has event information by day: AND events.event_id IN (SELECT event_id FROM event_day WHERE calendar_day BETWEEN begin_date AND end_date); Note that this subselect isn't sufficent on its own, because once again the query planner is unable to correctly estimate the selectivity of the subselect. It needs the "help" of the filter against events.event_date. This is the workaround I was using with 7.2. I had just hoped that some of the improvements that Tom has made over the last two versions would cure the problem, but no dice. -- -Josh Berkus Aglio Database Solutions San Francisco