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 <= 

... which works because I have a child table which has event information by 

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [EMAIL PROTECTED] so that your
      message can get through to the mailing list cleanly

Reply via email to