Is there a way to find which query is doing large io operations and/or which is using cached data and which is reading from disk. I need to see this on a production server to localize a slow and resource eating query. The pg_statio* tables are very handy, but don't help me at all in finding the most disk intensive query, just the most used table which I already knew.
And besides that, please share your experience on how do you decide which queries to optimize and how to reorganize your database? Is there any tools that you use to profile your database.