Оптимизация запросов
Материализация
16
Авторские права
© Postgres Professional, 2019–2024
Авторы: Егор Рогов, Павел Лузанов, Павел Толмачев, Илья Баштанов
Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал)
Использование материалов курса
Некоммерческое использование материалов курса (презентации,
демонстрации) разрешается без ограничений. Коммерческое
использование возможно только с письменного разрешения компании
Postgres Professional. Запрещается внесение изменений в материалы
курса.
Обратная связь
Отзывы, замечания и предложения направляйте по адресу:
Отказ от ответственности
Компания Postgres Professional не несет никакой ответственности за
любые повреждения и убытки, включая потерю дохода, нанесенные
прямым или непрямым, специальным или случайным использованием
материалов курса. Компания Postgres Professional не предоставляет
каких-либо гарантий на материалы курса. Материалы курса
предоставляются на основе принципа «как есть» и компания Postgres
Professional не обязана предоставлять сопровождение, поддержку,
обновления, расширения и изменения.
2
Темы
Материализация в запросах
Временные таблицы
Управление порядком соединений
Материализованные представления
3
Материализация —
сохранение промежуточного
набора строк
для последующего
многократного
использования
Materialize
Узел Materialize
Join
Scan Scan
Nested Loop
Scan
work_mem
temp_file_limit
Материализацией называется сохранение промежуточного набора
строк с целью повторного использования, как правило, многократного.
Сохранять набор можно на разных уровнях: для конкретного запроса,
на уровне сеанса или на уровне базы данных.
Обычно узлы запроса передают друг другу данные по принципу
конвейера: когда алгоритму, выполняющемуся в узле плана, требуется
очередная строка набора, узел обращается к одному из дочерних узлов
за следующей порцией. Однако в некоторых случаях исполнителю
запроса имеет смысл (а иногда и необходимо) сразу получить все
строки, сохранить их и иметь возможность обращаться к сохраненному
результату повторно. Такое сохранение выполняет узел Materialize.
Строки сохраняются в оперативной памяти, пока их объем
укладывается в ограничение work_mem. При превышении этого
ограничения все строки сбрасываются во временный файл и при
необходимости читаются оттуда. Объем всех временных файлов
одного сеанса ограничен значением параметра temp_file_limit.
Например, на слайде верхний узел Nested Loop выполняет соединение
вложенным циклом, но к внутреннему набору данных нет
эффективного доступа: он вычисляется с помощью другого соединения.
Чтобы не повторять многократно вложенное соединение, его результат
можно материализовать.
4
Материализация —
сохранение промежуточного
набора строк
для последующего
многократного
использования
Узел Materialize
Materialize
Join
Scan Scan
Nested Loop
Scan
work_mem
temp_file_limit
После того как результат вложенного соединения материализован, узел
Nested Loop будет обращаться к уже готовому набору строк.
6
CTE
CTE Scan
Материализация CTE
Join
Scan 1 Scan 2
Join
Scan 3
Join
Scan 3 Scan 2
Join
Scan 1
материализация
на усмотрение планировщика
или разработчика
вариант без
материализации
Общие табличные выражения (common table expression, CTE), они же
подзапросы WITH, — прекрасный способ структурировать запрос
и сделать его более понятным. В отличие от обычных подзапросов,
использование CTE не приводит к большой вложенности.
По возможности планировщик раскрывает (не материализует)
подзапросы CTE. Это позволяет ему выбирать оптимальный порядок
соединений. По умолчанию подзапрос материализуется в следующих
ситуациях:
●
Основной запрос обращается к подзапросу несколько раз —
чтобы не повторять вычисления.
В этом случае материализацию можно отменить, указав
предложение AS NOT MATERIALIZED.
●
Подзапрос имеет побочные эффекты (изменяет данные) —
чтобы изменение произошло ровно один раз. (К побочным
эффектам относится также обращение к изменчивым функциям,
см. тему «Функции».)
В этом случае материализацию отменить невозможно.
Материализацию всегда можно принудительно включить, указав
предложение AS MATERIALIZED.
8
CTE
Рекурсивные запросы
рабочая
таблица
CTE Scan
Recursive
Union
Result
Recursive
Union
Worktable
Scan
промежуточная
таблица
Рекурсивные запросы строятся на базе общих табличных выражений.
Узел Recursive Union использует рабочую и промежуточную таблицы:
рабочая таблица хранит строки, получаемые на текущей итерации,
а промежуточная — накапливает результат. Содержимое рабочей
таблицы считывается в рекурсивной части запроса узлом Worktable
Scan.
Каждая из этих двух таблиц материализуется по тем же правилам: пока
содержимое таблицы помещается в work_mem, она хранится в памяти,
а затем все строки сбрасываются на диск.
После выполнения рекурсивного запроса накопленное содержимое
промежуточной таблицы передается узлу CTE Scan, а рабочая таблица
отбрасывается.
10
Оконные функции
Sort
Seq Scan
WindowAgg
строки
текущего
раздела
При вычислении оконных функций в узле WindowAgg (см. тему
«Сортировка») тоже применяется материализация: строки текущего
раздела (PARTITION BY) могут неоднократно попадать в рамку,
и поэтому материализуются.
В этом случае материализованными строками пользуется только сам
узел WindowAgg: родительскому узлу передается результат
вычислений, а не эти промежуточные данные.
11
Временные таблицы
Таблица, доступная в пределах одного сеанса
регистрируется в системном каталоге
не журналируется
создаются файлы на диске
кеширование в локальной памяти сеанса (temp_buff ers)
Очистка и анализ
только вручную
Для получения промежуточных данных может использоваться сложный
алгоритм, например, написанный на процедурном языке. В таком
случае данные не получится посчитать в CTE, но их можно поместить
во временную таблицу и использовать в нескольких запросах.
Временные таблицы удобнее для промежуточных данных, чем
обычные, поскольку они существуют только в пределах сеанса или
транзакции (в зависимости от указанного при создании режима) и
автоматически удаляются вместе с данными и зависимыми объектами
(представлениями и индексами). К тому же временные таблицы не
журналируются и кешируются в локальной памяти процесса,
обслуживающего сеанс — это делает работу с ними более быстрой.
Локальная память процесса недоступна процессам автоочистки,
поэтому очистку и анализ следует проводить вручную. Память для кеша
выделяется по необходимости и ограничена для сеанса параметром
temp_buffers (после первого обращения к временной таблице изменить
ограничение уже нельзя).
Однако для временных таблиц создаются записи в системном каталоге
и файлы на диске. Поэтому при массовой работе с временными
таблицами — например, в системе 1С — будет разрастаться системный
каталог и увеличится нагрузка на файловую систему. Поэтому для 1С
используют специальные патчи, нивелирующие эти нежелательные
эффекты.
13
Порядок соединений
Количество вариантов соединений растет экспоненциально
с ростом количества таблиц в запросе
Соединение JOIN
полный перебор при числе соединений не более join_collapse_limit = 8
дальше — группами по join_collapse_limit таблиц
Соединение через запятую
полный перебор при числе соединений не более geqo_threshold = 12
дальше — генетический алгоритм
Материализация — подсказка планировщику
С ростом количества таблиц в запросе число вариантов соединений
и, как следствие, затраты на выбор плана растут экспоненциально.
Если число соединений (записанных в виде JOIN) превышает значение
join_collapse_limit (по умолчанию 8), планировщик рассматривает
возможные соединения в группах по join_collapse_limit таблиц, а затем
соединяет эти группы между собой.
(Для подзапросов в предложении FROM есть похожий параметр —
from_collapse_limit.)
Если таблицы соединены запятой (без ключевого слова JOIN),
планировщик рассматривает все варианты соединений, но
переключается на генетический алгоритм оптимизации, когда
количество соединений превышает geqo_threshold (12 по умолчанию).
Все это может приводить к неоптимальным планам. Подробности
можно прочитать в статье Павла Толмачева:
Вручную управляя материализацией — либо с помощью CTE, либо
разбивая запрос на части и задействуя временные таблицы, —
разработчик может разделить таблицы на группы, каждую из которых
оптимизатор будет планировать отдельно. Обычно CTE — более
простой и эффективный способ, но второй вариант позволяет
проанализировать временную таблицу и сообщить таким образом
планировщику больше информации о данных.
15
Мат. представления
Материализация результата запроса
только для чтения
возможно создание индексов
Обновление данных
ручная синхронизация
инкрементальное обновление — расширение pg_ivm
Очистка и анализ
вручную и автоматически
Материализованное представление — это именованный результат
запроса, сохраненный на уровне базы данных. К материализованному
представлению можно обращаться как к обычной таблице, доступной
только для чтения.
По материализованному представлению можно построить индексы
(но нельзя добавить ограничение целостности — ограничения должны
проверяться в базовых таблицах). Для материализованного
представления собирается такая же статистика, как и для обычных
таблиц.
В отличие от обычных представлений, строки материализованного
представления не изменяются при изменении базовых таблиц.
Синхронизация выполняется вручную.
Полная синхронизация материализованного представления может быть
слишком затратной. Штатное инкрементальное обновление (отдельных
строк по мере изменения базовых таблиц) не доступно, но такую
возможность дает расширение pg_ivm (автор — Юго Нагата,
17
Итоги
Оптимизатор может материализовать строки, полученные
узлами плана, для повторного использования
Можно управлять материализацией с помощью CTE,
временных таблиц и материализованных представлений
Материализация позволяет управлять порядком соединений
18
Практика
1. В начале демонстрации приводились примеры двух запросов
с узлом Materialize. Выдайте планировщику указание не
использовать этот узел и проверьте, в каком случае он
сможет обойтись без материализации, а в каком — нет.
2. Проверьте план выполнения запроса при значениях
параметра from_collapse_limit 8 (по умолчанию) и 1:
SELECT *
FROM
(
SELECT *
FROM ticket_flights tf, tickets t
WHERE tf.ticket_no = t.ticket_no
) ttf,
flights f
WHERE f.flight_id = ttf.flight_id;
1. Воспользуйтесь параметром enable_material:
2. Прочитайте про параметр в документации:
