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