pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedWhy is now()::date so much faster than current_date
3+ messages / 3 participants
[nested] [flat]
* Why is now()::date so much faster than current_date
@ 2015-11-17 08:49 Thomas Kellerer <spam_eater@gmx.net>
2015-11-17 11:03 ` Re: Why is now()::date so much faster than current_date David Rowley <david.rowley@2ndquadrant.com>
0 siblings, 1 reply; 3+ messages in thread
From: Thomas Kellerer @ 2015-11-17 08:49 UTC (permalink / raw)
To: pgsql-performance
Hello,
I stumbled over this answer: http://stackoverflow.com/a/9717125/330315 and this sounded quite strange to me.
So I ran this on my Windows laptop with Postgres 9.4.5, 64bit and indeed now()::date is much faster than current_date:
explain analyze
select current_date
from generate_series (1, 1000000);
Function Scan on generate_series (cost=0.00..6.00 rows=1000 width=0) (actual time=243.878..1451.839 rows=1000000 loops=1)
Planning time: 0.047 ms
Execution time: 1517.881 ms
And:
explain analyze
select now()::date
from generate_series (1, 1000000);
Function Scan on generate_series (cost=0.00..6.00 rows=1000 width=0) (actual time=244.491..785.819 rows=1000000 loops=1)
Planning time: 0.037 ms
Execution time: 826.612 ms
Running this on a CentOS 6.6. test server (Postgres 9.4.1, 64bit), there is still a difference, but not as big as on Windows:
explain analyze
select current_date
from generate_series (1, 1000000);
Function Scan on generate_series (cost=0.00..15.00 rows=1000 width=0) (actual time=233.599..793.032 rows=1000000 loops=1)
Planning time: 0.087 ms
Execution time: 850.198 ms
And
explain analyze
select now()::date
from generate_series (1, 1000000);
Function Scan on generate_series (cost=0.00..15.00 rows=1000 width=0) (actual time=198.385..570.171 rows=1000000 loops=1)
Planning time: 0.074 ms
Execution time: 623.211 ms
Any ideas?
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Why is now()::date so much faster than current_date
2015-11-17 08:49 Why is now()::date so much faster than current_date Thomas Kellerer <spam_eater@gmx.net>
@ 2015-11-17 11:03 ` David Rowley <david.rowley@2ndquadrant.com>
2015-11-17 15:26 ` Re: Why is now()::date so much faster than current_date Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: David Rowley @ 2015-11-17 11:03 UTC (permalink / raw)
To: Thomas Kellerer <spam_eater@gmx.net>; +Cc: pgsql-performance
On 17 November 2015 at 21:49, Thomas Kellerer <spam_eater@gmx.net> wrote:
> Hello,
>
> I stumbled over this answer: http://stackoverflow.com/a/9717125/330315
> and this sounded quite strange to me.
>
> So I ran this on my Windows laptop with Postgres 9.4.5, 64bit and indeed
> now()::date is much faster than current_date:
>
> explain analyze
> select current_date
> from generate_series (1, 1000000);
>
> Function Scan on generate_series (cost=0.00..6.00 rows=1000 width=0)
> (actual time=243.878..1451.839 rows=1000000 loops=1)
> Planning time: 0.047 ms
> Execution time: 1517.881 ms
>
> And:
>
> explain analyze
> select now()::date
> from generate_series (1, 1000000);
>
> Function Scan on generate_series (cost=0.00..6.00 rows=1000 width=0)
> (actual time=244.491..785.819 rows=1000000 loops=1)
> Planning time: 0.037 ms
> Execution time: 826.612 ms
>
>
>
The key to this is in the EXPLAIN VERBOSE output:
postgres=# explain verbose select current_date;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0)
Output: ('now'::cstring)::date
(2 rows)
You can see that the implementation of current_date requires using the
date_in() function as well as the date_out() function. date_in() parses the
'now' string, then the resulting date is converted back into a date string
with date_out(). Using now()::date does not have to parse any date
strings, it just needs to call date_out() to give the final output.
The reason for this is likely best explained by the comment in gram.y:
/*
* Translate as "'now'::text::date".
*
* We cannot use "'now'::date" because coerce_type() will
* immediately reduce that to a constant representing
* today's date. We need to delay the conversion until
* runtime, else the wrong things will happen when
* CURRENT_DATE is used in a column default value or rule.
*
* This could be simplified if we had a way to generate
* an expression tree representing runtime application
* of type-input conversion functions. (As of PG 7.3
* that is actually possible, but not clear that we want
* to rely on it.)
*
* The token location is attached to the run-time
* typecast, not to the Const, for the convenience of
* pg_stat_statements (which doesn't want these constructs
* to appear to be replaceable constants).
*/
--
David Rowley http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/;
PostgreSQL Development, 24x7 Support, Training & Services
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Why is now()::date so much faster than current_date
2015-11-17 08:49 Why is now()::date so much faster than current_date Thomas Kellerer <spam_eater@gmx.net>
2015-11-17 11:03 ` Re: Why is now()::date so much faster than current_date David Rowley <david.rowley@2ndquadrant.com>
@ 2015-11-17 15:26 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2015-11-17 15:26 UTC (permalink / raw)
To: David Rowley <david.rowley@2ndquadrant.com>; +Cc: Thomas Kellerer <spam_eater@gmx.net>; pgsql-performance
David Rowley <david.rowley@2ndquadrant.com> writes:
> On 17 November 2015 at 21:49, Thomas Kellerer <spam_eater@gmx.net> wrote:
>> So I ran this on my Windows laptop with Postgres 9.4.5, 64bit and indeed
>> now()::date is much faster than current_date:
> You can see that the implementation of current_date requires using the
> date_in() function as well as the date_out() function. date_in() parses the
> 'now' string, then the resulting date is converted back into a date string
> with date_out(). Using now()::date does not have to parse any date
> strings, it just needs to call date_out() to give the final output.
Actually, in the context of EXPLAIN ANALYZE, date_out() will never be
invoked at all --- EXPLAIN just throws away the query output without
bothering to transform it to text first. So what we're really comparing
is timestamptz_date(now()) versus date_in('now'). The useful work ends
up being exactly the same in either code path, but date_in has to expend
additional cycles on parsing the string and recognizing that it means
DTK_NOW.
> The reason for this is likely best explained by the comment in gram.y:
That bit of gram.y is just an old bad decision though, along with similar
choices for some other SQL special functions. Quite aside from any
efficiency issues, doing things this way makes it impossible to
reverse-list a call of CURRENT_DATE as CURRENT_DATE, which we really
ought to do if we pretend to be a SQL-compliant RDBMS. And it's just
ugly at a code level too: the raw grammar is not the place to encode
implementation decisions like these.
I've had it on my to-do list for awhile to replace those things with
some new expression node type, but haven't got round to it.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2015-11-17 15:26 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-11-17 08:49 Why is now()::date so much faster than current_date Thomas Kellerer <spam_eater@gmx.net>
2015-11-17 11:03 ` David Rowley <david.rowley@2ndquadrant.com>
2015-11-17 15:26 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox