now() and ::date

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(at)ontko(dot)com Phone 1.765.935.4283 Fax 1.765.962.9788
Ray Ontko & Co. Software Consulting Services http://www.ontko.com/