Re: caching query results

> Ed Loehr wrote:
>
> > What would persuasive numbers look like?
> >
> > As a novice, I'd guess key questions would seem to be...
> >
> > How often is a query run in which the results are identical to previous
> > invocations of that query?
> >
> > Typically send/recv rates vs. typical query planning/exec time?
>
> So wouldn't you get most of what you want if you could store a query plan?

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:

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.

These keys could be managed in a shared LRU table, and if the
same key appears a number of times (0-n), it's entire
querytree + plan (after planning) will be saved into the
shared mem. 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.

Postmaster startup options for tuning that come into mind
then are querycache memsize, minimum # of appearence before
caching, maximum lifetime or # usage of a plan and the like.
So setting the memsize to zero will completely disable and
fallback to current behavior.

After that, the entire parsing is still done for every query
(so application level controlled query cacheing is still
another thing to care for). We would only be able to skip the
planner/optimizer step. 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.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck(at)debis(dot)com (Jan Wieck) #