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

Re: Forcing filter/join order?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: josh(at)agliodbs(dot)com
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:26:10
Message-ID: 28696.1077164770@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
Josh Berkus <josh(at)agliodbs(dot)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

In response to

Responses

pgsql-performance by date

Next:From: Josh BerkusDate: 2004-02-19 04:49:49
Subject: Re: Forcing filter/join order?
Previous:From: Stephan SzaboDate: 2004-02-19 03:14:52
Subject: Re: Forcing filter/join order?

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