From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Rowley <david.rowley@2ndquadrant.com>
cc: Thomas Kellerer <spam_eater@gmx.net>,
 postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Why is now()::date so much faster than current_date
In-reply-to: 
 <CAKJS1f-uZ_uqXcWMrcqEjBZcWrx5SXn6BUn1bKpyt7X0PXtCnA@mail.gmail.com>
References: <n2epmf$647$1@ger.gmane.org>
 <CAKJS1f-uZ_uqXcWMrcqEjBZcWrx5SXn6BUn1bKpyt7X0PXtCnA@mail.gmail.com>
Comments: In-reply-to David Rowley <david.rowley@2ndquadrant.com>
 message dated "Wed, 18 Nov 2015 00:03:57 +1300"
Date: Tue, 17 Nov 2015 10:26:37 -0500
Message-ID: <30718.1447773997@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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


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