В обсуждении под статьями "СУБД Tantor Postgres 18: обзор улучшений для 1С" и "В погоне за APDEX-ом, или как создать HighLoad на недорогом серверном железе" чрезвычайно уважаемые люди задались вопросом про *_collapse_limit и его отношение к geqo_threshold. В сообществе обсуждали эти параметры в большом топике, в этой статье коротко описано то, к чему там пришли.

Число 20 взялось из поста Роберта Хааса 2009 года по то, можно ли что-то сделать с параметрами *_collapse_limit в PostgreSQL версии 8.5, которая в то время выходила. Техника добавления OFFSET 0 взялась из того же обсуждения.

В 2009 году Хаас предложил увеличить geqo_threshold до 16 и *_collapse_limit до 12 (сделать булевыми или вообще убрать), и спросил мнение практиков. GEQO появился в PostgreSQL 19 февраля 1997 года.

Практик, Кевин Гриттнер, написал: у нас отключен geqo, и мы установили _collapse_limit на 20. Вероятно, нам следует просто установить их на несколько сотен и не ждать, пока какой-нибудь запрос с более чем 20 таблицами начнет работать плохо, но я не уверен, что у нас пока есть такие запросы. По его опыту, в тех случаях, когда увеличение _collapse_limit хоть как-то влияло на результат, это всегда приводило к созданию плана, который экономил больше времени, чем дополнительное время на планирование.

Роберт Хаас выразил суть параметров: параметры join_collapse_limit и from_collapse_limit ограничивают порядок объединения. Если все оценки верны, установка их значения < бесконечности  либо оставит планы неизменными, либо ухудшит их. Если же они улучшат планы, значит, оценки неверны, и ограничение порядка объединения мешает планировщику учитывать случаи, которые действительно наносят ущерб. Но это в основном дело случая.

То есть что 20, что 100 разницы нет, если оценки верны. Сделать булевыми хотели, чтобы можно было сделать порядок соединения точно таким, как в запросе.

Том Лейн написал, что *_collapse_limit были добавлены исключительно на основе предположения, что кому-то они могут пригодиться. Том не против удаления join_collapse_limit или сведения его к логическому значению.

Альваро Эррера никогда не сталкивался со случаями, когда collapse_limits были бы  полезными инструментами.

Ноа Миш донес миру то, что добавление статистически неразличимых столбцов на PostgreSQL 8.3 он наблюдает увеличение времени планировании и памяти в 4 раза (В PostgreSQL 18 в 3.x раз или, как абсолютно правильно пишет Андрей Лепихов "сверхэкспоненциально") и привел пример соединения 16 таблиц. А для запросов с 19-21 соединениями, пришлось отключить детерминизм (включить GEQO),так как планирование длилось более, чем 600 секунд и использовало на этапе планирвания более 10 Гб памяти. Ноа Миш никогда не сталкивался с ситуацией, когда значение, отличное от 1 или "бесконечность", для *_collapse_limit, оказалось бы оптимальным.

Том Лейн сказал, что DBA любят предсказуемость, поэтому, значения по-умолчанию, для join_collapse_limit и from_collapse_limit установлены так, чтобы предотвратить использование GEQO.  Если убрать *_collapse_limit, то посыпятся жалобы, что запрос выполняется, то быстро то медленно, если только не отключить GEQO и сослался на недавнюю жалобу с соединением 17 таблиц.

Мнение Тома подтвердил Кевин Гриттнер (как представитель DBA в обсуждении), что главная причина, по которой он старается избегать GEQO, в том, что он никогда не знает, GEQO может редко, но метко, создать кривой план, что приведет к загадочным жалобам пользователей, и эти жалобы могут отнять много времени на разбор причин.

Андреас Фройнд потратил несколько дней и создал сложный тесткейс из реальной системы, который и опубликовал в топике. Любой может его попробовать. Я воспроизвёл на 18 версии тест Андреаса:

\timing on \\
SET SEARCH_PATH = test_data, test_view;
SET geqo = off;
SET join_collapse_limit = 11;
--- 2617.020 ms Memory: used=597053kB  allocated=599284kB
SET join_collapse_limit = 12;
--- 3205.557 ms used=759366kB  allocated=763124kB
SET join_collapse_limit = 13;
--- 3266.530 ms used=769482kB  allocated=771316kB
SET join_collapse_limit = 14;
--- 7079.614 ms used=1090170kB  allocated=1091380kB
SET join_collapse_limit = 15;
--- 15926.589 ms used=2179803kB  allocated=2181044kB
SET join_collapse_limit = 16;
--- 13124.880 ms used=2210545kB  allocated=2213812kB
SET join_collapse_limit = 17;
--- 19529.137 ms used=2793452kB  allocated=2796724kB
SET join_collapse_limit = 18;
--- 63925.147 ms used=8861636kB  allocated=8869044kB
SET join_collapse_limit = 19;
--- 56204.530 ms used=8861646kB  allocated=8869044kB
SET join_collapse_limit = 20;
--- 85993.891 ms used=11677634kB  allocated=11679733kB
SET geqo = off;
SET join_collapse_limit = 21;
--- следующий скачок использования памяти и времени.

Оказалось, что на тесте Андреаса Фройнда, память и время выделялось скачкообразно. Следующий скачок, с 11Гб памяти, которая выделялась серверным процессом на время планирования, происходил после join_collapse_limit = 20. Нехватка памяти выглядит, как подвисание системы (из-за кэшей) и можно подумать, что якобы процессор сильно грузится.

Собственно отсюда и взялось число 20. Устанавливать его стали, вероятно, из предположения, что при отключенном GEQO систсема хотя бы не подвиснет и памяти хватит. То есть *_collapse_limit = 20 разумная рекомендация чисто, чтобы защититься от geqo = off.

Но пример Андреаса и Кевина для типичных бизнес-запросов. Интересен граничный случай, подсказанный Ноа Миш. Создадим запрос для join_colapse_limit со статистически неразличимыми столбами:

\timing on \\
SET geqo = on;
SET geqo_threshold = 16;
SET join_collapse_limit = 16;
EXPLAIN (analyze, memory, settings) 
with t as (select 1 a)
select 1 from t t0 natural join t t1 natural join t t2 natural join t
t3 natural join t t4 natural join t t5 natural join t t6 natural join t t7
natural join t t8 natural join t t9 natural join t t10 natural join t t11
natural join t t12 natural join t t13 natural join t t14;
Settings: geqo_threshold = '15', join_collapse_limit = '15' 
Planning: 
  Memory: used=235kB  allocated=256kB 
Planning Time: 33.668 ms 
Execution Time: 0.328 ms

Settings: geqo_threshold = '15', join_collapse_limit = '14' 
Planning: 
  Memory: used=5155822kB  allocated=5162241kB 
Planning Time: 18779.568 ms 
Execution Time: 0.410 ms

Settings: geqo_threshold = '15', join_collapse_limit = '13' 
Planning: 
  Memory: used=1680916kB  allocated=1688193kB 
Planning Time: 5986.123 ms 
Execution Time: 0.409 ms

Settings: geqo_threshold = '15', join_collapse_limit = '12' 
Planning: 
  Memory: used=546392kB  allocated=549153kB 
Planning Time: 1937.615 ms 
Execution Time: 0.395 ms

То есть худший случай, когда *_collapse_limit меньше geqo_threshold на единицу.
При geqo_threshold = '16', join_collapse_limit = '15' выделяется и используется 15Гб памяти, что довольно много.
Planning Time: 41241.285 ms Memory: used=15761345kB

Рекомендация *_collapse_limit = 20 разумна. Если не отключать GEQO и не менять geqo_threshold, то его даже можно увеличить и установить 30, 40. На это указывает второй график в статье Павла Толмачёва. Хорошие картинки также есть в статье.

Остался ещё один вопрос (кто убил мистера Нолестро): нужно ли ставить *_collapse_limit больше, чем geqo_threshold, который оставляют 12. Да, нужно. В 1С была проблема, когда в конец  запроса с большим числом соединений добавалялся "ANDt1.col1 IN ( VALUES(..". Проблема описана в статье Александра Симонова. Андрей Лепихов и Алёна Рыбакина создали патч (ревьюверы Иван Кушнаренко и Александр Коротков), который чрезвычайно быстро был закоммичен Томом Лейном. Эта проблема ушла, но *_collapse_limit установленные в значение, большее geqo_threshold, позволяют планировщику соединить условия из начала и конца списка соединений или предикатов. Генерирует ли 1С сейчас запросы, где это нужно я не знаю, но до патча на VALUES генерировал. Дает ли минусы установка *_collapse_limit в чуть меньшее значение, чем 20? По графику из статьи Павла Толмачева почти нет. Планы не улучшит (если только нет ошибок планировщика).

Я воспроизвёл на 18 версии тест Андреаса: