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 A0928D1B278 for ; Sat, 5 Jun 2004 01:17:43 -0300 (ADT) 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 84757-01 for ; Sat, 5 Jun 2004 04:17:38 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id A3A5AD1B18C for ; Sat, 5 Jun 2004 01:17:36 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i554HZZp014803; Sat, 5 Jun 2004 00:17:35 -0400 (EDT) To: "Michael Nonemacher" Cc: pgsql-performance@postgresql.org Subject: Re: postgres performance: comparing 2 data centers In-reply-to: References: Comments: In-reply-to "Michael Nonemacher" message dated "Fri, 04 Jun 2004 17:07:52 -0500" Date: Sat, 05 Jun 2004 00:17:35 -0400 Message-ID: <14802.1086409055@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/43 X-Sequence-Number: 7101 "Michael Nonemacher" writes: > It seems like the statistics are wildly different depending on whether > the last operation on the table was a 'vacuum analyze' or an 'analyze'. > Vacuum or vacuum-analyze puts the correct number (~500k) in > pg_class.reltuples, but analyze puts 7000 in pg_class.reltuples. Okay, this is a known issue: in 7.4 and earlier, ANALYZE is easily fooled as to the total number of rows in the table. It samples the initial portion of the table and assumes that the density of live rows per page in that section is representative of the rest of the table. Evidently that assumption is way off for your table. There's an improved sampling algorithm in CVS tip that we hope will avoid this error in 7.5 and beyond, but the immediate problem for you is what to do in 7.4. I'd suggest either VACUUM FULL or CLUSTER to clean out the existing dead space; then you should look into whether you need to increase your vacuum frequency and/or FSM settings to keep it from getting into this state again. Ideally the average dead space per page *should* be consistent over the whole table, and the fact that it isn't suggests strongly that you've got space-management issues to deal with. regards, tom lane