Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 1368E9F9357 for ; Mon, 18 Dec 2006 02:07:16 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 36585-03 for ; Mon, 18 Dec 2006 02:07:03 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from smtp107.sbc.mail.re2.yahoo.com (smtp107.sbc.mail.re2.yahoo.com [68.142.229.98]) by postgresql.org (Postfix) with SMTP id 42A8F9F931E for ; Mon, 18 Dec 2006 02:07:10 -0400 (AST) Received: (qmail 65025 invoked from network); 18 Dec 2006 06:07:09 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=sbcglobal.net; h=Received:X-YMail-OSG:From:To:Subject:Date:Message-ID:MIME-Version:Content-Type:X-Priority:X-MSMail-Priority:X-Mailer:X-MimeOLE:Importance; b=STSg0220J8urteduD5fw5V1aHpo7ZsaVfj7YjZlfUVxvomMnuQbHmvb7/fhLNiEeJcCgxx9krptmVcxQZ2gxEevczEAsNQFocMahDg27JEKMYJRD9sYPwvO6dbJnuqfoIkwboQJA3SL6HlISYiGNsqCKa4J+bllhy1Tai9So9Q8= ; Received: from unknown (HELO dualcore) (adam.r@sbcglobal.net@75.2.73.81 with login) by smtp107.sbc.mail.re2.yahoo.com with SMTP; 18 Dec 2006 06:07:08 -0000 X-YMail-OSG: XpOYW6oVM1k.UOxG2nMOalI9Oc4G8frzjlyEntUguuLy3G9mieD0GgVL7513l_Sva9IXV1yDAxE5HWXOG1ESC7oc.Ql1u6fdwZiGOBeTtQi8t3ItR5_VAgjtP9x58KAWFpBN2hRQbRsbwLSXNOUhpg8m9tN.OC1WnpA0hOcCimks6zuXTOisi_WH.HLr From: "Adam Rich" To: Subject: Optimizing timestamp queries? Inefficient Overlaps? Date: Mon, 18 Dec 2006 00:07:46 -0600 Message-ID: <023801c7226a$d6978340$6400a8c0@dualcore> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0239_01C72238.8BFD1340" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.2627 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2757 Importance: Normal X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.884 tagged_above=0 required=5 tests=AWL, BAYES_40, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, HTML_50_60, HTML_MESSAGE X-Spam-Level: * X-Archive-Number: 200612/457 X-Sequence-Number: 22513 This is a multi-part message in MIME format. ------=_NextPart_000_0239_01C72238.8BFD1340 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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. ------=_NextPart_000_0239_01C72238.8BFD1340 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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 =3D = $1::date  or er.end_date::date =3D $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 >=3D $1::date = and er.start_date < ($1::date+1))
or (er.end_date >=3D $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 >=3D = $1::timestamptz and er.start_date < $2::timestamptz)
or (er.end_date >=3D = $1::timestamptz and er.end_date < $2::timestamptz)

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




























------=_NextPart_000_0239_01C72238.8BFD1340--