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)
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
