pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: josh@agliodbs.com
Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Forcing filter/join order?
Date: Wed, 18 Feb 2004 23:26:10 -0500
Message-ID: <28696.1077164770@sss.pgh.pa.us> (raw)
In-Reply-To: <200402181718.22687.josh@agliodbs.com>
References: <200402181610.31978.josh@agliodbs.com>
	<200402181630.43503.josh@agliodbs.com>
	<20040218165410.G36125@megazone.bigpanda.com>
	<200402181718.22687.josh@agliodbs.com>

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



view thread (12+ messages)  latest in thread

Message-ID: <28696.1077164770@sss.pgh.pa.us>
Permalink:  ../28696.1077164770@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/28696.1077164770@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, josh@agliodbs.com, sszabo@megazone.bigpanda.com
  Subject: Re: Forcing filter/join order?
  In-Reply-To: <28696.1077164770@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox