Skip site navigation (1) Skip section navigation (2)

Re: Forcing filter/join order?

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>,pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Forcing filter/join order?
Date: 2004-02-19 04:49:49
Message-ID: 200402182049.49111.josh@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-performance
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


In response to

Responses

pgsql-performance by date

Next:From: Josh BerkusDate: 2004-02-19 04:56:47
Subject: Re: Tables on multiple disk drives
Previous:From: Tom LaneDate: 2004-02-19 04:26:10
Subject: Re: Forcing filter/join order?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group