Re: Why is the comparison between timestamp and date so much slower then between two dates - Mailing list pgsql-general

From Alban Hertroys
Subject Re: Why is the comparison between timestamp and date so much slower then between two dates
Date
Msg-id 71DB9E8F-68C1-41D2-893E-95F991AC526E@gmail.com
Whole thread Raw
In response to Re: Why is the comparison between timestamp and date so much slower then between two dates  (Thomas Kellerer <spam_eater@gmx.net>)
Responses Re: Why is the comparison between timestamp and date so much slower then between two dates
List pgsql-general
> On 14 Apr 2016, at 15:12, Thomas Kellerer <spam_eater@gmx.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
resultingin 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.



pgsql-general by date:

Previous
From: Kevin Burke
Date:
Subject: Re: Deadlock between VACUUM and ALTER TABLE commands
Next
From: Thomas Kellerer
Date:
Subject: Re: Why is the comparison between timestamp and date so much slower then between two dates