Re: cache table

On Mon, 3 May 2004, Joseph Shraibman wrote:

> I have a big table with some int fields.  I frequently need to do 
> queries like:
> 
> SELECT if2, count(*) FROM table WHERE if1 = 20 GROUP BY if2;
> 
> The problem is that this is slow and frequently requires a seqscan. I'd 
> like to cache the results in a second table and update the counts with 
> triggers, but this would a) require another UPDATE for each 
> INSERT/UPDATE which would slow down adding and updating of data and b) 
> produce a large amount of dead rows for vacuum to clear out.
> 
> It would also be nice if this small table could be locked into the pg 
> cache somehow.  It doesn't need to store the data on disk because the 
> counts can be generated from scratch?

I think you might be interested in materialized views.  You could create 
this as a materialized view which should be very fast to just select * 
from.

While materialized views aren't a standard part of PostgreSQL just yet, 
there is a working implementation available from Jonathan Gardner at:

http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html

It's all implemented with plpgsql and is quite interesting to read 
through.  IT has a nice tutorial methodology to it.