Приложение годами гоняет один и тот же запрос с параметром, а в какой-то день часть вызовов вместо 2,2 с занимает 5,6 с. Код не менялся, данные не менялись, ANALYZE тоже. Меняется план: у prepared statement первые пять исполнений планируются под конкретное значение параметра, а с шестого планировщик может взять один общий план на все значения. Как читать оценки, на которых он строится, описано в статье про статистику ANALYZE (раздел про MCV и перекос), как читать сам план — в статье про EXPLAIN ANALYZE BUFFERS. Здесь — когда план переключается, чем это плохо при перекосе данных и как такую регрессию заметить.
Шесть исполнений одного prepared statement
Опыт 1: на каком запуске план переключается
Таблица заказов с одним «китом»: у клиента 1 половина заказов (1 млн из 2 млн), у остальных примерно по 20. К каждому заказу по две позиции, физический порядок позиций перемешан.
docker run -d --name dba-plan-regressions -e POSTGRES_PASSWORD=pg postgres:18 \
-c shared_preload_libraries=pg_stat_statements
docker exec -it dba-plan-regressions psql -U postgresCREATE EXTENSION pg_stat_statements;
CREATE TABLE orders AS
SELECT g AS id, CASE WHEN g % 2 = 0 THEN 1 ELSE 2 + (g % 100000) END AS customer_id, g % 500 AS amount
FROM generate_series(1, 2000000) g;
CREATE INDEX ON orders (customer_id);
CREATE TABLE items AS
SELECT g AS id, (g % 2000000) + 1 AS order_id, g % 7 AS qty FROM generate_series(1, 4000000) g ORDER BY random();
CREATE INDEX ON items (order_id);
ANALYZE orders;
ANALYZE items;
SET max_parallel_workers_per_gather = 0;
SET jit = off;
PREPARE j(int) AS
SELECT sum(i.qty) FROM orders o JOIN items i ON i.order_id = o.id WHERE o.customer_id = $1;
EXPLAIN (ANALYZE, BUFFERS OFF, TIMING OFF) EXECUTE j(1);
SELECT generic_plans, custom_plans FROM pg_prepared_statements;Последние две команды выполнены шесть раз подряд, потом с SET plan_cache_mode = force_custom_plan ещё раз. В выводе оставлены корень плана, соединение, время и счётчики (generic_plans | custom_plans):
Aggregate (cost=188384.01..188384.02 rows=1 width=8) (actual rows=1.00 loops=1)
-> Hash Join (cost=51371.68..183345.34 rows=2015466 width=4) (actual rows=2000000.00 loops=1)
Execution Time: 2240.922 ms
0 | 1
... запуски 2–4: тот же Hash Join, 2220.532 / 2307.393 / 2321.845 ms, счётчики 0 | 2, 0 | 3, 0 | 4
Aggregate (cost=188384.01..188384.02 rows=1 width=8) (actual rows=1.00 loops=1)
-> Hash Join (cost=51371.68..183345.34 rows=2015466 width=4) (actual rows=2000000.00 loops=1)
Execution Time: 2231.281 ms
0 | 5
Aggregate (cost=1612.29..1612.30 rows=1 width=8) (actual rows=1.00 loops=1)
-> Nested Loop (cost=0.86..1611.80 rows=198 width=4) (actual rows=2000000.00 loops=1)
Execution Time: 5624.553 ms
1 | 5
Aggregate (cost=188384.01..188384.02 rows=1 width=8) (actual rows=1.00 loops=1)
-> Hash Join (cost=51371.68..183345.34 rows=2015466 width=4) (actual rows=2000000.00 loops=1)
Execution Time: 2232.340 msПять исполнений вышли custom: планировщик видел значение 1 и по статистике MCV поставил Hash Join на 2 015 466 строк. На шестом generic_plans стал 1: план строился без значения, оценка 198 строк вместо 2 000 000, цена 1 612 — в 117 раз дешевле средней цены custom-плана. Исполнитель нагрузил его теми же 2 000 000 строк: по одной пробе индекса items на каждый из 1 000 000 заказов клиента вместо одного прохода и хеш-таблицы, 5,6 с вместо 2,2. Последняя запись — plan_cache_mode = force_custom_plan на том же prepared statement: снова Hash Join, 2,2 с.
Правило в коде занимает несколько строк, порог зашит константой:
/* Generate custom plans until we have done at least 5 (arbitrary) */
if (plansource->num_custom_plans < 5)
return true;А решение принимается сравнением стоимостей, не времён:
if (plansource->generic_cost < avg_custom_cost)
return false;
return true;generic_cost — стоимость generic-плана без параметров, avg_custom_cost — средняя стоимость уже построенных custom-планов с надбавкой за планирование: 1000 × cpu_operator_cost × (число отношений + 1), при cpu_operator_cost = 0,0025 это 2,5 единицы на отношение. Надбавка добавляется к стоимости custom-плана, поэтому generic побеждает, если он дешевле исполнения плюс перепланирования.
Опыт 2: порядок значений решает, переключится ли план
Тот же запрос, но первыми идут редкие клиенты (3, 5, 7, … по 20 заказов), а клиент 1 приходит последним. Новая сессия, тот же PREPARE j, восемь EXPLAIN (ANALYZE) EXECUTE j(<клиент>) со счётчиками. Оставлены узел, время и счётчики:
Aggregate (cost=800.37..800.38 rows=1 width=8) (actual rows=1.00 loops=1)
-> Nested Loop (cost=0.86..800.13 rows=98 width=4) (actual rows=40.00 loops=1)
Execution Time: 0.914 ms
3 | 0 | 1
... клиенты 5–13: тот же Nested Loop, 0.291–0.396 ms, счётчики 0 | 2 … 0 | 6
Aggregate (cost=800.37..800.38 rows=1 width=8) (actual rows=1.00 loops=1)
-> Nested Loop (cost=0.86..800.13 rows=98 width=4) (actual rows=40.00 loops=1)
Execution Time: 0.431 ms
15 | 0 | 7
Aggregate (cost=188384.01..188384.02 rows=1 width=8) (actual rows=1.00 loops=1)
-> Hash Join (cost=51371.68..183345.34 rows=2015466 width=4) (actual rows=2000000.00 loops=1)
Execution Time: 2371.250 ms
1 | 0 | 8Средний custom-план для редкого клиента стоит около 800, generic из опыта 1 — 1 612, то есть дороже. Generic не выбирается ни на шестом, ни на седьмом запуске (generic_plans остаётся 0), поэтому на восьмом клиент 1 получил собственный Hash Join, 2,4 с. Те же запросы, те же данные, а судьбу плана решила очерёдность значений, которая зависит от трафика. Воспроизвести такую регрессию на тестовой базе можно, только проиграв порядок обращений.
Опыт 3: устаревшая статистика после массовой загрузки
Регрессия бывает и без prepared statement: планировщик строит план по статистике, а она отстаёт от данных. В таблицу залили 100 000 строк нового клиента 999999. Порог автоанализа — 50 + 0,1 × reltuples (значения autovacuum_analyze_threshold и autovacuum_analyze_scale_factor по умолчанию), и загрузка в 5% таблицы его не достигает:
INSERT INTO orders SELECT 4000000 + g, 999999, g % 500 FROM generate_series(1, 100000) g;
SELECT pg_sleep(70);
SELECT n_mod_since_analyze, last_autoanalyze,
(SELECT reltuples::bigint FROM pg_class WHERE relname = 'orders') AS reltuples
FROM pg_stat_user_tables WHERE relname = 'orders';
EXPLAIN (ANALYZE, BUFFERS OFF, TIMING OFF, SUMMARY OFF) SELECT sum(amount) FROM orders WHERE customer_id = 999999;
ANALYZE orders;
EXPLAIN (ANALYZE, BUFFERS OFF, TIMING OFF, SUMMARY OFF) SELECT sum(amount) FROM orders WHERE customer_id = 999999; n_mod_since_analyze | last_autoanalyze | reltuples
---------------------+-------------------------------+-----------
100000 | 2026-10-27 21:29:16.656243+00 | 2000000
Aggregate (cost=195.86..195.87 rows=1 width=8) (actual rows=1.00 loops=1)
-> Index Scan using orders_customer_id_idx on orders (cost=0.43..195.73 rows=51 width=4) (actual rows=100000.00 loops=1)
Aggregate (cost=14019.18..14019.19 rows=1 width=8) (actual rows=1.00 loops=1)
-> Bitmap Heap Scan on orders (cost=1148.89..13766.14 rows=101220 width=4) (actual rows=100000.00 loops=1)Порог при reltuples = 2 000 000 — 200 050 изменений, а изменено 100 000. За 70 секунд (autovacuum_naptime — 1 минута) автоанализ не пришёл: last_autoanalyze остался прежним. Планировщик ждал 51 строку вместо 100 000 и выбрал Index Scan. После ANALYZE оценка 101 220 и Bitmap Heap Scan. Тот же механизм подводит и prepared statement: оценки custom- и generic-планов строятся по одной и той же устаревшей статистике.
Опыт 4: как это видно — pg_stat_statements и auto_explain
Запрос из опыта 1 выполнен через расширенный протокол (так ходят драйверы): 8 раз для клиента 1 и 100 раз для клиента 3. Статистика сброшена перед этим pg_stat_statements_reset():
SELECT calls, round(min_exec_time) AS min_ms, round(mean_exec_time::numeric, 1) AS mean_ms,
round(max_exec_time) AS max_ms, round(stddev_exec_time) AS stddev_ms
FROM pg_stat_statements WHERE query LIKE '%customer_id = $1%'; calls | min_ms | mean_ms | max_ms | stddev_ms
-------+--------+---------+--------+-----------
108 | 0 | 249.6 | 5419 | 980Среднее 250 мс выглядит терпимо, но максимум 5 419 мс в 22 раза выше среднего, а stddev_exec_time почти в четыре раза больше самого среднего. Такой разброс при одном queryid — признак того, что один текст запроса исполняется планами разной цены. Из pg_stat_statements не видно, каким планом и с каким параметром: queryid один на все значения. Это показывает auto_explain. Включён в сессии с порогом 3,5 секунды, шесть исполнений для клиента 1 подряд:
LOAD 'auto_explain';
SET auto_explain.log_min_duration = 3500;
SET auto_explain.log_analyze = on;
SET auto_explain.log_timing = off;
SET client_min_messages = log;LOG: duration: 5909.989 ms plan:
Query Text: SELECT sum(i.qty) FROM orders o JOIN items i ON i.order_id = o.id WHERE o.customer_id = $1
Query Parameters: $1 = '1'
Aggregate (cost=1612.29..1612.30 rows=1 width=8) (actual rows=1.00 loops=1)
-> Nested Loop (cost=0.86..1611.80 rows=198 width=4) (actual rows=2000000.00 loops=1)
-> Index Scan using orders_customer_id_idx on orders o (cost=0.43..375.80 rows=99 width=4) (actual rows=1000000.00 loops=1)
Index Cond: (customer_id = $1)Залогирован один запуск из шести: шестой, остальные пять быстрее порога. В плане видны Index Cond: (customer_id = $1) вместо литерала (признак generic-плана), параметр $1 = '1' и оценка rows=99 при факте 1 000 000.
Что из этого следует
- Переключение на generic происходит не по времени, а по оценке стоимости, поэтому его невозможно предсказать по логу медленных запросов: в опыте 1 оно случилось на шестом запуске, в опыте 2 не случилось за восемь.
- При перекосе (клиент с половиной таблицы) generic-план планируется на «средний» случай и бьёт по самым тяжёлым значениям. Если запрос в проде внезапно в 2–3 раза медленнее при неизменных данных, проверь, не пошёл ли он по generic-плану.
plan_cache_mode = force_custom_planвозвращает планирование под значение ценой повторного планирования на каждом запуске. Задавай его точечно, на роль или базу приложения с перекошенными таблицами, а не на весь сервер.- Сигнал в
pg_stat_statements—max_exec_timeиstddev_exec_timeна порядок вышеmean_exec_time. Причину, план и значение параметра показывает толькоauto_explainсlog_min_duration. - После загрузки меньше 10 % таблицы автоанализ не приходит, пока изменения не накопятся до порога: выполняй
ANALYZEсам сразу после загрузки. - В Oracle регрессию лечит закреплённый план (SQL plan baselines), в SQL Server — Query Store с принудительным планом. В ядре PostgreSQL такого механизма нет (порядок соединений можно закрепить расширением, см. подсказки pg_hint_plan), поэтому защита строится из статистики,
plan_cache_modeи наблюдения, как выше. Подробности — в документации Oracle и SQL Server.
Чек-лист
- Нашёл запрос, который стал медленнее без изменения данных: посмотри в
pg_stat_statementsmax_exec_time / mean_exec_timeиstddev_exec_timeпо егоqueryid. - Включи
auto_explain.log_min_durationна сессии или роли приложения и найди в логеIndex Cond: (... = $1): параметры вместо литералов говорят о generic-плане. - Проверь
generic_plansиcustom_plansвpg_prepared_statementsтой же сессии и сравни план под тяжёлое и редкое значение отдельно. - Если регрессия из-за generic-плана, выставь
plan_cache_mode = force_custom_planдля роли или базы и повтори замер. - После массовой загрузки выполни
ANALYZEвручную и убери стенд командойdocker rm -f -v dba-plan-regressions: без-vтом с данными остаётся на диске.