X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D2C3FD1B758 for ; Mon, 26 Jul 2004 12:42:56 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 57810-05 for ; Mon, 26 Jul 2004 15:42:53 +0000 (GMT) Received: from exchange2.netarx.com (unknown [66.147.137.11]) by svr1.postgresql.org (Postfix) with ESMTP id BD0DAD1B73C for ; Mon, 26 Jul 2004 12:42:31 -0300 (ADT) Received: from [10.0.0.227] ([10.0.0.227]) by exchange2.netarx.com with Microsoft SMTPSVC(5.0.2195.6713); Mon, 26 Jul 2004 11:42:26 -0400 Message-ID: <41052662.3090600@netarx.com> Date: Mon, 26 Jul 2004 11:42:26 -0400 From: "Harmon S. Nine" User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.1) Gecko/20040722 Debian/1.4-2 StumbleUpon/1.79 X-Accept-Language: en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: Timestamp-based indexing References: <410519F6.5020806@netarx.com> <41051C1E.90805@speedfc.com> In-Reply-To: <41051C1E.90805@speedfc.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 26 Jul 2004 15:42:26.0796 (UTC) FILETIME=[26F892C0:01C47327] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200407/174 X-Sequence-Number: 7565 Thank you for your response :) This improves the row estimation, but it is still using a sequential scan. It really seems like the query would go faster if an index scan was used, given the number of rows fetched (both estimated and actual) is significantly less than the number of rows in the table. Is there some way to get the planner to use the timestamp as an index on these queries? monitor=# explain analyze select * from "eventtable" where timestamp between (CURRENT_TIMESTAMP - INTERVAL '10 min') AND CURRENT_TIMESTAMP; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Seq Scan on "eventtable" (cost=0.00..23103.29 rows=2047 width=155) (actual time=10227.253..10276.944 rows=1662 loops=1) Filter: ((("timestamp")::timestamp with time zone >= (('now'::text)::timestamp(6) with time zone - '@ 10 mins'::interval)) AND (("timestamp")::timestamp with time zone <= ('now'::text)::timestamp(6) with time zone)) Total runtime: 10278.628 ms (3 rows) monitor=# SELECT COUNT(*) FROM "eventtable"; count -------- 425602 (1 row) monitor=# -- Harmon Kevin Barnard wrote: > > > Harmon S. Nine wrote: > >> monitor=# explain analyze select * from "eventtable" where timestamp >> > CURRENT_TIMESTAMP - INTERVAL '10 minutes'; >> QUERY PLAN > > > Try > > SELECT * FROM eventtable where timestamp BETWEEN (CURRENT_TIMESTAMP - > INTERVAL '10 minutes') AND CURRENT_TIMESTAMP; > > This should will use a range off valid times. What your query is > doing is looking for 10 minutes ago to an infinate future. Statically > speaking that should encompass most of the table because you have an > infinate range. No index will be used. If you assign a range the > planner can fiqure out what you are looking for. >