X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id B0F9C44E82E for ; Mon, 27 Dec 2004 19:27:22 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 20443-02 for ; Mon, 27 Dec 2004 19:27:13 +0000 (GMT) Received: from ct.radiology.uiowa.edu (ct.radiology.uiowa.edu [129.255.60.186]) by svr1.postgresql.org (Postfix) with ESMTP id 3B0E144E839 for ; Mon, 27 Dec 2004 19:27:12 +0000 (GMT) Received: from [192.168.1.11] (12-217-241-0.client.mchsi.com [12.217.241.0]) by ct.radiology.uiowa.edu (8.11.6/8.11.6) with ESMTP id iBRJR9331672; Mon, 27 Dec 2004 13:27:09 -0600 Message-ID: <41D06223.9060500@arbash-meinel.com> Date: Mon, 27 Dec 2004 13:27:31 -0600 From: John A Meinel User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Pallav Kalva Cc: pgsql-performance@postgresql.org Subject: Re: Wrong Stats and Poor Performance References: <41D059EB.9090201@deg.cc> In-Reply-To: <41D059EB.9090201@deg.cc> X-Enigmail-Version: 0.89.5.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enigB875B1535F936458369CDD6F" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200412/300 X-Sequence-Number: 9675 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enigB875B1535F936458369CDD6F Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Pallav Kalva wrote: > Hi Everybody. > > I have a table in my production database which gets updated > regularly and the stats on this table in pg_class are totally wrong. > I used to run vacuumdb on the whole database daily once and when i > posted the same problem of wrong stats in the pg_class most of them > from this list and also from postgres docs suggested me to run the > "vacuum analyze" more frequently on this table. > > I had a setup a cronjob couple of weeks ago to run vacuum analyze > every 3 hours on this table and still my stats are totally wrong. This > is affecting the performance of the queries running on this table very > badly. > How can i fix this problem ? or is this the standard postgres > behaviour ? > > Here are the stats from the problem table on my production database > > relpages | reltuples > ----------+------------- > 168730 | 2.19598e+06 > > If i rebuild the same table on dev db and check the stats they are > totally different, I was hoping that there would be some difference in > the stats from the production db stats but not at this extent, as you > can see below there is a huge difference in the stats. > > relpages | reltuples > ----------+----------- > 25230 | 341155 > > > Thanks! > Pallav > What version of the database? As I recall, there are versions which suffer from index bloat if there is a large amount of turnover on the table. I believe VACUUM FULL ANALYZE helps with this. As does increasing the max_fsm_pages (after a vacuum full verbose the last couple of lines can give you an indication of how big max_fsm_pages might need to be.) Vacuum full does some locking, which means you don't want to do it all the time, but if you can do it on the weekend, or maybe evenings or something it might fix the problem. I don't know if you can recover without a vacuum full, but there might also be something about rebuild index, or maybe dropping and re-creating the index. John =:-> --------------enigB875B1535F936458369CDD6F Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.6 (Cygwin) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFB0GIjJdeBCYSNAAMRAvqfAKCU+xkvO9JCwlJRBVErj0Bhq6drsACeLtv+ zUhfZHJhlbBzhP1i8lKrKo8= =QU/V -----END PGP SIGNATURE----- --------------enigB875B1535F936458369CDD6F--