Re: Restructuring plancache.c API

On 2010-11-11 23:21, Tom Lane wrote:
> I've been thinking about supporting automatic replan of cached plans
> using specific parameter values, as has been discussed several times,
> at greatest length in this thread:
> http://archives.postgresql.org/pgsql-hackers/2010-02/msg00607.php
..
> I want to rearrange it so there's
> an explicit notion of three levels of cacheable object:
>
> 1. Raw parse tree + source string. These obviously never change.
In the context of cached plans and specific parameter values, a idea for
the future might be to also consider a cached plan for planning of
simple queries. A way to do this is by regarding all constants in a
simple query as parameters, and look for a cached plan for that
parameterized query. To lower the chance for choosing a bad plan for the
actual parameter values, a cached plan could also store the actual
parameter values used during planning. (where planning was done with
constants, not parameters, this would require back replacing the actual
values as constants in the parameterized query). Based on exact match on
the raw parse tree of the parameterized source tree and neighbourhood of
the actual parameter values of the cached and current query, a plan
could be chosen or not. If replanning was chosen, this new plan could
also be stored as new cached plan of the same query but with different
parameter values.

It would require one more level in the plan cache
1 raw parse tree of parameterized query
2 one or more "source string + actual parameter values" (these were the
replaced constants)
then for each entry in level 2 the remaining levels.

regards,
Yeb Havinga