Subject: Re: Heavy write activity on first vacuum of fresh TOAST
	data
From: Simon Riggs <simon@2ndquadrant.com>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-performance@postgresql.org
In-Reply-To: <47610BF3.EE98.0025.0@wicourts.gov>
References: <4760FF69.EE98.0025.0@wicourts.gov>
	<1197562283.4255.1829.camel@ebony.site>
	<476108F2.EE98.0025.0@wicourts.gov>
	<476108F2.EE98.0025.0@wicourts.gov> <13267.1197563721@sss.pgh.pa.us>
	<47610BF3.EE98.0025.0@wicourts.gov>
Content-Type: text/plain
Date: Thu, 13 Dec 2007 17:00:42 +0000
Message-Id: <1197565242.4255.1853.camel@ebony.site>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Thu, 2007-12-13 at 10:39 -0600, Kevin Grittner wrote:
> >>> On Thu, Dec 13, 2007 at 10:35 AM, in message <13267.1197563721@sss.pgh.pa.us>,
> Tom Lane <tgl@sss.pgh.pa.us> wrote: 
> > "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> >> ... although to a naive user it's not clear what
> >> is known at vacuum time that the INSERT into the empty table
> >> couldn't have inferred.
> > 
> > The fact that the INSERT actually committed.
>  
> Fair enough.  I suppose that the possibility that of access before
> the commit would preclude any optimization that would assume the
> commit is more likely than a rollback, and do the extra work only in
> the unusual case?

No chance. There's an optimization of COPY I've not got around to as
yet, but nothing straightforward we can do with the normal case.

We might be able to have bgwriter set hint bits on dirty blocks, but the
success of that would depend upon the transit time of blocks through the
cache, i.e. it might be totally ineffective. So might be just overhead
for the bgwriter and worse, could divert bgwriter attention away from
what its supposed to be doing. That's a lot of work to fiddle with the
knobs to improve things and there's higher things on the list AFAICS.

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com