Жизненный цикл запросов и план выполнения запроса на примере PostgreSQL - Часть 1

Всем привет.

Давно не публиковал полезности на канале. Сегодня хочу немного рассказать о жизненном цикле запросов и плане их выполнения.

Если не сильно усложнять, то жизненный цикл запроса можно разделить на 3 основных этапа:

1) Парсинг запроса. На этом этапе парсер проверяет синтаксис запроса, компилирует SQL в более подходящий для компьютера вид на основе хранимых в системе правил и отправляет запрос к базе данных.

2) Оптимизация и построение плана выполнения запроса. На этом этапе формируются планы-кандидаты, для которых расчитывается стоимость (query cost). На основе стоимости планировщик выбирает оптимальный план для выполнения запроса.

3) Непосредственное выполнение запроса. На этом этапе запрос выполняется, согласно выбранному плану, и возвращает результат пользователю.


Переходим непосредственно к плану выполнения запроса.
Чтобы увидеть план выполнения запроса, в PostgreSQL существует команда EXPLAIN. Важно отметить, что EXPLAIN не выполняет запрос, а только показывает план его выполнения.
Для того, чтобы отобразить план выполнения запроса, достаточно просто указать EXPLAIN перед основным запросом. Например:

EXPLAIN 
SELECT * FROM retail.orders_fact;

Запрос вернёт нам результат следующего вида:

Seq Scan on orders_fact  (cost=0.00..195.86 rows=9186 width=55)

Как мы видим, наш план выполнения запроса состоит из 1 шага Seq Scan (Sequential Scanning), что означает последовательное сканирование всех строк таблицы. В скобках указаны различные оценки (estimates) выполнения запроса:

- первое число в cost обозначает время, которое проходит, прежде чем начнётся этап вывода данных;

- второе число в cost обозначает общую стоимость выполнения. Общая стоимость выполнения = время начала вывода данных + время окончания вывода данных;

- число в rows указывает на число строк, которое должен обработать запрос;

- число в width отображает количество байт, которое обработает запрос для вывода нужных строк.

Давайте теперь рассмотрим другой пример:

EXPLAIN 
SELECT * FROM retail.product_dim 
WHERE category IN ('Furniture', 'Technology');

Запрос вернёт нам такой результат:

Seq Scan on product_dim  (cost=0.00..58.14 rows=862 width=91) 
-> Filter: ((category)::text = ANY ('{Furniture,Technology}'::text[]))

Как мы видим, оператор WHERE добавил шаг Filter. Здесь важно сказать, что план выполнения запроса, как правило, читается снизу-вверх или справа-налево. В данном случае мы читаем наш план снизу вверх:

1) Первым выполняется Filter, который отбирает только те строки, где категория товара равна или Furniture, или Technology.

2) После фильтрации происходит последовательное сканирование таблицы (отфильтрованных строк).

Теперь давайте сделаем трюк и создадим индекс на столбец category:

CREATE INDEX category_idx on retail.product_dim (category);

И снова выполним запрос:

EXPLAIN 
SELECT * FROM retail.product_dim 
WHERE category IN ('Furniture', 'Technology');

Теперь этот же запрос выдаёт нам такой результат:

Index Scan using category_idx on product_dim  (cost=0.28..41.71 rows=862 width=91) 
   Index Cond: ((category)::text = ANY
   ('{Furniture,Technology}'::text[]))

Как мы видим, теперь вместо 2-х этапов в плане выполнения запроса только один - Index Scan (сканирование индекса).
Index Cond объясняет, почему происходит сканирование индекса, указывая, что в WHERE мы фильтруемся по полю, на которое и создали индекс.
Также можно заметить, что добавление индекса сократило общую стоимость выполнения запроса с 58.14 до 41.71. Здесь мы имеем наглядный пример того, как индексы могут оптимизировать скорость выполнения запроса. Но не забываем, что индексами нужно пользоваться с умом (об этом я писал здесь).

На сегодня всё. Ждите 2 часть🙂