Re: SELECT count(*) Generating Lots of Write Activity

"Logan Bowers" <logan(at)zillow(dot)com> writes:
> I'm potentially having a strange performance problem. I have a BIG
> table: ~100M, ~1KB rows. I do a SELECT count(*) from it (I know it will
> be slow) and as I watch procinfo on my DB server I see a huge amount of
> write activity. Thus,

> 1) Why does this statement generate any writes at all?

It could be that it's evicting unrelated dirty pages from cache
(although PG 8.0 is supposed to try to avoid doing that during a simple
seqscan). Another theory is that the table has a whole lot of
recently-changed rows, and the writes are a side effect of the SELECT
setting commit hint bits to tell future transactions what it found out
about the commit status of the rows.

I dunno what procinfo is --- personally I would use strace and see
exactly which file(s) the database processes are issuing writes against.
Also check whether a second SELECT against the same table continues
to issue writes...

regards, tom lane