Это продолжение первой части статьи.
Здесь мы рассмотрим, что означают термины, в которых PostgreSQL выводит информацию в EXPLAIN запросах. Возьмем пример из официальной документации по PostgreSQL:
SET enable_nestloop = off;
EXPLAIN SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
QUERY PLAN
------------------------------------------------------------------------------------------
Hash Join (cost=232.61..741.67 rows=106 width=488)
Hash Cond: (t2.unique2 = t1.unique2)
-> Seq Scan on tenk2 t2 (cost=0.00..458.00 rows=10000 width=244)
-> Hash (cost=232.35..232.35 rows=106 width=244)
-> Bitmap Heap Scan on tenk1 t1 (cost=2.37..232.35 rows=106 width=244)
Recheck Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0)
Index Cond: (unique1 < 100)
Из части 1 нам уже понятно, что здесь означают цифры, но может быть не до конца ясно, что за операции такие Hash Join и Seq Scan, Bitmap Heap Scan и Bitmap Index Scan, а также зачем нужен Recheck Cond и вообще, как понимать не только то, сколько ресурсов потребляет каждая операция, но и то, что она реально делает и зачем. Материал не претендует на всеохватность, но надеюсь его пополнять по мере возможности.
Как читать план выполнения запроса. Общие сведения
План выполнения разбивается на отдельные операции. Можно видеть, что для удобства каждый элемент плана выделяется стрелкой ->. Операции, входящие в состав более крупной операции, выделяются табуляцией. Численные показатели для операции верхнего уровня — это сумма всех численных показателей операций, входящих в ее состав плюс дополнительные издержки.
Cond
Ко многим операциям указываются условия их выполнения. В примере выше, Index Cond — это условие, по которому производится индексное сканирование. В данном случае система пробегает по индексу столбца unique1 и сравнивает каждое из значение с 100.
Filter
После отработки определённой операции к получившимся в ее результате данным может применяться фильтр с заданными параметрами, который «просеивает» эти результаты, если этого требует структура запроса. Это обозначается словом Filter и указанием условий фильтрации. Важно понимать, что, в отличие от Index Cond, этот фильтр применяется не во время поиска/выборки данных, а уже после того, как данные выбраны, то есть она достаточно медленна.
Index Scan
Индексное сканирование используется тогда, когда выгодно и возможно использовать индекс. Операция обычно очень быстрая, особенно быстро она отрабатывает в начале, однако, произвольный доступ к данным (особенно большого объёма) гораздо быстрее выполняется при операции последовательного поиска. Индекс хорош для поиска по нему, но не для операций чтения для больших объёмов данных.
Индекс служит для упорядочивания данных, чтобы искать среди них нужные было проще. Индексы используют две математические идеи: двусвязный список и сбалансированное дерево поиска, объединенные в поисковом алгоритме.
Двусвязные списки (списки, каждый элемент которых имеет ссылки на предыдущий и последующий элементы) используются для соединения так называемых узлов, каждый из которых — это блок или страница данных в БД. Данные группируются по несколько строк в такие блоки для оптимизации поиска. В свою очередь, индекс — это пары упорядоченных по возрастанию или убыванию значений определенного столбца и внутренний идентификатор соответствующей строки с данными в БД. Эти пары объединены в блоки с одинаковым количеством таких пар в каждом. Эти блоки упорядочены в виде двусвязного списка.
Для того, чтобы при поиске значения не приходилось последовательно проходить по всем блокам индекса, в нём используется алгоритм сбалансированного дерева поиска (B-Tree). Для его построения делается следующее. У каждого из блоков индекса берется максимальное по величине значение, далее берется определенное количество таких соседних блоков, и создается новый блок на следующем уровне иерархии, в котором перечислены все максимальные значения изначальных блоков с ссылками на эти блоки, далее этот уровень также является основой для создания следующего по тем же правилам, и так, пока количество значений не уместится в единственном, т.н. «корневом» узле. Поиск по дереву происходит в обратном порядке. Когда ищется какое-то значение, то обход дерева начинается с минимального значения корневого блока. Как только находится такое, которое больше искомого, осуществляется переход на соответствующий узел следующего уровня иерархии, и так, пока не будет достигнут последний уровень и не отыщется ссылка на строку БД, содержащую искомое значение.
Поскольку количество операций сканирования при таком подходе сокращается кардинально по сравнению с обычным последовательным поиском, данный механизм очень эффективен для поиска отдельных значений.
Обычно на выходе получается упорядоченный набор данных, выстроенных по индексу.
Index Only Scan
Это индексное сканирование со всеми его этапами, за исключением доступа к таблице данных. Очень быстрая операция, но может применяться только тогда, когда индекс включает все необходимые для выборки поля, и дополнительный доступ к таблице не требуется.
Seq Scan
Последовательное сканирование — это последовательный перебор всех строк базы в поисках интересующего значения. Обычно операция выполняется в десятки раз медленнее, чем индексное сканирование, но то, какая из операций будет применяться в конкретных обстоятельствах, зависит от ограничивающих условий запроса. Если индекса для столбца, в котором нужно найти определенное значение, вообще не создано, то будет выполняться последовательное сканирование. В некоторых случаях, например, при большом объеме данных для одного значения индексного поля, последовательное сканирование может быть эффективнее индексного, так как обычно оно работает с большими блоками данных, поэтому за одну операцию доступа потенциально может выбрать большее количество данных, чем индексное сканирование, соответственно, нужно меньше операций доступа и выше скорость. Вообще, операции ввода/вывода выполняются намного быстрее, чем при произвольном доступе, использующемся при индексном сканировании.
На выходе получается неупорядоченный набор данных.
Bitmap Index Scan / Bitmap Heap Scan / Recheck Cond
Сканирование с использованием битовых карт используется, когда при индексном сканировании нужно выбрать много записей за раз. В отличие от индексного сканирования, строки базы выбираются не по одной для каждого найденного соответствия в индексе, а сразу все. То есть сначала идёт поиск по индексу, а уже потом массовое чтение нужных данных из таблицы по найденным в индексе указателям. При этом сначала указатели выстраиваются в порядке, в котором запрашиваемые данные физически расположены в базе, чтобы ускорить их чтение. Для этого используется битовая карта, которая требует для своего поддержания дополнительных ресурсов, но это компенсируется возросшей скоростью чтения данных. Дополнительный недостаток — выбираемые данные располагаются не в индексном порядке, что не имеет значения, если мы не используем оператор order by.
Recheck Cond, то есть перепроверка условий запроса, может применяться, когда объем данных в таблице слишком большой, чтобы поддерживать битовую карту в актуальном состоянии. Тогда она переходит в режим работы с потерей информации, при котором она хренит не прямые указатели на строки в базе, а только указатели на страницы, в которых хранятся эти строки. Соответственно, после первоначального прохода по индексу требуется дополнительная проверка выбираемых данных на соответствие запросу.
В общем, такое сканирование — это по сути комбинация методов, описанных выше — последовательные операции ввода/вывода и выборка по индексу.
Начало операции требует много времени на чтение всех строк по индексу и их сортировку.
Может комбинировать несколько индексов.
Часто используется для IN или =ANY(массив).
Результирующий набор данных не упорядочен.
Другие операции доступа к данным
Также могут встретиться ключевые слова Function Scan (для условий поиска с применением функций), Values Scan (для операции values) и Result (для простого вывода результата).
Пример с Result:
tpcc=> explain select 1;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0)
Вывод
Обычное индексное сканирование оптимальнее всего для выборки небольшого числа строк, сканирование с битовой картой — для большего числа выбираемых строк, а последовательное сканирование — когда выбирается существенная часть таблицы. Конечно, операции с использованием индекса могут применяться, только если нужный индекс был создан.
В следующей части читайте об операциях объединения, сортировки, группировки и ограничения числа строк в планировщике PostgreSQL.