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

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.