Thread: plpgsql and index usage

plpgsql and index usage

From
Ryan Mahoney
Date:
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