'now' vs now() performance

[Jeffrey Melloy]

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

[Tom Lane]

Jeffrey Melloy <jm*****@visualdistortion.org> writes:

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?

Yes. Note the estimated numbers of rows in the different plans. In
general, a one-sided inequality (col > something) will *not* get turned
into an indexscan unless the planner can see that 'something' is close
enough to the end of the range of 'col' that the indexscan will pull
only a reasonably small number of columns. When the 'something' is not
determinable at plan time, the estimated number of rows will be large
enough to discourage an indexscan.

When you're certain that an indexscan is what you want, you can fake out
the planner by formulating the query as a range query with two variable
endpoints; for example

message_timestamp > now() AND
message_timestamp < (now() + '1000 years'::interval)

(adjusting this to 'date' datatype is left as an exercise for the
student). The planner still doesn't know what's going on, but its
guess for a range query is a lot smaller than for an open-interval
query; you should get an indexscan from it.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org