pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Kevin Barnard <kbarnard@speedfc.com>
To: Harmon S. Nine <hnine@netarx.com>
To: pgsql-performance@postgresql.org
Subject: Re: Timestamp-based indexing
Date: Mon, 26 Jul 2004 09:58:38 -0500
Message-ID: <41051C1E.90805@speedfc.com> (raw)
In-Reply-To: <410519F6.5020806@netarx.com>
References: <410519F6.5020806@netarx.com>



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.

-- 
Kevin Barnard
Speed Fulfillment and Call Center
kbarnard@speedfc.com
214-258-0120




view thread (13+ messages)  latest in thread

Message-ID: <41051C1E.90805@speedfc.com>
Permalink:  ../41051C1E.90805@speedfc.com/
Also on:    postgresql.org/message-id/41051C1E.90805@speedfc.com

 · 

reply

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: kbarnard@speedfc.com, hnine@netarx.com
  Subject: Re: Timestamp-based indexing
  In-Reply-To: <41051C1E.90805@speedfc.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