Финальный урок модуля — о ловушках, на которых спотыкаются даже опытные разработчики при чтении планов. Это не отдельные концепции, а паттерны: узоры, которые ты учишься распознавать, и при их виде сразу понимаешь, что план «не такой, как кажется».

Ловушка 1: estimated rows = 1 = «не знаю»

Когда планировщик видит rows=1 в EXPLAIN — это не значит «я уверен, что 1 строка». Это часто маркер «я не могу оценить и поставил минимум». Postgres никогда не оценивает узел в 0 строк (минимум 1), даже когда predicates явно противоречивы.

Случаи, когда rows=1 означает «не знаю»:

1. Predicate с функцией, по которой нет статистики. Например WHERE lower(email) = '[email protected]' — для функционального выражения без functional index или extended statistics селективность неизвестна.
2. Сравнение с подзапросом или volatile-функцией: WHERE created_at > now() - interval '1 day' — now() параметризуется только в runtime.
3. Cross-column condition, по которой нет multivariate statistics.
4. Предикат с unknown selectivity для типов без default selectivity функции.

Когда rows=1 в плане над scan’ом большой таблицы, и в actual rows получается 1 миллион — это тот самый каскадный crash. Узнать, что планировщик гадает, можно из исходников или из метода: запусти EXPLAIN ANALYZE и сравни estimate с actual.

Лечение: extended statistics (CREATE STATISTICS), functional index, переписать predicate.

Видишь разницу? Второй случай — rows ≈ 1 или small constant, потому что Postgres не знает селективность lower(country)='ru'. На простом predicate country='RU' он использует MCV (most common values).

Ловушка 2: Large under-/over-estimation на joins

Когда статистика по одной таблице нормальная, но join даёт перекошенную оценку. Причина — Postgres считает join selectivity как selectivity_left × selectivity_right, предполагая независимость. На реальных данных это часто не так.

Пример: orders есть только у активных customers (is_active=true). Если ты делаешь:

SELECT * FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.is_active = true;

Postgres ожидает: selectivity(o.customer_id matches some c) × selectivity(c.is_active) = 1.0 × 0.5 = 0.5. Но на самом деле 100% orders ведут именно на active customers (потому что неактивные не делают заказы). Estimated rows = 50K, actual = 100K — отклонение 2x. Если бы was-active фильтр был жёстче (1% активных), отклонение могло бы быть 100x.

Лечение: CREATE STATISTICS ... (dependencies, ndistinct) ON ... для коррелированных колонок. После ANALYZE Postgres начнёт учитывать функциональную зависимость.

Ловушка 3: Filter vs Index Cond

В плане у Index Scan может быть два разных вида predicates:

Index Scan using idx_orders_status on orders
  Index Cond: (status = 'paid')
  Filter: (total_cents > 50000)
  Rows Removed by Filter: 4500

Index Cond — predicate, по которому фактически идёт поиск в B-tree (или другом index). Только эти строки реально извлекаются из heap.

Filter — predicate, который применяется после извлечения. Эти строки уже прочитаны с heap, и большая Rows Removed by Filter — это работа впустую: страницы прочитаны, строки отброшены.

Если у тебя в плане Rows Removed by Filter: 4500 на узле, который должен был вернуть 500 — индекс не покрывает достаточно колонок. Решение — composite index на (status, total_cents). Тогда оба условия станут Index Cond.

Это паттерн «hidden index miss»: формально индекс есть и используется, но из-за Filter теряются десятки процентов работы.

Ловушка 4: JIT и инфляция timing

С Postgres 11+ для тяжёлых запросов (cost > jit_above_cost, default 100000) включается

В EXPLAIN ANALYZE с JIT появляется отдельный блок:

JIT:
  Functions: 12
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 1.234 ms, Inlining 25.456 ms, Optimization 145.678 ms, Emission 80.123 ms, Total 252.491 ms

Здесь 252 мс — это время JIT, которое уже включено в Execution Time запроса. Если ты бенчмаркишь EXPLAIN ANALYZE, и видишь Execution Time = 800 мс с JIT = 250 мс — реальная работа всего 550 мс. На повторных запусках того же запроса JIT-код кэшируется в plan cache (если запрос параметризованный или prepared), и timing будет меньше.

Ловушка: бенчмаркишь cold-cache запрос с JIT, делаешь вывод «запрос медленный», на самом деле — это просто компиляция. Решения:

1. Сравнить с SET jit = off; — увидеть, сколько занимает запрос без JIT.
2. Запускать запрос несколько раз (первый — для прогрева).
3. Параметризовать запрос и использовать prepared statements — JIT кэшируется лучше.

Ловушка 5: VERBOSE для скрытых деталей

Опция VERBOSE в EXPLAIN раскрывает то, что обычно скрыто:

• Output list каждого узла (какие колонки реально передаются).
• Worker’ы поименно (Worker 0, Worker 1) с их собственными actual time.
• Schema-qualified имена.

Иногда нужна именно эта информация. Например, если ты не понимаешь, зачем в плане появился Seq Scan на таблице, и думаешь «но мне же не нужны все колонки» — VERBOSE покажет, какие колонки реально protyагиваются. Если их 30 из 30 — оптимизация column-pruning не работает.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE) ...

Ловушка 6: planning time vs execution time

В конце EXPLAIN ANALYZE две строки:

Planning Time: 5.234 ms
Execution Time: 145.123 ms

Planning time — сколько Postgres думал, как выполнить запрос. На сложных queries (с 10+ tables в join, partitioned tables, many indices) planning может занять секунды. Если planning близок к execution — пора рассмотреть prepared statements или pg_hint_plan.

Особенно ярко это видно на запросах с большим числом partitions. Planning тратит время на partition pruning для каждой партиции. С PG 12+ это сильно улучшилось, но на 1000+ partitions всё ещё чувствуется.

auto_explain: ловить медленные запросы в production

Запускать EXPLAIN ANALYZE на проде вручную — плохая идея: ты должен знать, какой запрос проблемный, и ещё реально его выполнить (=изменить данные для DML). Решение — extension auto_explain, который автоматически логирует план для каждого запроса, чьё время выполнения превышает порог.

Включается в postgresql.conf:

shared_preload_libraries = 'auto_explain'

auto_explain.log_min_duration = '500ms'    # порог
auto_explain.log_analyze = on              # включает ANALYZE (имеет overhead!)
auto_explain.log_buffers = on              # BUFFERS
auto_explain.log_timing = on               # actual time
auto_explain.log_triggers = on             # триггеры тоже
auto_explain.log_verbose = on              # VERBOSE
auto_explain.log_nested_statements = on    # внутри PL/pgSQL функций

Главный trade-off: log_analyze = on означает, что каждый запрос будет выполняться с overhead EXPLAIN ANALYZE — это 1.5-3x замедление на time-критичных запросах. Поэтому или ставят высокий порог (log_min_duration = 5000ms), или отключают analyze, и логируют только estimated cost (log_analyze = off).

В production-tuning auto_explain выглядит как:

auto_explain.log_min_duration = '1s'
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_format = 'json'           # парсится инструментами
auto_explain.sample_rate = 0.01            # 1% запросов, чтобы снизить overhead

sample_rate (с PG 13) — выборочное логирование. Логирует только 1% запросов, остальные 99% работают без overhead. Для статистически значимой картины — достаточно.

После настройки планы попадают в postgresql.log. Удобно скармливать их в pgBadger или explain.depesz.com — там удобный анализ.

Ловушка 7: cached plans и параметры

При использовании prepared statements или PL/pgSQL Postgres кэширует план запроса. Кэшированный план — это generic plan: построен без знания актуальных значений параметров.

Симптом: один и тот же запрос с разными параметрами иногда быстрый, иногда медленный. Причина: для одних параметров generic plan хорош (например, parameter=common_value), для других катастрофичен (parameter=rare_value, для которого был бы лучше Index Scan).

Postgres с PG 12+ имеет адаптивный механизм: первые 5 выполнений делает custom plan (с известным параметром), потом сравнивает с generic plan, и если generic сильно дороже — продолжает custom; иначе переходит на generic. Параметр plan_cache_mode = auto | force_generic_plan | force_custom_plan управляет этим.

Чтобы посмотреть план PreparedStatement, используй:

PREPARE my_query AS SELECT ... WHERE col = $1;
EXPLAIN ANALYZE EXECUTE my_query(123);

После 5+ executions Postgres может перейти на generic, и план поменяется. Это очень тонкая ловушка production — диагностика «иногда быстро, иногда медленно» часто оборачивается именно этим.

Дополнительные ловушки кратко

Heap Fetches: 0 в Index Only Scan — отлично. Если число большое — visibility map устарела, нужен VACUUM.

Memory Usage: ... kB + Disk: ... в Hash/Sort — work_mem не хватил, ушло на временные файлы. См. урок 03.

Subplan vs InitPlan — Subplan выполняется для каждой строки родителя (потенциально дорого), InitPlan — один раз и кэшируется.

Materialize под Nested Loop — Postgres решил скопировать inner в память для быстрого повторного чтения. Не плохо, но указатель на то, что Nested Loop тяжёлый.

Несколько индексов в Bitmap Index Scan через BitmapAnd/BitmapOr — Postgres комбинирует индексы. Иногда это медленнее одного составного индекса; если такой план появляется регулярно — кандидат на composite index.

Recheck Cond на Bitmap Heap Scan — после lossy bitmap (когда битов на TID не хватило, и страница помечена целиком) executor re-проверяет каждую строку. Большие Rows Removed by Recheck — work_mem мал для bitmap.

Финальный workflow диагностики

Собираем всё в один алгоритм диагностики медленного запроса:

1. Сними EXPLAIN (ANALYZE, BUFFERS, VERBOSE) запроса. Если DML — обязательно BEGIN; ... ROLLBACK;.
2. Посмотри Planning Time и Execution Time. Если planning > 10% — рассмотри prepared statements.
3. Найди самый дорогой узел по total time × loops (помня, что parallel — исключение). Это hot spot.
4. На hot spot проверь:
   • estimated rows vs actual rows. Расхождение > 10x — статистика проблема.
   • Filter с большим Rows Removed — индекс неполный.
   • Buffers hit_rate. < 90% — кэш не помогает.
   • temp read/written. Любой → work_mem мал.
5. Проверь, что это не JIT. Если JIT Total Time большой, попробуй с SET jit = off; или повтори запрос (warm cache).
6. Сравни с альтернативами: отключи enable_hashjoin/enable_mergejoin/enable_seqscan по очереди, посмотри, какой план выбирает Postgres и какова его реальная стоимость.
7. Если запрос редкий и production — настрой auto_explain с sample_rate, чтобы поймать паттерн.

Этот workflow — то, что senior-разработчик делает за 15-30 минут. С опытом многое уходит в автоматику: глаз сразу видит «вот эти actual=50000 не сходятся с estimated=100».

В этом плане ты увидишь сразу всё: дерево узлов, cost-оценки, реальные actual rows/time/loops, Buffers, output list каждого узла, non-default settings. Это полная картина запроса.

Чек-лист

• rows=1 в EXPLAIN часто означает «планировщик не знает», особенно с функциями или сложными predicates.
• Filter под Index Scan + большой Rows Removed by Filter — индекс не покрывает всё, рассмотри composite index.
• JIT timing входит в Execution Time; на cold-cache запросах инфлирует timing на 50-500 мс.
• VERBOSE покажет output list — полезно при подозрении на отсутствие column-pruning.
• Planning Time на сложных queries может приближаться к Execution Time → prepared statements или hints.
• auto_explain — стандарт для production-диагностики. Используй sample_rate или высокий log_min_duration, чтобы избежать overhead.
• Heap Fetches > 0 в Index Only Scan = VACUUM нужен.
• Memory/Disk строки в Sort/Hash = work_mem мал.
• Subplan (per-row) vs InitPlan (once-cached) — разная семантика, разная стоимость.