pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Scott Kirkwood <scottakirkwood@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Caching of Queries
Date: Mon, 27 Sep 2004 10:00:14 -0300
Message-ID: <b3dc5117040927060073ccfa0d@mail.gmail.com> (raw)
In-Reply-To: <1096261381.25688.741.camel@localhost.localdomain>
References: <b3dc511704092212502a2ddb09@mail.gmail.com>
	<BAY9-DAV21Y1SWoBDys0000cef5@hotmail.com>
	<1096261381.25688.741.camel@localhost.localdomain>

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



view thread (72+ messages)  latest in thread

Message-ID: <b3dc5117040927060073ccfa0d@mail.gmail.com>
Permalink:  ../b3dc5117040927060073ccfa0d@mail.gmail.com/
Also on:    postgresql.org/message-id/b3dc5117040927060073ccfa0d@mail.gmail.com

 · 

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: scottakirkwood@gmail.com
  Subject: Re: Caching of Queries
  In-Reply-To: <b3dc5117040927060073ccfa0d@mail.gmail.com>

* 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