Анализ запросов в PostgreSQL. Часть 3. Операции объединения, группировки, сортировки и ограничения количества записей в PostgreSQL

В данной (последней) части статьи об анализе запросов в PostgreSQL я расскажу об операциях объединения таблиц (JOIN), группировки и сортировки данных.

Предыдущие части: часть 1, часть 2.

Операции объединения таблиц

Операции объединения обычно производятся только над двумя таблицами. Если у вас есть подзапросы или несколько Join-ов, то они будут выполняться в несколько последовательных операций объединения. Промежуточные результаты, например, подзапроса, также можно считать в данном случае за таблицу.

Nested Loops

Объединение двух таблиц, когда выбираются требуемые строки из первой таблицы, и для каждой строки результата идёт запрос ко второй таблице для выборки нужных данных.

Является самой медленной формой Join-а, но быстро выбирает первую строку. Если вторая подоперация медленна, то выполняется особенно медленно.

Hash Join/Hash

Объединение хешированием подразумевает загрузку строк-кандидатов из одной таблицы в хеш-таблицу (Hash в плане выполнения запроса), после чего они сравниваются с каждой строкой другой таблицы. Таким образом, устраняется недостаток объединения с использованием вложенных циклов (Nested loops), потому что внутренний цикл (тестирование выборки из первой таблицы с каждой строкой второй таблицы) теперь состоит из быстрых операций, благодаря хеш-таблице.

Может использоваться только для хешируемых типов данных и только для условия объединения через равенство значений столбцов.

Обычно идеально для объединения большой (первой) и маленькой таблиц (второй).

Медленно стартует из-за того, что нужно захешировать вторую (маленькую) таблицу.

Может быть слишком медленным, если оценка размеров таблиц была ошибочной.

Merge Join

Merge join объединяет два отсортированных списка. Надо помнить, что обе таблицы должны быть предварительно отсортированы.

Может применяться только для объединения с условием равенства значений столбцов.

Самый быстрый join, особенно для больших объемов данных.

Поскольку для выполнения требуются отсортированные таблицы, то может потребоваться предварительная медленная операция сортировки или индексные сканирования.

Append

Операция Append применяется там, где нужно сделать объединение через Union. Результаты одной операции/таблицы просто присоединяются к другой. Затрат на операцию нет почти никаких, и общие затраты по её итогам будут примерно равны сумме затрат всех подопераций.

Materialize

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

Setop (Intersect, Intersect All, Except, Except All)

Существует четыре Setop-операции: Setop Intersect, Setop Intersect All, Setop Except и Setop Except All. Они выпоняются, когда планировщик обнаруживает команды INTERSECT, INTERSECT ALL, EXCEPT или EXCEPT ALL, соответственно.

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

Группировка и сортировка

Sort / Sort Key

Сортировка по ключу, указанному в Sort Key. Для выполнения требует больших объемов памяти. Неконвейеризованная операция.

Обычно применяется для ORDER BY, DISTINCT, GROUP BY, UNION и merge join-ов.

Время, затрачиваемое на начало операции, обычно очень велико.

Если памяти (work_mem) хватает для выполнения операции, то будет отрабатывать быстрее, иначе используется внешний диск, и операция проходит медленно.

GroupAggregate

Агрегация предварительно отсортированного набора данных. Конвейеризованная операция.

HashAggregate

Агрегация с использованием временной хеш-таблицы. Не нуждается в предварительной сортировке данных, но зато использует большие объёмы памяти. Результат операции не упорядочен. Неконвейеризованная операция.

Ограничение по количеству строк в результате

Limit

Ограничение по количеству строк, накладываемое на нижестоящие операции. Эффективность зависит от типа этих операций. Для неконвейеризованных (как сортировка, например) эффективность страдает очень сильно.

Используется для LIMIT и OFFSET, а также для min() и max(), если в запросе нет WHERE.

Записи, пропускаемые по OFFSET, тем не менее, выбираются из таблицы, над ними проделываются все операции, и лишь затем они отбрасываются.

Если вместе с Limit применяется сортировка, то она может быть внутренне оптимизирована.

WindowAgg

Применение оконных функций.