Обсуждение: query with timestamp not using index

Поиск
Список
Период
Сортировка

query with timestamp not using index

От
Brian Hirt
Дата:
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

Re: query with timestamp not using index

От
Richard Huxton
Дата:
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

Re: query with timestamp not using index

От
Brian Hirt
Дата:

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

Re: query with timestamp not using index

От
Tom Lane
Дата:
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

Re: query with timestamp not using index

От
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