pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
query with timestamp not using index
5+ messages / 3 participants
[nested] [flat]

* query with timestamp not using index
@ 2004-12-01 16:46 Brian Hirt <bhirt@mobygames.com>
  2004-12-01 17:38 ` Re: query with timestamp not using index Richard Huxton <dev@archonet.com>
  2004-12-01 20:06 ` Re: query with timestamp not using index Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 5+ messages in thread

From: Brian Hirt @ 2004-12-01 16:46 UTC (permalink / raw)
  To: pgsql-performance; +Cc: Brian Hirt <bhirt@mobygames.com>

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=

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: query with timestamp not using index
  2004-12-01 16:46 query with timestamp not using index Brian Hirt <bhirt@mobygames.com>
@ 2004-12-01 17:38 ` Richard Huxton <dev@archonet.com>
  1 sibling, 0 replies; 5+ messages in thread

From: Richard Huxton @ 2004-12-01 17:38 UTC (permalink / raw)
  To: Brian Hirt <bhirt@mobygames.com>; +Cc: pgsql-performance

Brian Hirt wrote:
> 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';

Not quite what's wanted. Try keeping things as a timestamp with timezone 
(you can add a timestamp to a date):

SELECT count(*) FROM redir_log
WHERE redir_timestamp BETWEEN '2004-10-14+00'::timestamptz AND 
CURRENT_TIMESTAMP;

Putting two bounds on the range can also help index usage.

In actual fact, since you're comparing to a timestamp and not a date, 
I'd personally supply a valid timestamptz: '2004-10-14 00:00:00+00'
--
   Richard Huxton
   Archonet Ltd



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: query with timestamp not using index
  2004-12-01 16:46 query with timestamp not using index Brian Hirt <bhirt@mobygames.com>
@ 2004-12-01 20:06 ` Tom Lane <tgl@sss.pgh.pa.us>
  2004-12-01 22:09   ` Re: query with timestamp not using index Brian Hirt <bhirt@mobygames.com>
  1 sibling, 1 reply; 5+ messages in thread

From: Tom Lane @ 2004-12-01 20:06 UTC (permalink / raw)
  To: Brian Hirt <bhirt@mobygames.com>; +Cc: pgsql-performance

Brian Hirt <bhirt@mobygames.com> writes:
> select count(*) from redir_log
> where redir_timestamp >= '10/14/2004'::timestamp without time zone at time zone 'GMT';

That seems like the hard way to express a timestamp constant.  Why not

select count(*) from redir_log
where redir_timestamp >= '10/14/2004 00:00 GMT';

(FWIW, though, the AT TIME ZONE construct *should* have been collapsed
to a constant; 8.0 fixes this.)

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: query with timestamp not using index
  2004-12-01 16:46 query with timestamp not using index Brian Hirt <bhirt@mobygames.com>
  2004-12-01 20:06 ` Re: query with timestamp not using index Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-12-01 22:09   ` Brian Hirt <bhirt@mobygames.com>
  2004-12-01 22:16     ` Re: query with timestamp not using index Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 5+ messages in thread

From: Brian Hirt @ 2004-12-01 22:09 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance


On Dec 1, 2004, at 1:06 PM, Tom Lane wrote:
>
> That seems like the hard way to express a timestamp constant.  Why not
>

I realized after i sent this message that i might get this responese.  
I should have mentioned this was from within a stored pl/pgsql 
function, and the date wasn't a constant, but a variable.  I was just 
trying to simplify the example.

it's more like:

declare
	foo_date date;
begin
	select some_date into foo_date from some_table where something = 
something_else;
	
	select blah from redir_log where redir_timestamp >= 
foo_date::timestamp without time zone at time zone 'GMT';
	etc / etc / etc
end;

> select count(*) from redir_log
> where redir_timestamp >= '10/14/2004 00:00 GMT';
>
> (FWIW, though, the AT TIME ZONE construct *should* have been collapsed
> to a constant; 8.0 fixes this.)
>
> 			regards, tom lane
>
--------------------------------------------
MobyGames
http://www.mobygames.com
The world's largest and most comprehensive 
gaming database project

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: query with timestamp not using index
  2004-12-01 16:46 query with timestamp not using index Brian Hirt <bhirt@mobygames.com>
  2004-12-01 20:06 ` Re: query with timestamp not using index Tom Lane <tgl@sss.pgh.pa.us>
  2004-12-01 22:09   ` Re: query with timestamp not using index Brian Hirt <bhirt@mobygames.com>
@ 2004-12-01 22:16     ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 5+ messages in thread

From: Tom Lane @ 2004-12-01 22:16 UTC (permalink / raw)
  To: Brian Hirt <bhirt@mobygames.com>; +Cc: pgsql-performance

Brian Hirt <bhirt@mobygames.com> writes:
> it's more like:

> declare
> 	foo_date date;
> begin
> 	select some_date into foo_date from some_table where something = something_else;
> 	select blah from redir_log where redir_timestamp >= foo_date::timestamp without time zone at time zone 'GMT';
> 	etc / etc / etc

Ah.  In that case you're going to have trouble anyway with the planner
having no clue what the effective value of the comparison expression is,
because it'll certainly not be able to fold the plpgsql variable to a
constant.  I agree with the other person who suggested faking it out
by adding a dummy other-side-of-the-range constraint, perhaps
	AND redir_timestamp <= now()
(or whatever upper bound is okay for your purposes).  This should coax
it into using an indexscan.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2004-12-01 22:16 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-12-01 16:46 query with timestamp not using index Brian Hirt <bhirt@mobygames.com>
2004-12-01 17:38 ` Richard Huxton <dev@archonet.com>
2004-12-01 20:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-12-01 22:09   ` Brian Hirt <bhirt@mobygames.com>
2004-12-01 22:16     ` Tom Lane <tgl@sss.pgh.pa.us>

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