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

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

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