Re: Why is now()::date so much faster than current_date - Mailing list pgsql-performance

From David Rowley
Subject Re: Why is now()::date so much faster than current_date
Date
Msg-id CAKJS1f-uZ_uqXcWMrcqEjBZcWrx5SXn6BUn1bKpyt7X0PXtCnA@mail.gmail.com
Whole thread Raw
In response to Why is now()::date so much faster than current_date  (Thomas Kellerer <spam_eater@gmx.net>)
Responses Re: Why is now()::date so much faster than current_date
List 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/
 PostgreSQL Development, 24x7 Support, Training & Services

pgsql-performance by date:

Previous
From: Thomas Kellerer
Date:
Subject: Why is now()::date so much faster than current_date
Next
From: Tom Lane
Date:
Subject: Re: Why is now()::date so much faster than current_date