Alban Hertroys schrieb am 14.04.2016 um 21:22:
now() (and current_timestamp as well) are defined to return the
same value throughout the entire transaction.
So the optimizer _should_ be smart enough to do the conversion
only once at the beginning of the statement and then use that
converted value during the execution of the statement without the
need to re-evaluate it for each row.
As I understand it, that's not how it works.
If the optimizer would down-convert the value of now() from a
timestamp to a date, it would lose precision, possibly resulting in
wrong results for corner cases in general. For that reason, it
chooses to do the opposite and up-converts the dates. But, because
the dates are fields and not constants, it has to do so for every
row.
If that's indeed what happens, then indeed, now() gets evaluated only
once, but the slow-down is caused by having to do conversions (for
two field values) for every row.
Ah, that makes sense.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
