Как читать EXPLAIN и типичные антипаттерны

Медленный запрос почти никогда не медленный целиком. Медленный один узел плана: полное чтение большой таблицы, сортировка на диске, соединение с ошибочной оценкой. EXPLAIN показывает план, EXPLAIN ANALYZE выполняет запрос и рядом с оценкой печатает факт. Уметь найти в этом выводе проблемный узел важнее любых правил оптимизации.

В уроке большая база: 80 тысяч заказов, 180 тысяч позиций, 270 тысяч событий.

План читается снизу вверх

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
FROM orders o
JOIN customers c USING (customer_id)
WHERE c.city = 'Казань';

Aggregate  (cost=2407.82..2407.83 rows=1 width=8) (actual time=34.176..34.178 rows=1 loops=1)
  ->  Hash Join  (cost=435.56..2389.62 rows=7280 width=0) (actual time=3.136..32.893 rows=7768 loops=1)
        Hash Cond: (o.customer_id = c.customer_id)
        ->  Seq Scan on orders o  (cost=0.00..1744.00 rows=80000 width=4) (actual ... rows=80000 loops=1)
              Buffers: shared hit=944
        ->  Hash  (cost=418.50..418.50 rows=1365 width=4) (actual ... rows=1365 loops=1)
              Buckets: 2048  Batches: 1  Memory Usage: 46kB
              ->  Seq Scan on customers c  (cost=0.00..418.50 rows=1365 width=4) (actual ... rows=1365 loops=1)
                    Filter: (city = 'Казань'::text)
                    Rows Removed by Filter: 13635
Execution Time: 34.254 ms

Что здесь важно:

• Узлы с отступом выполняются первыми, результат передаётся наверх. Читайте от самых глубоких листьев.
• rows в скобках cost это оценка планировщика, rows в actual это факт. Оценка 7280 против факта 7768: планировщик угадал. Расхождение в десятки раз и есть проблема.
• cost в условных единицах, сравнивать его между запросами бессмысленно, только между узлами одного плана.
• actual time это миллисекунды, первое число до первой строки, второе до последней. Умножайте на loops: узел внутри Nested Loop выполняется много раз, и его время указано за один раз.
• Buffers: shared hit страницы из кэша, read с диска. Запрос, читающий сотни тысяч страниц, медленный при любом плане.
• Rows Removed by Filter сколько строк прочитали зря. 13 635 из 15 000 клиентов прочитаны и выброшены: индекс по городу мог бы помочь, если бы запрос был частым.

EXPLAIN ANALYZE выполняет запрос по-настоящему. Для UPDATE и DELETE оборачивайте его в транзакцию с откатом.

Откуда берутся плохие оценки

Планировщик оценивает число строк по статистике: сколько уникальных значений в колонке, какие значения самые частые, как распределены остальные. Статистику собирает ANALYZE, автоматически после определённой доли изменений или вручную.

Три причины ошибок, которые вы встретите:

Устаревшая статистика. После загрузки миллиона строк в пустую таблицу планировщик может считать её пустой и выбрать Nested Loop. Правило: ANALYZE после массовой загрузки, обязательно для временных таблиц.

Коррелированные колонки. Оценка для WHERE customer_id = 1 AND status = 'delivered' перемножает вероятности, как будто колонки независимы. Если у клиента 1 доставлено всё, факт будет в разы больше оценки. В PostgreSQL для таких пар создают расширенную статистику: CREATE STATISTICS ... ON customer_id, status FROM orders.

Выражения над колонками. Для lower(status) = 'delivered' статистики нет, планировщик берёт оценку по умолчанию: 0,5% строк, в нашей таблице 400 из 80 000. На деле доставленных 56%. Индекс по выражению создаёт и статистику по нему.

Антипаттерн 1: функция над колонкой в условии

SELECT count(*) FROM orders WHERE created_at::date = '2026-06-15';
-- Seq Scan on orders, Filter: ((created_at)::date = '2026-06-15')

Индекс по created_at есть, но не используется: в индексе лежат метки времени, а сравнивают дату. То же с lower(email), date_trunc('day', ts), customer_id::text. Переписывайте условие так, чтобы колонка стояла «голой»:

WHERE created_at >= '2026-06-15' AND created_at < '2026-06-16';
-- Bitmap Index Scan on ix_orders_created

Это ещё одна причина, по которой полуинтервалы из урока про типы лучше BETWEEN и приведений.

Антипаттерн 2: DISTINCT как пластырь

SELECT DISTINCT o.order_id
FROM orders o
JOIN payments p ON p.order_id = o.order_id;

Соединение размножило строки, DISTINCT их схлопнул. Результат правильный, но база сначала построила 93 тысячи строк, потом отсортировала или захешировала их, чтобы выбросить лишние. EXISTS даёт тот же результат без размножения и без агрегации. Правило: если в запросе появился DISTINCT, а зерно результата вы не меняли, где-то выше размножение.

Антипаттерн 3: OR в условии соединения

SELECT count(*)
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id OR c.city = o.delivery_city;
-- Nested Loop, Join Filter: (... OR ...), оценка 199 миллионов строк

С OR в ON ни Hash Join, ни Merge Join невозможны: они работают только по равенству одного выражения. Остаётся Nested Loop с проверкой каждой пары, на этих таблицах это 1,2 миллиарда проверок. Разбивайте на два запроса через UNION, или, если второе условие резервное, на LEFT JOIN по каждому с coalesce.

Антипаттерн 4: SELECT * и сортировка всего

SELECT * FROM orders ORDER BY total_amount DESC LIMIT 10 сортирует 80 тысяч строк со всеми колонками ради десяти. Планировщик использует top-N heapsort, это терпимо, но ширина строки (width=67) множит объём данных, которые проходят через сортировку. Выбирайте только нужные колонки, особенно перед сортировкой и в подзапросах, которые потом соединяются.

Отдельный случай: Sort Method: external merge Disk. Сортировка не поместилась в work_mem и ушла на диск. Лечится либо памятью, либо индексом, который отдаёт строки уже отсортированными.

Антипаттерн 5: коррелированный подзапрос в SELECT

SELECT o.order_id,
       (SELECT sum(amount) FROM payments p WHERE p.order_id = o.order_id) AS paid
FROM orders o;

Подзапрос выполняется для каждой из 80 тысяч строк. С индексом по payments.order_id это 80 тысяч быстрых поисков, без индекса 80 тысяч полных чтений payments. Планировщик PostgreSQL не переписывает такой запрос в соединение. Переписывайте сами: агрегат по order_id в CTE и LEFT JOIN, один Hash Join вместо цикла.

Порядок действий, когда запрос медленный

1. EXPLAIN (ANALYZE, BUFFERS) на реалистичных данных, не на пустой таблице разработки.
2. Найти узел с самым большим actual time с учётом loops.
3. Сравнить в нём rows оценки и факта. Расхождение в разы: обновить статистику, проверить коррелированные условия и функции над колонками.
4. Посмотреть на Seq Scan с большим Rows Removed by Filter и на Batches больше единицы у Hash, на external у Sort.
5. Только после этого думать об индексах и переписывании.

Практика