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

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

 



David Rowley <david.rowley@xxxxxxxxxxxxxxx> writes:
> On 17 November 2015 at 21:49, Thomas Kellerer <spam_eater@xxxxxxx> 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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance



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

  Powered by Linux