Re: caching query results

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