Установка и настройка pg_stat_statements в PostgreSQL: полное руководство

# Находим postgresql.conf
# Обычно расположен в:
# /etc/postgresql/[версия]/main/postgresql.conf
# /var/lib/pgsql/[версия]/data/postgresql.conf
# или проверяем через psql:
SHOW config_file;

# В раздел Shared Library Preloading добавляем:
shared_preload_libraries = 'pg_stat_statements'

# Рекомендуемые настройки для pg_stat_statements:
pg_stat_statements.max = 10000           # макс. количество отслеживаемых запросов
pg_stat_statements.track = all           # отслеживать все запросы
pg_stat_statements.track_utility = on    # отслеживать служебные команды
pg_stat_statements.save = on             # сохранять статистику между перезапусками

# Для systemd (большинство современных систем):
sudo systemctl restart postgresql

# Для init.d:
sudo service postgresql restart

# Проверяем, что сервер запустился:
sudo systemctl status postgresql

-- Подключаемся к нужной базе данных
\c my_database

-- Создаём расширение
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Проверяем, что расширение создано
\dx pg_stat_statements

\c template1
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Проверяем, что расширение загружено
SELECT name, setting FROM pg_settings 
WHERE name LIKE '%pg_stat_statements%';

-- Смотрим доступные столбцы в представлении
\d pg_stat_statements

-- Первый тестовый запрос: топ-5 самых долгих запросов
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements 
ORDER BY total_exec_time DESC 
LIMIT 5;

# В postgresql.conf:
shared_preload_libraries = 'pg_stat_statements'

# Настройки pg_stat_statements
pg_stat_statements.max = 15000
pg_stat_statements.track = all
pg_stat_statements.track_utility = on
pg_stat_statements.save = on

# Дополнительные настройки для точности
track_io_timing = on                # отслеживать время I/O
track_functions = pl                # отслеживать функции PL/pgSQL
log_min_duration_statement = 1000   # логировать медленные запросы (1 сек)

SELECT 
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    rows / calls as avg_rows
FROM pg_stat_statements 
WHERE calls > 0
ORDER BY mean_exec_time DESC 
LIMIT 10;

SELECT 
    LEFT(query, 100) as short_query,
    calls,
    total_exec_time,
    (100 * total_exec_time / sum(total_exec_time) OVER ()) as percentage
FROM pg_stat_statements 
ORDER BY total_exec_time DESC 
LIMIT 15;

-- Сброс всей статистики
SELECT pg_stat_statements_reset();

-- Сброс статистики для конкретного пользователя/базы
-- (требуется PostgreSQL 13+)
SELECT pg_stat_statements_reset(userid, dbid, queryid);

# Конфигурация postgres_exporter
pg_stat_statements:
  query: |
    SELECT
      query,
      calls,
      total_time,
      rows,
      100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
    FROM pg_stat_statements
    WHERE query !~* '^pg_stat'
  metrics:
    - calls:
        usage: "COUNTER"
        description: "Number of times executed"
    - total_time:
        usage: "COUNTER"
        description: "Total time spent in the statement"