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

Re: Caching of Queries

From: Scott Kirkwood <scottakirkwood(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Caching of Queries
Date: 2004-09-27 13:00:14
Message-ID: b3dc5117040927060073ccfa0d@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: Mitch PirtleDate: 2004-09-27 13:29:58
Subject: Re: Caching of Queries
Previous:From: Greg StarkDate: 2004-09-27 12:59:27
Subject: Re: Caching of Queries

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