Кеш планов, настройка константных стоимостей и внутренности планировщика

Внутренняя структура планировщика

Прежде чем объяснять, почему планировщик выбрал неожиданный план для сложного запроса, нужно понять, что именно он сравнивает — пути, а не готовые планы.

Для каждого запроса планировщик строит пути — лёгкие, частичные планы для фрагментов запроса (сканирование одной таблицы, соединения небольших наборов отношений). Пути сравниваются по стоимости; только дешевейший путь за «набор отношений + порядок сортировки» сохраняется (остальные отбрасываются). Для многоотношённых соединений рассматриваются все комбинации с помощью динамического программирования — исчерпывающим образом до geqo_threshold (умолчание 12) отношений, затем переключается на Genetic Query Optimizer (эвристический поиск) выше этого порога.

Ментальная модель для старших: когда видите неожиданный план, спрашивайте «какие ещё пути рассматривал планировщик и почему они были отброшены?». Ответ почти всегда — разница в стоимости, обусловленная оценками строк.

Общие vs кастомные планы (подготовленные операторы)

Каждый параметризованный запрос от драйвера ($1, $2, …) и каждая PL/pgSQL-функция с параметрами проходит через кеш планов. Жизненный цикл:

1. Первые 5 выполнений: Postgres планирует каждое с реальными значениями параметров — кастомные планы, оптимальные для конкретного параметра.
2. После 5-го выполнения: Postgres вычисляет среднюю стоимость 5 кастомных планов и сравнивает её со стоимостью общего плана (без привязки к параметрам).
3. Если стоимость общего плана в пределах ~10% от средней стоимости кастомных: Postgres навсегда переключается на общий план на время жизни подготовленного оператора.

Все три шага вместе означают, что переключение — автоматическое, невидимое и постоянное до закрытия соединения или явного вмешательства. Переключение невидимо для приложения. Для равномерно распределённых параметров общий план обычно подходит. Для косых распределений (workspace_id, где у некоторых workspace 8% строк, а у других 0.01%), общий план оптимизируется под «типичный» параметр и катастрофически неверен для выбросов.

Симптомы: высокое stddev_exec_time в pg_stat_statements (бимодальная задержка — быстро для распространённых параметров, медленно для выбросов).

Диагностика (PG 16+): EXPLAIN (GENERIC_PLAN) на подготовленной форме — показывает точно какой план получают все выполнения после переключения.

Исправление:

ALTER ROLE app SET plan_cache_mode = 'force_custom_plan';

Каждое выполнение перепланируется с реальным параметром. Стоимость планирования: ~0.4–2 мс — ничтожно по сравнению с 4-секундной хвостовой задержкой.

# Строка pg_stat_statements для проблемного запроса:
query: "SELECT * FROM orders WHERE workspace_id = $1 AND status = $2 ORDER BY created_at DESC LIMIT 50"
calls: 482,910
total_exec_time: 1,852,400 ms
mean_exec_time: 3.84 ms
stddev_exec_time: 412.6 ms   # ОГРОМНОЕ stddev -- симптом переключения плана
min_exec_time: 0.8 ms
max_exec_time: 4,290 ms

# EXPLAIN (ANALYZE, BUFFERS) на ТОМ ЖЕ запросе с workspace_id=42 (большой объём):
Limit (cost=0..14.2 rows=50) (actual time=2.1..2.3 rows=50 loops=1)
-> Index Scan using idx_orders_workspace_status_created on orders
     (cost=0..14000 rows=50000) (actual time=2.1..2.3 rows=50 loops=1)
     Index Cond: ((workspace_id=42) AND (status='pending'::text))
     Buffers: shared hit=12
Planning Time: 0.4 ms
Execution Time: 2.4 ms

# EXPLAIN (GENERIC_PLAN, BUFFERS) на подготовленной форме (PG 16+):
Limit (cost=0.43..28.5 rows=50)
-> Index Scan using idx_orders_created on orders
     (cost=0.43..15000.0 rows=27000)
     Filter: ((workspace_id = $1) AND (status = $2))
Planning Time: 0.2 ms

# Контекст:
# Распределение workspace_id: workspace 1 занимает 0.01% строк; workspace 42 — 8%.
# Распределение status: 'pending' 15%, 'shipped' 80%, 'cancelled' 5%.

Настройка константных стоимостей для SSD

Postgres поставляется с random_page_cost = 4.0 и seq_page_cost = 1.0 — откалиброваными для вращающихся HDD, где случайные чтения в 4× медленнее последовательных. На NVMe SSD отношение 1.5–2×; на системах, где рабочий набор помещается в RAM, приближается к 1.0.

-- В postgresql.conf для SSD-систем:
random_page_cost = 1.1
seq_page_cost    = 1.0

-- Сообщите планировщику, сколько данных кешировано ОС:
effective_cache_size = '24GB'  -- ~75% общей RAM

Эффект: планировщик становится более склонен использовать Index Scan и Index Only Scan вместо Seq Scan — что является правильным смещением для современного железа. Это единственная наиболее эффективная настройка планировщика на любом SSD-обеспеченном Postgres.

effective_cache_size — не выделение памяти, а подсказка планировщику о доступном объёме OS page-cache + shared_buffers. Правильная установка делает разницу стоимости между кешированными страницами индекса и некешированными страницами кучи более точной.

GEQO и большие соединения

Для запросов, соединяющих более geqo_threshold (умолчание 12) отношений, планировщик переключается с исчерпывающего динамического программирования на Genetic Query Optimizer — эвристику, которая быстра, но не гарантирует оптимальность. Эвристика рандомизирована — одинаковый запрос может производить разные планы при разных запусках.

Для аналитических запросов с 15–30 соединёнными таблицами GEQO может производить нестабильные планы. Обходные пути:

-- Принудительный исчерпывающий поиск (медленнее планирование, стабильные планы):
SET geqo = off;

-- Зафиксировать порядок JOIN в явном порядке SQL:
SET from_collapse_limit = 1;
SET join_collapse_limit = 1;

Трюк с from_collapse_limit = 1 полезен, когда вы вручную настроили порядок JOIN сложного запроса и хотите, чтобы он оставался настроенным при изменениях данных.

JIT-компиляция

PG 11+ поддерживает JIT-компиляцию вычисления выражений (предикаты фильтрации, проекции, агрегация). JIT включается когда общая стоимость плана превышает jit_above_cost (умолчание 100,000).

JIT помогает CPU-bound запросам на больших сканированиях (миллионы строк, сложные выражения) на 10–30%. Для коротких OLTP-запросов добавляет накладные расходы — время компиляции превышает экономию времени выполнения.

-- Для OLTP-нагрузок, где JIT вредит больше, чем помогает:
SET jit_above_cost = 500000;
-- Или полностью отключить для OLTP-ролей:
SET jit = off;

Используйте EXPLAIN (ANALYZE, JIT) для просмотра тайминов JIT по фазам и принятия решения о настройке порогов.