Skip site navigation (1) Skip section navigation (2)

Re: caching query results

From: wieck(at)debis(dot)com (Jan Wieck)
To: Adriaan Joubert <a(dot)joubert(at)albourne(dot)com>
Cc: pghackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: caching query results
Date: 2000-04-03 17:07:07
Message-ID: m12cAJf-0003kGC@orion.SAPserv.Hamburg.dsh.de (view raw or flat)
Thread:
Lists: pgsql-hackers
> 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) #



Responses

pgsql-hackers by date

Next:From: Adriaan JoubertDate: 2000-04-03 17:36:48
Subject: Re: BIT datatype (Fixed)
Previous:From: Wenjin ZhengDate: 2000-04-03 15:56:27
Subject: RE: slow join on postgresql6.5

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group