From: | Greg Stark <gsstark(at)mit(dot)edu> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Greg Stark <gsstark(at)MIT(dot)EDU>, Alex Adriaanse <alex(at)innovacomputing(dot)com>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Bad row estimates |
Date: | 2006-03-04 18:11:13 |
Message-ID: | 87fylyrsfy.fsf@stark.xeocode.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> Otherwise I think you really need a special datatype for time
> intervals and a GIST or r-tree index on it :-(.
You could actually take short cuts using expression indexes to do this. If it
works out well then you might want to implement a real data type to avoid the
overhead of the SQL conversion functions.
Here's an example. If I were to do this for real I would look for a better
datatype than the box datatype and I would wrap the whole conversion in an SQL
function. But this will serve to demonstrate:
stark=> create table interval_test (start_ts timestamp with time zone, end_ts timestamp with time zone);
CREATE TABLE
stark=> create index interval_idx on interval_test using gist (box(point(start_ts::abstime::integer, end_ts::abstime::integer) , point(start_ts::abstime::integer, end_ts::abstime::integer)));
CREATE INDEX
stark=> explain select * from interval_test where box(point(now()::abstime::integer,now()::abstime::integer),point(now()::abstime::integer,now()::abstime::integer)) ~ box(point(start_ts::abstime::integer, end_ts::abstime::integer) , point(start_ts::abstime::integer, end_ts::abstime::integer));
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using interval_idx on interval_test (cost=0.07..8.36 rows=2 width=16)
Index Cond: (box(point((((now())::abstime)::integer)::double precision, (((now())::abstime)::integer)::double precision), point((((now())::abstime)::integer)::double precision, (((now())::abstime)::integer)::double precision)) ~ box(point((((start_ts)::abstime)::integer)::double precision, (((end_ts)::abstime)::integer)::double precision), point((((start_ts)::abstime)::integer)::double precision, (((end_ts)::abstime)::integer)::double precision)))
(2 rows)
--
greg
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Brown | 2006-03-04 21:35:17 | Re: How to query and index for customer with lastname and city |
Previous Message | Tom Lane | 2006-03-04 16:09:59 | Re: Bad row estimates |