Re: I/O on select count(*)

"Jan de Visser" <jdevisser(at)digitalfairway(dot)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