Howdy,
I'm having a little trouble understanding the query optimizer
related to a timestamp with time zone column. I have a table
called "event" with a not null column called "event_date_time"
of type "timestamp with time zone" that has been recently analyzed.
There are about 500,000 rows in the table and about 10,000 distinct
values.
My basic questions are:
Why does "now()" disqualify use of the index?
Why does "::date" disqualify use of the index?
1) This works:
explain select count(*)
from event
where event_date_time >= '2002-12-25'::timestamp with time zone - '1 month'::interval
and event_date_time < '2002-12-25'::timestamp with time zone
;
NOTICE: QUERY PLAN:
Aggregate (cost=4647.02..4647.02 rows=1 width=0)
-> Index Scan using event_date_time on event (cost=0.00..4643.95
rows=1227 w
idth=0)
EXPLAIN
2) This fails to use the index when I cast the literals as "date". Why?
explain select count(*)
from event
where event_date_time >= '2002-12-25'::date - '1 month'::interval
and event_date_time < '2002-12-25'::date
;
NOTICE: QUERY PLAN:
Aggregate (cost=21479.33..21479.33 rows=1 width=0)
-> Seq Scan on event (cost=0.00..21337.66 rows=56665 width=0)
EXPLAIN
3) This fails to use the index when I try to use "now()" instead of
a literal date. Why?
explain select count(*)
from event
where event_date_time >= now()::timestamp with time zone - '1 month'::interval
and event_date_time < now()::timestamp with time zone
;
NOTICE: QUERY PLAN:
Aggregate (cost=21479.33..21479.33 rows=1 width=0)
-> Seq Scan on event (cost=0.00..21337.66 rows=56665 width=0)
EXPLAIN
Ray
----------------------------------------------------------------------
Ray Ontko rayo@ontko.com Phone 1.765.935.4283 Fax 1.765.962.9788
Ray Ontko & Co. Software Consulting Services http://www.ontko.com/