query with timestamp not using index - Mailing list 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)



<x-tad-smaller>--------------------------------------------

MobyGames

http://www.mobygames.com

The world's largest and most comprehensive 

gaming database project</x-tad-smaller>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