Обсуждение: how to invalidate a stored procedure's plan?

I have a procedure that queries a table. This should be fast because of an index, but some index bloat has caused the
indexto become expensive, and so the procedure has cached a plan that uses a full table scan. I've since fixed the
indexbloat, but the procedure still seems to be doing full table scans. Is there a non-disruptive way to invalidate the
cachedplan for that procedure globally? 

Ben Chobot <bench@silentmedia.com> writes:
> I have a procedure that queries a table. This should be fast because of an index, but some index bloat has caused the
indexto become expensive, and so the procedure has cached a plan that uses a full table scan. I've since fixed the
indexbloat, but the procedure still seems to be doing full table scans. Is there a non-disruptive way to invalidate the
cachedplan for that procedure globally? 

What PG version?  In 8.3 and up an ANALYZE on any of the tables
mentioned in the problem query should suffice.

            regards, tom lane

On Apr 23, 2010, at 6:00 PM, Tom Lane wrote:

> Ben Chobot <bench@silentmedia.com> writes:
>> I have a procedure that queries a table. This should be fast because of an index, but some index bloat has caused
theindex to become expensive, and so the procedure has cached a plan that uses a full table scan. I've since fixed the
indexbloat, but the procedure still seems to be doing full table scans. Is there a non-disruptive way to invalidate the
cachedplan for that procedure globally? 
>
> What PG version?  In 8.3 and up an ANALYZE on any of the tables
> mentioned in the problem query should suffice.
>
>             regards, tom lane

Heh, woops, sorry, it's 8.4, and yes, a simple table analyze was all I needed. Thanks!