pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Greg Stark <gsstark@mit.edu>
To: Neil Conway <neilc@samurai.com>
Cc: Aaron Werman <awerman@hotmail.com>
Cc: Scott Kirkwood <scottakirkwood@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Caching of Queries
Date: 27 Sep 2004 08:59:27 -0400
Message-ID: <87fz5398b4.fsf@stark.xeocode.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>

Neil Conway <neilc@samurai.com> writes:

> 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.

Well there's no reason to assume that just because other implementations are
weak that postgres would have to slavishly copy them.

I've often wondered whether it would make sense to cache the intermediate
results in queries. Any time there's a Materialize node, the database is
storing all those data somewhere; it could note the plan and parameters that
generated the data and reuse them if it sees the same plan and parameters --
including keeping track of whether the source tables have changed or whether
there were any non-immutable functions of course.

This could be quite helpful as people often do a series of queries on the same
basic data. Things like calculating the total number of records matching the
user's query then fetching only the records that fit on the current page. Or
fetching records for a report then having to calculate subtotals and totals
for that same report. Or even generating multiple reports breaking down the
same data along different axes.

-- 
greg




view thread (72+ messages)  latest in thread

Message-ID: <87fz5398b4.fsf@stark.xeocode.com>
Permalink:  ../87fz5398b4.fsf@stark.xeocode.com/
Also on:    postgresql.org/message-id/87fz5398b4.fsf@stark.xeocode.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: gsstark@mit.edu, neilc@samurai.com, awerman@hotmail.com, scottakirkwood@gmail.com
  Subject: Re: Caching of Queries
  In-Reply-To: <87fz5398b4.fsf@stark.xeocode.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