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 93E80D1B751 for ; Mon, 26 Jul 2004 11:52:28 -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 30968-04 for ; Mon, 26 Jul 2004 14:52:29 +0000 (GMT) Received: from exchange2.netarx.com (unknown [66.147.137.11]) by svr1.postgresql.org (Postfix) with ESMTP id 8CBE4D1B74B for ; Mon, 26 Jul 2004 11:49:49 -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 10:49:26 -0400 Message-ID: <410519F6.5020806@netarx.com> Date: Mon, 26 Jul 2004 10:49: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: Timestamp-based indexing Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 26 Jul 2004 14:49:26.0280 (UTC) FILETIME=[BF3C5880:01C4731F] 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/170 X-Sequence-Number: 7561 Hello -- To increase query (i.e. select) performance, we're trying to get postgres to use an index based on a timestamp column in a given table. Event-based data is put into this table several times a minute, with the timestamp indicating when a particular row was placed in the table. The table is purged daily, retaining only the rows that are less than 7 days old. That is, any row within the table is less than 1 week old (+ 1 day, since the purge is daily). A typical number of rows in the table is around 400,000. A "VACUUM FULL ANALYZE" is performed every 3 hours. The problem: We often query the table to extract those rows that are, say, 10 minutes old or less. Given there are 10080 minutes per week, the planner could, properly configured, estimate the number of rows returned by such a query to be: 10 min/ 10080 min * 400,000 = 0.001 * 400,000 = 400. Making an index scan, with the timestamp field the index, far faster then a sequential scan. However, we can't get the planner to do an timestamp-based index scan. Anyone know what to do? Here's the table specs: monitor=# \d "eventtable" Table "public.eventtable" Column | Type | Modifiers -----------+-----------------------------+-------------------------------------------------------------- timestamp | timestamp without time zone | not null default ('now'::text)::timestamp(6) with time zone key | bigint | not null default nextval('public."eventtable_key_seq"'::text) propagate | boolean | facility | character(10) | priority | character(10) | host | character varying(128) | not null message | text | not null Indexes: "eventtable_pkey" primary key, btree ("timestamp", "key") "eventtable_host" btree (host) "eventtable_timestamp" btree ("timestamp") Here's a query (with "explain analyze"): monitor=# explain analyze select * from "eventtable" where timestamp > CURRENT_TIMESTAMP - INTERVAL '10 minutes'; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------- Seq Scan on "eventtable" (cost=0.00..19009.97 rows=136444 width=155) (actual time=11071.073..11432.522 rows=821 loops=1) Filter: (("timestamp")::timestamp with time zone > (('now'::text)::timestamp(6) with time zone - '@ 10 mins'::interval)) Total runtime: 11433.384 ms (3 rows) Here's something strange. We try to disable sequential scans, but to no avail. The estimated cost skyrockets, though: monitor=# set enable_seqscan = false; SET monitor=# explain analyze select * from "eventtable" where timestamp > CURRENT_TIMESTAMP - INTERVAL '10 minutes'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------- Seq Scan on "eventtable" (cost=100000000.00..100019009.97 rows=136444 width=155) (actual time=9909.847..9932.438 rows=1763 loops=1) Filter: (("timestamp")::timestamp with time zone > (('now'::text)::timestamp(6) with time zone - '@ 10 mins'::interval)) Total runtime: 9934.353 ms (3 rows) monitor=# set enable_seqscan = true; SET monitor=# Any help is greatly appreciated :) -- Harmon