pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Josh Berkus <josh@agliodbs.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Forcing filter/join order?
Date: Wed, 18 Feb 2004 20:49:49 -0800
Message-ID: <200402182049.49111.josh@agliodbs.com> (raw)
In-Reply-To: <28696.1077164770@sss.pgh.pa.us>
References: <200402181610.31978.josh@agliodbs.com>
	<200402181718.22687.josh@agliodbs.com>
	<28696.1077164770@sss.pgh.pa.us>

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




view thread (12+ messages)  latest in thread

Message-ID: <200402182049.49111.josh@agliodbs.com>
Permalink:  ../200402182049.49111.josh@agliodbs.com/
Also on:    postgresql.org/message-id/200402182049.49111.josh@agliodbs.com

 · 

reply

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: josh@agliodbs.com, tgl@sss.pgh.pa.us, sszabo@megazone.bigpanda.com
  Subject: Re: Forcing filter/join order?
  In-Reply-To: <200402182049.49111.josh@agliodbs.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