DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:subject
	:content-type:content-transfer-encoding;
	b=I0sZOgogbH0UGO8oxJaSP1SKmGO9KPvW8MDgsJ3vaPXPF1h47lLawJc13IiC2rlt7k
	j0punzeUSzFgb/0AKzV/+kIrUqcFUpRul3CRXRjLpIqqkJR0jMMjBxErVKxkoq1tUMPb
	ZUOX9U/QKUX2KvqX6hZaiOUlQvVcjPLriC+9k=
Message-ID: <48551DB0.8050507@gmail.com>
Date: Sun, 15 Jun 2008 15:48:32 +0200
From: Uwe Bartels <uwe.bartels@gmail.com>
User-Agent: Thunderbird 2.0.0.12 (Macintosh/20080213)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: how to find the sql using most of the i/o in an oltp system
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi,

I read a lot about postgres tuning and did some of it. But one of the 
things, when you start tuning a system that is completely new to you, is 
checking which sql statement(s) cost most of the resources.

cpu instensive sql seems easy to find.
But how do I find i/o intensive sql as fast as possible?

Tuning a sql statements I'm familiar with. Finding a sql statement which 
takes too long due to i/o is probably easy as well. But how about 
statements that take about 100 ms, that read a lot and that are executed 
several times per second?

To ask the same question just different - Is there a possibility to 
check how many pages/kB a sql reads from shared buffer and how many 
pages/kB from disk?
Is there a possibility to read or create historical records about how 
often, how fast one sql is run and how many reads it used from shared 
buffers, from disk an so on.


Best regards,
Uwe