To: "Jan de Visser" <jdevisser@digitalfairway.com>
cc: "Greg Smith" <gsmith@gregsmith.com>,
	"Alvaro Herrera" <alvherre@commandprompt.com>,
	pgsql-performance@postgresql.org
Subject: Re: I/O on select count(*) 
In-reply-to: <1159c1e90805141938j5f85b490rb5c1cfdf9e428bce@mail.gmail.com> 
References: <920841.71690.qm@web44808.mail.sp1.yahoo.com>
	<482B1D5F.EE98.0025.0@wicourts.gov>
	<Pine.GSO.4.64.0805142133250.27290@westnet.com>
	<20080515020553.GN9838@alvh.no-ip.org>
	<Pine.GSO.4.64.0805142211300.5617@westnet.com>
	<1159c1e90805141938j5f85b490rb5c1cfdf9e428bce@mail.gmail.com>
Comments: In-reply-to "Jan de Visser" <jdevisser@digitalfairway.com>
	message dated "Wed, 14 May 2008 22:38:08 -0400"
Date: Thu, 15 May 2008 03:02:19 -0400
Message-ID: <28324.1210834939@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"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