Конференция PG Day’16 с каждым днем всё ближе, а мы продолжаем публиковать серию статей Hubert Lubaczewski об анализе explain и его основных операциях.
В этом, надеюсь, предпоследнем посте серии я расскажу об оставшихся наиболее распространенных операциях, которые вы можете встретить в выводе explain.
Название операции говорит само за себя – она удаляет дублирующие данные.
Такое может случиться, например, когда вы делаете следующее:
В более свежих версиях Постгреса этот запрос будет осуществлен с помощью HashAggregate.
Проблема Unique заключается в том, что данные для неё должны быть отсортированы. Не потому, что этой операции нужны данные в определенном порядке, а для того, чтобы все строки с одинаковыми значениями были «вместе».
Это делает Unique реально классной операцией (в тех случаях, когда её можно использовать), так как она практически не требует памяти. Она просто сравнивает значение в предыдущей строке с текущим и, если они одинаковые, отбрасывает его. Вот и всё.
Таким образом, мы можем стимулировать её использование, предварительно отсортировав данные:
Этот план просто запускает множество субопераций и возвращает все возвращенные ими строки в виде общего результата.
Это используется запросами UNION/UNION ALL:
Здесь вы видите, как append запустил три сканирования по трем таблицам и вернул все строки вместе.
Обратите внимание, что я использовал UNION ALL. Если бы я использовал UNION, мы бы получили следующее:
Так происходит, потому что UNION удаляет дублируюшие строки, что в данном случае было произведено операцией HashAggregate.
Result появляется, в основном, в очень простых тестовых запросах. Эта операция используется, когда ваш запрос выбирает какое-либо постоянное значение (или значения):
Помимо тестовых запросов её можно встретить в запросах, которые делают что-то вроде «вставить, но только если это не будет дублированием данных»:
Так же, как Result, Values Scan используется для возвращения простых введенных в запросе данных, но в данном случае это может быть целый набор записей, основанный на функциональности VALUES().
Если вдруг вы не в курсе, вы можете выбрать множество строк и множество столбцов без какой-либо таблицы, просто используя синтаксис VALUES, как в этом примере:
План такого запроса выглядит следующим образом:
Эта операция обычно используется в INSERT’ах, но у неё есть и другие способы применения, например, кастомная сортировка.
Эта операция схожа с HashAggregate, о которой мы говорили ранее.
Разница в том, что для работы GroupAggregate данные должны быть отсортированы с помощью того столбца или столбцов, которые вы использовали в условии GROUP BY.
Как и Unique, GroupAggregate использует очень мало памяти, но требует упорядоченности данных.
Пример:
Эта операция используется операциями INTERSECT/EXCEPT (с опциональным модификатором «ALL»).
Она работает следующим образом: запускает субоперации Append для пары подзапросов, а затем, на основании результата и опционального модификатора ALL, решает, какие строки нужно вернуть. Я не залезал глубоко в исходный код, поэтому не могу сказать, как именно это работает, но, исходя их названия, операция похожа на простое решение на базе счетчика.
Мы видим, что, в отличие от UNION, эти операции работают с двумя источниками данных:
А с тремя источниками у нас получится более сложное дерево:
Эта операция схожа с уже упомянутой операцией Materialize. Она запускает часть запроса и сохраняет ее вывод, чтобы он мог быть использован другой частью (или частями) запроса.
Пример:
Обратите внимание, что pg_class сканируется всего один раз – строка #6. Но его результаты хранятся в “x" и потом сканируются дважды – внутри агрегата (строка #9) и операцией HashAggregate (10).
В чем же отличие от Materialize? Чтобы дать развернутый ответ на этот вопрос, нужно погрузиться в исходный код, но я бы сказал, что различие основывается на том простом факте, что CTE определяются пользователем, в то время как Materialize – это вспомогательная операция, которую Постгрес решает использовать, когда посчитает нужным.
Важное замечание: CTE всегда запускаются именно так, как указано. Поэтому их можно использовать для того, чтобы обойти не самые удачные оптимизации, которые может осуществить планировщик.
Этот план происходит каждый раз, когда есть часть запроса, которая может (или должна) быть рассчитана перед всем остальным и не зависит ни от чего в оставшейся части вашего запроса.
Допустим, вы хотите вот такой запрос:
В этом случае необходимо запустить limit/sort/seq-scan до обычного последовательного сканирования по pg_class, потому что Постгресу нужно будет сравнить значение relkind со значением, возвращенным подзапросом.
С другой стороны, я мог бы написать:
Постгрес правильно видит, что столбец subselect не зависит ни от каких данных из таблицы pg_class, поэтому его можно прогнать один раз и не нужно пересчитывать длину для каждой строки.
Конечно, у вас может быть много единичных планов (init plans), как здесь:
Но стоит учитывать одну деталь – init plan’ы внутри одного запроса нумеруются «глобально», а не по операциям.
SubPlan’ы чем-то похожи на NestedLoop. В том смысле, что они тоже могут вызываться много раз.
SubPlan вызывается, чтобы посчитать данные из субзапроса, которые реально зависят от текущей строки.
Например:
Для каждой строки, возвращенной сканированием по «pg_class as c», Постгрес должен запустить SubPlan, который проверяет, сколько строк в pg_class имеют такое же (как у только что обработанной строки) значение в столбце relkind.
Обратите внимание на «loops=295» в строке «Seq Scan on pg_class x» и соответствующее ему значение «rows=295» в узле «Seq Scan on pg_class c».
Да, есть и другие операции. Некоторые из них слишком редко встречаются, чтобы удостоиться нашего внимания (особенно если учесть, что у вас есть прекрасный источник знаний – исходники), а некоторые являются (как я подозреваю) старыми версиями новых узлов.
Если у вас есть план с операцией, о которой я не рассказал, и вам он непонятен, напишите мне, пожалуйста, в комментариях ссылку на вывод explain на explain.depesz.com, название операции и версию Посгреса, в которой она вам встретилась. Я постараюсь найти всю возможную информацию по таким кейсам и дать вам развернутый ответ.
В этом, надеюсь, предпоследнем посте серии я расскажу об оставшихся наиболее распространенных операциях, которые вы можете встретить в выводе explain.
Unique
Название операции говорит само за себя – она удаляет дублирующие данные.
Такое может случиться, например, когда вы делаете следующее:
select distinct field from table
В более свежих версиях Постгреса этот запрос будет осуществлен с помощью HashAggregate.
Проблема Unique заключается в том, что данные для неё должны быть отсортированы. Не потому, что этой операции нужны данные в определенном порядке, а для того, чтобы все строки с одинаковыми значениями были «вместе».
Это делает 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)
Append
Этот план просто запускает множество субопераций и возвращает все возвращенные ими строки в виде общего результата.
Это используется запросами 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)
Здесь вы видите, как append запустил три сканирования по трем таблицам и вернул все строки вместе.
Обратите внимание, что я использовал 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.
Result
Result появляется, в основном, в очень простых тестовых запросах. Эта операция используется, когда ваш запрос выбирает какое-либо постоянное значение (или значения):
$ 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)
Values Scan
Так же, как Result, Values Scan используется для возвращения простых введенных в запросе данных, но в данном случае это может быть целый набор записей, основанный на функциональности VALUES().
Если вдруг вы не в курсе, вы можете выбрать множество строк и множество столбцов без какой-либо таблицы, просто используя синтаксис 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’ах, но у неё есть и другие способы применения, например, кастомная сортировка.
GroupAggregate
Эта операция схожа с 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)
HashSetOp
Эта операция используется операциями 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)
CTE Scan
Эта операция схожа с уже упомянутой операцией Materialize. Она запускает часть запроса и сохраняет ее вывод, чтобы он мог быть использован другой частью (или частями) запроса.
Пример:
$ 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 – это вспомогательная операция, которую Постгрес решает использовать, когда посчитает нужным.
Важное замечание: CTE всегда запускаются именно так, как указано. Поэтому их можно использовать для того, чтобы обойти не самые удачные оптимизации, которые может осуществить планировщик.
InitPlan
Этот план происходит каждый раз, когда есть часть запроса, которая может (или должна) быть рассчитана перед всем остальным и не зависит ни от чего в оставшейся части вашего запроса.
Допустим, вы хотите вот такой запрос:
$ 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, потому что Постгресу нужно будет сравнить значение 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, поэтому его можно прогнать один раз и не нужно пересчитывать длину для каждой строки.
Конечно, у вас может быть много единичных планов (init plans), как здесь:
$ 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)
Но стоит учитывать одну деталь – init plan’ы внутри одного запроса нумеруются «глобально», а не по операциям.
SubPlan
SubPlan’ы чем-то похожи на 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», Постгрес должен запустить SubPlan, который проверяет, сколько строк в pg_class имеют такое же (как у только что обработанной строки) значение в столбце relkind.
Обратите внимание на «loops=295» в строке «Seq Scan on pg_class x» и соответствующее ему значение «rows=295» в узле «Seq Scan on pg_class c».
Другие?
Да, есть и другие операции. Некоторые из них слишком редко встречаются, чтобы удостоиться нашего внимания (особенно если учесть, что у вас есть прекрасный источник знаний – исходники), а некоторые являются (как я подозреваю) старыми версиями новых узлов.
Если у вас есть план с операцией, о которой я не рассказал, и вам он непонятен, напишите мне, пожалуйста, в комментариях ссылку на вывод explain на explain.depesz.com, название операции и версию Посгреса, в которой она вам встретилась. Я постараюсь найти всю возможную информацию по таким кейсам и дать вам развернутый ответ.
