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