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 [EMAIL PROTECTED] Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Software Consulting Services http://www.ontko.com/ ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])