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
>