Re: Index scan vs. Seq scan on timestamps

Andrew - Supernews wrote:
> On 2004-12-06, Per Jensen <per@net-es.dk> wrote:
>
>>Why does PG not use the index on the time column in the second select,
>>timeofday() has been cast to a timestamp after all.
>
>
> "timestamp" is "timestamp without time zone" (not the most useful type in
> the world). Your column is of type "timestamp with time zone" (correct).
> The relationship between the two is not trivial and the lack of an index
> scan therefore expected. Try casting to "timestamp with time zone" instead.
>

Andrew,

thanks for your fast reply.

explain
select count(*)
from accesslog
where time  between (timeofday()::timestamptz - INTERVAL '30 d') and
timeofday()::timestamptz;

gives

  Aggregate  (cost=32398.12..32398.12 rows=1 width=0)
    ->  Seq Scan on accesslog  (cost=0.00..32255.42 rows=57077 width=0)
          Filter: (("time" >= ((timeofday())::timestamp with time zone -
'30 days'::interval)) AND ("time" <= (timeofday())::timestamp with time
zone))

Still a seq scan

/Per