Optimizing timestamp queries? Inefficient Overlaps?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Title: Optimizing timestamp queries? Inefficient Overlaps?

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.





























[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux