Re: Avoiding bad prepared-statement plans.

On Sun, Feb 28, 2010 at 2:52 AM, Mark Mielke <mark@mark.mielke.cc> wrote:
> On 02/27/2010 11:20 PM, Craig Ringer wrote:
>>
>> Essentially, you have:
>>
>> 1) People preparing statements to save on parse+plan time; and
>> 2) People preparing statements to get convenenient param placement.
>>
>> I suspect that most of (1) also want (2), but many of (2) don't care much
>> about (1) and are just preparing statements for sql-injection safety (param
>> placement), because they've been told to by someone, because their library
>> does it for them, etc.
>>
>> So: Would it be easier to handle control of replan vs no-replan at PREPARE
>> time? Or would that have very much the same protocol/pl change issues?
>
> I think if SQL hints were sufficient, that clients would only need to remove
> the prepared statement and re-create it whenever required.
>
> It should do the right thing automatically.
>
> I'm convinced that means generic plans are always wrong, and that some
> combination of performing fixed operations in PREPARE and variable
> operations in EXECUTE, combined with a plan caching against the prepared
> statement with criteria to determine whether or not the parameters match the
> assumptions made when creating one of the cached plans. Tom says extracting
> the fixed part of the planning out to PREPARE would be difficult or less
> valuable than I think. And the multi-plan caching with criteria seems to
> have been brought up and not commented on much by several people. So, it
> doesn't look like I will get this unless I learn how to implement it myself
> - which is probably not feasible at this time. :-)
>
> Not getting this, I think I'd be happy if PREPARE/EXECUTE can *easily*
> detect the worst cases (i.e. not slower in the general case), and generic
> plan plus custom plan plus custom execution is still significantly faster
> than generic plan plus generic execution.
>
> Adding SQL to indicate whether it should be re-planned or not is completely
> unappealing. If I could change the code, today, I'd just turn off or choose
> not to use PREPARE/EXECUTE. Today, PREPARE/EXECUTE seems like it should
> always be considered slower unless one can prove it is actually faster in a
> specific case, which is the exact opposite of what people expect.

I don't really understand most of what you're saying here, but there's
definitely some truth to your last sentence.  This has easily got to
be one of the top ten questions on -performance.

...Robert