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@xxxxxxxxxxxxx] Sent: Monday, August 01, 2005 7:09 PM To: Logan Bowers Cc: pgsql-general@xxxxxxxxxxxxxx Subject: Re: SELECT count(*) Generating Lots of Write Activity "Logan Bowers" <logan@xxxxxxxxxx> 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 ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
