Postgresql join_collapse_limit и время для планирования запросов

Question

Я только что обнаружил join_collapse_limit мешает планировщику PostgreSQL найти гораздо лучший порядок соединения. В моем случае увеличение предела до 10 (по умолчанию 8) позволило планировщику улучшить время поиска с ~30 секунд до ~1 мс, что гораздо более приемлемо.

Документация предполагает, что установка этого "слишком высокого" может привести к длительному времени планирования, но не предоставляет даже "практического правила" о том, какой длительности может быть этап планирования для различных значений. Я понимаю, что общая проблема экспоненциальна во времени, но я не могу найти способ определить фактическое время планирования, если это не просто время, необходимое для выполнения ANALYZE SELECT ..., Если это так, я считаю, что значение по умолчанию 8 для современных компьютеров довольно низкое, поскольку я не могу обнаружить разницы в скорости планирования между 8 и 10.

Вопросы:

1) Как можно измерить время планирования?

2) Приблизительно, как высоко можно join_collapse_limit быть и все еще ожидать, что планирование займет менее пары сотен миллисекунд?

Answer 1

1) Как можно измерить время планирования?

Новая версия PostgreSQL 9.4 (еще не выпущенная на момент написания этой статьи) добавит время планирования в EXPLAIN а также EXPLAIN ANALYZEи вы сможете их использовать.

Для более старых версий ваше предположение верно, лучший способ определить время планирования - выполнить простое EXPLAIN (нет ANALYZE) и проверить, сколько времени прошло, в psql Вы можете сделать это, включив \timing (Я обычно делаю это в ~/.psqlrc).

2) Приблизительно, как высоко может быть join_collapse_limit и все еще ожидать, что планирование займет менее пары сотен миллисекунд?

Команда хакеров PostgreSQL уже обсуждала вопрос о повышении его до более высоких значений. Но похоже, что они не могли гарантировать, что это будет хорошо для всех случаев.

Проблема заключается в том, что планирование, чтобы найти лучший порядок соединения для N столы занимает O(N!) (факторный) подход. И так, числа повышений очень высоки, вы можете просто увидеть это с помощью следующего запроса:

$ SELECT i, (i)! AS num_comparisons FROM generate_series(8, 20) i;
 i  |   num_comparisons   
----+---------------------
  8 |               40320
  9 |              362880
 10 |             3628800
 11 |            39916800
 12 |           479001600
 13 |          6227020800
 14 |         87178291200
 15 |       1307674368000
 16 |      20922789888000
 17 |     355687428096000
 18 |    6402373705728000
 19 |  121645100408832000
 20 | 2432902008176640000
(13 rows)

Как вы можете видеть, при значении по умолчанию, равном 8, мы делаем не более 40 тыс. Сравнений, а 10, предложенных вами, переводят в 3М, что не так уж много для современных компьютеров, но следующие значения начинают становиться слишком большими, они просто увеличиваются. слишком быстро, 20 просто безумие (21! даже не соответствует 64-битному целому числу).

Конечно, иногда вы можете установить для него более высокие значения, например 16, которые могут (теоретически) составить до 20 триллионов сравнений, и при этом иметь очень хорошее время планирования, потому что PostgreSQL сокращает некоторые пути при планировании и не нуждается в этом. всегда проверять все заказы, но если предположить, что так будет всегда, и установить такие высокие значения по умолчанию, это не выглядит хорошим подходом для меня. В будущем может возникнуть неожиданный запрос, который заставит его проверить все заказы, и тогда у вас будет только один запрос, который отключит ваш сервер.

Исходя из моего опыта, я принимаю значение 10 в качестве значения по умолчанию для любой установки на хороших серверах, некоторые из них я даже использую 12. Я рекомендую вам установить его на 10, если хотите, и иногда, попробуйте установить его выше (Я бы не пошел дальше 12) и продолжал следить (внимательно), чтобы увидеть, как он себя ведет.

Answer 2

Эта комбинация показала себя хорошо для меня в случае более 30 объединений, сгенерированных иерархиями NHibernate, создавая гораздо лучшие планы, чем с настройками по умолчанию.

      SET geqo = on;
SET geqo_threshold = 12;

SET from_collapse_limit = 40;
SET join_collapse_limit = 40;

В таких случаях часто создаются неоптимальные планы запросов, если только пределы свертывания не установлены на большее число, чем максимальное ожидаемое количество соединений. Хитрость в том, что при использовании таких высоких значений для from_collapse_limitа также join_collapse_limitвы также должны использовать с достаточно низким geqo_thresholdчтобы избежать (N!) проблемы.

Это кажется полной противоположностью тому, что рекомендует делать документация:

Установка этого значения на geqo_threshold или более может привести к использованию планировщика GEQO, что приведет к неоптимальным планам.

Но без , или слишком высокое значение его порога, даже такое низкое значение, как 16, приведет к тому, что этап планирования займет более 10 секунд и не сможет выбрать хороший план. В этом случае «неоптимальный» план, предоставляемый за ~100 мс, более желателен, чем оптимальный план, требующий часов процессорного времени для вычислений.

Те же настройки могут решить не использовать geqoи застрять в бесконечном планировании - ваш пробег может варьироваться.