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

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: Why is now()::date so much faster than current_date
Дата
Msg-id 30718.1447773997@sss.pgh.pa.us
обсуждение исходный текст
Ответ на Re: Why is now()::date so much faster than current_date  (David Rowley <david.rowley@2ndquadrant.com>)
Список pgsql-performance
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


В списке pgsql-performance по дате отправления:

Предыдущее
От: David Rowley
Дата:
Сообщение: Re: Why is now()::date so much faster than current_date
Следующее
От: Blas Pico
Дата:
Сообщение: Query that took a lot of time in Postgresql when not using trim in order by