To: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
cc: "Ivan Voras" <ivoras@freebsd.org>, pgsql-performance@postgresql.org,
	"lars" <lhofhansl@yahoo.com>
Subject: Re: UPDATEDs slowing SELECTs in a fully cached database 
In-reply-to: <4E1DCE81020000250003F2DC@gw.wicourts.gov> 
References: <4E16479D.3050605@yahoo.com> <4E1A0CBC.6090009@yahoo.com>
	<4E1A31AB.9010201@postnewspapers.com.au>
	<1310359653.80687.YahooMailRC@web121705.mail.ne1.yahoo.com>
	<4E1AEDAB020000250003F1C3@gw.wicourts.gov>
	<4E1B41EA.90104@yahoo.com>
	<4E1B05CC020000250003F1E6@gw.wicourts.gov>
	<CAHyXU0wPhZsQ_pMzfUWW0O=t_+2uwF-CJQNw-nuaxkjbi2zPEQ@mail.gmail.com>
	<4E1B810D.4070404@yahoo.com> <4E1B90AF.1020408@yahoo.com>
	<ivho9v$6dp$1@dough.gmane.org> <4E1C9A05.7060005@yahoo.com>
	<4E1C5563020000250003F252@gw.wicourts.gov>
	<4E1CA8E9.6090108@yahoo.com> <4E1CB56E.7080801@yahoo.com>
	<4E1D697C020000250003F29A@gw.wicourts.gov><4E1D697C020000250003F29A@gw.wicourts.gov>
	<4752.1310570261@sss.pgh.pa.us>
	<4E1DCE81020000250003F2DC@gw.wicourts.gov>
Comments: In-reply-to "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
	message dated "Wed, 13 Jul 2011 16:57:37 -0500"
Date: Wed, 13 Jul 2011 18:21:17 -0400
Message-ID: <11679.1310595677@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Huh?  If there was never an XID, there's no commit WAL record,
>> hence nothing to make asynchronous.
 
> If you look at the RecordTransactionCommit() function in xact.c
> you'll see that's not correct.

Oh, hmmm ... that code was written with the idea that things like
sequence XLOG_SEQ_LOG records ought to be flushed to disk before
reporting commit; otherwise you don't have a guarantee that the same
sequence value couldn't be handed out again after crash/restart,
in a transaction that just does something like
	SELECT nextval('myseq');
without any updates of regular tables.

It seems like we ought to distinguish heap cleanup activities from
user-visible semantics (IOW, users shouldn't care if a HOT cleanup has
to be done over after restart, so if the transaction only wrote such
records there's no need to flush).  This'd require more process-global
state than we keep now, I'm afraid.

Another approach we could take (also nontrivial) is to prevent
select-only queries from doing HOT cleanups.  You said upthread that
there were alleged performance benefits from aggressive cleanup, but
IMO that can charitably be described as unproven.  The real reason it
happens is that we didn't see a simple way for page fetches to know soon
enough whether a tuple update would be likely to happen later, so they
just do cleanups unconditionally.

			regards, tom lane