Регистрация на конференцию PG Day’16 в разгаре, а мы продолжаем публиковать перевод статей Hubert Lubaczewski об explain и его основных компонентах.
В прошлый раз я писал о том, что показывает вывод explain. Теперь я хочу больше поговорить о разных типах «узлов» / операций, которые вы можете встретить в планах explain.
Самая базовая операция – это последовательное сканирование (Seq Scan).
Она выглядит вот так:
explain analyze select * from pg_class;
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Seq Scan on pg_class (cost=0.00..10.92 rows=292 width=202) (actual time=0.009..0.049 rows=295 loops=1)
Total runtime: 0.249 ms
(2 rows)
Это самая простая операция из всех возможных – PostgreSQL открывает файл с таблицей, читает строки одну за другой и возвращает их пользователю или расположенному выше узлу дерева explain, например, LIMIT, как здесь:
explain analyze select * from pg_class limit 2;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..0.07 rows=2 width=202) (actual time=0.014..0.014 rows=2 loops=1)
-> Seq Scan on pg_class (cost=0.00..10.92 rows=292 width=202) (actual time=0.009..0.009 rows=2 loops=1)
Total runtime: 0.132 ms
(3 rows)
Важно понимать, что порядок возврата строк не является каким-то определенным. Они возвращаются не «в порядке вставки» или «последняя обновленная строка – первой», или ещё что-то в том же духе. Параллельные выборки, обновления, удаления, чистки (vacuums) могут менять порядок следования строк в любое время.
Seq Scan может фильтровать строки – то есть отбрасывать некоторые при возврате. Это происходит, например, когда вы добавляете условие “WHERE":
explain analyze select * from pg_class where relname ~ 'a';
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Seq Scan on pg_class (cost=0.00..11.65 rows=227 width=202) (actual time=0.030..0.294 rows=229 loops=1)
Filter: (relname ~ 'a'::text)
Rows Removed by Filter: 66
Total runtime: 0.379 ms
(4 rows)
Как вы видите, теперь у нас появилась информация “Filter:”. И, поскольку у меня версия СУБД 9.2 или новее, я также получил комментарий «Строки удалены фильтром» (“Rows removed by filter").
Следующий тип узла — “Index Scan".
Этот вид сканирования кажется очень простым, и большинство людей понимает хотя бы один случай его использования:
explain analyze select * from pg_class where oid = 1247;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Index Scan using pg_class_oid_index on pg_class (cost=0.15..8.17 rows=1 width=202) (actual time=0.007..0.007 rows=1 loops=1)
Index Cond: (oid = 1247::oid)
Total runtime: 0.077 ms
(3 rows)
Всё просто – у нас есть индекс, соответствующий условию, так что PostgreSQL:
- открывает индекс;
- в индексе, если находит, где (в данных таблицы) могут быть строки, соответствующие данному условию:
- открывает таблицу;
- получает строку(-и), указанную(-ые) индексом;
- если строки могут быть возвращены – то есть, если они видимы в текущей сессии – они возвращаются.
Конечно, вы можете спросить: как строка может быть невидимой? Это может случиться с удаленными строками, которые всё ещё находятся в таблице (не были вычищены vacuum). Или со строками, которые были обновлены. Или были вставлены, но после текущей транзакции.
Index Scan также используется, когда вы хотите отсортировать какие-то данные, используя порядок сортировки в индексе, например:
explain analyze select * from pg_class order by oid limit 10;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.15..1.67 rows=10 width=206) (actual time=0.017..0.029 rows=10 loops=1)
-> Index Scan using pg_class_oid_index on pg_class (cost=0.15..44.53 rows=292 width=206) (actual time=0.014..0.026 rows=10 loops=1)
Total runtime: 0.145 ms
(3 rows)
Здесь нет условия, но мы легко можем его добавить вот таким образом:
explain analyze select * from pg_class where oid > 1247 order by oid limit 10;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.15..4.03 rows=10 width=206) (actual time=0.021..0.035 rows=10 loops=1)
-> Index Scan using pg_class_oid_index on pg_class (cost=0.15..37.84 rows=97 width=206) (actual time=0.017..0.031 rows=10 loops=1)
Index Cond: (oid > 1247::oid)
Total runtime: 0.132 ms
(4 rows)
В этих случаях PG находит начальную точку отсчета в индексе (либо первую строку, которая старше 1247, либо просто самую маленькую величину в индексе), а потом просто возвращает следующие строки/значения, пока условие Limit не будет удовлетворено.
Есть версия Index Scan под названием “Index Scan Backward", которая делает то же самое, но используется для сканирования в порядке по убыванию:
explain analyze select * from pg_class where oid < 1247 order by oid desc limit 10;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.15..4.03 rows=10 width=206) (actual time=0.012..0.026 rows=10 loops=1)
-> Index Scan Backward using pg_class_oid_index on pg_class (cost=0.15..37.84 rows=97 width=206) (actual time=0.009..0.022 rows=10 loops=1)
Index Cond: (oid < 1247::oid)
Total runtime: 0.119 ms
(4 rows)
Это тот же тип операции: открыть индекс и, для каждой строки, на которую ссылается индекс, извлечь данные из таблицы. Просто это происходит не «от меньшего к большему», а «от большего к меньшему».
Ещё одна схожая операция — “Index Only Scan".
Давайте создадим простую таблицу:
create table test (id serial primary key, i int4);
CREATE TABLE
insert into test (i) select random() * 1000000000 from generate_series(1,100000);
INSERT 0 100000
vacuum analyze test;
VACUUM
Это даёт нам таблицу вроде этой:
select * from test limit 10;
id | i
----+-----------
1 | 546119592
2 | 253476978
3 | 235791031
4 | 654694043
5 | 187647296
6 | 709050245
7 | 210316749
8 | 348927354
9 | 120463097
10 | 5611946
(10 rows)
Здесь у меня есть индекс по id:
d test
Table "public.test"
Column | Type | Modifiers
--------+---------+---------------------------------------------------
id | integer | not null default nextval('test_id_seq'::regclass)
i | integer |
Indexes:
"test_pkey" PRIMARY KEY, btree (id)
Так что, если определенные условия выполняются (чуть позже расскажу об этом подробнее), я могу получить вот такой план:
explain analyze select id from test order by id asc limit 10;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.29..0.55 rows=10 width=4) (actual time=0.039..0.042 rows=10 loops=1)
-> Index Only Scan using test_pkey on test (cost=0.29..2604.29 rows=100000 width=4) (actual time=0.036..0.038 rows=10 loops=1)
Heap Fetches: 0
Total runtime: 0.092 ms
(4 rows)
Обратите внимание на слово “Only" в “Index Only Scan".
Это значит, что Постгрес понял, что я выбираю только данные (колонки) из индекса. И, возможно, ему не нужно ничего проверять в файлах таблицы. Так что он будет возвращать данные прямо из индекса.
Эти сканирования стали большим изменением в PostgreSQL 9.2, так как они могут работать намного быстрее обычного сканирования индекса, потому что им не нужно ничего проверять в данных таблицы.
Сложность в том, что для корректной работы, Index должен содержать информацию о том, что данные строки находятся на страницах, не подвергавшихся изменениям «в последнее время». То есть, для использования Index Only Scans ваша таблица должна быть хорошо вычищена с помощью vacuum. Но, с запущенным autovacuum это не должно стать проблемой.
Последний тип сканирования таблицы – так называемый Bitmap Index Scan. Он выглядит вот так:
explain analyze select * from test where i < 100000;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on test (cost=4.37..39.99 rows=10 width=8) (actual time=0.025..0.110 rows=13 loops=1)
Recheck Cond: (i < 100000)
-> Bitmap Index Scan on i1 (cost=0.00..4.37 rows=10 width=0) (actual time=0.013..0.013 rows=13 loops=1)
Index Cond: (i < 100000)
Total runtime: 0.154 ms
(5 rows)
(если вы читаете внимательно, то заметили, что он использует индекс, о создании которого я ранее не говорил. Это легко сделать: create index i1 on test (i);).
Bitmap Scans всегда состоят, минимум, из двух узлов. Сначала (на нижнем уровне) идет Bitmap Index Scan, а затем – Bitmap Heap Scan.
Как это работает?
Допустим, в вашей таблице 100000 страниц (это около 780MB). Bitmap Index Scan создаст битовую карту, где каждой странице вашей таблицы будет соответствовать один бит. Так что, в этом случае мы получим блок памяти на 100,000 бит ~ 12.5 кБ. Все эти биты будут установлены в 0. Затем, Bitmap Index Scan установит некоторые биты в 1, в зависимости от того, на какой странице таблицы может находиться строка, которую нужно вернуть.
Эта часть вообще не затрагивает данные в таблице. После того как это будет сделано – то есть когда все страницы, на которых находятся строки, которые нужно вернуть, будут «помечены» – эта битовая карта перейдет на уровень выше, к узлу Bitmap Heap Scan, который читает их в более последовательной манере.
В чем смысл такой операции? Обычные Index Scans вызывают случайные операции ввода/вывода – страницы с диска загружаются в случайном порядке. А это медленно. По крайней мере, на вращающихся дисках.
Последовательное сканирование быстрее, когда нужно получить одну страницу, но, с другой стороны, вам не всегда нужны все страницы.
Bitmap Index Scans объединяет оба случая: когда вам нужно много строк из таблицы, но не все, и когда строки, которые вы будете возвращать, находятся не в одном блоке (что было бы оправдано, если бы я производил операцию “… where id < ..."). У сканирований битовых карт есть ещё одно интересное свойство – они могут объединять две операции или два индекса, как в этом примере:
explain analyze select * from test where i < 5000000 or i > 950000000;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on test (cost=107.36..630.60 rows=5323 width=8) (actual time=1.023..4.353 rows=5386 loops=1)
Recheck Cond: ((i < 5000000) OR (i > 950000000))
-> BitmapOr (cost=107.36..107.36 rows=5349 width=0) (actual time=0.922..0.922 rows=0 loops=1)
-> Bitmap Index Scan on i1 (cost=0.00..12.25 rows=527 width=0) (actual time=0.120..0.120 rows=491 loops=1)
Index Cond: (i < 5000000)
-> Bitmap Index Scan on i1 (cost=0.00..92.46 rows=4822 width=0) (actual time=0.799..0.799 rows=4895 loops=1)
Index Cond: (i > 950000000)
Total runtime: 4.765 ms
(8 rows)
Здесь мы видим два сканирования Bitmap Index (их может быть больше), которые потом объединяются (но не так, как при операции “JOIN" в SQL!) с помощью BitmapOr.
Как вы помните, вывод Bitmap Index Scan – это битовая карта (блок памяти с единицами и нулями). Имея несколько таких битовых карт, вы можете легко производить на них логические операции: Or, And или Not.
Здесь мы видим, что две таких битовых карты были объединены с помощью оператора Or, и получившаяся битовая карта была передана в Bitmap Heap Scan, который загрузил подходящие строки из таблицы.
Хотя здесь оба сканирования индекса используют один и тот же индекс, так бывает не всегда. Например, давайте быстро добавим ещё несколько колонок:
alter table test add column j int4 default random() * 1000000000;
ALTER TABLE
alter table test add column h int4 default random() * 1000000000;
ALTER TABLE
create index i2 on test (j);
CREATE INDEX
create index i3 on test (h);
CREATE INDEX
А вот что получается:
explain analyze select * from test where j < 50000000 and i < 50000000 and h > 950000000;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on test (cost=280.76..323.61 rows=12 width=16) (actual time=2.295..2.352 rows=11 loops=1)
Recheck Cond: ((h > 950000000) AND (j < 50000000) AND (i < 50000000))
-> BitmapAnd (cost=280.76..280.76 rows=12 width=0) (actual time=2.278..2.278 rows=0 loops=1)
-> Bitmap Index Scan on i3 (cost=0.00..92.53 rows=4832 width=0) (actual time=0.546..0.546 rows=4938 loops=1)
Index Cond: (h > 950000000)
-> Bitmap Index Scan on i2 (cost=0.00..93.76 rows=4996 width=0) (actual time=0.783..0.783 rows=5021 loops=1)
Index Cond: (j < 50000000)
-> Bitmap Index Scan on i1 (cost=0.00..93.96 rows=5022 width=0) (actual time=0.798..0.798 rows=4998 loops=1)
Index Cond: (i < 50000000)
Total runtime: 2.428 ms
(10 rows)
Три сканирования Bitmap Index Scan, каждое из которых использует свой индекс, битовые карты объединены с помощью битовой операции “and", и результат скармливается Bitmap Heap Scan.
Если вы интересуетесь, почему BitmapAnd показывает “Actual rows = 0", ответ прост: этот узел вообще не имеет дела со строками (только битовая карта страниц диска). Так что он не может вернуть строки.
На этом пока всё. Это все возможные сканирования таблицы – способы, которыми вы получаете данные с диска. В следующий раз я расскажу об объединении разных источников данных и других видах планов.
Ещё больше полезной информации о PostgreSQL вы сможете получить, зарегистрировавшись на конференцию PG Day’16. Чем ближе конференция, тем дороже билеты, поэтому спешите приобрести их по выгодной цене!
Автор: rdruzyagin
