> 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:[EMAIL PROTECTED] 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 <[EMAIL PROTECTED]> 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 ;) ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
