Re: plpgsql and index usage

Ryan Mahoney <ryan(at)flowlabs(dot)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