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

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

Поиск и оптимизация медленных запросов — критический навык для backend разработчика. В PostgreSQL есть несколько встроенных инструментов для этого.

1. Включение логирования медленных запросов

Основной инструмент — log_min_duration_statement:

-- Логировать все запросы, которые дольше 1000 мс (1 сек)
ALTER SYSTEM SET log_min_duration_statement = 1000;
SELECT pg_reload_conf();  -- Перезагрузить конфиг

-- Или отредактировать postgresql.conf:
log_min_duration_statement = 1000  # -1 отключает (default)

-- Проверить текущее значение
SHOW log_min_duration_statement;

Теперь все запросы дольше 1 секунды будут записываться в лог PostgreSQL:

2026-03-22 10:15:23.456 UTC [1234] LOG:  duration: 1523.456 ms  statement: SELECT * FROM users WHERE status = 'active';

2. EXPLAIN и EXPLAIN ANALYZE

Самый мощный инструмент для анализа одного запроса:

-- EXPLAIN без выполнения запроса (показывает план)
EXPLAIN SELECT * FROM users WHERE id = 1;

-- Результат:
-- QUERY PLAN
-- Seq Scan on users  (cost=0.00..35.50 rows=1 width=100)
--   Filter: (id = 1)

-- EXPLAIN ANALYZE выполняет запрос и показывает реальные числа
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;

-- Результат:
-- QUERY PLAN
-- Seq Scan on users  (cost=0.00..35.50 rows=1 width=100) (actual time=0.015..0.025 rows=1 loops=1)
--   Filter: (id = 1)
-- Planning Time: 0.135 ms
-- Execution Time: 0.042 ms

Интерпретация EXPLAIN:

• cost=0.00..35.50 — предполагаемая стоимость (arbitrary units)
• rows=1 — сколько рядов будет возвращено
• actual time=0.015..0.025 — реальное время (мин..макс)
• loops=1 — сколько раз выполнился узел плана

3. EXPLAIN с опциями

-- Подробный анализ с информацией о буфферах
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 
SELECT * FROM orders WHERE user_id = 123;

-- Результат:
-- Seq Scan on orders (cost=0.00..45.00 rows=10 width=50) (actual time=0.10..0.20 rows=10 loops=1)
--   Filter: (user_id = 123)
--   Buffers: shared hit=100 read=5
-- Execution Time: 0.35 ms

-- Опции:
-- ANALYZE — выполнить запрос
-- BUFFERS — показать использование буфферов (какие данные из памяти, а какие с диска)
-- VERBOSE — дополнительная информация
-- FORMAT JSON — результат в JSON для парсинга

4. Частые причины медленных запросов

Seq Scan (полное сканирование таблицы) вместо Index Scan:

-- ПЛОХО — Seq Scan, медленно
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'john@example.com';
-- Seq Scan on users  (cost=0.00..35.50 rows=1 width=100)

-- РЕШЕНИЕ — создать индекс
CREATE INDEX idx_users_email ON users(email);

-- ХОРОШО — Index Scan, быстро
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'john@example.com';
-- Index Scan using idx_users_email on users  (cost=0.28..8.30 rows=1 width=100)

Hash Join вместо Nested Loop:

-- Запрос с объединением двух таблиц
EXPLAIN ANALYZE
SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active';

-- Если план использует Nested Loop — очень медленно
-- Если Hash Join — быстрее
-- Если Sort Merge Join — ещё быстрее для больших данных

-- РЕШЕНИЕ — создать индексы на столбцах JOIN
CREATE INDEX idx_orders_user_id ON orders(user_id);

Отсутствие индекса на WHERE условии:

EXPLAIN ANALYZE SELECT * FROM products WHERE category_id = 5 AND price > 100;
-- Seq Scan on products

CREATE INDEX idx_products_category_price ON products(category_id, price);

EXPLAIN ANALYZE SELECT * FROM products WHERE category_id = 5 AND price > 100;
-- Index Scan using idx_products_category_price

5. pg_stat_statements — логирование всех запросов

Расширение для отслеживания всех выполняемых запросов:

-- Включить расширение
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Посмотреть топ-10 медленных запросов
SELECT
    query,
    calls,
    mean_exec_time,
    max_exec_time,
    total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Результат:
-- query: SELECT * FROM users WHERE status = 'active' AND created > now() - interval '7 days'
-- calls: 1523
-- mean_exec_time: 245.67  (ms)
-- max_exec_time: 1523.45  (ms)
-- total_exec_time: 373,456.78  (ms)

Топ запросов по общему времени выполнения:

SELECT
    query,
    calls,
    total_exec_time / calls AS avg_time,
    max_exec_time,
    total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

6. Анализ с помощью Python и sqlalchemy

from sqlalchemy import text, create_engine

engine = create_engine('postgresql://user:pass@localhost/dbname')

# Выполнить EXPLAIN ANALYZE для запроса
query = text("""
    EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
    SELECT u.name, COUNT(o.id) as order_count
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    WHERE u.created > now() - interval '30 days'
    GROUP BY u.id, u.name
""")

with engine.connect() as conn:
    result = conn.execute(query)
    plan = result.fetchall()
    
    # JSON план легче парсить
    import json
    plan_json = json.loads(plan[0][0])
    
    # Достать информацию о выполнении
    exec_time = plan_json[0]['Execution Time']
    planning_time = plan_json[0]['Planning Time']
    
    print(f"Planning: {planning_time}ms, Execution: {exec_time}ms")

7. Практическая схема оптимизации

Шаг 1: Найти медленные запросы

SET log_min_duration_statement = 500;  -- Логировать >500ms
SELECT * FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 5;

Шаг 2: Анализировать план выполнения

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;  -- Наш медленный запрос

Шаг 3: Определить узкие места

• Seq Scan вместо Index Scan?
• Слишком много рядов?
• Неправильный типе JOIN?
• Выполняются ли подзапросы много раз?

Шаг 4: Применить оптимизацию

-- Добавить индекс
CREATE INDEX idx_table_column ON table(column);

-- Переписать запрос
SELECT ... FROM table WHERE indexed_column = value;

-- Денормализовать данные
ALTER TABLE users ADD COLUMN order_count INT;

Шаг 5: Измерить улучшение

EXPLAIN ANALYZE SELECT ...;  -- Проверить новый план

8. Инструменты визуализации

pgAdmin — веб-интерфейс:

• Встроенный EXPLAIN ANALYZE
• Визуализация плана
• Статистика таблиц

DBeaver — мощный IDE для БД:

• EXPLAIN с красивой визуализацией
• История запросов
• Профилирование

explain.depesz.com — онлайн анализатор: Скопируй вывод EXPLAIN ANALYZE на сайт, он даст рекомендации.

9. Частые ошибки

-- ОШИБКА 1: Проверка типов в WHERE (плохо для индекса)
SELECT * FROM users WHERE CAST(id AS TEXT) = '123';  -- Seq Scan
SELECT * FROM users WHERE id = 123;  -- Index Scan

-- ОШИБКА 2: Функции в WHERE
SELECT * FROM users WHERE EXTRACT(YEAR FROM created) = 2026;  -- Seq Scan
SELECT * FROM users WHERE created >= '2026-01-01' AND created < '2027-01-01';  -- Index

-- ОШИБКА 3: IN с большим списком
SELECT * FROM orders WHERE id IN (1,2,3,...,10000);  -- Медленно
-- Лучше: JOIN с таблицей или временной таблицей
SELECT o.* FROM orders o WHERE EXISTS (SELECT 1 FROM temp_ids t WHERE t.id = o.id);

10. Реальный пример оптимизации

# БЫЛО: N+1 проблема, очень медленно
from sqlalchemy import Session
from models import User, Order

users = session.query(User).all()
for user in users:
    orders = session.query(Order).filter_by(user_id=user.id).all()  # Отдельный запрос!
    print(f"{user.name}: {len(orders)} заказов")

# СТАЛО: Одна джойн, быстро
from sqlalchemy.orm import joinedload

users = session.query(User).options(joinedload(User.orders)).all()
for user in users:
    print(f"{user.name}: {len(user.orders)} заказов")

Итоги

Для поиска медленных запросов:

1. log_min_duration_statement — включить логирование всех медленных запросов
2. EXPLAIN ANALYZE — анализировать конкретный запрос
3. pg_stat_statements — найти топ медленных запросов
4. Основные оптимизации: добавить индексы, переписать запрос, избежать N+1
5. Инструменты: pgAdmin, DBeaver, explain.depesz.com