PostgreSQL – How to Get Detailed Execution Plan Information

deadlockexecution-planoptimizationperformancepostgresqlquery-performance

I want to get some physical features about a SQL query execution with PostgreSQL, like

  • Read/write page accesses
  • I/O operations
  • Response time
  • Sequential I/O throughput rate
  • Deadlocks
  • Pages scanned Queries ratio
  • Hit ratio, Pages read, Logging, Ratio of using indexes Rows selected , Average sort time, Number of locks held, Number of sorts Throughput..

and more, depends on what's available

Best Answer

You should use pg_stat_statements.

Modify postgresql.conf as follows:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
track_io_timing = on

and restart PostgreSQL.

Then

CREATE EXTENSION pg_stat_statements;

and find all the data you want in the view pg_stat_statements.

Related Question