pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Bruce Momjian <pgman@candle.pha.pa.us>
To: Scott Kirkwood <scottakirkwood@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Caching of Queries
Date: Mon, 27 Sep 2004 10:17:56 -0400 (EDT)
Message-ID: <200409271417.i8REHuc28214@candle.pha.pa.us> (raw)
In-Reply-To: <b3dc5117040927060073ccfa0d@mail.gmail.com>


Added to TODO:

* Consider automatic caching of queries at various levels:
        o Parsed query tree
        o Query execute plan
        o Query results


---------------------------------------------------------------------------

Scott Kirkwood wrote:
> On Mon, 27 Sep 2004 15:03:01 +1000, Neil Conway <neilc@samurai.com> wrote:
> > I think the conclusion of past discussions about this feature is that
> > it's a bad idea. Last I checked, MySQL has to clear the *entire* query
> > cache when a single DML statement modifying the table in question is
> > issued. Not to mention that the feature is broken for non-deterministic
> > queries (like now(), ORDER BY random(), or nextval('some_seq'), and so
> > on). That makes the feature close to useless for a lot of situations,
> > albeit not every situation.
> 
> I think it's important to demark three levels of possible caching:
> 1) Caching of the parsed query tree
> 2) Caching of the query execute plan
> 3) Caching of the query results
> 
> I think caching the query results (3) is pretty dangerous and
> difficult to do correctly.
> 
> Caching of the the execute plan (2) is not dangerous but may actually
> execute more slowly by caching a bad plan (i.e. a plan not suited to
> the current data)
> 
> Caching of the query tree (1) to me has very little downsides (except
> extra coding). But may not have a lot of win either, depending how
> much time/resources are required to parse the SQL and lookup the
> objects in the system tables (something I've never gotten a
> satisfactory answer about). Also, some of the query cache would have
> to be cleared when DDL statements are performed.
> 
> -Scott
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073



view thread (72+ messages)  latest in thread

Message-ID: <200409271417.i8REHuc28214@candle.pha.pa.us>
Permalink:  ../200409271417.i8REHuc28214@candle.pha.pa.us/
Also on:    postgresql.org/message-id/200409271417.i8REHuc28214@candle.pha.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: pgman@candle.pha.pa.us, scottakirkwood@gmail.com
  Subject: Re: Caching of Queries
  In-Reply-To: <200409271417.i8REHuc28214@candle.pha.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox