From: Adam Rich <adam.r@sbcglobal.net>
To: pgsql-performance@postgresql.org
Subject: Optimizing timestamp queries? Inefficient Overlaps?
Date: Mon, 18 Dec 2006 00:07:46 -0600
Message-ID: <023801c7226a$d6978340$6400a8c0@dualcore> (raw)
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.
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: adam.r@sbcglobal.net
Subject: Re: Optimizing timestamp queries? Inefficient Overlaps?
In-Reply-To: <023801c7226a$d6978340$6400a8c0@dualcore>
* 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