Re: Avoiding bad prepared-statement plans.

On Mon, Feb 15, 2010 at 2:11 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> Pavel Stehule wrote:
>> > The problem that we face is that we don't have any very good way to tell
>> > whether a fresh planning attempt is likely to yield a plan significantly
>> > better than the generic plan. ?I can think of some heuristics --- for
>> > example if the query contains LIKE with a parameterized pattern or a
>> > partitioned table --- but that doesn't seem like a particularly nice
>> > road to travel.
>> >
>> > A possible scheme is to try it and keep track of whether we ever
>> > actually do get a better plan. ?If, after N attempts, none of the custom
>> > plans were ever more than X% cheaper than the generic one, then give up
>> > and stop attempting to produce custom plans. ?Tuning the variables might
>> > be challenging though.
>>
>> I afraid so every heuristic is bad. Problem is identification of bad
>> generic plan. And nobody ensure, so non generic plan will be better
>> than generic. Still I thing we need some way for lazy prepared
>> statements - plan is generated everytime with known parameters.
>
> Yea, this opens a whole host of questions for me:
>
> 1. Why do we only do bind-level planning for anonymous wire-level queries?
>
> 2. I realize we did anonymous-only because that was the only way we had
> in the protocol to _signal_ bind-time planning, but didn't we think of
> this when we were implementing the wire-level protocol?
>
> 3. Do we have no place to add this cleanly without a protocol version
> bump?
>
> 4. Why don't we just always do planning at first bind time?  When is
> that worse than using generic values?
>
> 5. Why have we not added an option for SQL-level prepare to do this?
>
> 6. When do our generic columns costs significantly worse than having
> specific constants?  I assume unique columns are fine with generic
> constants.
>
> 7. Why is there no option to do parameterized-queries which replan every
> time?
>
> This just seems like an area that has been neglected, or maybe I am
> missing something and our current setup is acceptable.

No, our current setup is not acceptable, and your questions are all
right on target. I have been hoping that someone would take an
interest in this problem for years. An option to replan on every
execution would be a very, very fine thing. IMHO, there should also
be a way to signal to PL/pgsql that you want this behavior for a
particular query, short of wrapping it using EXECUTE, which is clunky
and also forces a re-parse on every execution.

...Robert