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:

SELECT substring(query, 1, 50) AS short_query,
              round(total_exec_time::numeric, 2) AS total_time,
              calls, rows,
              round(mean_exec_time::numeric, 2) AS mean,
              round((100 * total_exec_time /
              sum(total_exec_time::numeric) OVER ())::numeric, 2) AS percentage_overall,
			  query
FROM pg_stat_statements(true)
WHERE query NOT LIKE '%/*pga4dash*/%'
ORDER BY total_time DESC
LIMIT 50;

Finde Tabelle mit fehlende Indizes mit folgendes Statement:

SELECT schemaname, relname, seq_scan, seq_tup_read, 
idx_scan, seq_tup_read / seq_scan AS avg
FROM pg_stat_user_tables
WHERE seq_scan >0
ORDER BY seq_tup_read DESC
LIMIT

Reset:

SELECT pg_stat_statements_reset();

Rechtliche Hinweise / Impressum

EQ Odoo Wiki

PostgreSQL EXTENSION pg_stat_statements

Related content