Re: plpgsql and index usage

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Ryan Mahoney <ryan(at)flowlabs(dot)com>
Cc: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: plpgsql and index usage
Date: 2002-12-20 22:33:03
Message-ID: 25426.1040423583@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Ryan Mahoney 2002-12-20 22:35:19 plpgsql and index usage
Previous Message Ryan Mahoney 2002-12-20 22:20:52 plpgsql and index usage