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