2

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

1
  • turn on track_io_timing and run explain (analyze, buffers)
    – user1822
    Nov 11, 2019 at 6:46

1 Answer 1

4

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.

4
  • Can I use a view instead of an extension?
    – Islacine
    Mar 30, 2020 at 13:12
  • The extension creates the view. But the important part is the data collection, which is triggered by adding the module to shared_preload_libraries. Mar 30, 2020 at 13:58
  • I am using Postgres-XL, I cannot create an extension there and I am looking for more information like Hit ratio,... I am trying so hard to make pg_stat_statements works but I am getting things like HINT: No function matches the given name and argument types. You might need to add explicit type casts , the 3 lines are added in the coordinator postgres.conf
    – Islacine
    Mar 30, 2020 at 14:26
  • I have no idea if pg_stat_statements works on Postgres-XL. Mar 30, 2020 at 14:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.