Обсуждение: query with timestamp not using index
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
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
On Dec 1, 2004, at 1:06 PM, Tom Lane wrote: <excerpt> That seems like the hard way to express a timestamp constant. Why not </excerpt> 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; <excerpt>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 </excerpt><x-tad-smaller>-------------------------------------------- MobyGames http://www.mobygames.com The world's largest and most comprehensive gaming database project</x-tad-smaller> 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
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
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