Как мониторить производительность PostgreSQL?

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Топ-10 самых тяжёлых запросов по общему времени
SELECT
    queryid,
    substr(query, 1, 100) AS query,
    calls,
    round(total_exec_time::numeric, 2) AS total_time_ms,
    round(mean_exec_time::numeric, 2) AS avg_time_ms,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Запросы с наибольшим количеством чтений с диска
SELECT
    substr(query, 1, 80) AS query,
    calls, shared_blks_hit, shared_blks_read,
    round(100.0 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0), 2) AS cache_hit_pct
FROM pg_stat_statements
ORDER BY shared_blks_read DESC
LIMIT 10;

SELECT
    relname AS table_name,
    seq_scan,            -- много Seq Scan — нужен индекс?
    idx_scan,            -- количество Index Scan
    n_tup_ins AS inserts, n_tup_upd AS updates, n_tup_del AS deletes,
    n_dead_tup AS dead_tuples,
    last_autovacuum
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;

SELECT
    schemaname, tablename, indexrelname,
    idx_scan,          -- 0 = не используется
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

SELECT
    pid, usename, state,
    now() - query_start AS duration,
    wait_event_type, wait_event,
    substr(query, 1, 100) AS query
FROM pg_stat_activity
WHERE state = 'active' AND pid != pg_backend_pid()
ORDER BY duration DESC;

-- Завершить зависший запрос
SELECT pg_cancel_backend(pid);     -- мягкое (SIGINT)
SELECT pg_terminate_backend(pid);  -- жёсткое (SIGTERM)

SELECT
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_size_pretty(pg_relation_size(relid)) AS table_size,
    pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_size
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

SELECT
    sum(blks_hit) * 100.0 / NULLIF(sum(blks_hit) + sum(blks_read), 0) AS cache_hit_ratio
FROM pg_stat_database;
-- Хорошее значение: > 99%