From: | "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com> |
---|---|
To: | Joseph Shraibman <jks(at)selectacast(dot)net> |
Cc: | <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: cache table |
Date: | 2004-05-04 13:52:12 |
Message-ID: | Pine.LNX.4.33.0405040748311.30999-100000@css120.ihs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Joseph Shraibman | 2004-05-04 15:27:53 | Re: cache table |
Previous Message | Aaron Werman | 2004-05-04 12:06:22 | Re: linux distro for better pg performance |