To: Mark Mielke <mark@mark.mielke.cc>
cc: Conor Walsh <ctw@adverb.ly>, jd@commandprompt.com,
	Robert Haas <robertmhaas@gmail.com>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Does auto-analyze work on dirty writes? (was: Re: [HACKERS] Slow
	count(*) again...)
In-reply-to: <4D4B7317.3070803@mark.mielke.cc> 
References: <201102012247.p11Ml6u02682@momjian.us>
	<4D48914B.4050403@dunslane.net> <4D489AD1.1010105@vmsinfo.com>
	<AANLkTiknZi97dDwm7sHqy0EL7tpNEP9sY5uPV6AN+q=u@mail.gmail.com>
	<4D499E55.4020107@vmsinfo.com>
	<AANLkTimo2J-BagObs_V1bGaHOO9qL+kTF6s6-wZ=M3KW@mail.gmail.com>
	<AANLkTin7A_Q=Yc9Wf_5=POp2mVqmaiwqUDsFMLG68H45@mail.gmail.com>
	<4D4A7B69.2020704@gmail.com>
	<alpine.DEB.2.00.1102030208440.8162@asgard.lang.hm>
	<AANLkTi=Thet0VZ-X6b7tek+iSLVkE27JjiKrB5D8U0Jd@mail.gmail.com>
	<alpine.DEB.2.00.1102031243190.10088@asgard.lang.hm>
	<AANLkTikmChsPODbUD4V-gQ8k2yYvvjJY6b9K_29+k7nu@mail.gmail.com>
	<alpine.DEB.2.00.1102031637480.30983@asgard.lang.hm>
	<AANLkTi=Kx6rRFs3Kz2m32nLKwLGv0Ee87UF8ngKhppgM@mail.gmail.com>
	<alpine.DEB.2.00.1102031732470.30983@asgard.lang.hm>
	<AANLkTikN14hsVVVLJ=BQdqEcpmzkLLqSbEpqx-th4Ohx@mail.gmail.com>
	<AANLkTi=XUprgXqV84iY1sukC_6Pos+DfVPFveaK1CSHf@mail.gmail.com>
	<1296786810.18411.102.camel@jd-desktop>
	<AANLkTikR77m7ttGb5Y1y7HWa0Os95TQXj_+wgqa9Xkfm@mail.gmail.co!
	m > <4D4B7317.3070803@mark.mielke.cc>
Comments: In-reply-to Mark Mielke <mark@mark.mielke.cc>
	message dated "Thu, 03 Feb 2011 22:31:35 -0500"
Date: Fri, 04 Feb 2011 10:41:04 -0500
Message-ID: <22836.1296834064@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Mark Mielke <mark@mark.mielke.cc> writes:
> My understanding is:

> 1) Background daemon wakes up and checks whether a number of changes 
> have happened to the database, irrelevant of transaction boundaries.

> 2) Background daemon analyzes a percentage of rows in the database for 
> statistical data, irrelevant of row visibility.

> 3) Analyze is important for both visible rows and invisible rows, as 
> plan execution is impacted by invisible rows. As long as they are part 
> of the table, they may impact the queries performed against the table.

> 4) It doesn't matter if the invisible rows are invisible because they 
> are not yet committed, or because they are not yet vacuumed.

> Would somebody in the know please confirm the above understanding for my 
> own piece of mind?

No.

1. Autovacuum fires when the stats collector's insert/update/delete
counts have reached appropriate thresholds.  Those counts are
accumulated from messages sent by backends at transaction commit or
rollback, so they take no account of what's been done by transactions
still in progress.

2. Only live rows are included in the stats computed by ANALYZE.
(IIRC it uses SnapshotNow to decide whether rows are live.)

Although the stats collector does track an estimate of the number of
dead rows for the benefit of autovacuum, this isn't used by planning.
Table bloat is accounted for only in terms of growth of the physical
size of the table in blocks.

			regards, tom lane