Optimizing timestamp queries? Inefficient Overlaps?

From: "Adam Rich" <adam(dot)r(at)sbcglobal(dot)net>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: Optimizing timestamp queries? Inefficient Overlaps?
Date: 2006-12-18 06:07:46
Message-ID: 023801c7226a$d6978340$6400a8c0@dualcore
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance


I have a table similar to this:

CREATE TABLE event_resources (
event_resource_id serial NOT NULL,
event_id integer NOT NULL,
resource_id integer NOT NULL,
start_date timestamptz NOT NULL,
end_date timestamptz NOT NULL,
CONSTRAINT event_resources_pkey PRIMARY KEY (event_resource_id)
);

Where the same resource can be added to an event multiple times.
Since the table spans a few years, any day queried should
return at most 0.1% of the table, and seems perfect for indexes. So I
add these:

CREATE INDEX er_idx1 ON event_resources (start_date);
CREATE INDEX er_idx2 ON event_resources (end_date);

One query I need to perform is "All event resources that start or end
on a particular day". The first thing that comes to mind is this:

select *
from event_resources er
where er.start_date::date = $1::date or er.end_date::date = $1::date

This is very slow. Pg chooses a sequential scan. (I am running vacuum
and analyze) Shouldn't Pg be able to use an index here?

I've tried creating function indexes using cast, but Pg returns this
error message:

ERROR: functions in index expression must be marked IMMUTABLE

Which I assume is related to timezones and daylight saving issues in
converting
a timestamptz into a plain date.

This form strangely won't use an index either:

select *
from event_resources er
where (er.start_date, er.end_date) overlaps ($1::date, $1::date+1)

This is the only query form I've found that will use an index:

select *
from event_resources er
where (er.start_date >= $1::date and er.start_date < ($1::date+1))
or (er.end_date >= $1::date and er.end_date < ($1::date+1))

I know it's not exactly the same as the overlaps method, but since this
works
I would expect OVERLAPS to work as well. I prefer overlaps because it's
clean
and simple, self documenting.

Another (similar) query I need to perform is "All event resources that
overlap a given
time range". Seems tailor-made for OVERLAPS:

select *
from event_resources er
where (er.start_date, er.end_date) overlaps ($1::timestamptz,
$2::timestamptz)

Again. can't get this to use an index. I have to use this again:

select *
from event_resources er
where (er.start_date >= $1::timestamptz and er.start_date <
$2::timestamptz)
or (er.end_date >= $1::timestamptz and er.end_date < $2::timestamptz)

What am I doing wrong? This is Pg 8.1.2 on RHEL 4.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2006-12-18 06:31:23 Re: Optimizing timestamp queries? Inefficient Overlaps?
Previous Message Tom Lane 2006-12-18 04:59:12 Re: Scaling concerns