Re: Why is now()::date so much faster than current_date

On 17 November 2015 at 21:49, Thomas Kellerer <spam_eater(at)gmx(dot)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