By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,792 Members | 1,489 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,792 IT Pros & Developers. It's quick & easy.

'now' vs now() performance

P: n/a
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

Nov 11 '05 #1
Share this Question
Share on Google+
1 Reply

P: n/a
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

Nov 11 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.