Skip site navigation (1) Skip section navigation (2)

Re: PL/pgSQL functions - text / varchar - havy performance

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 (view raw or flat)
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

In response to

pgsql-performance by date

Next:From: Shridhar DaithankarDate: 2003-08-29 16:29:14
Subject: 2.4 v/s 2.6 again.
Previous:From: Ken GeisDate: 2003-08-29 16:06:31
Subject: Re: bad estimates

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group