[GENERAL] 'now' vs now() performance
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
Re: [GENERAL] 'now' vs now() performance
Jeffrey Melloy [EMAIL PROTECTED] 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
