| 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
| 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 |