Re: Type casting and indexes

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "David Olbersen" <DOlbersen(at)stbernard(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Type casting and indexes
Date: 2003-05-08 16:25:07
Message-ID: 20978.1052411107@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

"David Olbersen" <DOlbersen(at)stbernard(dot)com> writes:
> So it seems that the type conversion is killing the use of the index, even though the type conversion has to happen for the condition to be tested.

Seems like I just answered this yesterday ;-)

Note the difference in the number of estimated rows in the two explains.
The reason is that the timestamptz conversion is not a constant and so
the planner can't get a good estimate of the number of rows that will
satisfy it. (And the reason it's not a constant is that it depends on
SET TIMEZONE.)

Bottom line: declare the constant correctly. Or at least don't
gratuitously cast it to the wrong thing.

regards, tom lane

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2003-05-08 22:35:14 Re: Type casting and indexes
Previous Message scott.marlowe 2003-05-08 16:20:19 Re: [PERFORM] [SQL] Unanswered Questions WAS: An unresolved performance