Анализ и оптимизация медленных SQL-запросов (EXPLAIN ANALYZE)

Анализ и оптимизация медленных SQL-запросов (EXPLAIN ANALYZE)

Медленный запрос в продакшне — это конкретная причина деградации: full table scan на таблице в 50 миллионов строк, сортировка без индекса, декартово произведение таблиц из-за отсутствующего JOIN-условия. EXPLAIN ANALYZE показывает, что PostgreSQL делает на самом деле — не что, по мнению планировщика, он сделает, а что реально произошло в runtime.

Как читать EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.country = 'RU'
  AND o.created_at > '2025-01-01'
GROUP BY u.id, u.name
ORDER BY order_count DESC
LIMIT 20;

Пример вывода:

Limit  (cost=45231.23..45231.28 rows=20) (actual time=892.341..892.345 rows=20)
  ->  Sort  (cost=45231.23..45387.41) (actual time=892.340..892.341 rows=20)
        Sort Key: (count(o.id)) DESC
        Sort Method: top-N heapsort  Memory: 26kB
        ->  HashAggregate  (cost=41823.10..43011.52) (actual time=867.234..880.123 rows=12340)
              ->  Hash Left Join  (cost=12345.00..40234.12) (actual time=234.123..801.234 rows=450000)
                    Hash Cond: (o.user_id = u.id)
                    Buffers: shared hit=234 read=12890
                    ->  Seq Scan on orders o  (cost=0.00..18234.00 rows=450000) (actual time=0.023..345.234 rows=450000)
                          Filter: (created_at > '2025-01-01')
                          Rows Removed by Filter: 1234567
                          Buffers: shared hit=12 read=12878
                    ->  Hash  (cost=9876.00..9876.00 rows=123456) (actual time=234.012..234.012 rows=98765)
                          ->  Seq Scan on users u  (cost=0.00..9876.00 rows=123456) (actual time=0.021..189.234 rows=98765)
                                Filter: (country = 'RU')

Что видим и что с этим делать:

• Seq Scan on orders с Rows Removed by Filter: 1234567 — сканирует 1.7 млн строк, фильтрует 1.23 млн. Нужен индекс на (created_at) или (user_id, created_at).
• Buffers: shared hit=12 read=12878 — почти все страницы читаются с диска (read), не из кэша. Либо таблица больше shared_buffers, либо данные редко запрашиваются.
• actual time=892ms — для кнопки в интерфейсе это катастрофа.

Поиск медленных запросов через pg_stat_statements

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

-- postgresql.conf
-- shared_preload_libraries = 'pg_stat_statements'
-- pg_stat_statements.max = 10000
-- pg_stat_statements.track = all

-- Топ по суммарному времени (самое важное)
SELECT
    left(query, 100) AS query_preview,
    calls,
    round(total_exec_time::numeric, 0)   AS total_ms,
    round(mean_exec_time::numeric, 2)    AS avg_ms,
    round(stddev_exec_time::numeric, 2)  AS stddev_ms,
    rows
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
ORDER BY total_exec_time DESC
LIMIT 20;

-- Сброс статистики после оптимизации
SELECT pg_stat_statements_reset();

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

Seq Scan на большой таблице

-- Медленно: полный скан
SELECT * FROM orders WHERE status = 'pending';
-- EXPLAIN: Seq Scan on orders (rows=5000000) Filter: status='pending'

-- Решение: индекс
CREATE INDEX CONCURRENTLY idx_orders_status ON orders(status)
  WHERE status IN ('pending', 'processing');
-- Частичный индекс — только для активных статусов, меньше размер

-- Ещё лучше: покрывающий индекс (не обращается к таблице)
CREATE INDEX CONCURRENTLY idx_orders_status_cover
  ON orders(status, created_at DESC)
  INCLUDE (id, user_id, total_amount)
  WHERE status IN ('pending', 'processing');

Неэффективный JOIN

-- Медленно: JOIN без индекса на orders.user_id
SELECT u.name, o.total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.registered_at > '2025-01-01';

-- EXPLAIN: Hash Join ... Seq Scan on orders (rows=5000000)

-- Решение
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);
-- После: Hash Join ... Index Scan on orders

N+1 запрос в ORM

Это не SQL-проблема, а архитектурная, но проявляется в медленных запросах:

-- N+1: 1 запрос для списка + N запросов для каждого связанного объекта
-- В pg_stat_statements: простой SELECT выполнен 10000 раз за секунду

-- В Eloquent: было
$orders = Order::all();
foreach ($orders as $order) {
    echo $order->user->name; // N запросов к users
}

-- Стало: eager loading
$orders = Order::with('user:id,name')->get();
-- Один JOIN вместо N запросов

Сортировка без индекса

-- Медленно: Sort Method: external merge Disk: 45678kB
SELECT * FROM events ORDER BY created_at DESC LIMIT 100;

-- EXPLAIN: Sort ... actual time=3400ms
-- Сортировка через временный файл на диске

-- Решение: индекс по полю сортировки
CREATE INDEX CONCURRENTLY idx_events_created_at ON events(created_at DESC);
-- После: Index Scan Backward — 0.3ms

LIKE с префиксным wildcard

-- B-Tree индекс не работает для LIKE '%текст%'
SELECT * FROM products WHERE name LIKE '%телефон%';
-- EXPLAIN: Seq Scan, Filter: name LIKE '%телефон%'

-- Решение 1: pg_trgm для любого LIKE
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX CONCURRENTLY idx_products_name_trgm
  ON products USING gin(name gin_trgm_ops);
-- Теперь LIKE '%телефон%' использует индекс

-- Решение 2: full-text search для текстового поиска
ALTER TABLE products ADD COLUMN search_vector tsvector;
CREATE INDEX idx_products_fts ON products USING gin(search_vector);
UPDATE products SET search_vector = to_tsvector('russian', name || ' ' || description);

-- Запрос:
SELECT * FROM products
WHERE search_vector @@ plainto_tsquery('russian', 'телефон Samsung');

Функция на индексированной колонке

-- Плохо: функция на колонке отключает использование индекса
SELECT * FROM orders WHERE DATE(created_at) = '2025-01-15';
-- EXPLAIN: Seq Scan (функция DATE() применяется к каждой строке)

-- Хорошо: диапазон без функции
SELECT * FROM orders
WHERE created_at >= '2025-01-15 00:00:00'
  AND created_at <  '2025-01-16 00:00:00';
-- EXPLAIN: Index Scan (использует индекс на created_at)

-- Или: функциональный индекс
CREATE INDEX idx_orders_date ON orders(DATE(created_at));
SELECT * FROM orders WHERE DATE(created_at) = '2025-01-15';

Инструменты анализа

# auto_explain: автоматически логировать планы медленных запросов
# postgresql.conf:
# shared_preload_libraries = 'pg_stat_statements,auto_explain'
# auto_explain.log_min_duration = 1000  # мс
# auto_explain.log_analyze = true
# auto_explain.log_buffers = true

# Визуализация плана: https://explain.dalibo.com или https://explain.depesz.com
# Скопировать вывод EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) — получить визуальное дерево

Процесс оптимизации

1. Найти топ-10 запросов по total_exec_time через pg_stat_statements
2. EXPLAIN (ANALYZE, BUFFERS) на каждый
3. Определить узкое место: Seq Scan, сортировка, hash join
4. Создать или изменить индекс (CONCURRENTLY — без блокировки)
5. ANALYZE table_name — обновить статистику
6. Повторить EXPLAIN ANALYZE — сравнить планы
7. pg_stat_statements_reset() — сбросить и наблюдать новую статистику

Цикл занимает от нескольких часов до нескольких дней в зависимости от числа проблемных запросов и объёма данных.