Re: [HACKERS] TODO: Cache most recent query plan - Mailing list pgsql-hackers

On Wed, 16 Feb 2000, Tom Lane wrote:

> Chris <chris@bitmead.com> writes:
> >> * Cache most recent query plan(s) [prepare]
> 
> > I havn't been following what this is about, but
> > any implementation of caching query plans should
> > be careful about pg_class.relhasindex and 
> > pg_class.relhassubclass, otherwise reuse of
> > query plans could give incorrect results, _maybe_,
> > depending on what you are planning here.

Now, I have implemented parser part for PREPARE:
"PREPARE queryname AS SELECT * FROM aaa WHERE b = $1 WITH TYPE int4"

this allow use $1..$n values and set types of these values. (Yes, I not
sure if all keywords are right, but change it is easy..)

The PREPARE is CMD_UTILITY and plan for prepared query is create in 
command/prepare.c (it is easy and not needs changes in standard "the 
path of query". 

Hmm, how cache it, it is a good question. If I good understand Jan's TODO 
item, we not have (for PREPARE) plan-cache as across transaction/start-stop
persisten plans (example cache it to any relation).

> Well, of course the cached plan would only be good as long as you
> weren't changing the database schema underneath it.  I'm not sure
My idea (in current time) is write PREPARE as simple, no-longer-valid, *user 
controllable* cache, user problem is if he changes his tables (?).    

And about plan cache implementation; I want use hash table (hash_create ..etc)
system and as hash key use 'queryname'. I not sure how memory-context
use for this cache (or create new portal..?) I see Jan's FK implementation,
he uses SPI memory context - it not bad. Comments, ideas?

> how far the system ought to go to prevent the user from continuing
> to use a no-longer-valid plan ... exact detection of trouble seems
> impractical, but I'm not thrilled with a "let the programmer beware"
> approach either.
And what if user has PREPAREd any plans and he changes DB schema drop
all prepared plans. (You change DB schema..well, your caches with PREPAREd
plans go to .... /dev/null).

Or re-do the plan as you say. 

> Also, assuming that we do have some trouble detection mechanism, should
> we reject subsequent attempts to use the cached plan, or automatically
> re-do the plan on next use?  If we kept around source or querytree form
> of the original query, it ought to be possible to re-make the plan.
> This would let us adopt a fairly simple trouble-detection mechanism that
> would err in the direction of re-planning too much; say just replan on
> any relcache flush for the relevant tables & indices.  (If we're going
> to raise an error, that test would be much too prone to raise errors
> unnecessarily.)
> 
> This seems closely related to Jan's TODO item about recompiling rules
> when the DB schema changes, too.
> 
>             regards, tom lane
                        Karel