Queries causing highest I/O load since pg_stat_reset?

On 11/14/05, Mikael Carneholm <Mikael.Carneholm@wirelesscar.com> wrote:

Hi,

is it possible to retrieve a list of queries that has caused the highest i/o load? Something like

(1) select pg_stat_reset();
(2) run some queries for a while
(3) list the queries that caused the highest i/o since step (1)

?

/Mikael

select * from pg_stat_activity;

--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

select * from pg_stat_activity;

Nope, doesn't cut it. First, it only gives you the current_query (and current_query is just that - the current query for *active* connections, not historical sessions). Second, it doesn't provide any info on blocks read/written. And pg_stat_foo_tables gives you neither blocks read/written nor query strings, and pg_stat_database and pg_statio_foo_tables only give you the *total* number of blocks read (not connected to query strings).

I'd like to be able to do something like this:

-- list the 10 highest i/o stressing queries since last pg_stat_reset()
select s.query_string, so.blks_read
from sometable s, someothertable so
where s.foo = so.foo
order by so.blks_read desc
limit 10;

/Mikael

-----Original Message-----
From: Jaime Casanova [mailto:systemguards@gmail.com]
Sent: den 14 november 2005 21:08
To: Mikael Carneholm
Cc: 'Pgsql-General (E-mail)
Subject: Re: [GENERAL] Queries causing highest I/O load since
pg_stat_reset?

On 11/14/05, Mikael Carneholm <Mikael.Carneholm@wirelesscar.com> wrote:

Hi,

is it possible to retrieve a list of queries that has caused the highest i/o load? Something like

(1) select pg_stat_reset();
(2) run some queries for a while
(3) list the queries that caused the highest i/o since step (1)

?

/Mikael

select * from pg_stat_activity;

--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

I don't think there's any way to do this currently. Maybe if you wrote
an external program that polled pg_stat_activity and then correlated
procpid to the output of top, etc.

On Mon, Nov 14, 2005 at 08:59:19PM +0100, Mikael Carneholm wrote:

Hi,

is it possible to retrieve a list of queries that has caused the highest i/o load? Something like

(1) select pg_stat_reset();
(2) run some queries for a while
(3) list the queries that caused the highest i/o since step (1)

?

/Mikael

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461