[Перевод] Объясняя необъяснимое
Друзья, мы с радостью продолжаем публикацию интересных материалов, посвященных самым разнообразным аспектам работы с PostgreSQL. Сегодняшний перевод открывает целую серию статей за авторством Hubert Lubaczewski, которые наверняка заинтересуют широкий круг читателей.
Одна из первых вещей, которую слышит новоиспеченный администратор баз данных — «используй EXPLAIN». И при первой же попытке он сталкивается c непостижимым:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Sort (cost=146.63..148.65 rows=808 width=138) (actual time=55.009..55.012 rows=71 loops=1)
Sort Key: n.nspname, p.proname, (pg_get_function_arguments(p.oid))
Sort Method: quicksort Memory: 43kB
-> Hash Join (cost=1.14..107.61 rows=808 width=138) (actual time=42.495..54.854 rows=71 loops=1)
Hash Cond: (p.pronamespace = n.oid)
-> Seq Scan on pg_proc p (cost=0.00..89.30 rows=808 width=78) (actual time=0.052..53.465 rows=2402 loops=1)
Filter: pg_function_is_visible(oid)
-> Hash (cost=1.09..1.09 rows=4 width=68) (actual time=0.011..0.011 rows=4 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Seq Scan on pg_namespace n (cost=0.00..1.09 rows=4 width=68) (actual time=0.005..0.007 rows=4 loops=1)
Filter: ((nspname <> 'pg_catalog'::name) AND (nspname <> 'information_schema'::name))
Что бы это могло значить?
Бесполезно пытаться сразу понять представленный выше explain. Давайте лучше начнем с чего-то попроще. Но перед этим я хотел бы, чтобы вы поняли одну важную вещь:
PostgreSQL запоминает
Это значит, что PostgreSQL хранит некоторую мета-информацию (информацию об информации). Количество строк, количество различных значений, наиболее распространенные значения и так далее. Для больших таблиц эта информация основывается на случайной выборке, но в целом Постгрес действительно многое знает о наших данных.
Итак, давайте рассмотрим простой запрос и его explain:
$ explain select * from test where i = 1;
QUERY PLAN
------------------------------------------------------
Seq Scan on test (cost=0.00..40.00 rows=12 width=4)
Filter: (i = 1)
(2 rows)
Запрос достаточно прост и, как мне кажется, не требует никаких дополнительных комментариев.
В explain первая строка и все строки, начинающиеся с »→» — это операции. Остальные строки — дополнительная информация к операции выше.
В нашем случае есть всего одна операция — последовательное сканирование таблицы test.
Также есть дополнительная информация о фильтре.
Последовательное сканирование означает, что PostgreSQL будет «открывать» данные из таблицы и читать их. Теоретически, он может отфильтровывать (удалять) строки, но, в целом, готов прочитать и вернуть всю таблицу целиком.
Почему готов? Объясню через минуту.
Итак, строка Seqscan информирует нас о том, что мы сканируем таблицу в последовательном режиме. И что таблица называется «test» (хотя вот тут кроется одна из самых больших проблем explain — он не показывает схему, и это мне аукалось не один раз).
А что же это за числа в скобках после операции?
Хочу задать вам вопрос. У вас есть вот такая таблица:
Table "public.t"
Column | Type | Modifiers
-------------+---------+------------------------------------------------
id | integer | not null default nextval('t_id_seq'::regclass)
some_column | integer |
something | text |
Indexes:
"t_pkey" PRIMARY KEY, btree (id)
"q" btree (some_column)
Имея определение этой таблицы и запрос:
SELECT * FROM t where some_column = 123;
Как вы думаете, каким способом лучше всего выполнить этот запрос? Последовательно просканировать таблицу или использовать индекс?
Если ваш ответ: конечно, использовать индекс, на этой колонке есть индекс, так что такой способ будет быстрее, — то я спрошу: как насчет ситуации, когда в таблице всего одна строка, и она содержит значение some_column, равное 123?
Чтобы произвести последовательное сканирование, мне нужно прочитать всего одну страницу таблицы (8192 байт), и я получу строку. Для того чтобы использовать индекс, мне нужно прочитать страницу из индекса, проверить, есть ли в таблице строки, соответствующие условию, а потом прочитать страницу из таблицы.
В итоге — вдвое больше работы!
Вы могли бы сказать: «Конечно, но речь ведь об очень маленьких таблицах, так что скорость не имеет значения». Хорошо. Тогда давайте представим таблицу, в которой 10 миллиардов строк, и у каждой из них some_column = 123. Индекс здесь точно не поможет, а в реальности вообще серьезно ухудшит ситуацию.
Конечно, если у вас миллион строк и только у одной из них some_column = 123, сканирование индексов будет наиболее правильным решением.
Таким образом, невозможно сказать, будет ли данный запрос использовать индекс, и надо ли ему вообще использовать индекс (речь идёт об общих случаях). Чтобы понять это, вам нужно больше информации. И этот факт приводит нас к простому выводу: в зависимости от ситуации один способ получения данных будет лучше или хуже другого.
PostgreSQL (до определенного момента) проверяет все возможные сценарии. Он знает, сколько у вас строк и сколько строк (вероятнее всего) попадут под заданные критерии, поэтому может принимать весьма умные решения.
Но как принимаются эти решения? Именно это и показывает первый набор цифр в explain. Это затраты.
Некоторые думают, что затраты оцениваются в секундах. Это не так. Их единица измерения — «извлечение одной страницы в последовательном (sequential) порядке». То есть оценивается и время, и использование ресурсов.
В postgresql.conf вы могли заметить вот такие параметры:
seq_page_cost = 1.0 # measured on an arbitrary scale
random_page_cost = 4.0 # same scale as above
cpu_tuple_cost = 0.01 # same scale as above
cpu_index_tuple_cost = 0.005 # same scale as above
cpu_operator_cost = 0.0025 # same scale as above
То есть, мы даже можем изменить издержки на чтение последовательной страницы. Эти параметры устанавливают затраты, которые, как предполагает PostgreSQL, потребуются для того, чтобы осуществить разные методы выполнения одного и того же запроса.
Давайте для примера создадим простую таблицу из 1000 строк с какими-нибудь текстами и индексом:
create table test (id serial primary key, some_text text);
CREATE TABLE
insert into test (some_text) select 'whatever' from generate_series(1,1000);
INSERT 0 1000
Мы видим, что запуск explain с условием по id выдаёт следующее:
explain select * from test where id = 50;
QUERY PLAN
-----------------------------------------------------------------------
Index Scan using test_pkey on test (cost=0.28..8.29 rows=1 width=36)
Index Cond: (id = 50)
(2 rows)
А что если бы мы сказали постгресу, что сканирование индекса нельзя использовать ни при каких условиях?
explain select * from test where id = 50;
QUERY PLAN
------------------------------------------------------------------------
Bitmap Heap Scan on test (cost=4.28..8.30 rows=1 width=13)
Recheck Cond: (id = 50)
-> Bitmap Index Scan on test_pkey (cost=0.00..4.28 rows=1 width=0)
Index Cond: (id = 50)
(4 rows)
И это тоже давайте отключим:
explain select * from test where id = 50;
QUERY PLAN
------------------------------------------------------
Seq Scan on test (cost=0.00..18.50 rows=1 width=13)
Filter: (id = 50)
(2 rows)
OK, а теперь давайте выведем их рядом друг с другом:
Index Scan using test_pkey on test (cost=0.28..8.29 rows=1 width=36)
Bitmap Heap Scan on test (cost=4.28..8.30 rows=1 width=13)
Seq Scan on test (cost=0.00..18.50 rows=1 width=13)
По умолчанию постгрес использует IndexScan. Почему? Всё просто — в данном случае это наименее затратный способ. Издержки составят 8.29, в то время как для bitmap heap scan (что бы это ни было) потребуется 8.30, а для seq scan — 18.5.
OK, но издержки обозначаются двумя числами: number…number. Что это такое, и почему я говорю только о втором числе? Если бы мы учитывали первое число, то победителем оказался бы seq scan, так как у него это значение равно нулю, а у indexscan — 0.28, и аж 4.28 у bitmap heap scan.
Значение издержек выводится в диапазоне (number …number), потому что оно показывает затраты на строку начала операции и затраты на получение всех строк (под всеми имеются в виду возвращенные этой операцией, а не все, имеющиеся в таблице).
Каковы начальные издержки? Для seqscan их нет — вы просто читаете страницу и возвращаете строки. И всё. Но, например, для сортировки набора данных вам нужно прочитать все данные и действительно отсортировать их прежде, чем рассматривать возможность возврата даже первой из строк. Это хорошо видно на следующем explain:
QUERY PLAN
-------------------------------------------------------------------
Sort (cost=22.88..23.61 rows=292 width=202)
Sort Key: relfilenode
-> Seq Scan on pg_class (cost=0.00..10.92 rows=292 width=202)
(3 rows)
Обратите внимание, что начальные затраты для Sort — 22.88, в то время как общие издержки составят всего 23.61. Так что возвращение строк из Sort с точки зрения затрат незначительно, а вот их сортировка — да.
Следующая информация в explain — это «rows». Это приблизительное количество строк, которое, как считает PostgreSQL, эта операция способна вернуть (она может вернуть меньше, например, в случае наличия LIMIT). Это также очень важно для некоторых операций — например, объединений (join). Объединение двух таблиц, в которых суммарно имеется 20 строк, может осуществляться множеством способов, и, по большому счету, неважно, каким именно. Но когда вы объединяете таблицу из миллиона строк с таблицей из миллиарда строк, способ, которым вы это делаете, очень важен (я говорю не об inner join / left join, а скорее о hash join, nested loop, merge join — если вы не понимаете, о чем речь, не переживайте, я всё объясню чуть позже).
Конечно, это число может быть оценено неверно — по многим причинам. Иногда это не имеет значения, а иногда имеет. Но о неправильных оценках мы поговорим позже.
Последний кусочек информации — ширина (width). Это оценка PostgreSQL того, сколько, в среднем, байт содержится в одной строке, возвращенной в рамках данной операции. Например:
explain select * from pg_class;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on pg_class (cost=0.00..10.92 rows=292 width=202)
(1 row)
explain select relname, relkind from pg_class;
QUERY PLAN
------------------------------------------------------------
Seq Scan on pg_class (cost=0.00..10.92 rows=292 width=65)
(1 row)
Как вы видите, ограничение числа полей модифицировало ширину, а, следовательно, и количество данных, которое должно пройти через исполнение запроса.
А теперь внимание, наиболее важная информация: explain«ы — это деревья. Верхнему узлу требуются данные от узлов, расположенных под ним.
Давайте рассмотрим этот план.
В нём 5 операций: сортировка, hash join, seq scan, hash и снова seq scan. PostgreSQL выполняет верхнюю операцию — сортировку, которая в свою очередь выполняет следующую, находящуюся прямо под ней (hash join) и получает данные от нее. Hash join, чтобы вернуть данные в сортировку, должен запустить seq scan (по pg_proc) и hash (#4). И, наконец, hash, чтобы вернуть данные, должно прогнать seq scan по pg_namespace.
Очень важно понимать, что некоторые операции могут возвращать данные мгновенно или, что ещё важнее, постепенно. Например, Seq Scan. А некоторые не могут. К примеру, здесь мы видим, что Hash (#4) имеет те же начальные издержки, что и его «субоперация» seq scan для «всех строк». Это значит, что для начала операции hash (для того, чтобы она могла вернуть хоть одну строку), нужно прочитать все строки из её субопераций.
Часть о постепенном возврате строк становится особенно важна, когда вы начинаете писать функции. Давайте рассмотрим вот такую функцию:
CREATE OR REPLACE FUNCTION public.test()
RETURNS SETOF integer
LANGUAGE plpgsql
AS $function$
declare
i int4;
begin
for i in 1..3 loop
return next i;
perform pg_sleep(1);
end loop;
return;
end;
$function$;
Если вы ничего не поняли, не волнуйтесь. Функция возвращает 3 строки, каждая из которых содержит одно целое число — 1, 2 и 3. Важно то, что она засыпает на 1 секунду после возвращения каждой строки.
Это значит, что если я сделаю вот так:
select * from test();
Мне придётся ждать результатов 3 секунды.
Но как долго придётся ждать возвращения при вот таком раскладе:
select * from test() limit 1;
Давайте посмотрим:
\timing
Timing is on.
select * from test() limit 1;
test
------
1
(1 row)
Time: 3005.334 ms
Те же 3 секунды. Почему? Потому что PL/pgSQL (и большинство, если не все, языков PL/*) не могут возвращать частичные результаты. Кажется, что могут — с помощью «return next» –, но на самом деле все результаты хранятся в буфере и возвращаются вместе, когда исполнение функции завершается.
С другой стороны, «нормальные» операции обычно могут возвращать частичные данные. Это можно увидеть, если провести какую-нибудь банальную операцию, вроде последовательного сканирования, на непростой таблице:
create table t as
select i as id,
repeat('depesz', 100)::text as payload
from generate_series(1,1000000) i;
По этой таблице видно, что:
explain analyze select * from t;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Seq Scan on t (cost=0.00..185834.82 rows=10250082 width=36) (actual time=0.015..232.380 rows=1000000 loops=1)
Total runtime: 269.666 ms
(2 rows)
explain analyze select * from t limit 1;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..0.02 rows=1 width=36) (actual time=0.003..0.003 rows=1 loops=1)
-> Seq Scan on t (cost=0.00..185834.82 rows=10250082 width=36) (actual time=0.003..0.003 rows=1 loops=1)
Total runtime: 0.016 ms
(3 rows)
(пожалуйста, смотрите пока только на «Total runtime: …»)
Как вы видите, последовательное сканирование закончилось очень быстро — сразу как только удовлетворило аппетит LIMIT ровно на 1 строку.
Пожалуйста, отметьте, что здесь даже издержки (которые не являются лучшим критерием для сравнения запросов) показывают, что верхний узел (seq scan в первом запросе и limit во втором) имеет очень разные значения для возврата всех рядов — 185834.82 против 0.02.
Так что первые 4 числа для любой операции (две оценки затрат, кол-во строк и ширина) являются приблизительными. Они могут оказаться правильными, а могут и не оказаться.
Другие 4 числа, которые вы получаете, когда запускаете «EXPLAIN ANALYZE query» или «EXPLAIN (ANALYZE on) query», показывают реальные результаты.
Время по-прежнему указывается диапазоном, но на этот раз это реальное время. Именно столько времени PostgreSQL потратил на работу над данной операцией (в среднем, потому что он мог прогонять одну и ту же операцию множество раз). И так же, как издержки, время представлено диапазоном: время на начало выполнения операции и время на возврат всех данных. Давайте проверим этот план:
$ explain analyze select * from t limit 100;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..9.33 rows=100 width=608) (actual time=0.008..0.152 rows=100 loops=1)
-> Seq Scan on t (cost=0.00..93333.86 rows=999986 width=608) (actual time=0.007..0.133 rows=100 loops=1)
Total runtime: 0.181 ms
(3 rows)
Как вы видите, время начала операции для Limit — 0.008 (единица измерения тут — миллисекунды). Так происходит, потому что Seq Scan (который Limit вызвал для получения данных) потребовалось 0.007 мс для возвращения первой строки, а потом еще 0.001 мс ушло на обработку внутри самого limit.
Далее (после возврата первой строки), limit продолжил получать данные из Seq Scan, пока не получил 100 строк. Тогда он прекратил последовательное сканирование (это произошло через 0.133 мс после начала запроса) и завершился сам еще через 0.019 мс.
Фактическое количество строк, как становится ясно из названия, показывает, сколько строк (в среднем) эта операция вернула. А loop показывает, сколько всего раз эта операция выполнялась.
В каком случае операция будет вызываться более одного раза? Например, в некоторых случаях с join или вложенными запросами. Это будет похоже на вот этот план.
Отметьте, что в третьей операции всего два цикла. Это значит, что данный seq scan был запущен дважды, возвращал, в среднем, 1 строку и для завершения ему, в среднем, требовалось 0.160 мс. Так что общее время, затраченное на эту конкретную операцию: 2×0.160 мс = 0.32 мс (что указано в колонках exclusive/inclusive на explain.depesz.com).
Очень часто низкая производительность запроса связана с тем, что ему пришлось много раз выполнять цикл по какому-то подзапросу. Например, как здесь.
(Конечно, это не значит, что во всем виноват постгрес, выбравший такой план. Возможно, других вариантов просто не было или они оказались ещё более затратными).
В примере выше стоит обратить внимание на то, что, хотя фактическое время операции 3 — всего лишь 0.003 мс, эта операция производилась более 26000 раз, что в итоге вылилось в почти 79 мс затраченного времени.
Думаю, на этом теоретическая информация, необходимая для чтения explain«ов, исчерпана. Скорее всего, вы до сих пор не понимаете, что означают операции и другая информация, но, по крайней мере, теперь вы знаете, что означают числа и в чем разница между explain (который показывает затраты в абстрактных единицах измерения, основанных на приблизительной оценке случайных примеров) и explain analyze (который показывает фактическое время, число строк и время выполнения в единицах измерения, которые позволяют сравнивать разные запросы).
Как всегда, я боюсь, что пропустил много вещей, которые могут быть важны, но не попались мне на глаза, или (что ещё хуже) я посчитал их «очевидными». Если вам покажется, что чего-то не хватает, пожалуйста, сообщите мне, и я постараюсь восполнить пробелы как можно скорее.
Но хочу также добавить, что планирую развить этот текст ещё в 2–3 публикациях, в которых больше расскажу о том:
- какие бывают операции, как они работают и чего стоит ожидать, когда вы видите их в выводе explain;
- что такое статистика, как Pg её получает, как её просматривать и как извлечь из неё максимум пользы.
Надеюсь, что эта статья оказалась для вас полезной. Подписывайтесь и следите за публикациями. В скором времени в блоге появятся переводы следующих серий! Как обычно, не стесняйтесь оставлять фидбек и пожелания. Самые интересные мы включим в программу предстоящего PG Day'16 Russia:)
