Greenplum. План запроса. Рекомендации по оптимизации (draft). 

Решил законспектировать основные вещи по сабжу из пары учебных курсов.

Перед тем, как смотреть план запроса, стоит посмотреть на сам запрос. Возможно, в глаза сразу бросится код, который выглядит подозрительно неоптимальным. Например, бывает, что остаются отладочные артефакты в коде, типа сортировки в подзапросах (order by). Или ошибочный union вместо union all. Или distinct по большому количеству полей. Соединения таблиц по условию неравенства или OR на ключи join. Проход таблицы более 1 раза на ровном месте (мне такие запросы аналитики регулярно подкидывают, причём разные люди, как по шаблону пишут). И тд и тп.

На что обратить внимание в плане запроса:

• Узлы с наибольшей добавочной стоимостью
• Узлы с наибольшим временем выполнения (при explain analyze)
• Большое количество возвращаемых строк на сегмент
• Nested Loop join при большом количестве строк
• Hash join при небольшом количестве строк
• Отсутствие Partition Selector при обращении к партиционированной таблице
• Операторы Redistribute Motion, Broadcast Motion (Появление Redistribute или Broadcast Motion перед операторами соединения значит, что ключ распределения таблицы не совпадает с ключом join. Иногда это говорит о неоптимальной физической модели данных). Оператор Gather Motion, появившийся в середине плана (Это значит, что данные со всех сегментов обрабатываются на мастере, который значительно уступает по производительности множеству сегментов кластера. В норме Gather Motion появляется только в самом конце запроса (наверху плана) для вывода результатов запроса через мастер клиенту).
• Наличие факта создания спилл-файлов (в выводе команды EXPLAIN ANALYZE один или несколько слайсов запроса отмечены звёздочкой, например * (slice1))
• rows=1 в операторах Seq Scan, Dynamic Seq Scan, Index Scan и Bitmap Heap Scan
• Несколько Seq Scan одной таблицы (Seq Scan on my_table ... Seq Scan on my_table my_table_1 и тп)

Планировщик может построить неоптимальное дерево плана запроса по разным причинам:

• Отсутствующая или неактуальная статистика, из-за которой планировщик неверно оценивает стоимость плана.
• Неоптимальная физическая модель данных, из-за которой планировщику приходится добавлять тяжёлые операторы Redistribute / Broadcast Motion и неэффективно читать данные из таблиц.
• Сам SQL-запрос, в котором логика получения результата может быть слишком сложной: планировщик не может упростить логику самостоятельно и выбирает среди заведомо неоптимальных вариантов.

Как можно ускорить тяжёлый запрос? Или переписать сам запрос, или оптимизация окружения выполнения запроса. Или и то, и то.

По рефакторингу запроса это скорее магия, чем последовательность чётких инструкций. А по второму варианту - это актуализация статистики, изменение физической модели и, возможно, изменение типа планировщика.

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