Received: from localhost (unknown [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id 117742E02D1
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 13 Dec 2007 19:43:43 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024)
	with ESMTP id 70314-05-2
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 13 Dec 2007 19:43:27 -0400 (AST)
Received: from gwmta.wicourts.gov (gwmta.wicourts.gov [165.219.244.99])
	by postgresql.org (Postfix) with ESMTP id 6AA2E2E0051
	for <pgsql-performance@postgresql.org>;
	Thu, 13 Dec 2007 19:43:37 -0400 (AST)
Received: from Courts-MTA by gwmta.wicourts.gov
	with Novell_GroupWise; Thu, 13 Dec 2007 17:43:36 -0600
Message-Id: <47616F3F.EE98.0025.0@wicourts.gov>
X-Mailer: Novell GroupWise Internet Agent 7.0.2 
Date: Thu, 13 Dec 2007 17:43:27 -0600
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: "Simon Riggs" <simon@2ndquadrant.com>
Cc: <pgsql-performance@postgresql.org>
Subject: Re: Heavy write activity on first vacuum of fresh
	TOAST data
References: <4760FF69.EE98.0025.0@wicourts.gov>
	<1197562283.4255.1829.camel@ebony.site>
	<47611B9D.EE98.0025.0@wicourts.gov>
	<1197569564.4255.1855.camel@ebony.site>
	<47614D86.EE98.0025.0@wicourts.gov>
	<1197582010.4255.1912.camel@ebony.site>
In-Reply-To: <1197582010.4255.1912.camel@ebony.site>
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: 200712/205
X-Sequence-Number: 28021

>>> On Thu, Dec 13, 2007 at  3:40 PM, in message
<1197582010.4255.1912.camel@ebony.site>, Simon Riggs <simon@2ndquadrant.com=
>
wrote:=20
> On Thu, 2007-12-13 at 15:19 -0600, Kevin Grittner wrote:
>> >>> On Thu, Dec 13, 2007 at 12:12 PM, in message
>> <1197569564.4255.1855.camel@ebony.site>, Simon Riggs <simon@2ndquadrant.=
com>
>> wrote:=20
>> > On Thu, 2007-12-13 at 11:46 -0600, Kevin Grittner wrote:
>> >> If the hint bit changes are written to the WAL ...
>> >=20
>> > They're not.
>> =20
>> So one would expect a write-intensive initial vacuum after a
>> PITR-style recovery?
>=20
> Very perceptive. I was just thinking about that myself. An interesting
> issue when running with full_page_writes off.
>=20
>> What impact would lack of the hint bits have until a vacuum?
>=20
> Vacuum isn't important here. Its the first idiot to read the data that
> gets hit.
=20
OK, I want to understand this well enough to recognize it when I
see it.  (As always, I appreciate the helpful answers here.)
=20
Assuming no data is toasted, after a bulk INSERT or COPY into the
database, a subsequent SELECT COUNT(*) would figure out the correct
hint bits and rewrite all rows during execution of the SELECT
statement?
=20
The same is true following a PITR-style recovery?
=20
Toasted data would not be rewritten unless accessed (whether that
be for selection criteria, sort order, results, or whatever)?
=20
A database VACUUM is going to run into every page not previously
accessed and make all hint bits correct?
=20
Would a VACUUM FREEZE of a bulk-loaded table do one write for both
the hint bits and the transaction ID?  (I know that hackers
generally prefer that people leave the transaction IDs unfrozen
for a long time to aid in debugging problems, but that seems less
useful in a large table which has just been bulk-loaded, true?)
=20
-Kevin
=20