Received: from localhost (unknown [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 227DB2E2368
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Fri, 16 Nov 2007 18:13:04 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 24281-05 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Fri, 16 Nov 2007 18:12:46 -0400 (AST)
Received: from gwmta.wicourts.gov (gwmta.wicourts.gov [165.219.244.99])
	by postgresql.org (Postfix) with ESMTP id 0BD9D2E2D82
	for <pgsql-performance@postgresql.org>;
	Fri, 16 Nov 2007 18:12:59 -0400 (AST)
Received: from Courts-MTA by gwmta.wicourts.gov
	with Novell_GroupWise; Fri, 16 Nov 2007 16:12:58 -0600
Message-Id: <473DC181.EE98.0025.0@wicourts.gov>
X-Mailer: Novell GroupWise Internet Agent 7.0.2 
Date: Fri, 16 Nov 2007 16:12:49 -0600
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: "Brad Nicholson" <bnichols@ca.afilias.info>,
	"Merlin Moncure" <mmoncure@gmail.com>
Cc: <pgsql-performance@postgresql.org>, "Russell Smith" <mr-russ@pws.com.au>,
	"Tom Lane" <tgl@sss.pgh.pa.us>, "Jean-David Beyer" <jeandavid8@verizon.net>
Subject: Re: Curious about dead rows.
References: <4735DBDB.5050208@verizon.net>
	<b42b73150711131628q1469df3el6f557e10de07a05@mail.gmail.com>
	<473A5CC0.3060003@verizon.net>
	<b42b73150711131936l65b97b9ya7953383673b2710@mail.gmail.com>
	<473AE63D.7090305@verizon.net> <20071114152249.GA16041@crankycanuck.ca>
	<473B2A1F.30804@verizon.net> <473B6C9D.1020804@pws.com.au>
	<16999.1195080384@sss.pgh.pa.us>
	<1195228608.8966.364.camel@bnicholson-desktop>
	<b42b73150711161401p13e93e4dn19bc8388a2da9208@mail.gmail.com>
In-Reply-To: <b42b73150711161401p13e93e4dn19bc8388a2da9208@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200711/253
X-Sequence-Number: 27668

>>> On Fri, Nov 16, 2007 at  4:01 PM, in message
<b42b73150711161401p13e93e4dn19bc8388a2da9208@mail.gmail.com>, "Merlin =
Moncure"
<mmoncure@gmail.com> wrote:=20
> On Nov 16, 2007 10:56 AM, Brad Nicholson <bnichols@ca.afilias.info> =
wrote:
>> On Wed, 2007-11-14 at 17:46 -0500, Tom Lane wrote:
>> > Russell Smith <mr-russ@pws.com.au> 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
>=20
> I am almost 100% I've seen this behavior in the field...
=20
I know I've seen bulk loads go significantly faster with autovacuum
turned off.  It always seemed like a bigger difference than what the
ANALYZE would cause.  I bet this explains it.
=20
-Kevin
=20