On 17 November 2015 at 21:49, Thomas Kellerer <spam_eater@xxxxxxx> 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/
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
