On 2004-12-06, Per Jensen <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
