From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Andrew Sullivan <andrew(at)libertyrms(dot)info> |
Cc: | pgsql-performance(at)postgresql(dot)org, Oliver Siegmar <o(dot)siegmar(at)vitrado(dot)de>, Bill Moran <wmoran(at)potentialtech(dot)com> |
Subject: | Re: PL/pgSQL functions - text / varchar - havy performance |
Date: | 2003-08-29 16:18:35 |
Message-ID: | 28309.1062173915@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Andrew Sullivan <andrew(at)libertyrms(dot)info> writes:
> On Fri, Aug 29, 2003 at 11:34:13AM -0400, Bill Moran wrote:
>> Have any explanation as to why that function is so slow?
> Sorry, no. It might have to do with the planning, though.
Specifically, I'll bet he's getting an indexscan plan with one and not
with the other. It's just ye olde cross-datatype-comparisons-aren't-
indexable problem. "varchar = varchar" matches the index on the varchar
column, but "text = text" is a different operator that doesn't match.
Guess which one gets selected when the initial input is "varchar = text".
7.4 has fixed this particular problem by essentially eliminating the
separate operators for varchar, but in prior releases the behavior
Oliver describes is entirely to be expected. A workaround is to
cast inside the function:
... where varcharcolumn = textarg::varchar;
so that "=" gets interpreted as "varchar = varchar".
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Shridhar Daithankar | 2003-08-29 16:29:14 | 2.4 v/s 2.6 again. |
Previous Message | Ken Geis | 2003-08-29 16:06:31 | Re: bad estimates |