Re: Avoiding bad prepared-statement plans.

On 02/26/2010 01:59 PM, Tom Lane wrote:
>> ... 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.
>>
> I think that's a significant overstatement. There are a large number
> of cases where a custom plan isn't worth it, even if it doesn't generate
> exactly the same plan.
>

There must be some way to lift the cost of planning out of the plan
enumeration and selection phase, such that only plan enumeration and
selection is run at execute time. In most cases, plan enumeration and
selection, provided that all data required to make these decisions is
all cached in data structures ready to go, should be very fast? Right?
Wrong? If right, my original post suggested that prepare should do the
parts of planning which are fixed, and not change based on the input
parameters, while execute should do the dynamic parts that would change
based on the input parameters.

By "not worth it", do you mean development effort or run time?

For development effort, it would definitely be worth it in the grand
scheme of things, but perhaps not worth it to specific individuals.

For run time, I've having trouble seeing the situation where it would
not be worth it. In the case that the resulting plan is the same (custom
vs generic) there should be no cost. In the case that the plan is
different, I think the difference proves that it is worth it. The case
where it wouldn't be worth it would be if a prepared statement was
called many times with many different parameters, and each set of
parameters required a re-plan - but my experience in this regard tells
me that the current model is to choose a sub-optimal plan, and the
entire query will run much slower than the planning time, on every
execute. We wouldn't be having this discussion if generic plans were
considered adequate. So, I feel that it is worth it in this case as well.

It's the development effort that is the problem. I can't do it, and I
can't make you do it. If you say "too hard", there isn't anything I can
do about it. :-)

Cheers,
mark