Конференция PG Day’16 приближается с каждым днём, а мы продолжаем публиковать серию статей Хуберт Любачевски о объяснении анализа и его основных операциях.
В этом я надеюсь предпоследний почта ряд Я расскажу об остальных наиболее распространенных операциях, которые вы можете найти в выводе объяснения.
Уникальный
Название операции говорит само за себя – она удаляет повторяющиеся данные.Это может произойти, например, если вы сделаете следующее:
В более поздних версиях Postgres этот запрос будет выполняться с использованием HashAggregate. Проблема с Unique в том, что его данные необходимо сортировать.select distinct field from table
Не потому, что для этой операции нужны данные в определенном порядке, а чтобы все строки с одинаковыми значениями были «вместе».
Это делает Unique действительно крутой операцией (в тех случаях, когда ее можно использовать), поскольку она практически не требует памяти.
Он просто сравнивает значение в предыдущей строке с текущим и, если они совпадают, отбрасывает его.
Вот и все.
Таким образом, мы можем стимулировать его использование путем предварительной сортировки данных: $ explain select distinct relkind from (select relkind from pg_class order by relkind) as x;
QUERY PLAN
-----------------------------------------------------------------------
Unique (cost=22.88.27.26 rows=4 width=1)
-> Sort (cost=22.88.23.61 rows=292 width=1)
Sort Key: pg_class.relkind
-> Seq Scan on pg_class (cost=0.00.10.92 rows=292 width=1)
(4 rows)
Добавить
Этот план просто выполняет множество подопераций и возвращает все строки, которые они возвращают, как общий результат. Это используется запросами UNION/UNION ALL: $ explain select oid from pg_class union all select oid from pg_proc union all select oid from pg_database;
QUERY PLAN
-----------------------------------------------------------------
Append (cost=0.00.104.43 rows=2943 width=4)
-> Seq Scan on pg_class (cost=0.00.10.92 rows=292 width=4)
-> Seq Scan on pg_proc (cost=0.00.92.49 rows=2649 width=4)
-> Seq Scan on pg_database (cost=0.00.1.02 rows=2 width=4)
(4 rows)
Здесь вы можете увидеть, как приложение выполнило три сканирования трех таблиц и вернуло все строки вместе.
Обратите внимание, что я использовал UNION ALL. Если бы я использовал UNION, мы получили бы следующее: $ explain select oid from pg_class union select oid from pg_proc union select oid from pg_database;
QUERY PLAN
-----------------------------------------------------------------------
HashAggregate (cost=141.22.170.65 rows=2943 width=4)
-> Append (cost=0.00.133.86 rows=2943 width=4)
-> Seq Scan on pg_class (cost=0.00.10.92 rows=292 width=4)
-> Seq Scan on pg_proc (cost=0.00.92.49 rows=2649 width=4)
-> Seq Scan on pg_database (cost=0.00.1.02 rows=2 width=4)
(5 rows)
Это происходит потому, что UNION удаляет повторяющиеся строки, что в данном случае было выполнено операцией HashAggregate.
Результат
Результат появляется в основном в очень простых тестовых запросах.
Эта операция используется, когда ваш запрос выбирает какое-то постоянное значение (или значения): $ explain select 1, 2;
QUERY PLAN
------------------------------------------
Result (cost=0.00.0.01 rows=1 width=0)
(1 row)
Помимо тестовых запросов, его можно встретить в запросах, которые делают что-то вроде «вставить, но только если это не будет дублировать данные»: $ explain insert into t (i) select 1 where not exists (select * from t where i = 1);
QUERY PLAN
---------------------------------------------------------------------
Insert on t (cost=3.33.3.35 rows=1 width=4)
-> Result (cost=3.33.3.34 rows=1 width=0)
One-Time Filter: (NOT $0)
InitPlan 1 (returns $0)
-> Seq Scan on t t_1 (cost=0.00.40.00 rows=12 width=0)
Filter: (i = 1)
(6 rows)
Сканирование значений
Так же, как и Result, Values Scan используется для возврата простых данных, введенных в запрос, но в этом случае это может быть целый набор записей на основе функционала VALUES().
Если вы не знаете, вы можете выбрать множество строк и столбцов без какой-либо таблицы, просто используя синтаксис ЗНАЧЕНИЙ, как в этом примере: $ select * from ( values (1, 'hubert'), (2, 'depesz'), (3, 'lubaczewski') ) as t (a,b);
a | b
---+-------------
1 | hubert
2 | depesz
3 | lubaczewski
(3 rows)
План такого запроса выглядит следующим образом: QUERY PLAN
--------------------------------------------------------------
Values Scan on "*VALUES*" (cost=0.00.0.04 rows=3 width=36)
(1 row)
Эта операция обычно используется в INSERT, но у нее есть и другие применения, например: пользовательская сортировка .
ГруппаАгрегат
Эта операция аналогична HashAggregate, о которой мы говорим.Разница в том, что для работы GroupAggregate данные должны быть отсортированы по столбцу или столбцам, которые вы использовали в предложении GROUP BY. Как и Unique, GroupAggregate использует очень мало памяти, но требует организации данных.
Пример: $ explain select relkind, count(*) from (select relkind from pg_class order by relkind) x group by relkind;
QUERY PLAN
-----------------------------------------------------------------------
GroupAggregate (cost=22.88.28.03 rows=4 width=1)
-> Sort (cost=22.88.23.61 rows=292 width=1)
Sort Key: pg_class.relkind
-> Seq Scan on pg_class (cost=0.00.10.92 rows=292 width=1)
(4 rows)
Хэшсетоп
Эта операция используется операциями INTERSECT/EXCEPT (с необязательным модификатором «ALL»).Это работает следующим образом: он запускает подоперации Append для пары подзапросов, а затем на основе результата и необязательного модификатора ALL решает, какие строки возвращать.
Я не вникал глубоко в исходный код, поэтому не могу точно сказать, как это работает, но судя по названию, операция похожа на простое решение на основе счетчиков.
Мы видим, что в отличие от UNION эти операции работают с двумя источниками данных: $ explain select * from (select oid from pg_Class order by oid) x intersect all select * from (select oid from pg_proc order by oid) y;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
HashSetOp Intersect All (cost=0.15.170.72 rows=292 width=4)
-> Append (cost=0.15.163.36 rows=2941 width=4)
-> Subquery Scan on "*SELECT* 1" (cost=0.15.18.37 rows=292 width=4)
-> Index Only Scan using pg_class_oid_index on pg_class (cost=0.15.12.53 rows=292 width=4)
-> Subquery Scan on "*SELECT* 2" (cost=0.28.145.00 rows=2649 width=4)
-> Index Only Scan using pg_proc_oid_index on pg_proc (cost=0.28.92.02 rows=2649 width=4)
(6 rows)
А с тремя источниками получаем более сложное дерево: $ explain select * from (select oid from pg_Class order by oid) x intersect all select * from (select oid from pg_proc order by oid) y intersect all select * from (Select oid from pg_database order by oid) as w;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
HashSetOp Intersect All (cost=1.03.172.53 rows=2 width=4)
-> Append (cost=1.03.171.79 rows=294 width=4)
-> Subquery Scan on "*SELECT* 3" (cost=1.03.1.07 rows=2 width=4)
-> Sort (cost=1.03.1.03 rows=2 width=4)
Sort Key: pg_database.oid
-> Seq Scan on pg_database (cost=0.00.1.02 rows=2 width=4)
-> Result (cost=0.15.170.72 rows=292 width=4)
-> HashSetOp Intersect All (cost=0.15.170.72 rows=292 width=4)
-> Append (cost=0.15.163.36 rows=2941 width=4)
-> Subquery Scan on "*SELECT* 1" (cost=0.15.18.37 rows=292 width=4)
-> Index Only Scan using pg_class_oid_index on pg_class (cost=0.15.12.53 rows=292 width=4)
-> Subquery Scan on "*SELECT* 2" (cost=0.28.145.00 rows=2649 width=4)
-> Index Only Scan using pg_proc_oid_index on pg_proc (cost=0.28.92.02 rows=2649 width=4)
(13 rows)
КТР-сканирование
Эта операция аналогична уже упоминалось Операция «Материализовать».Он запускает часть запроса и сохраняет его выходные данные, чтобы их можно было использовать в другой части (или частях) запроса.
Пример: $ explain analyze with x as (select relname, relkind from pg_class) select relkind, count(*), (select count(*) from x) from x group by relkind;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
HashAggregate (cost=24.80.26.80 rows=200 width=1) (actual time=0.466.0.468 rows=6 loops=1)
CTE x
-> Seq Scan on pg_class (cost=0.00.10.92 rows=292 width=65) (actual time=0.009.0.127 rows=295 loops=1)
InitPlan 2 (returns $1)
-> Aggregate (cost=6.57.6.58 rows=1 width=0) (actual time=0.085.0.085 rows=1 loops=1)
-> CTE Scan on x x_1 (cost=0.00.5.84 rows=292 width=0) (actual time=0.000.0.055 rows=295 loops=1)
-> CTE Scan on x (cost=0.00.5.84 rows=292 width=1) (actual time=0.012.0.277 rows=295 loops=1)
Total runtime: 0.524 ms
(8 rows)
Обратите внимание, что pg_class сканируется только один раз — строка №6. Но его результаты сохраняются в «x», а затем сканируются дважды — внутри агрегата (строка №9) и с помощью операции HashAggregate (10).
В чем отличие от Materialize? Чтобы дать подробный ответ на этот вопрос, придется углубиться в исходный код, но я бы сказал, что разница основана на том простом факте, что CTE определяются пользователем, а Materialize — это служебная операция, которую Postgres решает использовать, когда оно считает нужным.
Важное примечание: CTE всегда запускаются точно так, как указано.
Следовательно, их можно использовать для обхода некоторых плохих оптимизаций, которые может реализовать планировщик.
ИнитПлан
Этот план применяется всякий раз, когда есть часть запроса, которая может (или должна) быть оценена раньше всего остального и не зависит ни от чего в остальной части вашего запроса.
Допустим, вам нужен такой запрос: $ explain select * from pg_class where relkind = (select relkind from pg_class order by random() limit 1);
QUERY PLAN
------------------------------------------------------------------------------------------
Seq Scan on pg_class (cost=13.11.24.76 rows=73 width=203)
Filter: (relkind = $0)
InitPlan 1 (returns $0)
-> Limit (cost=13.11.13.11 rows=1 width=1)
-> Sort (cost=13.11.13.84 rows=292 width=1)
Sort Key: (random())
-> Seq Scan on pg_class pg_class_1 (cost=0.00.11.65 rows=292 width=1)
(7 rows)
В этом случае необходимо запустить limit/sort/seq-scan перед обычным последовательным сканированием pg_class, поскольку Postgres потребуется сравнить значение relkind со значением, возвращаемым подзапросом.
С другой стороны, я мог бы написать: $ explain select *, (select length('depesz')) from pg_class;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on pg_class (cost=0.01.10.93 rows=292 width=203)
InitPlan 1 (returns $0)
-> Result (cost=0.00.0.01 rows=1 width=0)
(3 rows)
Постгрес правильно видит, что столбец subselect не зависит ни от каких данных из таблицы pg_class, поэтому его можно запустить один раз и не нужно пересчитывать длину для каждой строки.
Конечно, у вас может быть много планов инициализации, например: $ explain select *, (select length('depesz')) from pg_class where relkind = (select relkind from pg_class order by random() limit 1);
QUERY PLAN
------------------------------------------------------------------------------------------
Seq Scan on pg_class (cost=13.12.24.77 rows=73 width=203)
Filter: (relkind = $1)
InitPlan 1 (returns $0)
-> Result (cost=0.00.0.01 rows=1 width=0)
InitPlan 2 (returns $1)
-> Limit (cost=13.11.13.11 rows=1 width=1)
-> Sort (cost=13.11.13.84 rows=292 width=1)
Sort Key: (random())
-> Seq Scan on pg_class pg_class_1 (cost=0.00.11.65 rows=292 width=1)
(9 rows)
Но стоит учесть одну деталь — планы инициализации внутри одного запроса нумеруются «глобально», а не по операциям.
Подплан
Подпланы чем-то похожи на NestedLoop. В том смысле, что их тоже можно вызывать много раз.SubPlan вызывается для вычисления данных из подзапроса, который фактически зависит от текущей строки.
Например: $ explain analyze select c.relname, c.relkind, (Select count(*) from pg_Class x where c.relkind = x.relkind) from pg_Class c;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Seq Scan on pg_class c (cost=0.00.3468.93 rows=292 width=65) (actual time=0.135.26.717 rows=295 loops=1)
SubPlan 1
-> Aggregate (cost=11.83.11.84 rows=1 width=0) (actual time=0.090.0.090 rows=1 loops=295)
-> Seq Scan on pg_class x (cost=0.00.11.65 rows=73 width=0) (actual time=0.010.0.081 rows=93 loops=295)
Filter: (c.relkind = relkind)
Rows Removed by Filter: 202
Total runtime: 26.783 ms
(7 rows)
Для каждой строки, возвращаемой при сканировании по «pg_class as c», Postgres должен запускать SubPlan, который проверяет, сколько строк в pg_class имеют такое же (как только что обработанная строка) значение в столбце relkind.
Обратите внимание на «loops=295» в строке «Seq Scan on pg_class x» и соответствующее значение «rows=295» в узле «Seq Scan on pg_class c».
Другой?
Да, есть и другие операции.Некоторые из них слишком редки, чтобы заслуживать нашего внимания (тем более, что у вас есть отличный источник знаний — исходный код), а некоторые представляют собой (я подозреваю) старые версии новых узлов.
Если у вас есть план с операцией, о котором я не рассказал, и он вам не ясен, напишите мне в комментариях со ссылкой на вывод объяснения по адресу объяснение.
depesz.com , имя операции и версия Posgres, в которой вы с ней столкнулись.
Я постараюсь найти всю возможную информацию по таким случаям и дать вам подробный ответ. Теги: #sql #postgresql #explain #база данных #разработка веб-сайтов #postgresql #sql
-
Национальная Кибербезопасность
19 Oct, 24 -
Urthecast — Привет, Мир!
19 Oct, 24 -
Thq Потеряла Половину Своей Капитализации
19 Oct, 24 -
Proreport - Интерактивный Кабинет Отчетности
19 Oct, 24 -
Cnews Изменил Формат
19 Oct, 24
