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

Thanks for the quick responses everyone. It did turn out to be the
commit bit as the table was just loaded (never accessed) and subsequent
SELECTs did not incur any write activity.

As a side note, I saw in the archives a past conversation about adding
an option to disable touching the commit hint bit. There were some
conversations about possible uses for such a feature and I'd like to
propose this as a common one:
-Load a whole bunch of "raw" data into big table
-Munge/Transform data and insert it into existing, normalized schema
-Drop table of "raw" data

In my case, the "raw" data is on the order of hundreds of gigabytes and
the increased write activity is a HUGE penalty. Even with smaller data
sets, this relatively common usage pattern could benefit greatly.

Logan Bowers

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Monday, August 01, 2005 7:09 PM
To: Logan Bowers
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] 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