How to find postgresql query which is consuming the most I/O

Question

Lets say I have an iotop view like this:

How to find the SELECT which is hitting the hard disk so heavy here?

Answer 1

Preparation:

Edit postgresql.conf:

• Add pg_stat_statements to shared_preload_libraries and restart PostgreSQL.

• Set track_io_timing = on.

Now let the workload run for a while.

Then find your I/O hog:

SELECT query
FROM pg_stat_statements
ORDER BY blk_read_time + blk_write_time DESC
LIMIT 1;