Re: Forcing filter/join order?

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