From: Aaron Werman <awerman@hotmail.com>
To: Jeff <threshar@torgo.978.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Caching of Queries
Date: Mon, 27 Sep 2004 16:11:53 -0400
Message-ID: <BAY9-DAV22UD5euwdUT000835f3@hotmail.com> (raw)
References: <b3dc511704092212502a2ddb09@mail.gmail.com>
<BAY9-DAV21Y1SWoBDys0000cef5@hotmail.com>
<1096261381.25688.741.camel@localhost.localdomain>
<18766.1096262336@sss.pgh.pa.us>
<330532b6040927062938c1d596@mail.gmail.com>
<ABA05F02-10B2-11D9-99D5-000D9366F0C4@torgo.978.org>
The context of the discussion was a hack to speed queries against static
tables, so MVCC is not relevent. As soon as any work unit against a
referenced table commits, the cache is invalid, and in fact the table
shouldn't be a candidate for this caching for a while. In fact, this cache
would reduce some the MVCC 'select count(*) from us_states' type of horrors.
(The attraction of a server side cache is obviously that it could *with no
server or app changes* dramatically improve performance. A materialized view
is a specialized denormalization-ish mechanism to optimize a category of
queries and requires the DBA to sweat the details. It is very hard to cache
things stochastically without writing a server. Trigger managed extracts
won't help you execute 1,000 programs issuing the query "select sec_level
from sec where division=23" each second or a big table loaded monthly.)
----- Original Message -----
From: "Jeff" <threshar@torgo.978.org>
To: "Mitch Pirtle" <mitch.pirtle@gmail.com>
Cc: "Aaron Werman" <awerman@hotmail.com>; "Scott Kirkwood"
<scottakirkwood@gmail.com>; "Neil Conway" <neilc@samurai.com>;
<pgsql-performance@postgresql.org>; "Tom Lane" <tgl@sss.pgh.pa.us>
Sent: Monday, September 27, 2004 2:25 PM
Subject: Re: [PERFORM] Caching of Queries
> [ discussion of server side result caching ]
>
> and lets not forget PG's major fork it will throw into things: MVCC
> The results of query A may hold true for txn 1, but not txn 2 and so on
> .
> That would have to be taken into account as well and would greatly
> complicate things.
>
> It is always possible to do a "poor man"'s query cache with triggers..
> which would just leave you with basically a materialized view.
>
> --
> Jeff Trout <jeff@jefftrout.com>
> http://www.jefftrout.com/
> http://www.stuarthamm.net/
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
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: awerman@hotmail.com, threshar@torgo.978.org
Subject: Re: Caching of Queries
In-Reply-To: <BAY9-DAV22UD5euwdUT000835f3@hotmail.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