The following statements do not utilize an index when executed inside a
plpgsql procedure, but does when executed interactively in psql!

Does not use index:

FOR somemorerows IN 
 SELECT zipcode_list 
 FROM pa_zipcode_proximity
 WHERE zipcode = zipcode_in
 AND proximity <= proximity_range_in
LOOP
 zipcodes := zipcodes || '','' || somemorerows.zipcode_list;
END LOOP;

However:
 SELECT zipcode_list 
 FROM pa_zipcode_proximity
 WHERE zipcode = zipcode_in
 AND proximity <= proximity_range_in;

Does use the index!  zipcode_list and zipcode are text, proximity is an
integer.  There is a primary key on zipcode and proximity.  We are using
PostgreSQL 7.3 on i686-pc-linux-gnu, compiled by GCC 2.96.  Any ideas? 
We have recently done a vacuum full and analyze.

Any help is much appreciated!

-r
-- 
Ryan Mahoney
ryan@flowlabs.com
(718)721-8790

"Tomorrow's Company. Today's Budget."
				http://www.flowlabs.com


20 Dec 2002 17:20:52 -0500
Ryan Mahoney <ryan@flowlabs.com>
plpgsql and index usage
Ryan Mahoney <ryan@flowlabs.com> writes:
> The following statements do not utilize an index when executed inside a
> plpgsql procedure, but does when executed interactively in psql!

I suspect you are not telling the full truth here.

> However:
>  SELECT zipcode_list 
>  FROM pa_zipcode_proximity
>  WHERE zipcode = zipcode_in
>  AND proximity <= proximity_range_in;
> Does use the index!

Where are zipcode_in and proximity_range_in coming from?  Did you
actually type the statement just like that, or are there really
constants there?

I suspect that you're seeing the difference between what the planner
does when it can see a constant comparison value and what it has to do
when it sees a plpgsql variable as the comparison value --- it has to
use default selectivity estimates in the latter case.  But it's hard to
say more without a lot more info.  In particular I'd like to know what
you *really* typed, what EXPLAIN output you get, and what the pg_stats
rows for zipcode and proximity contain ...

			regards, tom lane

Fri, 20 Dec 2002 17:33:03 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: plpgsql and index usage 
Ryan Mahoney <ryan@flowlabs.com> writes:
>  Index Scan using pa_zipcode_proximity_pk on pa_zipcode_proximity  (cost=
> 0.00..13.53 rows=3 width=862)
>    Index Cond: ((zipcode = '11103'::bpchar) AND (proximity <= 100))
> (183 rows)

Hmm ... evidently zipcode is declared as type char(5) (note the "bpchar"
coercion).  Is the plpgsql variable it's being compared to declared the
same way?  This could be ye olde cross-datatype-coercion problem.

			regards, tom lane

Fri, 20 Dec 2002 18:27:49 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: plpgsql and index usage 
>Ryan Mahoney <ryan@flowlabs.com> writes:
>>  Index Scan using pa_zipcode_proximity_pk on pa_zipcode_proximity  (cost=
>> 0.00..13.53 rows=3 width=862)
>>    Index Cond: ((zipcode = '11103'::bpchar) AND (proximity <= 100))
>> (183 rows)
>
>Hmm ... evidently zipcode is declared as type char(5) (note the "bpchar"
>coercion).  Is the plpgsql variable it's being compared to declared the
>same way?  This could be ye olde cross-datatype-coercion problem.

Interesting!  I think in plpgsql the data type of the argument coming
in to the function is "text".  Are you suggesting that I:
	a. use a different datatype in plpgsql or 
	b. cast the values or
	c. change the data type in the table

I am not familiar with the bpchar type, but I am looking into it now.

Thanks!

-r

Sat, 21 Dec 2002 02:47:20 -0500
Ryan Mahoney <ryan@flowlabs.com>
Re: plpgsql and index usage
Ryan Mahoney <ryan@flowlabs.com> writes:
>> Hmm ... evidently zipcode is declared as type char(5) (note the "bpchar"
>> coercion).  Is the plpgsql variable it's being compared to declared the
>> same way?  This could be ye olde cross-datatype-coercion problem.

> Interesting!  I think in plpgsql the data type of the argument coming
> in to the function is "text".

Bingo.

>  Are you suggesting that I:
> 	a. use a different datatype in plpgsql or 
> 	b. cast the values or
> 	c. change the data type in the table

Yes ;-).  Presumably (b) would be the least painful route, but any of
these would do the trick.

> I am not familiar with the bpchar type, but I am looking into it now.

Internal name for char(n).

			regards, tom lane

Sat, 21 Dec 2002 11:37:52 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: plpgsql and index usage 
>>  Are you suggesting that I:
>> 	a. use a different datatype in plpgsql or 
>> 	b. cast the values or
>> 	c. change the data type in the table
>
>Yes ;-).  Presumably (b) would be the least painful route, but any of
>these would do the trick.

It turns out that the text and char(5) were not the problem - it was
an integer field that was passed into the function as a text (the
proximity field)!  It now utilizes the index and has gone from taking
over 7 seconds to complete to .011 seconds!

I love postgres!

Thanks Tom!

-r


Sat, 21 Dec 2002 16:12:50 -0500
Ryan Mahoney <ryan@flowlabs.com>
Re: plpgsql and index usage