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

Re: Caching of Queries

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Scott Kirkwood <scottakirkwood(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Caching of Queries
Date: 2004-09-27 14:17:56
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
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(at)samurai(dot)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                        |
  pgman(at)candle(dot)pha(dot)pa(dot)us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

In response to

pgsql-performance by date

Next:From: Harald FuchsDate: 2004-09-27 16:15:35
Subject: Re: Caching of Queries
Previous:From: Mitch PirtleDate: 2004-09-27 13:29:58
Subject: Re: Caching of Queries

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