Re: Avoiding bad prepared-statement plans.

On 02/26/2010 11:27 AM, Tom Lane wrote:
> Also, I think there is a lot of confusion here over two different
> issues: generic plan versus parameter-specific plan, and bad planner
> estimates leading to a wrong plan choice. While the latter is certainly
> an issue sometimes, there is no reason to believe that it affects
> prepared statements worse than non-prepared ones. So I think that
> designing a fix for prepared statements on the assumption that you can't
> trust the planner's estimates is solving the wrong problem.
>

Just to point out that I agree, and as per my original post, I think the
only time prepared statements should be re-planned for the statistics
case, is after 'analyze' has run. That sounds like a quicker solution,
and a much smaller gain. After 'analyze' of an object, invalidate all
cached plans for prepared statements that rely on that object and
require a re-plan. I doubt this will help me or many others very often.
It's something that should be done some day, but I don't recall ever
concluding that a performance problem I was experiencing was related to
using prepared statements too long. Also, the client is able to figure
this out. The client can choose to free prepared statements after 1
minute or 1000 calls. It's not really a problem.

It also has nothing to do with trust of the planner's estimates. Given
the same criteria, the planner should come up with the same best plan
most or all of the time. Trial and error planning, with the exception of
hugely complicated plans that cannot be produced in a finite time frame,
does not appeal to me at all. I do trust the planner's estimates.

The issue of specific parameter is the one I think most of us would
benefit from, and I think the most effective benefit is to not create
generic plans. I would prefer a prepare with specific plan and re-plan
when the specific plan does not apply, over generic plan, every time.
This has nothing to do with "time to prepare" or a ratio of "time to
prepare" vs "time to execute", or plans that are expected to take some
time to execute. The fact that I can run a PREPARE/EXECUTE, and SELECT,
and with only one invocation see a difference of over 100X shows that
generic plans is just not the right approach. It works according to
spec, but it is not practical under the current model.

Generic plans is the problem. My post was to bring attention to this, as
I see most comments focusing on an assumption that generic plans provide
value, and specific plans should only be used when generic plans are
expected to take a while to execute. It's walking around the problem
that the idea of a generic plan is just wrong. The only time a generic
plan is right, is when the specific plan would result in the same.

Cheers,
mark