Re: Index scan vs. Seq scan on timestamps

Andrew - Supernews wrote:
> On 2004-12-06, Per Jensen <per(at)net-es(dot)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