X-Original-To: pgsql-performance@postgresql.org Received: from houston.familyhealth.com.au (unknown [203.59.48.253]) by postgresql.org (Postfix) with ESMTP id 8EE98475461 for ; Mon, 17 Mar 2003 21:44:00 -0500 (EST) Received: (from root@localhost) by houston.familyhealth.com.au (8.11.6/8.11.6) id h2I2i3s99417 for pgsql-performance@postgresql.org; Tue, 18 Mar 2003 10:44:03 +0800 (WST) (envelope-from chriskl@familyhealth.com.au) Received: from mariner (mariner.internal [192.168.0.101]) by houston.familyhealth.com.au (8.11.6/8.9.3) with SMTP id h2I2hv799191; Tue, 18 Mar 2003 10:43:57 +0800 (WST) Message-ID: <088d01c2ecf8$3bf742e0$6500a8c0@fhp.internal> From: "Christopher Kings-Lynne" To: "Logan Bowers" , "Tom Lane" Cc: "Neil Conway" , , References: <20030316060125.GD19570@vt.edu> <3E741B93.1090203@joeconway.com> <20030316075206.GE19570@vt.edu> <20030317061011.GH23355@perrin.int.nxad.com> <33137.130.15.224.150.1047881939.squirrel@mailbox.samurai.com> <495.1047882860@sss.pgh.pa.us> Subject: Re: postgresql meltdown on PlanetMath.org Date: Tue, 18 Mar 2003 10:44:01 +0800 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1106 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 X-scanner: scanned by Inflex 0.1.5c - (http://www.inflex.co.za/) X-Archive-Number: 200303/131 X-Sequence-Number: 1429 > I'm running this db on a celeron 450Mhz with 256MB RAM and a 60GB HDD > (7200 rpm). For the most part I have the db running "well enough." Over > time however, I find that performance degrades, the count(*) above is an > example of a command that does worse over time. It gets run once an hour > for stats collection. When I first migrated the db to v7.3.1 it would > take about 5-10 seconds (which it is close to now after a VACUUM FULL) but > after a few weeks it would take over a minute of really intense HDD > activity. Also of note is that when I first loaded the data it would > cache very well with the query taking maybe taking 15 seconds if I had > just started the db after reboot, but when it was in its "slow" state > repeating the query didn't noticably use the disk less (nor did it take > less time). To speed up your COUNT(*), how about doing this: Create a separate table to hold a single integer. Add a trigger after insert on your table to increment the counter in the other table Add a trigger after delete on your table to decrement the counter in the other table. That way you always have an O(1) count... Chris