quiet? Re: The query cache - first snapshot (long) - Mailing list pgsql-hackers

Still *quiet* for this theme? I output it two weeks ago and I haven't 
still some reaction. I can stop work on this if it is not wanted and not 
interesting...
                    Karel


On Wed, 19 Jul 2000, Karel Zak wrote:

>  The Query Cache and new SPI description
>  =======================================
> 
>      Note: cache is based on new memory design.
>    
>  Implementation
>  ~~~~~~~~~~~~~~
>    The qCache allows to save queryTree and queryPlan. Available are two space 
>    for data caching. 
>   
>          LOCAL - data are cached in backend non-shared memory and data aren't
>                 available in other backends.                  
>   
>          SHARE - data are cached in backend shared memory and data are 
>                  visible in all backends.
>   
>          Because size of share memory pool is limited and it's set during
>          postmaster start, the qCache must remove all old planns if pool is 
>          full. You can mark each entry as "REMOVEABLE" or "NOTREMOVEABLE". 
>   
>          The removeable entry is removed if pool is full and entry is last 
>          in list that keep track usage of entry.   
>   
>          A not-removeable entry must be removed via qCache_Remove() or 
>          the other routines. The qCache not remove this entry itself.
>   
>    All records in the qCache are cached in the hash table under some key. The
>    qCache knows two alternate of key --- "KEY_STRING" and "KEY_BINARY". A
>    key must be always less or equal "QCACHE_HASH_KEYSIZE" (128b)  
>   
>    The qCache API not allows to access to shared memory, all cached planns  
>    that API returns are copy to CurrentMemoryContext or to defined context. 
>    All (qCache_ ) routines lock shmem itself (exception is 
>    qCache_RemoveOldest_ShareRemoveAble()).
> 
>         - for locking is used spin lock.
> 
>    Memory management
>    ~~~~~~~~~~~~~~~~~
>    The qCache use for qCache's shared pool organized via memory contexts 
>    independent on standard aset/mcxt, but use compatible API --- it allows 
>    to use standard palloc() (it is very needful for basic plan-tree operations, 
>    an example for copyObject()). The qCache memory management is very simular 
>    to current aset.c code. It is chunked blocks too, but the block is smaller 
>    - 1024b.
> 
>    The number of blocks is available set in postmaster 'argv' via option
>    '-Z'.
> 
>    For planns storing is used separate MemoryContext for each plan, it 
>    is good idea (Hiroshi's ?), bucause create new context is simple and 
>    inexpensive and allows easy destroy (free) cached plan. This method is 
>    used in my SPI overhaul instead TopMemoryContext feeding.
> 
>    Postmaster
>    ~~~~~~~~~~
>    The query cache memory is init during potmaster startup. The size of
>    query cache pool is set via '-Z <number-of-blocks>' switch --- default 
>    is 100 blocks where 1 block = 1024b, it is sufficient for 20-30 cached
>    planns. One query needs somewhere 3-10 blocks, for example query like
> 
>         PREPARE sel AS SELECT * FROM pg_class;
> 
>    needs 10Kb, because table pg_class has very much columns. 
>    -- 
> 
>    Note: for development I add SQL function: "SELECT qcache_state();",
>          this routine show usage of qCache.
> 
>  SPI
>  ~~~
>     I a little overwrite SPI save plan method and remove TopMemoryContext
>     "feeding" (already discussed).
> 
>     Standard SPI:
> 
>         SPI_saveplan() - save each plan to separate standard memory context.
> 
>         SPI_freeplan() - free plan.
> 
>     By key SPI:
> 
>         It is SPI interface for query cache and allows save planns to SHARED
>         or LOCAL cache 'by' arbitrary key (string or binary). Routines:
> 
>         SPI_saveplan_bykey()        - save plan to query cache
> 
>         SPI_freeplan_bykey()        - remove plan from query cache
> 
>         SPI_fetchplan_bykey()        - fetch plan saved in query cache
> 
>         SPI_execp_bykey()        - execute (via SPI) plan saved in query
>                                   cache 
> 
>         - now, users can write functions that save planns to shared memory 
>         and planns are visible in all backend and are persistent arcoss 
>         connection. 
> 
>         Example:
>         ~~~~~~~
>         /* ----------
>          * Save/exec query from shared cache via string key
>          * ----------
>          */
>         int        keySize = 0;        
>                 flag = SPI_BYKEY_SHARE | SPI_BYKEY_STRING;
>         char        *key = "my unique key";
>         
>         res = SPI_execp_bykey(values, nulls, tcount, key, flag, keySize);
>         
>         if (res == SPI_ERROR_PLANNOTFOUND) 
>         {
>                 /* --- not plan in cache - must create it --- */
>                 
>                 void *plan;
> 
>                 plan = SPI_prepare(querystr, valnum, valtypes);
>                 SPI_saveplan_bykey(plan, key, keySize, flag);
>                 
>                 res = SPI_execute(plan, values, Nulls, tcount);
>         }
>         
>         elog(NOTICE, "Processed: %d", SPI_processed);
> 
> 
>  PREPARE/EXECUTE
>  ~~~~~~~~~~~~~~~
>    * Syntax:
>         
>         PREPARE <name> AS <query> 
>                 [ USING type, ... typeN ] 
>                 [ NOSHARE | SHARE | GLOBAL ]
>         
>         EXECUTE <name> 
>                 [ INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table ]
>                 [ USING val, ... valN ]
>                        [ NOSHARE | SHARE | GLOBAL ]
> 
>         DEALLOCATE PREPARE 
>                 [ <name> [ NOSHARE | SHARE | GLOBAL ]]
>                 [ ALL | ALL INTERNAL ]
> 
> 
>      I know that it is a little out of SQL92... (use CREATE/DROP PLAN instead
>      this?) --- what mean SQL standard guru?
> 
>    * Where:
>  
>         NOSHARE --- cached in local backend query cache - not accessable
>                     from the others backends and not is persisten a across
>                     conection.
> 
>         SHARE   --- cached in shared query cache and accessable from
>                     all backends which work over same database.
> 
>         GLOBAL  --- cached in shared query cache and accessable from
>                     all backends and all databases. 
> 
>         - default is 'SHARE'
>    
>         Deallocate:
>                 
>                 ALL          --- deallocate all users's plans
> 
>                 ALL INTERNAL --- deallocate all internal plans, like planns
>                                  cached via SPI. It is needful if user
>                                  alter/drop table ...etc.
> 
>    * Parameters:
>         
>         "USING" part in the prepare statement is for datetype setting for
>         paremeters in the query. For example:
> 
>         PREPARE sel AS SELECT * FROM pg_class WHERE relname ~~ $1 USING text;
> 
>         EXECUTE sel USING 'pg%';
>         
> 
>    * Limitation:
>         
>         - prepare/execute allow use full statement of SELECT/INSERT/DELETE/
>           UPDATE. 
>         - possible is use union, subselects, limit, ofset, select-into
> 
> 
>  Performance:
>  ~~~~~~~~~~~
>  * the SPI
> 
>         - I for my tests a little change RI triggers to use SPI by_key API
>         and save planns to shared qCache instead to internal RI hash table.
> 
>         The RI use very simple (for parsing) queries and qCache interest is 
>         not visible. It's better if backend very often startup and RI check 
>         always same tables. In this situation speed go up --- 10-12%. 
>         (This snapshot not include this RI change.)
> 
>         But all depend on how much complicate for parser is query in 
>         trigger.
> 
>  * PREPARE/EXECUTE
>         
>         - For tests I use query that not use some table (the executor is 
>         in boredom state), but is difficult for the parser. An example:
> 
>         SELECT 'a text ' || (10*10+(100^2))::text || ' next text ' || cast
>         (date_part('year', timestamp 'now') AS text );
>                   
>         - (10000 * this query):
> 
>         standard select:        54 sec
>         via prepare/execute:     4 sec   (93% better)
> 
>         IMHO it is nod bad.
>  
>         - For standard query like:
> 
>         SELECT u.usename, r.relname FROM pg_class r, pg_user u WHERE 
>         r.relowner = u.usesysid;
> 
>         it is with PREPARE/EXECUTE 10-20% faster.
> 
> 
>  I will *very glad* if someone try and test patch; some discussion is wanted 
> too.
> 
>  Thanks.
>         
>                              Karel
> 
> PS. Excuse me, my English is poor and this text is long --- it is not good 
>     combination...
>         
>