472,338 Members | 1,734 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,338 software developers and data experts.

'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.


jmelloy=# explain analyze select distinct sender_id from messages where
message_date > now()::date;
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;

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
1 1766
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?


Nov 11 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: Neuruss | last post by:
IronPython is currently at a pre-alpha stage suitable for experimentation but not for serious development work. http://www.ironpython.com
by: David Morgan | last post by:
Hi This is a really weird problem. We have a website that has been running for about six months with no problems. We have recently moved it to...
by: serge | last post by:
I have an SP that is big, huge, 700-800 lines. I am not an expert but I need to figure out every possible way that I can improve the performance...
by: Spare Change | last post by:
This is a recommendation to Microsoft. VB.net and c# are almost exactly equivalent. I know why Microsoft invented VB.net -- to wean old style...
by: teedilo | last post by:
We have an application with a SQL Server 2000 back end that is fairly database intensive -- lots of fairly frequent queries, inserts, updates --...
by: John Lee | last post by:
Hi, All We had a lots of debate on how to use web services (in some extent I think web service is miss used) - here is the scenario I really want...
by: bjarne | last post by:
Willy Denoyette wrote; > ... it > was not the intention of StrousTrup to the achieve the level of efficiency > of C when he invented C++, ......
by: Willie jan | last post by:
place this behind a button that fills a listbox. as you will see the time is now and then 0 or filled in???????????? by hitting the button. is...
by: jvn | last post by:
I am experiencing a particular problem with performance counters. I have created a set of classes, that uses System.Diagnostics.PerformanceCounter...
by: concettolabs | last post by:
In today's business world, businesses are increasingly turning to PowerApps to develop custom business applications. PowerApps is a powerful tool...
by: better678 | last post by:
Question: Discuss your understanding of the Java platform. Is the statement "Java is interpreted" correct? Answer: Java is an object-oriented...
by: teenabhardwaj | last post by:
How would one discover a valid source for learning news, comfort, and help for engineering designs? Covering through piles of books takes a lot of...
by: CD Tom | last post by:
This only shows up in access runtime. When a user select a report from my report menu when they close the report they get a menu I've called Add-ins...
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.