kb/planner ── Планировщик и оптимизатор ── advanced

Порядок соединений, GEQO и кеш плана

Число порядков соединения растёт быстрее факториала; до geqo_threshold (12) планировщик ищет порядок динамическим программированием, дальше - генетическим GEQO. Кеш плана: после ~5 вызовов custom может стать generic.

view as markdownaka: join-order, geqo, generic-plan, plan-cache

Порядок соединений

Способов соединить N таблиц - больше факториала от N. На 4-5 таблицах перебор реален, на 10 - миллионы вариантов. Планировщик ищет порядок динамическим программированием, ограничивая себя двумя порогами:

sql

SHOW join_collapse_limit;  -- 8: сколько JOIN волен переставлять

SHOW geqo_threshold;       -- 12: с какого числа таблиц включить GEQO

Сверх join_collapse_limit порядок берётся как написан; join_collapse_limit = 1 фиксирует порядок вручную. При числе таблиц от geqo_threshold включается GEQO - генетический поиск: быстрый, но недетерминированный и без гарантии оптимума.

Кеш плана

Подготовленное выражение разбирается один раз. Сначала строится custom plan под значения параметров; после ~5 вызовов планировщик может перейти на generic plan (без привязки к значению), если тот не дороже в среднем.

sql

PREPARE q(int) AS SELECT * FROM tickets WHERE flight_id = $1;

SELECT name, generic_plans, custom_plans FROM pg_prepared_statements;

Подводный камень generic plan

На перекошенных данных (значения с разной селективностью) generic plan усредняет и становится плохим для части вызовов. Симптом - «вдруг замедлилось» при том же тексте. Лечение:

sql

SET plan_cache_mode = force_custom_plan;

Время планирования видно в EXPLAIN ANALYZE (Planning Time) и EXPLAIN (MEMORY). Выбор алгоритма соединения - в join-algorithms.

§ команды

bash

SET join_collapse_limit = 1;

Зафиксировать порядок соединений как в запросе

bash

SELECT name, generic_plans, custom_plans FROM pg_prepared_statements;

Сколько раз использован generic и custom план

bash

SET plan_cache_mode = force_custom_plan;

Всегда планировать под значения - спасает от плохого generic plan

§ см. также

kb/planner ── Планировщик и оптимизатор ── advanced

Порядок соединений, GEQO и кеш плана

view as markdownaka: join-order, geqo, generic-plan, plan-cache

Порядок соединений

sql

SHOW join_collapse_limit;  -- 8: сколько JOIN волен переставлять

SHOW geqo_threshold;       -- 12: с какого числа таблиц включить GEQO

Кеш плана

sql

PREPARE q(int) AS SELECT * FROM tickets WHERE flight_id = $1;

SELECT name, generic_plans, custom_plans FROM pg_prepared_statements;

Подводный камень generic plan

sql

SET plan_cache_mode = force_custom_plan;

Время планирования видно в EXPLAIN ANALYZE (Planning Time) и EXPLAIN (MEMORY). Выбор алгоритма соединения - в join-algorithms.

§ команды

bash

SET join_collapse_limit = 1;

Зафиксировать порядок соединений как в запросе

bash

SELECT name, generic_plans, custom_plans FROM pg_prepared_statements;

Сколько раз использован generic и custom план

bash

SET plan_cache_mode = force_custom_plan;

Всегда планировать под значения - спасает от плохого generic plan