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)
<x-tad-smaller>--------------------------------------------
MobyGames
http://www.mobygames.com
The world's largest and most comprehensive
gaming database project</x-tad-smaller>
