DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=from:to:subject:date:user-agent:cc:references:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:message-id:sender;
	b=sdj02HJNJfJHxTowxwCJqCmn92AbgaWk77bFwqSX5TuhQQvWZXTxhtjbWT0xKWGr1u8gQ1FiSotsh41UeMV792y9Zen9NQTQ/UulZvlYBLq2SLC1LXjOWIl/GixNVS6CWK//sBfZ+P4mpkm2RX89SfsoP5TwOhl9ttS6fTrdH78=
From: Jan de Visser <jdevisser@digitalfairway.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: I/O on select count(*)
Date: Thu, 15 May 2008 09:15:40 -0400
User-Agent: KMail/1.9.9
Cc: "Greg Smith" <gsmith@gregsmith.com>,
	"Alvaro Herrera" <alvherre@commandprompt.com>,
	pgsql-performance@postgresql.org
References: <920841.71690.qm@web44808.mail.sp1.yahoo.com>
	<1159c1e90805141938j5f85b490rb5c1cfdf9e428bce@mail.gmail.com>
	<28324.1210834939@sss.pgh.pa.us>
In-Reply-To: <28324.1210834939@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200805150915.40793.jdevisser@digitalfairway.com>

On Thursday 15 May 2008 03:02:19 Tom Lane wrote:
> "Jan de Visser" <jdevisser@digitalfairway.com> writes:
> > Obviously, this issue is tied to the slow count(*) one, as I found out
> > the hard way. Consider the following scenario:
> > * Insert row
> > * Update that row a couple of times
> > * Rinse and repeat many times
> >
> > Now somewhere during that cycle, do a select count(*) just to see
> > where you are. You will be appalled by how slow that is, due to not
> > only the usual 'slow count(*)' reasons. This whole hint bit business
> > makes it even worse, as demonstrated by the fact that running a vacuum
> > before the count(*) makes the latter noticably faster.
>
> Uh, well, you can't blame that entirely on hint-bit updates.  The vacuum
> has simply *removed* two-thirds of the rows in the system, resulting in
> a large drop in the number of rows that the select even has to look at.
>
> It's certainly true that hint-bit updates cost something, but
> quantifying how much isn't easy.  The off-the-cuff answer is to do the
> select count(*) twice and see how much cheaper the second one is.  But
> there are two big holes in that answer: the first is the possible cache
> effects from having already read in the pages, and the second is that
> the follow-up scan gets to avoid the visits to pg_clog that the first
> scan had to make (which after all is the point of the hint bits).
>
> I don't know any easy way to disambiguate the three effects that are at
> work here.  But blaming it all on the costs of writing out hint-bit
> updates is wrong.
>
> 			regards, tom lane

True. But it still contributes to the fact that queries sometimes behave in a 
non-deterministic way, which IMHO is the major annoyance when starting to 
work with pgsql. And contrary to other causes (vacuum, checkpoints) this is 
woefully underdocumented.

jan