Re: Queries causing highest I/O load since pg_stat_reset?

> 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(at)gmail(dot)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(dot)Carneholm(at)wirelesscar(dot)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 ;)