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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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/
 PostgreSQL Development, 24x7 Support, Training & Services

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux