Привет, Хабр!

Запрос работает 30 секунд. Вы смотрите на него, всё вроде ок: JOIN по индексированным полям, WHERE по дате, LIMIT 100. Должен летать, но что-то не летает. Добавляете индекс наугад — не помогает. Переписываете подзапрос в CTE и стало ещё хуже.

Проблема не в запросе, а в в том, что вы не смотрели в план выполнения. EXPLAIN ANALYZE показывает не что вы написали, а что PostgreSQL делает: какие индексы использует (и использует ли вообще), в каком порядке соединяет таблицы, где тратит время, сколько строк ожидал и сколько получил.

EXPLAIN и EXPLAIN ANALYZE

-- EXPLAIN: показывает план БЕЗ выполнения запроса
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

-- EXPLAIN ANALYZE: выполняет запрос и показывает реальные цифры
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

EXPLAIN полезен, когда нельзя выполнять запрос (тяжёлый INSERT, DELETE). EXPLAIN ANALYZE уже показывает реальное время, реальное количество строк, реальные I/O-операции. Почти всегда вам нужен ANALYZE.

Максимум информации:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE customer_id = 42;

BUFFERS покажет, сколько страниц прочитано с диска и из кеша.

Строение плана

EXPLAIN ANALYZE
SELECT o.id, o.amount, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= '2025-01-01'
  AND o.status = 'paid'
ORDER BY o.amount DESC
LIMIT 10;
Limit  (cost=1234.56..1234.58 rows=10 width=52) (actual time=12.345..12.367 rows=10 loops=1)
  ->  Sort  (cost=1234.56..1256.78 rows=8901 width=52) (actual time=12.344..12.355 rows=10 loops=1)
        Sort Key: o.amount DESC
        Sort Method: top-N heapsort  Memory: 26kB
        ->  Hash Join  (cost=234.56..1100.00 rows=8901 width=52) (actual time=1.234..10.567 rows=9200 loops=1)
              Hash Cond: (o.customer_id = c.id)
              ->  Index Scan using idx_orders_created on orders o  (cost=0.43..800.00 rows=8901 width=36) (actual time=0.023..7.891 rows=9200 loops=1)
                    Index Cond: (created_at >= '2025-01-01')
                    Filter: (status = 'paid')
                    Rows Removed by Filter: 3800
              ->  Hash  (cost=150.00..150.00 rows=5000 width=20) (actual time=1.100..1.100 rows=5000 loops=1)
                    Buckets: 8192  Batches: 1  Memory Usage: 280kB
                    ->  Seq Scan on customers c  (cost=0.00..150.00 rows=5000 width=20) (actual time=0.010..0.600 rows=5000 loops=1)
Planning Time: 0.234 ms
Execution Time: 12.456 ms

Читаем снизу вверх и изнутри наружу:

Seq Scan on customers — полное сканирование таблицы customers. 5000 строк. Время 0.6 мс. Таблица маленькая, seq scan нормален.

Index Scan on orders — сканирование по индексу idx_orders_created. Index Cond: фильтр по дате выполняется через индекс. Filter: status = 'paid' — дополнительный фильтр уже после индекса. Rows Removed by Filter: 3800 — индекс вернул 13000 строк (9200 + 3800), а фильтр по статусу убрал 3800. Если таких лишних строк много, стоит подумать о составном индексе (created_at, status).

Hash Join — соединение через хеш-таблицу. Customers загружены в хеш (280kB в памяти), orders проходят через неё. Быстро для этих объёмов.

Sort — сортировка по amount. top-N heapsort — PostgreSQL умён: раз нужны только 10 строк, он не сортирует все 9200, а поддерживает кучу из 10 элементов. 26kB памяти.

Limit — берём первые 10.

На что смотреть

rows (estimated vs actual). rows=8901 в cost — прогноз планировщика. rows=9200 в actual — реальность. Близко — планировщик хорошо знает данные. Если прогноз 100, а факт 100000 — кривая статистика, нужен ANALYZE таблицы.

Rows Removed by Filter. Если из индекса пришло 100000 строк, а фильтр убрал 99000 — индекс неэффективен. Нужен составной индекс, который покрывает все условия WHERE.

actual time. Два числа: start..total. Первое — время до первой строки. Второе — время до последней. Если первое большое — долго стартуем (например, сортировка — нужно всё прочитать перед выдачей первой строки). Если разница между ними большая — долго итерируем.

loops. Сколько раз узел выполнялся. Для Nested Loop Join внутренний узел выполняется loops раз. actual time и rows — на одну итерацию. Общее время = time × loops.

->  Index Scan on orders  (actual time=0.010..0.015 rows=3 loops=5000)

0.015 мс × 5000 = 75 мс суммарно. Не 0.015 мс.

BUFFERS: диск или кеш?

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE id = 42;
Index Scan using orders_pkey on orders (actual time=0.023..0.025 rows=1 loops=1)
  Buffers: shared hit=4

shared hit=4 — четыре страницы прочитаны из shared_buffers (кеш PostgreSQL).

Seq Scan on orders (actual time=0.100..450.000 rows=1000000 loops=1)
  Buffers: shared hit=50000 read=15000

shared read=15000 — 15000 страниц пришлось читать с диска (или из кеша ОС). Если read велик — данные не помещаются в shared_buffers, запрос I/O-bound.

shared dirtied и shared written — страницы, которые пришлось записать (при UPDATE/DELETE или при hint bit updates).

Популярные проблемы и что делать

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

Seq Scan on orders (actual time=0.100..1200.000 rows=50 width=100)
  Filter: (customer_id = 42)
  Rows Removed by Filter: 999950

Миллион строк просканировано, 50 подошли. Нет индекса на customer_id.

Решение:

CREATE INDEX idx_orders_customer ON orders (customer_id);

Но не всегда. Если customer_id = 42 возвращает 80% таблицы — seq scan быстрее индекса (меньше random I/O). Планировщик знает это и выбирает seq scan сознательно.

Оценка строк далека от реальности

Hash Join (rows=100) (actual rows=500000)

Планировщик думал 100 строк, получил полмиллиона. Выбрал Hash Join, хотя для 500K лучше Merge Join или другая стратегия. Причина в устаревшей статистике.

ANALYZE orders;  -- Обновить статистику таблицы

Или для сложных случаев можно увеличить статистику по столбцу:

ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

Nested Loop на больших таблицах

Nested Loop (actual time=0.050..45000.000 rows=100000 loops=1)
  ->  Seq Scan on customers (rows=5000)
  ->  Index Scan on orders (rows=20 loops=5000)

5000 × 20 обращений к индексу = 100000 index lookups. Если таблица orders не в кеше — каждый lookup = random read с диска. Для больших объёмов Hash Join или Merge Join будет поэффективнее.

Почему планировщик выбрал Nested Loop? Возможно, неверная оценка строк в customers. ANALYZE и проверяйте.

Sort на диске

Sort (actual time=500.000..600.000 rows=1000000)
  Sort Method: external merge  Disk: 150000kB

external merge Disk: 150MB — данные не влезли в work_mem, сортировка ушла на диск.

Решения:

SET work_mem = '256MB';  -- Увеличить для сессии

Или добавить индекс, который даёт данные уже в нужном порядке:

CREATE INDEX idx_orders_amount_desc ON orders (amount DESC);

CTE и подзапросы

В PostgreSQL 12+ CTE по умолчанию инлайнятся (оптимизатор может «развернуть» CTE в подзапрос). Но если CTE используется несколько раз, PostgreSQL материализует его. Это может быть как быстрее (один раз вычислили), так и медленнее (нельзя протолкнуть фильтр внутрь).

-- Принудительно материализовать
WITH cte AS MATERIALIZED (
    SELECT * FROM orders WHERE status = 'paid'
)
SELECT * FROM cte WHERE amount > 1000;
-- Фильтр amount > 1000 НЕ попадёт внутрь CTE

-- Принудительно инлайнить
WITH cte AS NOT MATERIALIZED (
    SELECT * FROM orders WHERE status = 'paid'
)
SELECT * FROM cte WHERE amount > 1000;
-- PostgreSQL объединит оба фильтра

Смотрите в EXPLAIN если CTE материализован, вы увидите CTE Scan, а фильтры будут снаружи.

explain.dalibo.com: визуализация

Текстовый план тяжело читать для сложных запросов. Скопируйте вывод EXPLAIN (FORMAT JSON) в explain.dalibo.com или explain.depesz.com, получите визуальное дерево с подсветкой узких мест.

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT ...;

Привычка

Перед каждым CREATE INDEX — смотрите EXPLAIN ANALYZE. Индекс может не помочь (или даже навредить, если update-heavy таблица). После создания индекса — снова EXPLAIN ANALYZE. Убедитесь, что планировщик его использует.

Перед деплоем запроса на прод — EXPLAIN ANALYZE на копии прода с реальными данными. На таблице с 1000 строк всё быстро. На таблице с 100 миллионов уже будет совсем другой план и совсем другое время.

Если чтения плана уже недостаточно и нужно понимать, где именно упирается PostgreSQL — в статистику, ввод-вывод, индексы или саму архитектуру кластера, — дальше нужен уже системный уровень. Курс «Администрирование PostgreSQL. Экспертный уровень» как раз про это: производительность, профилирование, мониторинг, резервное копирование и работа с отказоустойчивыми кластерами без гадания на симптомах.

Чтобы узнать больше о формате обучения и познакомиться с преподавателем, приходите на бесплатный урок «PostgreSQL как векторная база данных: ИИ‑поиск без лишних сервисов» 9 апреля в 20:00. Записаться на урок

Немного практики в тему — пройдите вступительный тест по PostgreSQL и узнаете, есть ли пробелы в знаниях. До 30 апреля за прохождение теста действует скидка 15%