Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 69CBA2E2D94 for ; Fri, 16 Nov 2007 11:57:02 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 16155-06 for ; Fri, 16 Nov 2007 11:56:50 -0400 (AST) Received: from mail.libertyrms.com (vgateway.libertyrms.info [207.219.45.62]) by postgresql.org (Postfix) with ESMTP id D1D742E2DBE for ; Fri, 16 Nov 2007 11:56:56 -0400 (AST) Received: from dba5.int.libertyrms.com ([10.1.3.44]) by mail.libertyrms.com with esmtp (Exim 4.22) id 1It3Ym-0003l9-Kp; Fri, 16 Nov 2007 10:56:48 -0500 Subject: Re: Curious about dead rows. From: Brad Nicholson To: Tom Lane Cc: Russell Smith , Jean-David Beyer , pgsql-performance@postgresql.org In-Reply-To: <16999.1195080384@sss.pgh.pa.us> References: <4735DBDB.5050208@verizon.net> <16627.1194714491@sss.pgh.pa.us> <4735FA9F.1080805@verizon.net> <473A5CC0.3060003@verizon.net> <473AE63D.7090305@verizon.net> <20071114152249.GA16041@crankycanuck.ca> <473B2A1F.30804@verizon.net> <473B6C9D.1020804@pws.com.au> <16999.1195080384@sss.pgh.pa.us> Content-Type: text/plain Date: Fri, 16 Nov 2007 10:56:48 -0500 Message-Id: <1195228608.8966.364.camel@bnicholson-desktop> Mime-Version: 1.0 X-Mailer: Evolution 2.10.1 Content-Transfer-Encoding: 7bit X-SA-Exim-Mail-From: bnichols@ca.afilias.info X-SA-Exim-Scanned: No; SAEximRunCond expanded to false X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/243 X-Sequence-Number: 27658 On Wed, 2007-11-14 at 17:46 -0500, Tom Lane wrote: > Russell Smith writes: > > It is possible that analyze is not getting the number of dead rows right? > > Hah, I think you are on to something. ANALYZE is telling the truth > about how many "dead" rows it saw, but its notion of "dead" is "not good > according to SnapshotNow". Thus, rows inserted by a not-yet-committed > transaction would be counted as dead. So if these are background > auto-analyzes being done in parallel with inserting transactions that > run for awhile, seeing a few not-yet-committed rows would be > unsurprising. > > I wonder if that is worth fixing? I'm not especially concerned about > the cosmetic aspect of it, but if we mistakenly launch an autovacuum > on the strength of an inflated estimate of dead rows, that could be > costly. Sounds to me like that could result in autovacuum kicking off while doing large data loads. This sounds suspiciously like problem someone on -novice was having - tripping over a windows autovac bug while doing a data load http://archives.postgresql.org/pgsql-novice/2007-11/msg00025.php -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp.