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.