I was recently running into performance problems with a query
containing now()::date or CURRENT_DATE. When I went to debug,
'now'::date made efficient use of the index (on a timestamp field).
The docs say that 'now' is turned into a constant right away. Is this
overhead/poor planning simply because 'now' gets converted to a
constant so much earlier in the process?
I've pasted the query plans below.
Jeff
jmelloy=# explain analyze select distinct sender_id from messages where
message_date > now()::date;
QUERY PLAN
------------------------------------------------------------------------
--------------------------------------------------
Unique (cost=4517.17..4639.74 rows=2451 width=4) (actual
time=1697.62..1697.90 rows=4 loops=1)
-> Sort (cost=4517.17..4578.45 rows=24515 width=4) (actual
time=1697.61..1697.74 rows=62 loops=1)
Sort Key: sender_id
-> Seq Scan on messages (cost=0.00..2729.88 rows=24515
width=4) (actual time=1695.42..1697.22 rows=62 loops=1)
Filter: (message_date > ((now())::date)::timestamp
without time zone)
Total runtime: 1698.11 msec
(6 rows)
jmelloy=# explain analyze select distinct sender_id from messages where
message_date > 'now'::date;
QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------------------------
--------
Unique (cost=201.86..202.14 rows=6 width=4) (actual time=1.24..1.52
rows=4 loops=1)
-> Sort (cost=201.86..202.00 rows=56 width=4) (actual
time=1.23..1.36 rows=62 loops=1)
Sort Key: sender_id
-> Index Scan using adium_msg_date_sender_recipient on
messages (cost=0.00..200.22 rows=56 width=4) (actual time=0.23..0.84
rows=62 loops=1)
Index Cond: (message_date > '2003-08-18
00:00:00'::timestamp without time zone)
Total runtime: 1.74 msec
(6 rows)
