Блог

У меня есть запрос, который выполняется намного медленнее (~ 5 минут), когда я запускаю его с enable_nestloop по умолчанию= true и enable_nestloop=false (~ 10 секунд).

Объясните результат анализа для обоих случаев:

Обработайте nestloop=true — http://explain.depesz.com/s/nkj0 (~5 минут) Обработайте nestloop=false — http://explain.depesz.com/s/wBM (~ 10 секунд)

На другой, немного более медленной машине копирование базы данных и оставление enable_nestloop=true по умолчанию занимает ~ 20 секунд.

Машина B nestloop=true — (~ 20 секунд)

Во всех приведенных выше случаях я убедился, что перед выполнением запросов я провел АНАЛИЗ. Параллельно не было никаких других запросов.

На обеих машинах работает Postgres 8.4. На машине A работает 32-разрядная версия Ubuntu 10.04, а на машине B работает 32-разрядная версия Ubuntu 8.04.

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

1. Что я могу сделать, чтобы заставить планировщик делать то, чего я добился, установив enable_nestloop=false, не прибегая к вводу чего-то вроде материализованных представлений?
2. Из проведенного мной исследования кажется, что причина, по которой планировщик выбирает, казалось бы, неоптимальный запрос, заключается в огромной разнице между оценочными и фактическими строками. Как я могу приблизить эту цифру?
3. Если я должен переписать запрос, что я должен изменить?
4. Почему кажется, что планировщик поступает правильно для машины B. Что я должен сравнивать на обеих машинах?

Если планировщик запросов выбирает неоптимальные планы запросов, то, скорее всего, у него неполная или вводящая в заблуждение информация для работы.

См. Эту вики-страницу PostgreSQL о настройке сервера. Особенно обратите внимание на главы о random_page_cost и default_statistics_target .
Также прочитайте соответствующие главы в руководстве по статистике, используемой планировщиком, и константы затрат планировщика.

Более конкретно, это может помочь увеличить значение statistics target для следующих столбцов:

 ALTER TABLE postgres.products ALTER COLUMN id SET STATISTICS 1000;
ALTER TABLE postgres.sales_orders ALTER COLUMN retailer_id SET STATISTICS 1000;
ALTER TABLE postgres.sales_orders ALTER COLUMN company_id SET STATISTICS 1000;

ALTER TABLE goods_return_notes ALTER COLUMN retailer_id SET STATISTICS 1000;
ALTER TABLE goods_return_notes ALTER COLUMN company_id SET STATISTICS 1000;

ALTER TABLE retailer_category_leaf_nodes ALTER COLUMN tree_left SET STATISTICS 1000;
ALTER TABLE channels ALTER COLUMN principal_id SET STATISTICS 1000;
 

Они участвуют в фильтрах, приводящих к

огромная разница между расчетными и фактическими строками.

Есть и другие. Проверьте каждый столбец, где планировщик сильно отклоняется от оценки. Значение по умолчанию — всего 100. Имеет смысл только для таблиц с>> 1000 строк. Поэкспериментируйте с настройкой. После этого запустите ANALYZE таблицы, чтобы изменения вступили в силу.

Это также может помочь создать частичный индекс postgres(sales_orders.retailer_id) WHERE retailer_id IS NOT NULL (в зависимости от того, насколько распространены значения NULL).

Еще одна вещь, которая может вам помочь, — это обновление до последней версии 9.1. В этой области был внесен ряд существенных улучшений.

Оказывается, переписывание запроса было лучшим решением. Запрос был написан таким образом, что он в значительной степени опирался на левые соединения и имел много соединений. Я сгладил его и уменьшил левые соединения, используя свои знания о характере соединения данных в таблицах, к которым присоединялся запрос. Я думаю, эмпирическое правило заключается в том, что если планировщик выдает действительно дрянные оценки, может быть лучший способ написания запроса.

Это может быть полезно для чтения: учебное пособие по PostgreSQL о явных соединениях.

Планировщик запросов пытается проанализировать порядок соединения, чтобы найти наилучший порядок для объединения.

Я видел, что в вашем запросе было не менее 15 соединений. Количество возможных заказов на объединение увеличивается как факториал (n!). Поэтому планировщику запросов неразумно пытаться найти наилучший порядок соединения, если есть 15 соединений — ему пришлось бы смотреть на 15! = 1307674368000 разных планов.

Поэтому вместо этого он использует оптимизатор генетических запросов. См. Планирование запросов: параметры оптимизатора генетических запросов. Параметр «geqo_threshold» определяет, сколько соединений должно присутствовать, чтобы планировщик запросов использовал оптимизатор генетических запросов.

Таким образом, планировщик PostgreSQL просматривает только небольшую часть возможных вариантов и пытается найти лучший (случайным образом). Поэтому каждый раз, когда вы запускаете АНАЛИЗ, он может предложить лучший план.

Я думаю, что в целом, если у вас так много таблиц для ОБЪЕДИНЕНИЯ, вам лучше поступить так, как вы это делали: переписать запрос в оптимальном порядке соединения.

Обычно есть только одна причина для разных планов для одних и тех же данных и одинаковых запросов на двух серверах с одинаковым PostgreSQL. Это другая конфигурация — в основном значение work_mem . Хэш-соединение обычно выполняется быстрее, но требует много доступной памяти.