Анализ запросов

Андрей Аксенов

Contents

EXPLAIN
EXPLAIN ANALYZE
Анализ SELECT-запросов
Параметры EXPLAIN
- Форматы вывода
Анализ запросов, изменяющих данные
Выявление проблем с производительностью запросов

Greengage DB создает план выполнения для каждого запроса. Для достижения оптимальной производительности важно выбирать наиболее подходящий план с учетом характера запроса и структуры данных. План запроса определяет, каким образом Greengage DB выполняет запрос в условиях параллельной обработки.

Оптимизатор запросов использует статистические данные, чтобы выбрать план с наименьшей стоимостью. Используйте команду EXPLAIN, чтобы просмотреть план выполнения запроса, будь то SELECT-запрос или запрос на изменение данных (INSERT, UPDATE, DELETE). Команда EXPLAIN отображает оценочную стоимость, рассчитанную оптимизатором, а также выбранную стратегию выполнения запроса.

ПРИМЕЧАНИЕ

В Greengage DB оптимизатор GPORCA работает совместно с планировщиком Postgres. Вывод EXPLAIN может отличаться в зависимости от используемого оптимизатора. По умолчанию, когда это возможно, Greengage DB использует GPORCA для построения планов выполнения.

Предварительные требования

Для выполнения команд, описанных в следующих разделах, подключитесь к мастер-хосту Greengage DB с помощью psql, как описано в статье Подключение к Greengage DB с использованием psql. Создайте новую базу данных и подключитесь к ней:

CREATE DATABASE marketplace;
\c marketplace

Затем создайте таблицу и заполните ее данными:

CREATE TABLE sales
(
    id     INT,
    date   DATE,
    region TEXT,
    amount DECIMAL(10, 2)
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (id);

INSERT INTO sales (id, date, region, amount)
SELECT gs.id,
       DATE '2025-01-01' + (gs.id % 90),
       CASE WHEN gs.id % 2 = 0 THEN 'Asia' ELSE 'Europe' END,
       round((random() * 1000)::NUMERIC, 2)
FROM generate_series(1, 40000) AS gs(id);

Команда EXPLAIN отображает план выполнения запроса и оценку его стоимости, рассчитанную оптимизатором. В следующем примере EXPLAIN выводит план выполнения запроса для выборки данных из таблицы sales:

EXPLAIN
SELECT date, amount
FROM sales;

Вывод показывает план запроса, включая шаги выполнения, оценку стоимости и используемый оптимизатор:

                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Gather Motion 4:1  (slice1; segments: 4)  (cost=0.00..432.71 rows=40000 width=10)
   ->  Seq Scan on sales  (cost=0.00..431.18 rows=10000 width=10)
 Optimizer: Pivotal Optimizer (GPORCA)
(3 rows)

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

Seq Scan — последовательно считывает все строки из таблицы sales; каждый сегмент обрабатывает около 10000 строк.
Gather Motion — собирает строки со всех сегментов (всего 40000 строк) и объединяет их на мастере.

План выполнения разделен на слайсы (slice), каждый из которых соответствует отдельному процессу на сегменте. Слайсы — это независимые части плана запроса, которые выполняются параллельно и передают результаты в слайсы более высокого уровня.

План запроса — это дерево узлов. Каждый узел в плане соответствует отдельной операции, например, сканированию таблицы, соединению, агрегации или сортировке:

Узлы нижнего уровня

Узлы нижнего уровня, как правило, выполняют операции сканирования таблиц: последовательное, индексное или сканирование по bitmap-индексу. Смотрите раздел Узлы сканирования ниже.
Промежуточные узлы

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

Узлы верхнего уровня обычно представляют собой узлы перемещений данных (motion), такие как Redistribute Motion или Gather Motion. Эти операции перемещают строки между сегментами в процессе выполнения запроса. Смотрите раздел Узлы перемещения данных ниже.

Вывод EXPLAIN также показывает оценки стоимости выполнения для каждого узла плана. Узнайте подробнее в разделе Оценка стоимости выполнения ниже.

Операции сканирования считывают строки из таблицы, чтобы определить, какие из них соответствуют запросу. Конкретная операция выбирается в зависимости от запроса и способа доступа к данным. Типовые операции сканирования включают:

Seq Scan — последовательно считывает все строки таблицы.
Index Scan — использует индекс для поиска и извлечения нужных строк.
Bitmap Heap Scan — собирает указатели на строки из индекса и обращается к строкам таблицы в порядке их расположения на диске. Обратите внимание, что эта операция называется Bitmap Heap Scan даже для оптимизированных для добавления (AO) таблиц.
Dynamic Seq Scan — сканирует только соответствующие партиции.

Операции соединения включают:

Hash Join — строит хеш-таблицу из меньшей таблицы, используя столбцы соединения в качестве хеш-ключа (хеш-соединение или hash join). Затем выполняется сканирование большей таблицы, для ее столбцов соединения вычисляется хеш-ключ и выполняется поиск соответствующих строк в хеш-таблице. Хеш-соединение обычно является самым быстрым методом соединения в Greengage DB. Hash Cond в плане EXPLAIN показывает столбцы, используемые для соединения.
Nested Loop — перебирает строки в большем наборе данных, при каждой итерации сканируя строки из меньшего набора данных (соединение с вложенным циклом или nested loop join). Для этого соединения одна из таблиц должна быть передана на все сегменты, чтобы все ее строки были доступны для сравнения со всеми строками другой таблицы.

Соединения с вложенным циклом эффективны для небольших таблиц или в случаях, когда индекс позволяет ограничить количество сканируемых строк. Также используется для декартовых и диапазонных соединений. Использование соединения с вложенным циклом для больших таблиц может влиять на производительность. Для узлов плана, которые содержат операцию Nested Loop, проверьте SQL-запрос и убедитесь, что результаты соответствуют ожидаемым. Чтобы оптимизатор выбирал хеш-соединения, убедитесь, что параметр конфигурации enable_nestloop установлен в OFF (по умолчанию).
Merge Join — сортирует оба набора данных и выполняет их слияние (соединение слиянием или merge join). Соединения слиянием эффективны для предварительно отсортированных данных, но редко используются на практике. Чтобы отдавать предпочтение соединениям слиянием перед хеш-соединениями, установите параметр конфигурации enable_mergejoin в ON.

Некоторые узлы в плане запроса выполняют операции перемещения данных (motion), которые при необходимости перемещают строки между сегментами для выполнения запроса. Основные операции перемещения данных включают:

Broadcast Motion — каждый сегмент отправляет свои строки всем остальным сегментам, чтобы на каждом сегменте была полная копия таблицы. Оптимизатор обычно выбирает этот метод только для небольших таблиц, так как для больших он может быть затратным. Если ключ соединения не соответствует распределению данных, система может динамически перераспределить строки из одной таблицы на другой сегмент с помощью Broadcast Motion.
Redistribute Motion — каждый сегмент выполняет повторное хеширование данных и отправляет строки на соответствующие сегменты в соответствии с хеш-ключом.
Gather Motion — строки со всех сегментов собираются в единый набор. Обычно это выполняется на последнем этапе плана запроса.

Ниже приведены некоторые примеры других операторов, которые могут встречаться в планах запросов:

Materialize — сохраняет результат подзапроса, чтобы его можно было использовать без повторного вычисления для каждой внешней строки (например, когда CTE используется несколько раз).
InitPlan — предзапрос, выполняемый до основного запроса во время выполнения; часто используется для динамического сканирования ограниченного числа партиций (partition elimination), когда необходимые значения становятся известны только при выполнении.
Sort — упорядочивает строки, обычно подготавливая данные для операций, которые требуют сортировки (например, Aggregate или Merge Join).
GroupAggregate — группирует строки по одному или нескольким столбцам.
HashAggregate — вычисляет агрегаты с использованием хеш-метода.
Append — объединяет несколько наборов результатов, например строки, полученные из разных партиций партиционированной таблицы.
Filter — выбирает строки в соответствии с условием WHERE.
Limit — ограничивает количество возвращаемых строк.

Вывод EXPLAIN включает следующие оценки стоимости выполнения для каждого узла плана:

cost — измеряется в единицах обращений к страницам диска. 1.0 соответствует одному последовательному чтению страницы диска. Первая оценка — это стартовая стоимость получения первой строки, а вторая — общая стоимость получения всех строк. Общая стоимость предполагает, что будут получены все строки, что не всегда верно; например, если запрос использует LIMIT, извлекаются не все строки.

Стоимость — это внутренняя оценка оптимизатора и не отражает напрямую время выполнения запроса.

ПРИМЕЧАНИЕ

Значения стоимости, рассчитанные GPORCA и планировщиком Postgres, нельзя сравнивать напрямую. Каждый оптимизатор использует собственную модель оценки стоимости. Оценки пригодны только для сравнения альтернативных планов одного и того же запроса с одним и тем же оптимизатором.
rows — оценочное количество строк, выдаваемых данным узлом плана. Обычно оно меньше числа строк, которые сканируются или обрабатываются, так как учитывает селективность условий WHERE. Для верхнего узла оценка должна примерно соответствовать количеству строк, которое фактически возвращает, обновляет или удаляет запрос.
width — оценочный средний размер (в байтах) одной строки, выводимой этим узлом плана.

ПРИМЕЧАНИЕ

Обратите внимание на следующее:

Стоимость узла включает стоимость его дочерних узлов. Верхний узел плана содержит оценку общей стоимости выполнения плана. Это значение оптимизатор пытается минимизировать.
Стоимость отражает только те аспекты выполнения плана, которые учитывает оптимизатор. Например, она не учитывает время, затрачиваемое на передачу строк клиенту.

Команда EXPLAIN ANALYZE выполняет запрос и показывает фактический план выполнения. Это позволяет сравнить реальную производительность с оценками оптимизатора.

Этот запрос использует EXPLAIN ANALYZE для отображения плана выполнения и запуска запроса:

EXPLAIN ANALYZE
SELECT date, amount
FROM sales;

Результат включает как оценки оптимизатора, так и фактическую статистику выполнения:

                                                            QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Gather Motion 4:1  (slice1; segments: 4)  (cost=0.00..432.71 rows=40000 width=10) (actual time=7.141..14.390 rows=40000 loops=1)
   ->  Seq Scan on sales  (cost=0.00..431.18 rows=10000 width=10) (actual time=0.116..2.266 rows=10055 loops=1)
 Planning time: 2.337 ms
   (slice0)    Executor memory: 74K bytes.
   (slice1)    Executor memory: 204K bytes avg x 4 workers, 204K bytes max (seg0).
 Memory used:  128000kB
 Optimizer: Pivotal Optimizer (GPORCA)
 Execution time: 21.573 ms
(8 rows)

В выводе EXPLAIN ANALYZE также отображаются:

Общее время выполнения запроса (в миллисекундах).
Память, потребляемая каждым слайсом плана запроса, и общий объем памяти, зарезервированный для выполнения запроса.
Количество процессов-обработчиков (сегментов), участвующих в выполнении узла плана. Учитываются только те сегменты, которые возвращают строки.
Максимальное число строк, возвращенных одним сегментом в операции. Если несколько сегментов вернули одинаковое число строк, EXPLAIN ANALYZE показывает тот, у которого значение time до завершения было наибольшим.
ID сегмента, который вернул наибольшее число строк в операции.
Для соответствующих операций указывается объем используемой памяти (work_mem). Если work_mem недостаточен, план показывает объем данных, сброшенных на диск, для наименее производительного сегмента. Узнайте больше в разделе Команда EXPLAIN ANALYZE.
Время (в миллисекундах), затраченное сегментом, который вернул наибольшее число строк, на получение первой строки и всех строк. Значение time до возврата первой строки может не отображаться, если оно совпадает со временем получения всех строк.

ВАЖНО

Используйте EXPLAIN ANALYZE осторожно:

Эта команда фактически выполняет SQL-запрос и создает нагрузку на систему, что может повлиять на производительность кластера.
Для DML-операций (INSERT, UPDATE или DELETE) EXPLAIN ANALYZE изменяет данные; если нужно проанализировать такие запросы, выполняйте их внутри транзакции и откатывайте после анализа. Узнайте больше в разделе Анализ запросов, изменяющих данные.

Этот пример показывает план запроса, фильтрующего строки по региону:

EXPLAIN
SELECT date, amount
FROM sales
WHERE region = 'Europe';

Вывод показывает план выполнения запроса: выражение WHERE (region = 'Europe') применяется как условие фильтрации при последовательном сканировании таблицы sales:

                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Gather Motion 4:1  (slice1; segments: 4)  (cost=0.00..432.34 rows=20075 width=10)
   ->  Seq Scan on sales  (cost=0.00..431.67 rows=5019 width=10)
         Filter: (region = 'Europe'::text)
 Optimizer: Pivotal Optimizer (GPORCA)
(4 rows)

Результаты последовательного сканирования передаются операции Gather Motion. В этом примере четыре экземпляра сегментов отправляют свои данные на мастер.

Оценочная начальная стоимость — 0.00 (без затрат); суммарная — 432.34 обращений к страницам диска. Оптимизатор оценивает, что запрос вернет 20075 строк.

Этот пример использует EXPLAIN, чтобы показать план выполнения запроса, который фильтрует строки с помощью условия WHERE и выполняет сортировку с помощью ORDER BY date DESC:

EXPLAIN
SELECT date, amount
FROM sales
WHERE region = 'Europe'
ORDER BY date DESC;

Узел Sort показывает, что после сканирования и фильтрации таблицы sales соответствующие строки упорядочиваются по столбцу date (Sort Key):

                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Gather Motion 4:1  (slice1; segments: 4)  (cost=0.00..435.83 rows=20075 width=10)
   Merge Key: date
   ->  Sort  (cost=0.00..435.16 rows=5019 width=10)
         Sort Key: date
         ->  Seq Scan on sales  (cost=0.00..431.67 rows=5019 width=10)
               Filter: (region = 'Europe'::text)
 Optimizer: Pivotal Optimizer (GPORCA)
(7 rows)

Этот пример использует EXPLAIN, чтобы показать план выполнения запроса, который группирует строки по региону и вычисляет общую сумму продаж с помощью SUM(amount):

EXPLAIN
SELECT region,
       SUM(amount) AS total_amount
FROM sales
GROUP BY region;

Вывод содержит несколько узлов, включая:

HashAggregate — выполняет локальное агрегирование на каждом сегменте.
Redistribute Motion — перераспределяет строки между сегментами для выравнивания групп.
GroupAggregate — завершает группировку и агрегирование отсортированных, перераспределенных данных.

                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Gather Motion 4:1  (slice2; segments: 4)  (cost=0.00..432.67 rows=2 width=14)
   ->  GroupAggregate  (cost=0.00..432.67 rows=1 width=14)
         Group Key: region
         ->  Sort  (cost=0.00..432.67 rows=1 width=14)
               Sort Key: region
               ->  Redistribute Motion 4:4  (slice1; segments: 4)  (cost=0.00..432.67 rows=1 width=14)
                     Hash Key: region
                     ->  Result  (cost=0.00..432.67 rows=1 width=14)
                           ->  HashAggregate  (cost=0.00..432.67 rows=1 width=14)
                                 Group Key: region
                                 ->  Seq Scan on sales  (cost=0.00..431.19 rows=10000 width=12)
 Optimizer: Pivotal Optimizer (GPORCA)
(12 rows)

Приведенный ниже запрос создает bitmap-индекс по столбцу region:

CREATE INDEX sales_region_idx ON sales USING bitmap (region);

После создания индекса необходимо собрать статистику с помощью ANALYZE:

ANALYZE;

Этот пример использует EXPLAIN, чтобы показать план выполнения запроса, который вычисляет среднюю сумму для строк, отфильтрованных по указанному региону:

EXPLAIN
SELECT AVG(amount) AS avg_amount
FROM sales
WHERE region = 'Asia';

Результат должен включать Bitmap Index Scan по индексу sales_region_idx:

                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Aggregate  (cost=0.00..430.71 rows=1 width=8)
   ->  Gather Motion 4:1  (slice1; segments: 4)  (cost=0.00..430.71 rows=1 width=8)
         ->  Aggregate  (cost=0.00..430.71 rows=1 width=8)
               ->  Bitmap Heap Scan on sales  (cost=0.00..430.66 rows=4998 width=6)
                     Recheck Cond: (region = 'Asia'::text)
                     ->  Bitmap Index Scan on sales_region_idx  (cost=0.00..0.00 rows=0 width=0)
                           Index Cond: (region = 'Asia'::text)
 Optimizer: Pivotal Optimizer (GPORCA)
(8 rows)

Тестовая таблица

CREATE TABLE sales
(
    id     INT,
    date   DATE,
    region TEXT,
    amount DECIMAL(10, 2)
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (id)
    PARTITION BY RANGE (date)
        (
        START (DATE '2025-01-01') INCLUSIVE
            END (DATE '2025-04-01') EXCLUSIVE
            EVERY (INTERVAL '1 month')
        );

INSERT INTO sales (id, date, region, amount)
SELECT gs.id,
       DATE '2025-01-01' + (gs.id % 90),
       CASE WHEN gs.id % 2 = 0 THEN 'Asia' ELSE 'Europe' END,
       round((random() * 1000)::NUMERIC, 2)
FROM generate_series(1, 40000) AS gs(id);

В Greengage DB партиционированные таблицы позволяют планировщику запросов пропускать нерелевантные партиции, что может существенно повысить производительность. В этом примере условие WHERE содержит даты, которые попадают в самую раннюю партицию (январь 2025):

EXPLAIN
SELECT AVG(amount) AS avg_amount
FROM sales
WHERE date < '2025-01-15';

План показывает, что сканируется только одна партиция таблицы sales. Для этого используется Dynamic Seq Scan, который применяет условие фильтрации к выбранной партиции:

                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=0.00..431.00 rows=1 width=8)
   ->  Gather Motion 4:1  (slice1; segments: 4)  (cost=0.00..431.00 rows=1 width=8)
         ->  Result  (cost=0.00..431.00 rows=1 width=8)
               ->  Sequence  (cost=0.00..431.00 rows=1 width=12)
                     ->  Partition Selector for sales (dynamic scan id: 1)  (cost=10.00..100.00 rows=25 width=4)
                           Partitions selected: 1 (out of 3)
                     ->  Dynamic Seq Scan on sales (dynamic scan id: 1)  (cost=0.00..431.00 rows=1 width=12)
                           Filter: (date < '2025-01-15'::date)
 Optimizer: Pivotal Optimizer (GPORCA)
(9 rows)

Тестовые таблицы

Создайте таблицу для хранения различных категорий товаров:

CREATE TABLE categories
(
    category_code TEXT PRIMARY KEY,
    category_name TEXT
)
    DISTRIBUTED REPLICATED;

Добавьте данные:

INSERT INTO categories (category_code, category_name)
SELECT 'cat' || gs.id,
       'Category ' || gs.id
FROM generate_series(1, 100) AS gs(id);

Создайте таблицу для хранения информации о каждом товаре, включая категорию, к которой он относится:

CREATE TABLE products
(
    id            INT,
    product_name  TEXT,
    category_code TEXT REFERENCES categories (category_code)
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (id);

Добавьте данные:

INSERT INTO products (id, product_name, category_code)
SELECT gs.id,
       'Product ' || gs.id,
       'cat' || ((gs.id % 100) + 1)
FROM generate_series(1, 1000) AS gs(id);

Создайте таблицу, содержащую записи о продажах:

CREATE TABLE sales
(
    id         INT,
    product_id INT,
    date       DATE
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (product_id);

Добавьте данные:

INSERT INTO sales (id, product_id, date)
SELECT gs.id,
       ((gs.id % 1000) + 1),
       DATE '2025-01-01' + (gs.id % 90)
FROM generate_series(1, 40000) AS gs(id);

Этот EXPLAIN строит план выполнения запроса для перекрестного соединения (cross join) таблиц categories и products:

EXPLAIN
SELECT categories.category_name,
       products.product_name
FROM categories
         CROSS JOIN products;

План запроса использует соединение Nested Loop, сравнивая каждую строку одной таблицы со всеми строками другой таблицы:

                                       QUERY PLAN
----------------------------------------------------------------------------------------
 Gather Motion 4:1  (slice1; segments: 4)  (cost=0.00..1326704.61 rows=100000 width=22)
   ->  Nested Loop  (cost=0.00..1326697.25 rows=25000 width=22)
         Join Filter: true
         ->  Seq Scan on categories  (cost=0.00..431.00 rows=100 width=11)
         ->  Seq Scan on products  (cost=0.00..431.00 rows=250 width=11)
 Optimizer: Pivotal Optimizer (GPORCA)
(6 rows)

Этот EXPLAIN строит план выполнения запроса для внутреннего соединения (inner join) таблиц products и sales:

EXPLAIN
SELECT products.id,
       products.product_name,
       sales.id   AS sale_id,
       sales.date AS sale_date
FROM products
         JOIN sales ON products.id = sales.product_id;

План запроса использует Hash Join: таблица products сканируется и хешируется, а затем объединяется со строками из sales по одинаковым значениям id:

                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Gather Motion 4:1  (slice1; segments: 4)  (cost=0.00..868.18 rows=40000 width=23)
   ->  Hash Join  (cost=0.00..865.10 rows=10000 width=23)
         Hash Cond: (sales.product_id = products.id)
         ->  Seq Scan on sales  (cost=0.00..431.14 rows=10000 width=12)
         ->  Hash  (cost=431.00..431.00 rows=250 width=15)
               ->  Seq Scan on products  (cost=0.00..431.00 rows=250 width=15)
 Optimizer: Pivotal Optimizer (GPORCA)
(7 rows)

Тестовая таблица

CREATE TABLE sales
(
    id     INT,
    date   DATE,
    region TEXT,
    amount DECIMAL(10, 2)
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (id);

INSERT INTO sales (id, date, region, amount)
SELECT gs.id,
       DATE '2025-01-01' + (gs.id % 90),
       CASE WHEN gs.id % 2 = 0 THEN 'Asia' ELSE 'Europe' END,
       round((random() * 1000)::NUMERIC, 2)
FROM generate_series(1, 40000) AS gs(id);

Помимо параметра ANALYZE, который исполняет запрос для сбора статистики выполнения, EXPLAIN поддерживает и другие параметры. Эти параметры управляют уровнем детализации плана, форматом вывода и отображением дополнительной информации.

Параметр COSTS OFF скрывает оценочные затраты на выполнение в плане запроса:

EXPLAIN (COSTS OFF)
SELECT date, amount
FROM sales
WHERE region = 'Asia';

Результат выглядит так:

                QUERY PLAN
------------------------------------------
 Gather Motion 4:1  (slice1; segments: 4)
   ->  Seq Scan on sales
         Filter: (region = 'Asia'::text)
 Optimizer: Pivotal Optimizer (GPORCA)
(4 rows)

Параметр VERBOSE отображает подробную информацию о плане запроса, включая внутренние идентификаторы узлов, имена отношений (relation) и сведения о столбцах:

EXPLAIN (VERBOSE)
SELECT date, amount
FROM sales
WHERE region = 'Asia';

Результат выглядит так:

                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Gather Motion 4:1  (slice1; segments: 4)  (cost=0.00..432.33 rows=19973 width=10)
   Output: date, amount
   ->  Seq Scan on public.sales  (cost=0.00..431.66 rows=4994 width=10)
         Output: date, amount
         Filter: (sales.region = 'Asia'::text)
 Optimizer: Pivotal Optimizer (GPORCA)
(6 rows)

ПРИМЕЧАНИЕ

Включив дополнительные параметры конфигурации, такие как explain_memory_verbosity и gp_enable_explain_allstat, и используя EXPLAIN (ANALYZE, VERBOSE) вместо EXPLAIN ANALYZE, вы можете получить более подробную информацию об использовании памяти и выполнении запроса.

Вы можете комбинировать несколько параметров EXPLAIN:

EXPLAIN (COSTS OFF, VERBOSE)
SELECT date, amount
FROM sales
WHERE region = 'Asia';

Результат будет выглядеть следующим образом:

                  QUERY PLAN
-----------------------------------------------
 Gather Motion 4:1  (slice1; segments: 4)
   Output: date, amount
   ->  Seq Scan on public.sales
         Output: date, amount
         Filter: (sales.region = 'Asia'::text)
 Optimizer: Pivotal Optimizer (GPORCA)
(6 rows)

Можно настроить EXPLAIN для вывода плана запроса в различных форматах, например JSON, XML или YAML. В примере ниже используется FORMAT JSON для вывода плана в формате JSON:

EXPLAIN (FORMAT JSON)
SELECT date, amount
FROM sales
WHERE region = 'Asia';

Результат выглядит так:

                   QUERY PLAN
-------------------------------------------------
 [                                              +
   {                                            +
     "Plan": {                                  +
       "Node Type": "Gather Motion",            +
       "Senders": 4,                            +
       "Receivers": 1,                          +
       "Slice": 1,                              +
       "Segments": 4,                           +
       "Gang Type": "primary reader",           +
       "Startup Cost": 0.00,                    +
       "Total Cost": 432.33,                    +
       "Plan Rows": 19972,                      +
       "Plan Width": 10,                        +
       "Plans": [                               +
         {                                      +
           "Node Type": "Seq Scan",             +
           "Parent Relationship": "Outer",      +
           "Slice": 1,                          +
           "Segments": 4,                       +
           "Gang Type": "primary reader",       +
           "Relation Name": "sales",            +
           "Alias": "sales",                    +
           "Startup Cost": 0.00,                +
           "Total Cost": 431.66,                +
           "Plan Rows": 19972,                  +
           "Plan Width": 10,                    +
           "Filter": "(region = 'Asia'::text)"  +
         }                                      +
       ]                                        +
     },                                         +
     "Settings": {                              +
       "Optimizer": "Pivotal Optimizer (GPORCA)"+
     }                                          +
   }                                            +
 ]
(1 row)

Тестовая таблица

CREATE TABLE sales
(
    id     INT,
    date   DATE,
    region TEXT,
    amount DECIMAL(10, 2)
)
    DISTRIBUTED BY (id);

INSERT INTO sales (id, date, region, amount)
SELECT gs.id,
       DATE '2025-01-01' + (gs.id % 90),
       CASE WHEN gs.id % 2 = 0 THEN 'Asia' ELSE 'Europe' END,
       round((random() * 1000)::NUMERIC, 2)
FROM generate_series(1, 40000) AS gs(id);

EXPLAIN также можно использовать с операциями изменения данных, такими как INSERT, UPDATE или DELETE. В приведенном ниже примере показан план вставки строк в таблицу без фактического выполнения вставки:

EXPLAIN
INSERT INTO sales (id, date, region, amount)
SELECT gs.id,
       DATE '2025-01-01' + (gs.id % 90),
       CASE WHEN gs.id % 2 = 0 THEN 'Asia' ELSE 'Europe' END,
       round((random() * 1000)::NUMERIC, 2)
FROM generate_series(1, 40000) AS gs(id);

План запроса выглядит следующим образом:

                                          QUERY PLAN
----------------------------------------------------------------------------------------------
 Insert  (cost=0.00..27.42 rows=250 width=24)
   ->  Result  (cost=0.00..0.08 rows=250 width=28)
         ->  Result  (cost=0.00..0.05 rows=250 width=24)
               ->  Result  (cost=0.00..0.01 rows=250 width=4)
                     ->  Function Scan on generate_series  (cost=0.00..0.00 rows=250 width=4)
 Optimizer: Pivotal Optimizer (GPORCA)
(6 rows)

Чтобы проанализировать запрос на изменение данных без изменения таблиц, выполните его внутри транзакции и затем выполните откат:

BEGIN;

EXPLAIN ANALYZE
INSERT INTO sales (id, date, region, amount)
SELECT gs.id,
       DATE '2025-01-01' + (gs.id % 90),
       CASE WHEN gs.id % 2 = 0 THEN 'Asia' ELSE 'Europe' END,
       round((random() * 1000)::NUMERIC, 2)
FROM generate_series(1, 40000) AS gs(id);

ROLLBACK;

Результат будет следующим:

BEGIN
                                                                 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 Insert  (cost=0.00..27.42 rows=250 width=24) (actual time=3.567..55.458 rows=10055 loops=1)
   ->  Result  (cost=0.00..0.08 rows=250 width=28) (actual time=3.211..49.076 rows=10055 loops=1)
         ->  Result  (cost=0.00..0.05 rows=250 width=24) (actual time=3.209..38.545 rows=10055 loops=1)
               ->  Result  (cost=0.00..0.01 rows=250 width=4) (actual time=3.179..11.862 rows=10055 loops=1)
                     ->  Function Scan on generate_series  (cost=0.00..0.00 rows=250 width=4) (actual time=3.174..6.194 rows=40000 loops=1)
 Planning time: 4.740 ms
   (slice0)    Executor memory: 2745K bytes avg x 4 workers, 2745K bytes max (seg0).  Work_mem: 1763K bytes max.
 Memory used:  128000kB
 Optimizer: Pivotal Optimizer (GPORCA)
 Execution time: 67.447 ms
(10 rows)

ROLLBACK

Если запрос выполняется медленно, проанализируйте его план выполнения и задайте следующие вопросы:

Занимают ли операции в плане аномально много времени?

Обратите внимание на операцию, которая потребляет большую часть времени выполнения запроса. Например, если индексное сканирование занимает больше времени, чем ожидалось, индекс может быть устаревшим и его нужно перестроить. Вы также можете изменить параметры enable_<operator> (например, enable_hashjoin, enable_mergejoin), чтобы указать планировщику Postgres выбрать другой план, деактивировав определенный оператор плана для этого запроса.
Превышает ли время планирования запроса время его выполнения?

Когда запрос соединяет много таблиц, планировщик Postgres использует динамический алгоритм, частично зависящий от числа соединений. Вы можете уменьшить время, затрачиваемое на планирование запроса, установив серверные параметры join_collapse_limit и from_collapse_limit в меньшее значение, например, 8. Учитывайте, что меньшие значения уменьшают время планирования, но могут приводить к менее оптимальным планам.
Соответствуют ли оценки оптимизатора реальным данным?

Выполните EXPLAIN ANALYZE и проверьте, близко ли количество строк, оцениваемое оптимизатором, к фактическому количеству строк, возвращаемых запросом. Если наблюдается значительное расхождение, обновите статистику для соответствующих столбцов.
Применяются ли селективные условия на раннем этапе плана?

Применяйте самые селективные фильтры в начале плана, чтобы сократить число строк, перемещающихся вверх по дереву плана. Если план запроса некорректно оценивает селективность условий, обновите статистику для соответствующих столбцов. Можно также попробовать изменить порядок условий WHERE в SQL-запросе.
Выбирает ли оптимизатор оптимальный порядок соединений?

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

Если план не использует оптимальный порядок соединений, установите join_collapse_limit=1 и используйте явный синтаксис JOIN в SQL-запросе, чтобы принудить планировщик Postgres к заданному порядку соединений. Вы также можете обновить статистику по соответствующим столбцам, используемым для соединений.
Селективно ли оптимизатор сканирует партиционированные таблицы?

Если вы используете партиционирование, сканирует ли оптимизатор только те дочерние таблицы, которые необходимы для выполнения условий запроса? При сканировании родительских таблиц результат должен быть пустым, так как данные в них отсутствуют.
Выбирает ли оптимизатор операции Hash Aggregate и Hash Join там, где это применимо?

Hash-операции обычно значительно быстрее других типов соединений или агрегаций. Сравнение строк и сортировка выполняются в памяти, без обращения к диску. Для применения hash-операций оптимизатору требуется достаточное количество памяти для хранения оценочного числа строк. Попробуйте увеличить work_mem для улучшения производительности запроса. Если возможно, выполните EXPLAIN ANALYZE для запроса, чтобы определить, какие операции плана создавали spill-файлы на диске, сколько памяти они использовали и какой объем памяти был необходим, чтобы избежать их создания. Узнайте больше в разделе Команда EXPLAIN ANALYZE.

JSON

PL/Container

Анализ запросов

EXPLAIN

Обзор

Как читать планы EXPLAIN

Узлы плана запроса

Узлы сканирования

Узлы соединения

Узлы перемещения данных

Другие узлы

Оценка стоимости выполнения

EXPLAIN ANALYZE

Анализ SELECT-запросов

Фильтрация

Сортировка

Группировка

Индексы

Партиционированные таблицы

Соединения

Параметры EXPLAIN

Форматы вывода

Анализ запросов, изменяющих данные

Выявление проблем с производительностью запросов