В обсуждении под статьями «СУБД 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=599284kBSET join_collapse_limit = 12;--- 3205.557 ms used=759366kB allocated=763124kBSET join_collapse_limit = 13;--- 3266.530 ms used=769482kB allocated=771316kBSET join_collapse_limit = 14;--- 7079.614 ms used=1090170kB allocated=1091380kBSET join_collapse_limit = 15;--- 15926.589 ms used=2179803kB allocated=2181044kBSET join_collapse_limit = 16;--- 13124.880 ms used=2210545kB allocated=2213812kBSET join_collapse_limit = 17;--- 19529.137 ms used=2793452kB allocated=2796724kBSET join_collapse_limit = 18;--- 63925.147 ms used=8861636kB allocated=8869044kBSET join_collapse_limit = 19;--- 56204.530 ms used=8861646kB allocated=8869044kBSET join_collapse_limit = 20;--- 85993.891 ms used=11677634kB allocated=11679733kBSET 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 tt3 natural join t t4 natural join t t5 natural join t t6 natural join t t7natural join t t8 natural join t t9 natural join t t10 natural join t t11natural 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 msSettings: geqo_threshold = '15', join_collapse_limit = '14' Planning: Memory: used=5155822kB allocated=5162241kB Planning Time: 18779.568 ms Execution Time: 0.410 msSettings: geqo_threshold = '15', join_collapse_limit = '13' Planning: Memory: used=1680916kB allocated=1688193kB Planning Time: 5986.123 ms Execution Time: 0.409 msSettings: 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 версии тест Андреаса:
\timing on \\SET SEARCH_PATH = test_data, test_view;SET geqo = off;SET join_collapse_limit = 11;--- 2617.020 ms Memory: used=597053kB allocated=599284kBSET join_collapse_limit = 12;--- 3205.557 ms used=759366kB allocated=763124kBSET join_collapse_limit = 13;--- 3266.530 ms used=769482kB allocated=771316kBSET join_collapse_limit = 14;--- 7079.614 ms used=1090170kB allocated=1091380kBSET join_collapse_limit = 15;--- 15926.589 ms used=2179803kB allocated=2181044kBSET join_collapse_limit = 16;--- 13124.880 ms used=2210545kB allocated=2213812kBSET join_collapse_limit = 17;--- 19529.137 ms used=2793452kB allocated=2796724kBSET join_collapse_limit = 18;--- 63925.147 ms used=8861636kB allocated=8869044kBSET join_collapse_limit = 19;--- 56204.530 ms used=8861646kB allocated=8869044kBSET join_collapse_limit = 20;--- 85993.891 ms used=11677634kB allocated=11679733kBSET 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 tt3 natural join t t4 natural join t t5 natural join t t6 natural join t t7natural join t t8 natural join t t9 natural join t t10 natural join t t11natural 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 msSettings: geqo_threshold = '15', join_collapse_limit = '14' Planning: Memory: used=5155822kB allocated=5162241kB Planning Time: 18779.568 ms Execution Time: 0.410 msSettings: geqo_threshold = '15', join_collapse_limit = '13' Planning: Memory: used=1680916kB allocated=1688193kB Planning Time: 5986.123 ms Execution Time: 0.409 msSettings: 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? По графику из статьи Павла Толмачева почти нет. Планы не улучшит (если только нет ошибок планировщика).
ссылка на оригинал статьи https://habr.com/ru/articles/1038762/