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)
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 ? |