Thread: plpgsql and index usage

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_proximityWHERE zipcode = zipcode_inAND proximity <=
proximity_range_in
LOOPzipcodes := zipcodes || '','' || somemorerows.zipcode_list;
END LOOP;

However:SELECT zipcode_list FROM pa_zipcode_proximityWHERE zipcode = zipcode_inAND 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

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

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

>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
orc.change the data type in the table
 

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

Thanks!

-r

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

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