Re: Avoiding bad prepared-statement plans.

On 02/26/2010 03:11 PM, Yeb Havinga wrote:
> Tom Lane wrote:
>> Right, but if the parameter is unknown then its distribution is also
>> unknown. In any case that's just nitpicking, because the solution is
>> to create a custom plan for the specific value supplied. Or are you
>> suggesting that we should create a way for users to say "here is the
>> expected distribution of this parameter", and then try to fold that into
>> the planner estimates?
> Or instead of letting users give the distribution, gather it
> automatically in some plan statistics catalog? I suspect in most
> applications queries stay the same for months and maybe years, so
> after some number of iterations it is possible to have decent call
> statistics / parameter distributions. Maybe the the parameter value
> distribution could even be annotated with actual cached plans.

The problem with the last - actual cached plans - is that it implies the
other aspect I have been suggesting: In order to have a custom cached
plan, the primary model must be to use custom plans. If PREPARE/EXECUTE
uses generic plans normally, than the only cached plans available will
be generic plans.

Cheers,
mark