План запросов Postgres постоянно меняется - иногда выполнение запроса занимает минуту, а иногда не завершается

Question

У меня огромный SQL-запрос. Вероятно, 15-20 столов. Есть 6-7 подзапросов, которые снова объединяются. Этот запрос чаще всего выполняется за минуту и ​​возвращает 5 миллионов записей.

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

Иногда план запроса изматывается, а затем он никогда не заканчивается. Я провожу анализ вакуума каждую ночь на таблицах, участвующих в запросе. Work_memory в настоящее время установлен на 200 МБ. Я попытался увеличить это до 2 ГБ. Я не сталкивался с ошибкой запроса, когда work_memory был 2 ГБ. Но когда я уменьшил его и запустил запрос, он запутался. Теперь, когда я увеличил его до 2 ГБ, запрос все еще запутался. Это как-то связано с тем, что план запроса не обновляется с новой настройкой? Я попытался отказаться от плана на моей сессии.

На данный момент я могу думать только о work_mem и вакуумном анализе. Любые другие факторы, которые могут повлиять на бесперебойно выполняемый запрос, который возвращает результаты за минуту и ​​ничего не возвращает?

Дайте мне знать, если вам нужно больше информации о каких-либо настройках? или сам запрос? Я тоже могу вставить план... Но запрос и план или слишком большой для вставки здесь..

Answer 1

Если есть больше чем geqo_treshold (обычно 12) записей в таблице диапазонов, сработает генетический оптимизатор, что часто приводит к случайному поведению, как описано в вопросе. Вы можете решить это:

• увеличение geqo_limit
• переместите часть вашей таблицы референс в CTE. Если у вас уже есть несколько подзапросов, продвиньте один (или несколько) из них в CTE. Это своего рода черное искусство, позволяющее идентифицировать кластеры таблиц в вашем запросе, которые поместятся в компактном CTE (с относительно небольшим числом кортежей результатов и не слишком большим количеством ключевых ссылок на внешний запрос).

Установка слишком высокого значения geqo_treshold (вероятно, слишком высокое значение 20) приведет к тому, что планировщику потребуется много времени для оценки всех планов. (количество планов увеличивается в основном экспоненциально по сравнению с количеством RTE) Если вы ожидаете, что вашему запросу потребуется несколько минут для выполнения, несколько секунд времени планирования, вероятно, не принесут вреда.