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

On 13 April 2016 at 15:45, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Thomas Kellerer <spam_eater(at)gmx(dot)net> writes:
>> So my question is: why is comparing a timestamp to a date so much slower?
>
> The date has to be up-converted to a timestamptz (not timestamp).
> I think the expensive part of that is determining what timezone
> applies, in particular whether DST is active. You could try it
> with "localtimestamp" (no parens) instead of "now()" to see how
> it performs with a non-tz timestamp.
>
> regards, tom lane

Or... you can manually down-convert the timestamptz now() to a date ;)
That's basically what you're doing when you use current_date instead of now().

The reason that the other way around is so much more expensive is that
the database needs to do that conversion twice for every row in the
table. When down-converting now(), the DB only needs to do that once
for all rows.

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