From: Harmon S. Nine <hnine@netarx.com>
To: pgsql-performance@postgresql.org
Subject: Re: Timestamp-based indexing
Date: Mon, 26 Jul 2004 11:42:26 -0400
Message-ID: <41052662.3090600@netarx.com> (raw)
In-Reply-To: <41051C1E.90805@speedfc.com>
References: <410519F6.5020806@netarx.com>
<41051C1E.90805@speedfc.com>
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.
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: hnine@netarx.com
Subject: Re: Timestamp-based indexing
In-Reply-To: <41052662.3090600@netarx.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox