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. ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
