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:20:52 |
Message-ID: | 1040422860.1573.77.camel@localhost.localdomain |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2002-12-20 22:33:03 | Re: plpgsql and index usage |
Previous Message | Bruce Momjian | 2002-12-20 22:09:10 | Re: Okay to tighten definition of oprcanhash? |