'now' vs now() performance

From: Jeffrey Melloy <jmelloy(at)visualdistortion(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: 'now' vs now() performance
Date: 2003-08-19 01:52:55
Message-ID: DA3CAD0E-D1E7-11D7-A756-000393C78AC0@visualdistortion.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Stephan Szabo 2003-08-19 02:16:01 Re: Why lower's not accept an AS declaration ?
Previous Message Tom Lane 2003-08-19 00:44:58 Re: Why lower's not accept an AS declaration ?