[Перевод] Объясняя необъяснимое. Часть 2

Регистрация на конференцию PG Day»16 в разгаре, а мы продолжаем публиковать перевод статей Hubert Lubaczewski об explain и его основных компонентах.

В прошлый раз я писал о том, что показывает вывод explain. Теперь я хочу больше поговорить о разных типах «узлов» / операций, которые вы можете встретить в планах explain.
977038b644384b6abc4e453fb72d4c9d.jpg

Самая базовая операция — это последовательное сканирование (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. Чем ближе конференция, тем дороже билеты, поэтому спешите приобрести их по выгодной цене!

© Habrahabr.ru