Thread: TODO: Cache most recent query plan

In TODO is:CACHE:    * Cache most recent query plan(s) [prepare]
!--> I'm working on this. 

TODO.detail (Jan's idea): 
    I can think of the following construct:
       PREPARE optimizable-statement;
   That one will run parser/rewrite/planner, create a new memory   context with a unique identifier and  saves  the
querytree's  and  plan's  in  it.   Parameter values are identified by the   usual $n notation. The command returns the
identifier.
       EXECUTE QUERY identifier [value [, ...]];
   then get's back the prepared plan and querytree  by  the  id,   creates  an  executor  context  with  the given
valuesin the   parameter array and calls ExecutorRun() for them.
 
    .... etc (cut).                    Karel

----------------------------------------------------------------------
Karel Zak <zakkr@zf.jcu.cz>              http://home.zf.jcu.cz/~zakkr/

Docs:        http://docs.linux.cz                    (big docs archive)    
Kim Project: http://home.zf.jcu.cz/~zakkr/kim/        (process manager)
FTP:         ftp://ftp2.zf.jcu.cz/users/zakkr/        (C/ncurses/PgSQL)
-----------------------------------------------------------------------

Karel Zak - Zakkr wrote:
> 
>  In TODO is:
> 
>  CACHE:
>      * 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.

-- 
Chris Bitmead
mailto:chris@bitmead.com

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.

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

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

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