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

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

From

Thomas Kellerer

Date:

17 November 2015, 11:49:39

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?

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

From

David Rowley

Date:

17 November 2015, 14:04:02

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

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

From

Tom Lane

Date:

17 November 2015, 18:26:57

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