PostgreSQL EXTENSION pg_stat_statements
Zunächst in der postgresql.conf den Parameter
shared_preload_libraries = 'pg_stat_statements'Danach den Server restarten und den View mit dem Statement aktivieren.
CREATE EXTENSION pg_stat_statements;Jetzt können die Daten ausgewertet werden.
SELECT pg_stat_statements.userid,
pg_stat_statements.dbid,
pg_stat_statements.toplevel,
pg_stat_statements.queryid,
pg_stat_statements.query,
pg_stat_statements.plans,
pg_stat_statements.total_plan_time,
pg_stat_statements.min_plan_time,
pg_stat_statements.max_plan_time,
pg_stat_statements.mean_plan_time,
pg_stat_statements.stddev_plan_time,
pg_stat_statements.calls,
pg_stat_statements.total_exec_time,
pg_stat_statements.min_exec_time,
pg_stat_statements.max_exec_time,
pg_stat_statements.mean_exec_time,
pg_stat_statements.stddev_exec_time,
pg_stat_statements.rows,
pg_stat_statements.shared_blks_hit,
pg_stat_statements.shared_blks_read,
pg_stat_statements.shared_blks_dirtied,
pg_stat_statements.shared_blks_written,
pg_stat_statements.local_blks_hit,
pg_stat_statements.local_blks_read,
pg_stat_statements.local_blks_dirtied,
pg_stat_statements.local_blks_written,
pg_stat_statements.temp_blks_read,
pg_stat_statements.temp_blks_written,
pg_stat_statements.blk_read_time,
pg_stat_statements.blk_write_time,
pg_stat_statements.wal_records,
pg_stat_statements.wal_fpi,
pg_stat_statements.wal_bytes
FROM pg_stat_statements(true) pg_stat_statements(userid, dbid, toplevel, queryid, query, plans, total_plan_time, min_plan_time, max_plan_time, mean_plan_time, stddev_plan_time, calls, total_exec_time, min_exec_time, max_exec_time, mean_exec_time, stddev_exec_time, rows, shared_blks_hit, shared_blks_read, shared_blks_dirtied, shared_blks_written, local_blks_hit, local_blks_read, local_blks_dirtied, local_blks_written, temp_blks_read, temp_blks_written, blk_read_time, blk_write_time, wal_records, wal_fpi, wal_bytes);Um die langsamsten Queries zu finden reicht folgendes Statement:
Finde Tabelle mit fehlende Indizes mit folgendes Statement:
Reset: