X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id A31BE9DCBD1 for ; Sat, 4 Mar 2006 14:11:21 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 16266-09 for ; Sat, 4 Mar 2006 14:11:21 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from stark.xeocode.com (stark.xeocode.com [216.58.44.227]) by postgresql.org (Postfix) with ESMTP id 1BF449DCAC5 for ; Sat, 4 Mar 2006 14:11:16 -0400 (AST) Received: from localhost ([127.0.0.1] helo=stark.xeocode.com) by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian)) id 1FFbDl-0006sz-00; Sat, 04 Mar 2006 13:11:13 -0500 To: Tom Lane Cc: Greg Stark , Alex Adriaanse , pgsql-performance@postgresql.org Subject: Re: Bad row estimates References: <4408E91C.90104@innovacomputing.com> <87wtfasng0.fsf@stark.xeocode.com> <14850.1141488599@sss.pgh.pa.us> In-Reply-To: <14850.1141488599@sss.pgh.pa.us> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 04 Mar 2006 13:11:13 -0500 Message-ID: <87fylyrsfy.fsf@stark.xeocode.com> Lines: 28 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.4 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.129 required=5 tests=[AWL=0.129] X-Spam-Score: 0.129 X-Spam-Level: X-Archive-Number: 200603/27 X-Sequence-Number: 17464 Tom Lane 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