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

plpgsql and index usage

From: Ryan Mahoney <ryan(at)flowlabs(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgreSQL(dot)org>
Subject: plpgsql and index usage
Date: 2002-12-20 22:35:19
Message-ID: 1040423719.1573.80.camel@localhost.localdomain (view raw or flat)
Thread:
Lists: pgsql-hackers
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(at)flowlabs(dot)com
(718)721-8790

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


pgsql-hackers by date

Next:From: Tom LaneDate: 2002-12-20 23:27:49
Subject: Re: plpgsql and index usage
Previous:From: Tom LaneDate: 2002-12-20 22:33:03
Subject: Re: plpgsql and index usage

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