Re: Avoiding bad prepared-statement plans.

On 26/02/2010 11:40 AM, Tom Lane wrote:

> But putting support for a per-query level
> of control into the protocol (and then every client library) as well as
> every PL is going to be painful to implement, and even more painful to
> use.

You mean something like 'EXECUTE REPLAN' and protocol/PL-level equivalents?

That's what people on -GENERAL often seem to need. A way, for a
particular query, to say "replan this every time, because the stats are
very signifcant here".

> I still like the idea of automatically replanning with the known
> parameter values, and noting whether the result plan was estimated to be
> noticeably cheaper than the generic plan, and giving up on generating
> custom plans if we didn't observe any such win over N tries.

That risks making prepared statements less efficient when re-used
between 2 and N times. People would end up asking for a 'no replan'
knob, which I'm not sure is any improvement over a 'force replan' knob.

OTOH, a GUC to turn that off would be (IMO) fairly harmless - if you
know you don't have any problematic prepared queries, turn it off to
save some cycles.

Another thought: I wonder if this should be viewed from one step back.
Many of these issues come from people who don't actually want prepared
statements for performance, they're just using them to get convienient
and secure parameter placement and server-side caching of the query text.

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?

--
Craig Ringer