From: Tom Lane <tgl@sss.pgh.pa.us>
To: Adam Rich <adam.r@sbcglobal.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Optimizing timestamp queries? Inefficient Overlaps?
Date: Mon, 18 Dec 2006 01:31:23 -0500
Message-ID: <22736.1166423483@sss.pgh.pa.us> (raw)
In-Reply-To: <023801c7226a$d6978340$6400a8c0@dualcore>
References: <023801c7226a$d6978340$6400a8c0@dualcore>
"Adam Rich" <adam.r@sbcglobal.net> writes:
> 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)
> );
> CREATE INDEX er_idx1 ON event_resources (start_date);
> CREATE INDEX er_idx2 ON event_resources (end_date);
> 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?
No, unless you were to create the indexes on start_date::date and
end_date::date ...
> I've tried creating function indexes using cast, but Pg returns this
> error message:
> ERROR: functions in index expression must be marked IMMUTABLE
... which you can't do because the cast from timestamptz to date is
dependent on the current timezone setting.
If the start and end are really intended to be accurate only to the
day, as the column names seem to suggest, why didn't you declare them
as date to start with?
> 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.
Sorry, but no -- read the SQL spec for OVERLAPS sometime. It's not even
close to being the same, and with all the weird special cases for nulls,
it's just about unoptimizable :-(
regards, tom lane
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, adam.r@sbcglobal.net
Subject: Re: Optimizing timestamp queries? Inefficient Overlaps?
In-Reply-To: <22736.1166423483@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