pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Richard Huxton <dev@archonet.com>
To: josh@agliodbs.com, "Arnau" <arnaulist@andromeiberica.com>
To: pgsql-performance@postgresql.org
Subject: Re: Queries with timestamps
Date: Wed, 21 Jan 2004 20:38:22 +0000
Message-ID: <200401212038.22391.dev@archonet.com> (raw)
In-Reply-To: <200401211106.06506.josh@agliodbs.com>
References: <029701c3e04b$ea608930$3c0aa8c0@iberica.andromeiberica.com>
	<200401211106.06506.josh@agliodbs.com>

On Wednesday 21 January 2004 19:06, Josh Berkus wrote:
> Arnau,
>
> >     As the number of rows grows the time needed to execute this query
> > takes longer.  What'd I should do improve the performance of this query?
>
> Tip #1)  add an index to the timestamp column
> Tip #2) make sure that you VACUUM and ANALYZE regularly
> Tip #3) You will get better performance if you pass the "current_date - 1
> month" as a constant from the client instead of in the query.   This is a
> known issue, expected to be fixed in 7.5.

(I think Tip 3 is already fixed in 7.3, or I misunderstand what Josh is 
saying)

Note that this is timestamp-related and not "timestamp with time zone" 
related. Most of the time you want the latter anyway. If you can use with 
time zones and drop the cast you might well find the index is being used...

EXPLAIN ANALYSE SELECT * FROM log_core WHERE log_ts > CURRENT_DATE - '1 
week'::interval;
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Index Scan using log_core_ts_idx on log_core  (cost=0.00..18.73 rows=239 
width=117) (actual time=0.79..0.79 rows=0 loops=1)
   Index Cond: (log_ts > ((('now'::text)::date - '7 
days'::interval))::timestamp with time zone)
 Total runtime: 1.03 msec
(3 rows)

It seems to help an accurate estimate of number-of-rows if you put an upper 
and lower limit in.

-- 
  Richard Huxton
  Archonet Ltd



view thread (4+ messages)  latest in thread

Message-ID: <200401212038.22391.dev@archonet.com>
Permalink:  ../200401212038.22391.dev@archonet.com/
Also on:    postgresql.org/message-id/200401212038.22391.dev@archonet.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: dev@archonet.com, arnaulist@andromeiberica.com
  Subject: Re: Queries with timestamps
  In-Reply-To: <200401212038.22391.dev@archonet.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