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

On Mon, 2008-04-14 at 16:54 +0300, Heikki Linnakangas wrote:
> 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%".

Another point is when the cardinality distribution of some key's values
is very skewed, with some values very frequent and the majority of
values being unique. There you could check the stats at execution time
just for deciding to go for the low cardinality plan or the high one...

> 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.

... or plan the query with the actual parameter value you get, and also
record the range of the parameter values you expect the plan to be valid
for. If at execution time the parameter happens to be out of that range,
replan, and possibly add new sublpan covering the extra range. This
could still work with prepared queries (where you don't get any
parameter values to start with) by estimating the most probable
parameter range (whatever that could mean), and planning for that.

Cheers,
Csaba.