pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedquery 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