Search code examples
postgresqlioquery-performance

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

Lets say I have an iotop view like this:

I/O top

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

Solution

  • 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;