Re: Caching of Queries

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(at)torgo(dot)978(dot)org>
To: "Mitch Pirtle" <mitch(dot)pirtle(at)gmail(dot)com>
Cc: "Aaron Werman" <awerman(at)hotmail(dot)com>; "Scott Kirkwood"
<scottakirkwood(at)gmail(dot)com>; "Neil Conway" <neilc(at)samurai(dot)com>;
<pgsql-performance(at)postgresql(dot)org>; "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)jefftrout(dot)com>
> http://www.jefftrout.com/
> http://www.stuarthamm.net/
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>