I'd like to look at it from the object level and see how much I/O is being done on specific table or index and then check which sessions are responsible for that.
also, what's the catalog table you would recommend me to use if I want to see I/O activity on an object regardless of the session?
Using the catalog tables, is there any way to correlate session id/user id to which object (i.e. tables, indexes etc) it access and much how disk reads or I/O wait has been done against the objects. in general, I'd like to see which objects are being accessed by which user and the time/amount of I/O wait/reads.
On recent Linux systems, the iotop utility is handy to figure out which individual users are doing lots of I/O. There are some cases where the user doing the I/O and the one who caused the I/O are different, which includes things from synchronized scans to background writer writes. But for the most part that utility gives a useful view into per-user I/O.
And if you're on Solaris you can extract of a lot of this data with custom DTrace scripting.
I have a rough plan for directly instrumenting more of this information from within the database, more like what Oracle does here. But that's going to take months of development time, and I'm not sure the PostgreSQL core will even accept the overhead it would add in all cases. If we could get one Oracle user who's on the fence over a PostgreSQL conversion to throw a small portion of the money they'd save toward that project, I'm sure I could get it developed. It's just that nobody has been interested enough in such a thing to sponsor it so far.
-- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support greg@2ndQuadrant.com www.2ndQuadrant.us