query with timestamp not using index

From: Brian Hirt <bhirt(at)mobygames(dot)com>
To: "'Postgresql Performance'" <pgsql-performance(at)postgresql(dot)org>
Cc: Brian Hirt <bhirt(at)mobygames(dot)com>
Subject: query with timestamp not using index
Date: 2004-12-01 16:46:43
Message-ID: 95241C28-43B8-11D9-AD0F-000D93AD2E74@mobygames.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

I have a query that fetches information from a log, based on an indexed
column. The timestamp in the table is with time zone, and the server
time zone is not GMT. However, i want all of the timestamps for a
particular day in GMT. If i simply use a date constant, the index is
used, but the incorrect rows are fetched, since the date is converted
to a timestamp in the server's time zone. When i cast that date to a
GMT date, the index is no longer used. Is there some better way to
write the query so that the planner will use the index? I have
simplied the queries below to demonstrate the problem i'm having.
Thanks for any advice.

SLOW:
basement=# select count(*) from redir_log
basement-# where redir_timestamp >= '10/14/2004'::timestamp without
time zone at time zone 'GMT';
count
-------
33696
(1 row)

basement=# explain analyze
basement-# select count(*) from redir_log
basement-# where redir_timestamp >= '10/14/2004'::timestamp without
time zone at time zone 'GMT';

Aggregate (cost=223093.00..223093.00 rows=1 width=0) (actual
time=5036.975..5036.976 rows=1 loops=1)
-> Seq Scan on redir_log (cost=0.00..219868.95 rows=1289621
width=0) (actual time=4941.127..5006.133 rows=33696 loops=1)
Filter: (redir_timestamp >= timezone('GMT'::text, '2004-10-14
00:00:00'::timestamp without time zone))
Total runtime: 5037.023 ms

FAST:
basement=# select count(*) from redir_log where redir_timestamp >=
'10/14/2004';
count
-------
33072
(1 row)

basement=# explain analyze select count(*) from redir_log where
redir_timestamp >= '10/14/2004';

QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------------------------
--
Aggregate (cost=70479.79..70479.79 rows=1 width=0) (actual
time=84.771..84.772 rows=1 loops=1)
-> Index Scan using redir_log_timestamp on redir_log
(cost=0.00..70404.02 rows=30308 width=0) (actual time=0.022..55.337
rows=33072 loops=1)
Index Cond: (redir_timestamp >= '2004-10-14
00:00:00-06'::timestamp with time zone)
Total runtime: 84.823 ms
(4 rows)

--------------------------------------------
MobyGames
http://www.mobygames.com
The world's largest and most comprehensive 
gaming database project

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Richard Huxton 2004-12-01 17:38:28 Re: query with timestamp not using index
Previous Message Tom Lane 2004-12-01 15:38:37 Re: [PERFORM] pg_restore taking 4 hours!