Re: Cached Query Plans (was: global prepared statements)

Csaba Nagy wrote:
> If cached plans would be implemented, the dependence on parameter values
> could be solved too: use special "fork" nodes in the plan which execute
> different sub-plans depending on special parameter values/ranges,
> possibly looking up the stats at runtime, so that the plan is in a
> compiled state with the "decision points" wired in.

That's an idea I've been thinking about for a long time, but never got
around implementing. I see that as a completely orthogonal feature to
the server-side shared plan cache, though. There's plenty of scenarios,
like with client-side prepared statement cache, where it would be useful.

Figuring out the optimal "decision points" is hard, and potentially very
expensive. There is one pretty simple scenario though: enabling the use
of partial indexes, preparing one plan where a partial index can be
used, and another one where it can't. Another such case is "col LIKE ?"
queries, where ? is actually a prefix query, "foo%".

As an optimization, we could decide the decision points on the prepare
message, and delay actually planning the queries until they're needed.
That way we wouldn't waste time planning queries for combinations of
parameters that are never used.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com