Josh Berkus <josh@agliodbs.com> writes:
> SELECT events.*, cases.case_name
> FROM events LEFT OUTER JOIN cases ON events.case_id = cases.case_id
> WHERE (event_date >= '2004-03-05' OR (event_date + duration) <= '2004-02-18')
> AND events.status <> 0;
> ... this is to get me all vaild events which overlap with the range
> '2004-02-18' to '2004-03-05'.
Did you mean events that *don't* overlap with the range? Seems like
what you say you want should be expressed as
event_date <= 'end-date' AND (event_date + duration) >= 'start-date'
This assumes duration is never negative of course.
I think you could make this btree-indexable by negating the second
clause. Imagine
create index evi on events (event_date, (-(event_date+duration)))
and then transforming the query to
event_date <= 'end-date' AND -(event_date + duration) <= -'start-date'
but that doesn't quite work because there's no unary minus for date or
timestamp types. Is this too ugly for you?
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.
regards, tom lane
