EXPLAIN

Команда EXPLAIN добавляется перед DQL- и DML-запросами для того, чтобы показать, как будет выглядеть план исполнения запроса, при этом не выполняя сам запрос. План строится на узле, к которому подключился пользователь, и позволяет наглядно оценить структуру и последовательность действий при выполнении запроса. EXPLAIN является инструментом для анализа и оптимизации запросов.

Синтаксис

Структура плана запроса

План запроса выглядит как граф, исполнение которого происходит снизу вверх.

Для DQL-запросов основными элементами являются:

• scan — сканирование (получение данных) таблицы
• projection — уточненный набор колонок таблицы для выполнения запроса

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

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

Дополнительно, запрос EXPLAIN показывает следующие элементы:

• текущие значения VDBE_MAX_STEPS и VDBE_MAX_ROWS, служащие для ограничения запросов
• расчетный диапазон бакетов, на которых будет выполнен запрос

Пример получения столбца таблицы целиком:

EXPLAIN SELECT item FROM warehouse;

Результат:

projection ("warehouse"."item"::string -> "item")
    scan "warehouse"
execution options:
    vdbe_max_steps = 45000
    vtable_max_rows = 5000
buckets = [1-3000]

В одном запросе может быть несколько узлов scan и projection, в зависимости от количества обращений к таблицам, наличия условий, подзапросов и т.п. В общем случае, каждому ключевому слову SELECT соответствует своя проекция, а каждому FROM — свое сканирование.

Запрос с условием

Построение проекции (projection) всегда происходит после сканирования. В рамках построения проекции планировщик создает псевдоним для столбца: "ORDERS"."AMOUNT" -> "AMOUNT".

Если в запросе есть условие (where), то в план добавляется узел selection:

EXPLAIN SELECT amount FROM orders WHERE amount > 1000;

Вывод в консоль:

projection ("ORDERS"."AMOUNT"::integer -> "AMOUNT")
    selection ROW("ORDERS"."AMOUNT"::integer) > ROW(1000::unsigned)
        scan "ORDERS"

Если projection выбирает столбцы (атрибуты таблицы), то selection фильтрует данные по строкам (ROW).

Фраза selection ROW("ORDERS"."AMOUNT"::integer) > ROW(1000::unsigned) является результатом трансформации фильтра WHERE "AMOUNT" > 1000 в WHERE ("AMOUNT") > (1000), т.е. превращения значения в строку из одного столбца.

Запрос с несколькими проекциями

Пример построения проекции из более сложного запроса:

EXPLAIN SELECT id,name FROM items
EXCEPT
SELECT id,item FROM orders
WHERE amount > 1000;

Вывод в консоль:

except
    projection ("ITEMS"."ID"::integer -> "ID", "ITEMS"."NAME"::string -> "NAME")
        scan "ITEMS"
    projection ("ORDERS"."ID"::integer -> "ID", "ORDERS"."ITEM"::string -> "ITEM")
        selection ROW("ORDERS"."AMOUNT"::integer) > ROW(1000::unsigned)
            scan "ORDERS"

В таком плане запроса присутствует два блока projection, перед которыми стоит логическое условие (EXCEPT). В каждом блоке есть свое сканирование таблицы и, опционально, дополнительный фильтр по строкам (selection).

Варианты перемещения данных

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

1. Локальная вставка. Представляет собой локальную материализацию данных с подсчетом значений bucket_id для каждого кортежа (соответственно, кортежи будут сгруппированы по этим бакетам). Перемещения данных на другие узлы хранения через узел-маршрутизатор не происходит. На текущем узле хранения будет локально создана виртуальная таблица из результатов читающего запроса или из переданных VALUES, а потом данные из нее будут вставлены локально в целевую таблицу. Планировщик отобразит значение motion [policy: local segment].
2. Локальная материализация. Данный вариант аналогичен предыдущему с той разницей, что при материализации данных не происходит вычисление bucket_id. При таком запросе планировщик отобразит значение motion [policy: local].
3. Частичное перемещение. При выполнении запроса на каждый узел кластера будет отправлена только востребованная часть данных (таблица перераспределяется по новому ключу). При таком запросе планировщик отобразит значение motion [policy: segment].
4. Полное перемещение. На каждый узел кластера будет отправлена вся таблица. Планировщик отобразит значение motion [policy: full].

Перемещение данных происходит в тех случаях, когда в запросе требуется обработать данные из нескольких таблиц или несколько раз из одной таблицы (JOIN, EXCEPT, подзапросы), а также при выполнении агрегатных функций (SUM, COUNT...). Перемещение данных происходит по следующей схеме:

• на узле-маршрутизаторе (router) собираются запрошенные данные со всех узлов хранения (storage);
• в случае частичного перемещения (motion [policy: segment]), собранные данные объединяются в виртуальную таблицу с новым ключом шардирования;
• узел-маршрутизатор отправляет на узлы хранения только нужные им строки из этой виртуальной таблицы.

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

Вариант перемещения данных (motion policy) зависит от того, какие данные доступны на локальных узлах хранения. При простом чтении из одной таблицы перемещения нет никогда. При работе с несколькими таблицами перемещения также может не быть, если в каждой части запроса адресуются те столбцы, по которым таблица распределена (указан ключ шардирования). При этом, использование агрегатных функций и/или соединения при работе с одной или несколькими таблицами может потребовать частичного или полного перемещения данных.

Примеры разных вариантов motion policy приведены ниже.

Локальная вставка

Локальная вставка характерна для INSERT с передачей строки значений:

EXPLAIN INSERT INTO warehouse VALUES (1, 'bricks', 'heavy');

Вывод в консоль:

insert "WAREHOUSE" on conflict: fail
    motion [policy: segment([ref("COLUMN_1")])]
        values
            value row (data=ROW(1::unsigned, 'bricks'::string, 'heavy'::string))

Локальная материализация

Локальная материализация относится к тем случаям, когда требуется положить в память прочитанные данные из локального запроса для их дальнейшей обработки. Перемещения данных нет и вычисление bucket_id не требуется (см. подробнее). Примером может служить удаление данных из таблицы:

EXPLAIN DELETE FROM warehouse WHERE id = 1;

Вывод в консоль:

delete "WAREHOUSE"
    motion [policy: local]
        projection ("WAREHOUSE"."ID"::integer -> pk_col_0)
            selection ROW("WAREHOUSE"."ID"::integer) = ROW(1::unsigned)
                scan "WAREHOUSE"

Локальная материализация происходит и при обновлении данных в тех случаях, если не затрагивается колонка, по которой таблица шардирована. Например, если при создании таблицы было указано шардирование по колонке ID (distributed by (id)), то обновление данных в других колонках не приведет к их перемещению через узел-маршрутизатор. Поскольку при UPDATE не происходит пересчет bucket_id, то планировщик использует политику local:

EXPLAIN UPDATE warehouse SET type = 'N/A';

Вывод в консоль:

update "WAREHOUSE"
"TYPE" = COL_0
    motion [policy: local]
        projection ('N/A'::string -> COL_0, "WAREHOUSE"."ID"::integer -> COL_1)
            scan "WAREHOUSE"

Частичное перемещение

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

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

EXPLAIN INSERT INTO orders (id, item, amount) SELECT * FROM items WHERE id = 5;

Вывод в консоль:

insert "ORDERS" on conflict: fail
    motion [policy: local segment([ref("ID")])]
        projection ("ITEMS"."ID"::integer -> "ID", "ITEMS"."NAME"::string -> "NAME", "ITEMS"."STOCK"::integer -> "STOCK")
            selection ROW("ITEMS"."ID"::integer) = ROW(5::unsigned)
                scan "ITEMS"

Пример JOIN двух таблиц с разными ключами шардирования:

EXPLAIN SELECT id,item FROM orders
JOIN
(SELECT nmbr,product FROM deliveries) AS new_table
ON orders.id=new_table.nmbr;

Вывод в консоль:

projection ("ORDERS"."ID"::integer -> "ID", "ORDERS"."ITEM"::string -> "ITEM")
    join on ROW("ORDERS"."ID"::integer) = ROW("NEW_TABLE"."NMBR"::integer)
        scan "ORDERS"
            projection ("ORDERS"."ID"::integer -> "ID", "ORDERS"."ITEM"::string -> "ITEM", "ORDERS"."AMOUNT"::integer -> "AMOUNT", "ORDERS"."SINCE"::datetime -> "SINCE")
                scan "ORDERS"
        motion [policy: segment([ref("NMBR")])]
            scan "NEW_TABLE"
                projection ("DELIVERIES"."NMBR"::integer -> "NMBR", "DELIVERIES"."PRODUCT"::string -> "PRODUCT")
                    scan "DELIVERIES"

Пример UPDATE с обновлением колонки, по которой шардирована таблица (например, distributed by (product)):

EXPLAIN UPDATE deliveries SET product = 'metals',quantity = 4000 WHERE nmbr = 1;

Вывод в консоль:

update "DELIVERIES"
"NMBR" = COL_0
"PRODUCT" = COL_1
"QUANTITY" = COL_2
    motion [policy: segment([])]
        projection ("DELIVERIES"."NMBR"::integer -> COL_0, 'metals'::string -> COL_1, 4000::unsigned -> COL_2, "DELIVERIES"."PRODUCT"::string -> COL_3)
            selection ROW("DELIVERIES"."NMBR"::integer) = ROW(1::unsigned)
                scan "DELIVERIES"

Полное перемещение

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

Пример JOIN с соединениям не по колонкам шардирования для обеих таблиц:

EXPLAIN SELECT NAME FROM items
JOIN
(SELECT item FROM orders) AS new_table
ON items.name = new_table.item;

Вывод в консоль:

projection ("ITEMS"."NAME"::string -> "NAME")
    join on ROW("ITEMS"."NAME"::string) = ROW("NEW_TABLE"."ITEM"::string)
        scan "ITEMS"
            projection ("ITEMS"."ID"::integer -> "ID", "ITEMS"."NAME"::string -> "NAME", "ITEMS"."STOCK"::integer -> "STOCK")
                scan "ITEMS"
        motion [policy: full]
            scan "NEW_TABLE"
                projection ("ORDERS"."ITEM"::string -> "ITEM")
                    scan "ORDERS"

Пример выполнения агрегатной функции.

EXPLAIN SELECT COUNT(id) FROM warehouse;

Вывод в консоль:

projection (sum(("0e660ad12ab24037a48f169fcf315549_count_11"::integer))::decimal -> "COL_1")
    motion [policy: full]
        scan
            projection (count(("WAREHOUSE"."ID"::integer))::integer -> "0e660ad12ab24037a48f169fcf315549_count_11")
                scan "WAREHOUSE"

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

Планировщик SQL-запросов в Picodata показывает, на каких бакетах будет исполнен запрос. В зависимости от характера запроса, это значение будет выглядеть как:

• подмножество ([1410,1934,1958])
• номер бакета ([215])
• любой бакет (any)
• неизвестный набор бакетов (unknown)

Подмножество

Подмножество бакетов может быть точно вычислено для запросов без перемещения данных и вызванного им добавления в план узлов motion. При использовании фильтра с конкретными значениями это будет список соответствующих им бакетов:

EXPLAIN SELECT * FROM warehouse WHERE id IN (1,2,3);

Вывод в консоль:

projection ("warehouse"."id"::integer -> "id", "warehouse"."item"::string -> "item", "warehouse"."type"::string -> "type")
    selection ROW("warehouse"."id"::integer) in ROW(1::unsigned, 2::unsigned, 3::unsigned)
        scan "warehouse"
execution options:
    vdbe_max_steps = 45000
    vtable_max_rows = 5000
buckets = [1410,1934,1958]

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

EXPLAIN UPDATE warehouse SET type = 'N/A';

Вывод в консоль:

update "warehouse"
"type" = "col_0"
    motion [policy: local]
        projection ('N/A'::string -> "col_0", "warehouse"."id"::integer -> "col_1")
            scan "warehouse"
execution options:
    vdbe_max_steps = 45000
    vtable_max_rows = 5000
buckets = [1-3000]

Номер бакета

В ряде случаев планировщик может определить конкретный номер бакета, на котором будет выполнен распределенный запрос. Такой вариант характерен для локальных DML-запросов. В примере ниже номер бакета выведен из условия (id = 5):

EXPLAIN INSERT INTO orders (id, item, amount) SELECT * FROM items WHERE id = 5;

Вывод в консоль:

insert "orders" on conflict: fail
    motion [policy: local segment([ref("id")])]
        projection ("items"."id"::integer -> "id", "items"."name"::string -> "name", "items"."stock"::integer -> "stock")
            selection ROW("items"."id"::integer) = ROW(5::unsigned)
                scan "items"
execution options:
    vdbe_max_steps = 45000
    vtable_max_rows = 5000
buckets = [219]

Любой бакеты

Значение any выводится для запросов к глобальным таблицам, которые присутствуют на всех шардах. Запрос будет выполнен на том же узле, к которому подключен клиент Picodata. Пример:

EXPLAIN SELECT id FROM _pico_table;

Вывод в консоль:

projection ("_pico_table"."id"::unsigned -> "id")
    scan "_pico_table"
execution options:
    vdbe_max_steps = 45000
    vtable_max_rows = 5000
buckets = any

Неизвестный набор

Значение unknown означает, что ни точный диапазон, ни конкретный номер бакета не могут быть посчитаны. Такая ситуация возможна, к примеру, при нелокальных DML-запросах, когда запрос затрагивает некоторое неизвестное количество шардов:

EXPLAIN UPDATE deliveries SET product = 'metals',quantity = 4000 WHERE nmbr = 1;

Вывод в консоль:

update "deliveries"
"nmbr" = "col_0"
"product" = "col_1"
"quantity" = "col_2"
    motion [policy: segment([])]
        projection ("deliveries"."nmbr"::integer -> "col_0", 'metals'::string -> "col_1", 4000::unsigned -> "col_2", "deliveries"."product"::string -> "col_3")
            selection ROW("deliveries"."nmbr"::integer) = ROW(1::unsigned)
                scan "deliveries"
execution options:
    vdbe_max_steps = 45000
    vtable_max_rows = 5000
buckets = unknown