Оптимизация запросов
Последовательное сканирование
13
Авторские права
© Postgres Professional, 2019–2022
Авторы: Егор Рогов, Павел Лузанов, Павел Толмачев, Илья Баштанов
Использование материалов курса
Некоммерческое использование материалов курса (презентации,
демонстрации) разрешается без ограничений. Коммерческое
использование возможно только с письменного разрешения компании
Postgres Professional. Запрещается внесение изменений в материалы
курса.
Обратная связь
Отзывы, замечания и предложения направляйте по адресу:
Отказ от ответственности
Компания Postgres Professional не несет никакой ответственности за
любые повреждения и убытки, включая потерю дохода, нанесенные
прямым или непрямым, специальным или случайным использованием
материалов курса. Компания Postgres Professional не предоставляет
каких-либо гарантий на материалы курса. Материалы курса
предоставляются на основе принципа «как есть» и компания Postgres
Professional не обязана предоставлять сопровождение, поддержку,
обновления, расширения и изменения.
2
Темы
Последовательное сканирование (Seq Scan)
Параллельные планы выполнения
Параллельное сканирование (Parallel Seq Scan)
Агрегация при параллельном выполнении
Команда EXPLAIN
3
Seq Scan
Последовательное чтение всех страниц
страницы читаются в кеш (используется буферное кольцо)
проверяется видимость версий строк
данные возвращаются в произвольном порядке
время сканирования зависит от физического размера файла
14 3 5 2 1 10 12 8 7 13 11 4 9 6
В распоряжении оптимизатора имеется несколько способов доступа
к данным. Самый простой из них — последовательное сканирование
таблицы. Файл (или файлы) таблицы читается постранично от начала
до конца. При этом рассматриваются все версии строк на каждой
странице: удовлетворяют ли они условиям запроса и соблюдены ли
правила видимости.
Напомним, что чтение происходит через буферный кеш. Чтобы
большая таблица не вытеснила все полезные данные, для
последовательного сканирования создается «кольцо буферов»
небольшого размера. При этом другие процессы, одновременно
выполняющие последовательное сканирование той же таблицы,
«присоединяются» к тому же кольцу и тем самым экономят дисковые
чтения (если процесс присоединился не сразу, он затем отдельно
дочитывает начальные страницы таблицы).
Последовательное чтение файла позволяет использовать тот факт, что
операционная система обычно читает данные порциями больше, чем
размер страницы: с большой вероятностью несколько следующих
страниц уже окажутся в кеше ОС.
Последовательное сканирование эффективно работает, когда надо
прочитать всю таблицу или значительную ее часть (если селективность
условия низка). Если же из всей таблицы нужна только небольшая
часть записей, более предпочтительными являются методы доступа,
использующие индекс.
5
Параллельные планы
Ведущий процесс
выполняет последовательную часть плана
запускает рабочие процессы и получает от них данные
Рабочие процессы
одновременно работают над параллельной частью плана
Gather
параллельный
план
параллельный
план
параллельный
план
ведущий процесс
рабочий процесс рабочий процесс
последовательный
план
подключается,
если нет
другой работы
PostgreSQL поддерживает параллельное выполнение запросов. Идея
состоит в том, что ведущий процесс, выполняющий запрос, порождает
(с помощью postmaster, конечно) несколько рабочих процессов,
которые одновременно выполняют одну и ту же «параллельную» часть
плана. Результаты этого выполнения передаются ведущему процессу,
который собирает их в узле Gather.
Если рабочие процессы не успевают загрузить ведущего данными,
ведущий процесс тоже подключается к выполнению того же самого
параллельного плана.
Разумеется, запуск процессов и пересылка данных требуют
определенных ресурсов, поэтому далеко не каждый запрос
выполняется параллельно.
Кроме того, даже при параллельном выполнении не все шаги плана
запроса могут быть распараллелены. Часть операций может
выполняться ведущим процессом в одиночку, последовательно.
Заметим, что в PostgreSQL нет другого теоретически возможного
режима распараллеливания, при котором несколько процессов
составляют конвейер для обработки данных (грубо говоря, отдельные
узлы плана выполняются отдельными процессами). Разработчики
PostgreSQL сочли такой режим неэффективным.
6
Parallel Seq Scan
14 3 5 2 1 10 12 8 7 13 11 4 9 6
Gather
Parallel Seq scan
Parallel Seq Scan Parallel Seq Scan
страницы читаются последовательно, но разными процессами
Примером параллельного выполнения является Parallel Seq Scan —
«параллельное последовательное сканирование».
Название звучит противоречиво, но, тем не менее, отражает суть
операции. Страницы таблицы читаются последовательно, в том же
самом порядке, в котором они читались бы при обычном
последовательном сканировании. Однако запросы на чтение выдаются
несколькими параллельно работающими процессами. Процессы
синхронизируются между собой, чтобы их запросы шли в правильном
порядке.
Преимущество такого сканирования проявляется в том, что
параллельные процессы одновременно обрабатывают свои страницы.
Чтобы эффект оправдал дополнительные накладные расходы на
пересылку данных от процесса к процессу, обработка должна быть
достаточно ресурсоемкой. Хорошим примером является агрегация
данных, поскольку для нее необходимы ресурсы процессора, а
пересылать требуется только одно итоговое число. В таком случае
параллельное выполнение запроса может занять существенно меньше
времени, чем последовательное.
8
Общие настройки
Gather Gather
max_parallel_workers_per_gather = 2
max_parallel_workers = 8
max_worker_processes = 8
рабочие процессы
параллельное выполнение запросов другие задачи
Параллельным выполнением управляет довольно много параметров.
Сначала рассмотрим те, что ограничивают число рабочих процессов.
Вообще механизм рабочих процессов используется не только для
параллельного выполнения запросов. Например, рабочие процессы
задействованы в логической репликации, ими могут пользоваться
расширения. Общее число одновременно выполняющихся рабочих
процессов ограничено параметром max_worker_processes (по
умолчанию 8).
Число одновременно выполняющихся рабочих процессов,
занимающихся параллельными планами, ограничено параметром
max_parallel_workers (по умолчанию тоже 8).
Число одновременно выполняющихся рабочих процессов,
обслуживающих один ведущий процесс, ограничено параметром
max_parallel_workers_per_gather (по умолчанию 2).
Значения этих параметров следует изменить в зависимости от
возможностей аппаратуры, объема данных и загруженности системы.
Например, даже если в базе данных есть большие таблицы и запросы
могли бы выиграть от распараллеливания, но при этом в системе нет
свободных ядер, то параллельное выполнение не будет иметь смысла.
9
Число рабочих процессов
Равно нулю (параллельный план не строится)
если размер таблицы < min_parallel_table_scan_size = 8MB
Фиксировано
если для таблицы указан параметр хранения parallel_workers
Вычисляется по формуле
1 + ⌊ log
3
(размер таблицы / min_parallel_table_scan_size) ⌋
Но не больше, чем max_parallel_workers_per_gather
Сколько рабочих процессов будет использоваться?
Планировщик вообще не будет рассматривать параллельное
сканирование, если физический размер таблицы меньше значения
параметра min_parallel_table_scan_size.
Обычно число процессов вычисляется по формуле, приведенной на
слайде. Она означает, что при увеличении таблицы в три раза будет
добавляться очередной процесс. Например, для стандартного значения
min_parallel_table_scan_size = 8MB:
таблица процессы таблица процессы
8MB 1 216MB 4
24MB 2 648MB 5
72MB 3 1.9GB 6
Число процессов можно и явно указать в параметре хранения
parallel_workers таблицы.
При этом число процессов в любом случае не будет превышать
значения параметра max_parallel_workers_per_gather. Если при
выполнении запроса доступное число процессов окажется меньше
запланированного, будут использоваться только доступные (вплоть до
последовательного выполнения, если пул полностью исчерпан).
11
Не распараллеливаются
Запросы на запись
а также запросы с блокировкой строк
Курсоры
в том числе запросы в цикле FOR в PL/pgSQL
Запросы с функциями PARALLEL UNSAFE
Запросы в функциях,
вызванных из распараллеленного запроса
Не каждый запрос может выполняться в параллельном режиме.
Не распараллеливаются запросы, изменяющие или блокирующие
данные (UPDATE, DELETE, SELECT FOR UPDATE и т. п.).
Не распараллеливаются запросы, выполнение которых может быть
приостановлено — это относится к запросам в курсорах, в том числе
в циклах FOR PL/pgSQL.
Не распараллеливаются запросы, содержащие функции, помеченные
как PARALLEL UNSAFE (все основные стандартные функции
безопасны; список небезопасных можно получить из таблицы pg_proc
по условию proparallel = 'u').
Не распараллеливаются запросы, содержащиеся в функциях, которые
вызываются из распараллеленного запроса (чтобы не допустить
рекурсивного разрастания).
Часть из этих ограничений может быть снята в следующих версиях
PostgreSQL.
12
Только последовательно
Чтение результатов общих табличных выражений (CTE)
Чтение результатов нераскрываемых подзапросов
Обращения к временным таблицам
Вызовы функций PARALLEL RESTRICTED
Функции, использующие вложенные транзакции
В целом, чем большую часть плана удается выполнить параллельно,
тем больший возможен эффект. Однако есть ряд операций, которые
в целом не препятствуют распараллеливанию, но сами могут
выполняться только последовательно в ведущем процессе.
К ним относятся:
- чтение результатов общих табличных выражений (подзапросов
в предложении WITH);
- чтение результатов других нераскрываемых подзапросов (которые
представляются в плане узлами, например, SubPlan);
- обращения ко временным таблицам (так как они доступны только
ведущему процессу);
- вызовы функций, помеченных как PARALLEL RESTRICTED (список
можно получить из таблицы pg_proc по условию proparallel = 'r').
Если в запросе вызывается функция, использующая вложенные
транзакции (например, функции на PL/pgSQL с обработкой
исключений), запрос завершится с ошибкой. Такие функции должны
быть помечены как PARALLEL RESTRICTED.
14
Итоги
Последовательное сканирование читает всю таблицу
эффективно при низкой селективности
Параллельное выполнение в ряде случаев позволяет
ускорить запрос
15
Практика
1. Убедитесь, что запрос, вычисляющий среднюю стоимость
перелета, выполняется параллельно.
2. Проверьте, как выполняется тот же запрос, если чтение из
таблицы перелетов оформлено как общее табличное
выражение.
3. В демонстрации запрос всех строк из таблицы рейсов
выполнялся последовательно. Почему? Может ли такой
запрос в принципе быть распараллелен или он запрещает
параллельное выполнение?
Воспользуйтесь параметром force_parallel_mode, чтобы
ответить на этот вопрос.
2. Используйте указание MATERIALIZE, чтобы планировщик не
раскрывал общее табличное выражение.
3. Параметр force_parallel_mode можно использовать только для
проверки, допускает ли запрос распараллеливание, но не для реальной
работы. С этим параметром PostgreSQL распараллеливает любой
запрос, если только это возможно, даже если в этом нет никакого
смысла. Количество параллельных процессов всегда выбирается
равным единице, так что ускорения при таком режиме в любом случае
не будет.
