prepared-statements

Prepared statements, generic plan и «плавающая» производительность

Предпосылки: планировщик запросов (оценки и выбор плана), EXPLAIN (как сравнивать планы и фактическое время), B-tree (индексный vs последовательный доступ).

← Память и spill | Диагностика медленных запросов →

Одна из самых неприятных ситуаций в приложении — «один и тот же запрос иногда быстрый, иногда медленный» без изменений в данных и индексе. Частая причина — сочетание параметров и кэширования планов: PostgreSQL может строить план по‑разному для литерала WHERE status = 'active' и для параметра WHERE status = $1, а при повторном выполнении подготовленного запроса план может стать общим (generic) и перестать учитывать конкретное значение параметра.

Что такое prepared statement

Prepared statement — это заранее распарсенный и спланированный запрос, который можно выполнять много раз с разными параметрами.

Две причины, почему приложения их используют:

1. Меньше накладных расходов на парсинг/планирование. План строится один раз, дальше переиспользуется.
2. Безопасная параметризация. Параметры передаются отдельно от текста SQL.

В PostgreSQL prepared statement живёт на уровне сессии. В пуле соединений это означает: «план живёт внутри конкретного соединения», а не «внутри приложения целиком».

Custom plan vs generic plan

Когда запрос содержит параметры ($1, $2, …), планировщик в принципе может выбирать план двумя способами:

Custom plan — построить план с учётом конкретных значений параметров.
Это похоже на ситуацию «в запросе литерал»: селективность status='active' и status='archived' может быть очень разной, и лучший план тоже будет разным.

Generic plan — построить один «средний» план, который будет использоваться для любых значений параметров.

Зачем вообще нужен generic plan: планирование тоже стоит денег. Если запрос выполняется очень часто, иногда выгоднее заплатить за план один раз и дальше выполнять быстро, даже если план не идеально оптимален для каждого параметра.

Почему параметры меняют оптимизацию

Планировщик выбирает между Seq Scan и Index Scan (или Bitmap) по оценке: сколько строк вернёт условие.

Пример: таблица событий, где 99% строк имеют status='archived' и 1% — status='active'.

• Для status='active' выгоден index scan: очень мало строк.
• Для status='archived' выгоднее seq scan: слишком много строк, index scan превратится в тысячи походов в heap.

Если запрос параметризован (status = $1) и PostgreSQL использует generic plan, он может выбрать «средний» вариант, который будет плохим на одном из краёв распределения.

Именно поэтому два вызова «одного и того же» запроса в приложении могут иметь разную скорость: фактически это не один и тот же plan shape.

Как это увидеть в EXPLAIN

Воспроизводимый способ — принудительно переключить тип плана через plan_cache_mode и сравнить результаты для одного значения параметра.

Подготовка: таблица событий с перекошенным распределением status и индексом на нём.

PREPARE event_query(text) AS
  SELECT * FROM events WHERE status = $1 AND created_at > now() - interval '7 days';

Сначала заставляем PostgreSQL строить план с учётом конкретного значения:

SET plan_cache_mode = 'force_custom_plan';
EXPLAIN ANALYZE EXECUTE event_query('active');

Index Scan using idx_events_status on events
    (cost=0.43..1250.00 rows=1050 width=85) (actual time=0.03..1.6 rows=1038 loops=1)
  Index Cond: (status = 'active')
  Rows Removed by Filter: 12
  Planning Time: 0.15 ms
  Execution Time: 1.8 ms

Планировщик знает, что active — 1% таблицы, и выбирает Index Scan. Оценка rows=1050 близка к actual rows=1038 — план адекватен данным.

Теперь заставляем generic plan — план без учёта значения параметра:

SET plan_cache_mode = 'force_generic_plan';
EXPLAIN ANALYZE EXECUTE event_query('active');

Seq Scan on events
    (cost=0.00..35420.00 rows=500000 width=85) (actual time=0.02..418.5 rows=1038 loops=1)
  Filter: (status = $1 AND created_at > ...)
  Rows Removed by Filter: 985000
  Planning Time: 0.05 ms
  Execution Time: 420 ms

Generic plan не знает, что $1 будет 'active'. Он использует усреднённую селективность — rows=500000 при actual rows=1038, ошибка в 480 раз. Планировщик выбирает Seq Scan как «более безопасный» вариант для полумиллиона строк. Для 'archived' (99% строк) Seq Scan был бы оправдан, но для 'active' — деградация в 200 раз по времени.

plan_cache_mode — диагностический инструмент, а не production-решение. Если контраст между custom и generic планом значителен, это подтверждает parameter sensitivity. В production решение зависит от контекста: разделение на отдельные запросы для разных значений, использование plan_cache_mode = 'force_custom_plan' на конкретной сессии, или изменение формы запроса так, чтобы оптимальный план не зависел от параметра.

Как это проявляется в приложении

Это не «тонкость оптимизатора ради тонкости». Это прямой источник:

• неожиданных регрессий после перехода на prepared statements или после смены драйвера/ORM,
• нестабильного времени ответа (особенно на «редких» значениях параметров),
• ситуаций, когда индекс есть, но «иногда не используется», хотя запрос тот же.

Если в приложении есть запросы, где разные значения параметра ведут к разной селективности на порядки, то parameter sensitivity становится частью дизайна: либо вы принимаете нестабильность, либо делаете форму запроса более предсказуемой.

Sources

• PostgreSQL Documentation (пример: v16): Prepared statements (PREPARE/EXECUTE). https://www.postgresql.org/docs/16/sql-prepare.html, https://www.postgresql.org/docs/16/sql-execute.html
• PostgreSQL Documentation (пример: v16): Plan caching (generic vs custom plan). https://www.postgresql.org/docs/16/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING

---

← Память и spill | Диагностика медленных запросов →