Re: Avoiding bad prepared-statement plans.

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Fri, Feb 26, 2010 at 11:27 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 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.

> The two issues seem intimately connected to me. Of course, estimates
> can be inaccurate for many reasons, but ONE of those reasons is that
> the planner is optimizing for an unknown parameter value which has
> very different distribution from the actually supplied value.

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? I think the uptake on that would be about nil;
not to mention that it fails to fix several of the most pressing
problems, such as LIKE indexing and partition selection, where all the
distribution information in the world doesn't help. You need the exact
value.

regards, tom lane