Re: Why is the comparison between timestamp and date so much slower then between two dates

> On 14 Apr 2016, at 15:12, Thomas Kellerer <spam_eater(at)gmx(dot)net> wrote:
>
> 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.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.