Re: Plan invalidation vs. unnamed prepared statements

On Tue, 2007-03-06 at 12:22 -0500, Tom Lane wrote:

> A. Just accept the extra overhead, thereby preserving the current
> behavior of unnamed statements, and gaining the benefit that plan
> invalidation will work correctly in the few cases where an unnamed
> statement's plan lasts long enough to need replanning.

With connection pooling, multiple sessions will execute each statement.
If we check the cache each time this does seem more expensive for each
individual session, but we should gain synergy from other similar
sessions. Taken across multiple sessions, A will be a win because it
will reduce planning overhead by ~99%.

> C. Don't store the unnamed statement in the plan cache. To make sure
> it's not used anymore when the plan might be stale, don't analyze or
> plan at Parse-message time, but postpone *all* that work until Bind;
> and always discard the plan after Execute. We could still do "raw
> parsing" at Parse time, since that's independent of database state,
> but all but the most trivial syntactic errors would now occur at Bind
> not Parse time, as well as the majority of the time expenditure.

ISTM there will be some cases where the current behaviour will not be
maintained if we implement A exactly. One thing I've not seen mentioned
is the effect of constants on various plans.

The current system plans at Bind time so it can make longer term
decisions based upon the values of initial parameters. So I'd say we
need to check the cache at Parse time, but if we do need to plan,
continue to do this at Bind time (and so don't write to plan cache until
that point). That might mean we end up giving some of our benefit away
if multiple sessions all concurrently plan a previously unplanned query.
That does seem less likely and in any case much better than taking a
step backwards in query planning of parameterised queries.

Also, some of those plans are only currently possible with actual
constants, specifically predicate proving for partial indexes and
constraint exclusion. Parameter to constant folding may change the plan
completely and make it non-reusable anyhow. How would we cope with that
type of prepared query with plan inval?

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com