Thread: 'now' vs now() performance

'now' vs now() performance

From
Jeffrey Melloy
Date:
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)


Re: 'now' vs now() performance

From
Tom Lane
Date:
Jeffrey Melloy <jmelloy@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