Re: caching query results - Mailing list pgsql-hackers

On Mon, 3 Apr 2000, Jan Wieck wrote:

>     This  should wait until after the proposed querytree overhaul
>     we have in mind. I already discussed it with  Tom  Lane.  The
>     idea goes like this:
This is very interesting discussion for me, I have prepared code for 
7.1? with PREPARE/EXECUTE commands and SPI changes for query cache. This
features allow users define cache entry (via SPI or PREPARE). But it is 
already discussed before now. A validity of plans is a problem.

>     After  the  overhaul,  the rewriter is a very simple and fast
>     step. So we could hook into  the  rewriter,  who  builds  for
>     EVERY  query  kinda  key  based  on  the nodes, relations and
>     functions that appear in the querytree.

It is good idea. What exactly is a key? If I good understand this key
is for query identification only. Right?

>     These keys could be managed in a shared LRU table, and if the

My current code is based on HASH table with keys and query&plan is 
saved in special for a plan created MemoryContext (it is good for
a example SPI_freeplan()).  

>     same  key  appears  a  number  of  times  (0-n),  it's entire
>     querytree + plan (after planning)  will  be  saved  into  the
>     shared  mem.  

Here I not understend. Why is here any time checking? 

>     At  a subsequent occurence, the querycache will
>     look  closer  onto  the  two  trees,  if  they   are   really
>     identically  WRT  all the nodes. If only constant values have
>     changed, the already known plan could be reused.

IMHO users can use PREPARE / EXECUTE for same query. Suggested idea is 
really good if this query cache will in shared memory and more backends 
can use it.

Good. It is solution for 'known-query' and allow it skip any steps in the
query path. But we still not have any idea for cached plans validity. What
if user changes oid for any operator, drop column (etc)?  

Or I anything overlook?

>     We would only be able to skip the
>     planner/optimizer step. 

Instead a PREPARE/EXECUTE which allow skip all in the query path (only
executor is called. But it works for user defined query not for every
query.

>     The question therefore is how much of
>     the entire processing time  for  a  query  can  be  saved  if
>     replacing  this  step by some shared memory overhead. I'm not
>     sure if this is worth the entire efford at all,  and  we  can
>     only  judge after the querytree overhaul is done. Then again,
>     improving the query optimizer directly, so he's able to  make
>     better join order decisions faster, might be the way to go.

Is really sure that this will faster? (it must create key for nodes, 
search same query in any table (cache), copy new query&plan to cache 
..etc.)
                        Karel