Этот документ представляет основные компоненты планов запросов и объясняет, как их читать. Приводятся примеры и пояснения как для однокузовных, так и для параллельных (распределённых) планов запросов.
План запроса (также известный как план выполнения или план выполнения запроса) — это ключевой компонент процесса выполнения SQL-запроса. Он генерируется оптимизатором запросов и детально описывает, как конкретный SQL-запрос будет выполнен в базе данных.
В централизованных базах данных (таких как PostgreSQL и другие системы с одним узлом) все данные находятся на одном экземпляре узла, и всё выполнение запроса происходит локально. Для таких сред достаточно однокузовного плана запроса.
Однако в распределённых базах данных, таких как Greenplum и YMatrix, данные распределены между несколькими узлами. При создании таблиц пользователь должен указать стратегию распределения и ключ распределения. Данные затем распределяются между несколькими экземплярами MXSegment на основе этого ключа, причём каждый сегмент содержит лишь часть полного набора данных.
В результате традиционные однокузовные планы запросов оказываются недостаточными. Необходимо использовать новый тип плана — параллельный план запроса (или распределённый план запроса). Этот план использует распределение данных для обеспечения эффективных параллельных вычислений и гарантирует, что обработка происходит как можно ближе к данным. В частности, вычисления должны выполняться на экземплярах MXSegment, а не на MXMaster, когда это возможно, чтобы минимизировать перемещение данных и максимизировать производительность запросов.
После загрузки данных в YMatrix необходимо сначала выполнить команду ANALYZE для сбора статистики. Также рекомендуется периодически выполнять полные операции ANALYZE. Точная статистика необходима оптимизатору для принятия обоснованных решений при выборе наиболее эффективного плана запроса, что улучшает производительность.
После сбора статистики используйте команду EXPALIN, чтобы просмотреть логические планы запросов, сгенерированные оптимизатором для заданного SQL-запроса. Оптимизатор обычно оценивает несколько потенциальных планов и выбирает оптимальный для выполнения. Анализ и понимание этих планов помогают разработчикам и администраторам баз данных (DBA) оптимизировать производительность запросов и выявлять узкие места.
План запроса имеет древовидную структуру, состоящую из нескольких узлов плана. Каждый узел представляет конкретную операцию — такую как сканирование таблицы, соединение или агрегация — и передаёт свой результат родительскому узлу для дальнейшей обработки.
Каждый узел содержит детальную информацию о выполнении, включая порядок выполнения, метод доступа, уровень параллелизма и оценку стоимости. Дерево плана начинается с листовых узлов и заканчивается корневым узлом. Обход дерева снизу вверх позволяет проследить полный процесс выполнения.
В распределённых планах запросов распространённые типы узлов плана и соответствующие им операторы приведены ниже:
| Тип узла плана | Описание операции | Скалярный оператор | Векторизованный оператор |
|---|---|---|---|
| Узлы сканирования | Отвечают за сканирование таблиц или индексов | SeqScan: Последовательное сканирование всех строк в таблице IndexScan: Обход индекса для извлечения строк из таблицы BitmapHeapScan & BitmapIndexScan: Часто используются вместе. Сканирование битовой карты индекса считывает подходящие записи индекса и строит битовую карту (размерность зависит от размера индекса). Затем сканирование битовой карты таблицы использует эту карту для доступа к таблице. Примечание: Сканирование индекса извлекает одну запись за раз и немедленно проверяет условия, чередуя доступ к индексу и таблице — идеально подходит для точных поисков. Сканирование битовой карты работает в два этапа: сначала собирает все подходящие записи индекса и сортирует их в памяти; затем выполняет доступ к таблице — лучше подходит для широких диапазонных запросов DynamicSeqScan: Использует функцию выбора партиций для выбора партиций ParallelSeq: При включении параллелизма количество параллельных рабочих процессов следует устанавливать равным числу ядер CPU для максимальной эффективности |
MxVScan: Векторизованное последовательное сканирование для таблиц MARS2/AOCO |
| Узлы соединения | Объединяет две или более таблиц | HashJoin: Строит хеш-таблицу из меньшей таблицы, используя столбец соединения в качестве ключа хеша, затем последовательно проверяет строки в большей таблице. HashCond показывает столбцы соединенияNestedLoopJoin: Перебирает строки одного набора данных и для каждой итерации сканирует другой набор на совпадения. Требует широковещательной рассылки одной таблицы, если обе таблицы не имеют одинакового ключа распределения, позволяя выполнять локальные соединения без перераспределения данных MergeJoin: Сортирует оба набора данных и объединяет их |
MxVHashJoin: Векторизованное хеш-соединение для таблиц MARS2/AOCO |
| Другие узлы плана | Materialize: Кэширует результаты подзапросов для повторного использования; сохраняет выходные кортежи при первом выполнении Sort: Упорядочивает кортежи, возвращаемые дочерними узлами Group: Группирует отсортированные кортежи из нижних узлов Agg: Применяет агрегатные функции Unique: Удаляет дубликаты строк Hash: Строит хеш-таблицу, обычно используется как вспомогательный элемент для HashJoin Limit: Обрабатывает условия LIMIT/OFFSETWindowAgg: Обрабатывает оконные функции LockRows: Блокирует выбранные строки, опционально отфильтрованные по FOR SHARE/FOR UPDATESetOP: Выполняет операции над множествами между несколькими наборами данных: объединение ( UNION), пересечение (INTERSECT), разность (EXCEPT/MINUS)SubqueryScan: Выполняет подзапросы Append: Объединяет несколько наборов результатов в один Result: Обрабатывает выражения, вычисляемые один раз, или простые операторы VALUES с только INSERT ... VALUESGroupAgg: Сначала сортирует данные по групповым столбцам, чтобы одинаковые значения были соседними, затем агрегирует при сканировании HashAgg: Группирует и агрегирует без сортировки, создавая хеш-таблицу Broadcast Motion: Каждый сегмент отправляет свои строки всем другим сегментам, создавая полную локальную копию на каждом сегменте Redistribute Motion: Каждый сегмент пересчитывает распределение данных на основе ключа перераспределения и отправляет строки соответствующему целевому сегменту Gather Motion: Результаты всех сегментов собираются в один поток. Это обычно последний шаг в большинстве планов запросов |
MxVMotion: Векторизованное Gather Motion для таблиц MARS2/AOCO MxVSort: Векторизованная сортировка для таблиц MARS2/AOCO MxVResult: Векторизованная операция Result MxVSubqueryScan: Векторизованное сканирование подзапроса MxVLimit: Векторизованная операция Limit MxVHashAgg: Векторизованный HashAgg MxVGroupAgg: Векторизованный GroupAgg MxVAppend: Векторизованный Append |
Эти узлы могут комбинироваться по мере необходимости для формирования полного плана запроса.
Из-за его вложенной древовидной структуры план запроса необходимо читать снизу вверх.
Ниже приведены примеры однокузовного и параллельного планов запроса.
Сначала создайте две таблицы. (Пример использует PostgreSQL 9.2.)
=# CREATE TABLE sale (
cid int,
pid int,
pnum int,
qty float,
date timestamp
);=# CREATE TABLE customer (
cid int,
cname text,
cwechat text
);Вставьте тестовые данные.
=# INSERT INTO sale (cid, pid, pnum, qty, date) VALUES
(1, 1, 1, 10.0, '2023-07-20 08:37:06'),
(2, 3, 6, 35.0, '2023-07-15 18:22:00'),
(3, 2, 1, 3.0, '2023-07-17 11:37:00'),
(4, 1, 2, 10.0, '2023-07-20 10:37:09'),
(5, 3, 2, 35.0, '2023-07-17 08:12:06'),
(6, 1, 1, 10.0, '2023-07-02 12:10:23'),
(7, 3, 1, 35.0, '2023-07-07 08:07:00'),
(8, 5, 3, 99.0, '2023-07-20 10:37:06'),
(9, 3, 1, 35.0, '2023-07-20 15:30:00'),
(10, 3, 1, 35.0, '2023-07-20 09:00:00');=# INSERT INTO customer (cid, cname, cwechat) VALUES
(1, 'kepler', 'kepler1997'),
(2, 'amiee', 'amiee1995'),
(3, 'lila', 'lila2002'),
(4, 'cici', 'cici1982'),
(5, 'damien', 'damien1983'),
(6, 'ariana', 'ariana1990'),
(7, 'kanye', 'kanye1960'),
(8, 'taylor', 'taylor1996'),
(9, 'michael', 'mike2005'),
(10, 'ray', 'ray1957');Теперь вычислите общую сумму продаж по каждому клиенту, объединив таблицы sale и customer по общему ключу cid, и проанализируйте план запроса.
Сначала соберите статистику.
=# ANALYZE sale;
=# ANALYZE customer;Сгенерируйте план запроса.
=# EXPLAIN SELECT c.cname, sum(s.pnum * s.qty) AS amount FROM sale s, customer c WHERE s.cid = c.cid GROUP BY c.cname;
QUERY PLAN
------------------------------------------------------------------------------
HashAggregate (cost=2.56..2.66 rows=10 width=14)
Group Key: c.cname
-> Hash Join (cost=1.23..2.46 rows=10 width=18)
Hash Cond: (s.cid = c.cid)
-> Seq Scan on sale s (cost=0.00..1.10 rows=10 width=16)
-> Hash (cost=1.10..1.10 rows=10 width=10)
-> Seq Scan on customer c (cost=0.00..1.10 rows=10 width=10)
(7 rows)Этот план содержит два основных узла: Hash Join и HashAggregate. Каждый узел включает три оценки стоимости: cost, rows и width. Они помогают предсказать время выполнения, размер результата и сложность обработки.
Структура дерева плана показана ниже:
Понимание плана запроса требует распознавания его вложенности. Каждый узел — это подоперация, и данные передаются сверху вниз от дочернего узла к родительскому. Оптимизатор начинает с последовательного сканирования таблицы customer, стоимость которого составляет 1.10. Затем в памяти строится хеш-таблица из таблицы sale с помощью другого последовательного сканирования. Используя cid в качестве ключа соединения, система сравнивает строки из таблицы sale с хеш-таблицей. На этом этапе начальная и общая стоимость составляют соответственно 1.23 и 2.46. Наконец, результаты группируются и агрегируются по cname, что даёт общую стоимость 2.66.
Подробное объяснение компонентов плана (см. таблицу операторов выше):
WHERE. Общая стоимость предполагает извлечение всех строк, хотя это может не произойти (например, при использовании LIMIT).Как и ранее, создайте две таблицы.
Перед созданием таблиц MARS2 установите расширение matrixts для функциональности временных рядов.
=# CREATE EXTENSION matrixts;
=# CREATE TABLE sale (
cid int,
pid int,
pnum int,
qty float,
date timestamp
) USING MARS2
DISTRIBUTED BY (cid,date);
=# CREATE INDEX ON sale USING mars2_btree (cid,date);=# CREATE TABLE customer (
cid int,
cname text,
cwechat text
) USING MARS2
DISTRIBUTED BY (cid);
=# CREATE INDEX ON customer USING mars2_btree (cid);Вставьте те же тестовые данные и соберите статистику.
Теперь сгенерируйте план запроса с использованием того же SQL. Поскольку YMatrix по умолчанию включает векторизацию, в плане появляются многие векторизованные операторы, повышающие производительность.
=# EXPLAIN SELECT c.cname, sum(s.pnum * s.qty) AS amount FROM sale s, customer c WHERE s.cid = c.cid GROUP BY c.cname;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Gather Motion 4:1 (slice1; segments: 4) (cost=72.26..72.44 rows=10 width=14)
-> GroupAggregate (cost=72.26..72.32 rows=2 width=14)
Group Key: c.cname
-> Sort (cost=72.26..72.27 rows=2 width=18)
Sort Key: c.cname
-> Redistribute Motion 4:4 (slice2; segments: 4) (cost=0.00..72.25 rows=2 width=18)
Hash Key: c.cname
-> Nested Loop (cost=0.00..72.20 rows=2 width=18)
Join Filter: (s.cid = c.cid)
-> Custom Scan (MxVMotion) Redistribute Motion 4:4 (slice3; segments: 4) (cost=0.00..36.07 rows=2 width=16)
Hash Key: s.cid
-> Custom Scan (MxVScan) on sale s (cost=0.00..36.02 rows=2 width=16)
-> Materialize (cost=0.00..36.04 rows=2 width=10)
-> Custom Scan (MxVScan) on customer c (cost=0.00..36.02 rows=2 width=10)
Optimizer: Postgres query optimizer
(15 rows)Этот план содержит шесть основных узлов: Materialize, Redistribute Motion, Nested Loop, Sort, GroupAggregate и Gather Motion. Каждый включает оценки стоимости, количества строк и ширины.
Структура дерева плана показана ниже:
Оптимизатор начинает с операции материализации. Сканирование и материализация таблицы customer занимают примерно 36. Поскольку таблица customer распределена по cid, а таблица sale — по (cid,date), таблица sale должна быть перераспределена по cid, чтобы связанные строки находились на одном сегменте, что позволяет выполнять локальные соединения. После перераспределения применяется соединение по вложенному циклу с условием фильтра s.cid = c.cid. К этому моменту общая стоимость достигает примерно 72, что указывает на значительное использование ресурсов. Поскольку запрос требует группировки по cname, происходит второе перераспределение, за которым следует сортировка по cname и агрегирование по группам. Конечные результаты отправляются на узел Gather Motion, где узел Gather Motion вычисляет общую стоимость (72.32) и возвращает результаты на MXMaster.
Объяснение ключевых компонентов (см. таблицу операторов выше):
slice1. Не все запросы требуют этого — например, CREATE TABLE x AS SELECT... записывает напрямую в таблицу, минуя MXMaster.Разделение срезов для сегмента 1 и сегмента 2 показано ниже:
