Re: Avoiding bad prepared-statement plans.

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.

Cheers,
mark