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