Did the patch that allows multiple seqscans to piggyback on each other make it into 8.1? It might help in this situation.
BTW, if a query requires loading more than a few percent of an index PostgreSQL will usually go with a sequential scan instead. You should check explain/explain analyze on your queries and see what's actually happening. If you've got stats turned on you can also look at pg_stat_user_indexes to get a better idea of what indexes are and aren't being used. On Thu, Oct 27, 2005 at 03:41:10PM -0500, PostgreSQL wrote: > Thank each of you for your replies. I'm just beginning to understand the > scope of my opportunities. > > Someone (I apologize, I forgot who) recently posted this query: > SELECT oid::regclass, reltuples, relpages > FROM pg_class > ORDER BY 3 DESC > > Though the application is a relatively low-volume TP system, it is > structured a lot like a data warehouse with one primary table that > everything else hangs off. What the query above shows is that my largest > table, at 34 million rows, takes almost 1.4 million pages or 10+ Gb if my > math is good. The same table has 14 indexes, totaling another 12Gb. All > this is running on a box with 4Gb of memory. > > So what I believe I see happening is that almost every query is clearing out > memory to load the particular index it needs. Hence my "first queries are > the fastest" observation at the beginning of this thread. > > There are certainly design improvements to be done, but I've already started > the process of getting the memory increased on our production db server. We > are btw running 8.1 beta 3. > > ""Steinar H. Gunderson"" <[EMAIL PROTECTED]> wrote in message > news:[EMAIL PROTECTED] > > On Mon, Oct 24, 2005 at 11:09:55AM -0400, Alex Turner wrote: > >> Just to play devils advocate here for as second, but if we have an > >> algorithm > >> that is substational better than just plain old LRU, which is what I > >> believe > >> the kernel is going to use to cache pages (I'm no kernel hacker), then > >> why > >> don't we apply that and have a significantly larger page cache a la > >> Oracle? > > > > There have (AFAIK) been reports of setting huge amounts of shared_buffers > > (close to the total amount of RAM) performing much better in 8.1 than in > > earlier versions, so this might actually be okay these days. > > > > I haven't heard of anybody reporting increase setting such values, though. > > > > /* Steinar */ > > -- > > Homepage: http://www.sesse.net/ > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 2: Don't 'kill -9' the postmaster > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly > -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
