Продолжаю публикацию расширенных транскриптов лекционного курса "PostgreSQL для начинающих", подготовленного мной в рамках "Школы backend-разработчика" в "Тензоре".

В первой части лекции мы узнали, что такое план выполнения запроса, как и зачем его читать (и почему это совсем непросто), и о каких проблемах с производительностью базы он может сигнализировать. В этой - разберем, что такое Seq Scan, Bitmap Heap Scan, Index Scan и почему Index Only Scan бывает нехорош.

Как обычно, для предпочитающих смотреть и слушать, а не читать - доступна видеозапись (часть 1, часть 2) и слайды:

Краткий путеводитель:

1. Основы SQL

2. Простые SELECT

3. Сложные SELECT

4. Анализ запросов (часть 1)

5. Индексы

6. Транзакции

7. Блокировки

---

Содержание текущей лекции:

• "Нематериальные" источники данных

  • Result

  • Values Scan

  • Function Scan (Filter, Rows Removed by Filter)

  • ProjectSet

  • Subquery Scan

  • CTE Scan/CTE

  • Recursive Union/WorkTable Scan

  • InitPlan/SubPlan

• Чтение из таблицы

  • TID Scan

  • Seq Scan

  • Index Scan [Backward] (Index Cond)

  • Index Only Scan [Backward] (Heap Fetches, Order By)

• "Нетипичное" получение данных

  • Sample Scan (Sampling)

  • Table Function Scan

  • Foreign Scan (Remote SQL, Relations)

  • Async Foreign Scan

  • Named Tuplestore Scan

  • Custom Scan

• Работа с битовыми картами

  • Bitmap Index Scan

  • BitmapAnd / BitmapOr

  • Bitmap Heap Scan (Recheck Cond, Rows Removed by Index Recheck, Heap Blocks)

---

Наконец, мы добрались до "основного блюда" нашей лекции - перечня тех операций, про которые план как раз может нам рассказать:

И самые важные из них - операции получения данных - основной функционал, ради которого мы с СУБД и работаем. Поэтому их видов больше всего - наверное, даже больше, чем всех остальных вместе взятых.

"Нематериальные" источники данных

Result

Первое, с чего мы начнем - узел Result, который вы можете увидеть в плане самого простого запроса:

Он появляется, когда вы просто хотите получить в столбце результирующей выборки какое-то константное значение (например, SELECT 1) или результат вычисления простой функции:

"Простой" не в смысле сложности производимых ей операций, конечно, а возвращающей некоторое единичное значение, а не целую SETOF-выборку.

Третий вариант, когда может возникнуть Result-узел - "однострочная" команда VALUES, о которой мы говорили на второй лекции:

Values Scan

Тут как раз важно, чтобы строка была единственная. Потому что когда присутствует несколько строк внутри VALUES, уже возникает узел Values Scan, в rows-атрибуте у которого будет как раз количество этих самых строк:

В качестве источника данных будет указано имя "*VALUES*" или какой-то из его "номерных" вариантов типа "*VALUES*_1", отличать которые друг от друга можно как раз по количеству возвращаемых ими записей - потому что на момент планирования выполнения запроса база прекрасно знает, сколько конкретно их там будет, поскольку вы их все в тело SQL-запроса переписали, и больше им взяться неоткуда.

Function Scan

Выше мы видели результат обращения к простой функции в поле, а если указать генерирующую выборку функцию и во FROM-часть запроса, как, например, generate_series, которую рассматривали в лекции про "сложные SELECT"?.. Тогда мы увидим в плане узел Function Scan:

Тут, дополнительно к имени самой вызываемой функции, из запроса в узел будет странслирован и указанный для нее алиас, по которым можно сопоставлять узлы с разными точками вызова одних и тех же функций в теле SQL.

Атрибуты фильтрации (Filter / Rows Removed by Filter)

Раз мы дошли до узлов, которые что-то берут из базы (конкретно тут мы ничего еще пока не взяли из таблиц, но уже взяли из функции), то мы можем в запросе наложить некоторое условие. Раз у нас есть FROM-часть в запросе, то может присутствовать и WHERE-условие:

В этом случае у узла мы получим атрибутную строку Filter, в которой и будет записано наше условие отбора записей. Точнее, почти оно - в том виде, в который его преобразует PostgreSQL для конкретного узла: например, n IN (2, 3, 5) будет преобразовано в n = ANY('{2,3,5}'::integer[]), а сложное условие по нескольким функциям или таблицам может "расползтись" частями на несколько узлов плана.

А раз есть условие отбора, то при фактическом исполнении запроса (или при вызове EXPLAIN ANALYZE) могут возникнуть и строки, которые этот отбор не прошли - их количество выводится в атрибутной строке Rows Removed by Filter:

В нашем примере, очевидно, условию будет соответствовать только лишь первая строка. Но база об этом ничего не знает, поэтому честно генерирует все 4 строки, 3 отбрасывает (Rows Removed by Filter: 3), а 1 - возвращает нам (rows=1).

Фактически, уменьшение значения RRbF - непроизводительной работы базы, когда записи сначала генерируются/вычитываются, а потом отбрасываются - один из способов оптимизации эффективности выполнения SQL-запросов. В труднодостижимом идеальном состоянии, этой атрибутной строки у вас вообще не должно быть.

ProjectSet

Но если мы обратимся к генерирующей функции в поле, то вместо Function Scan получим совсем другой узел - ProjectSet:

По сути, в этот момент происходит "запоминание" результата вычисления всего набора результирующих значений.

То есть, в зависимости от того что это за функция (возвращающая единственное значение или целую выборку) и где мы ее вызвали (во FROM-части запроса или в качестве столбца результирующей выборки), мы можем получить 3 варианта узлов: Result, Function Scan и ProjectSet.

Subquery Scan

Раз мы научились что-то из функций извлекать, давайте попробуем как-то с этим результатом повзаимодействовать. И когда мы начинаем это делать, завернув саму основную выборку во вложенный запрос, в плане возникает узел Subquery Scan:

Причем у нас действительно должно быть взаимодействие со столбцами этой выборки, иначе, например, если мы в этом примере убрать условие, от всего плана останется единственный Result, поскольку последние версии PostgreSQL достаточно умны для упрощения и запроса, и порожденного им плана, избавляясь от бесполезных "SELECT-от-SELECT".

CTE Scan/CTE

А вот если вложенную выборку поместить не в подзапрос, а в WITH-часть, о которой говорили на третьей лекции, то появится возможность делать обращения к Common Table Expression, "запоминающих" результат выборки:

До версии PostgreSQL 12 наличие обращения к CTE однозначно приводило к появлению в плане узлов CTE Scan, где мы обращаемся к выборке, и узлов CTE, где эта выборка формируется.

Но, уже начиная с v12, в случае однократного нерекурсивного обращения и неиспользования ключевого слова MATERIALIZED, узел CTE Scan в плане будет заменен на ее непосредственное вычисление без генерации CTE.

Recursive Union/WorkTable Scan

Но CTE бывают еще и рекурсивные, мы с их помощью в прошлой лекции вычисляли ряд чисел Фибоначчи. В этом случае в плане мы увидим узлы Recursive Union, который реализует добавление найденного на текущем шаге к набранному на предыдущих итерациях, и WorkTable Scan - итеративного обращения к предыдущему сегменту накапливаемых данных:

InitPlan/SubPlan

Раз мы упомянули такие "ничего не делающие" узлы плана как CTE, лишь описывающие свое тело, то стоит рассказать еще про два подобных узла (в плане они выводятся "без стрелочек") - InitPlan и SubPlan, относящихся к вложенным запросам.

Между ними лишь одно, очень тонкое, отличие. Если вложенный запрос ни от чего "снаружи" не зависит, то у вас будет узел InitPlan и его однократное выполнение:

В этом примере мы для каждой записи последовательности вычислили random() и умножили на значение. А в этом - умножение на значение мы сделали внутри подзапроса, и получили узел SubPlan:

Мало того, что мы получаем разные планы - так еще и результаты принципиально различны! В случае с InitPlan random() выполняется лишь раз, и все значения пропорциональны первому, то в варианте с SubPlan, вычисление функции происходит каждый раз:

Этот момент, безусловно, стоит учитывать, когда вы пишете SQL с вложенным запросом - сколько раз "на самом деле" он у вас выполняется .

Чтение из таблицы

Наконец-то мы закончили с узлами, получающими данные "из воздуха", без обращения к каким-то "физическим" контейнерам, и дальше уже будем смотреть, как PostgreSQL умеет извлекать данные из файлов таблиц/индексов.

Подробно этого вопроса я касался в статье о поиске избыточно занятого места, а если вкратце, то таблица/индекс хранится в одном контейнере, который "физически" делится на файлы-сегменты по 1GB, которые, в свою очередь, на логические страницы данных по 8KB:

Чтение каждой страницы - это и есть "+1" в соответствующее значение buffers, о которых мы говорили в первой части лекции - в зависимости от того, была ли она уже спроецирована куда-то в память, или нам приходится читать ее с носителя.

На каждой такой странице отдельно лежат указатели на начало каждой из записей и отдельно их двоичные представления:

TID Scan

Поэтому самый быстрый и простой способ обращения к одной конкретной записи - прямая адресация, представленная узлом TID Scan - буквально, "прочитай со страницы #2 запись #3".

Его можно использовать для такой вещи как идентификация строк в таблице, где уникальные ключи в принципе отсутствуют (типа таблиц логов или значений метрик мониторинга), но поработать с конкретными экземплярами записей хочется - например, чтобы удалить лишние дубли.

Seq Scan

Следующий, самый простой и, наверное, наиболее часто встречающийся узел - Seq Scan - последовательный просмотр страниц таблицы. То есть "указатель" чтения встает в начало файла таблицы, или может, и в середину - как базе покажется "удобнее" - и начинает читать страницу за страницей:

Этот способ не только не требует какой-то дополнительной настройки и доступен для любой таблицы всегда, но и наиболее быстр как при чтении с любого носителя, так и при доступе к памяти - поскольку последовательный доступ получает все преимущества упреждающего кэширования.

Если вы читаете из небольшой таблицы, то никакой другой вариант доступа быстрее заведомо не будет. Если у вас в самой таблице 3 страницы, да еще и в индексе те же 3 - какой смысл читать тогда плюсом индекс?..

Или если у вас на таблице нет никаких подходящих к условиям запроса индексов - Seq Scan выручит всегда: надо - пофильтруем, надо - потом отсортируем.

Но если у вас в плане Seq Scan возвращает или фильтрует много-много записей (rows/RRbF) - это неудачный план, и такой подход становится объективным злом.

Index Scan [Backward]

Чтобы это зло лишний раз не тревожить, люди придумали индексы.

Ведь понятно, что "самый быстрый" не означает "самый эффективный" - Seq Scan прочитает быстро-быстро, но все-все записи из всех страниц, которые ему попадутся, и, наверняка, большинство из них отбросит как неподходящие по условиям.

Индекс - это дополнительная, как правило, древовидная логически упорядоченная структура, которая хранится аналогично модели хранения таблицы. В индексе мы всегда знаем, к какой его следующей странице надо двигаться, чтобы дойти до конкретной записи, соответствующей условиям в запросе - за это "движение" как раз и отвечает узел Index Scan - чтение записей таблицы в порядке, заданном индексом:

Однако, упорядоченным этот доступ может быть только лишь по тем ключам и условиям, которые были заранее заданы при создании индекса. В этом случае в атрибутной строке Index Cond мы увидим как раз то условие, которое используется для поиска записей.

Если такие условия обратимы с точки зрения линейного порядка ("справа - налево" вместо "слева - направо") следования записей в индексе (например, x > y и x <= y), то при "обратном" чтении узел будет Index Scan Backward.

Но проблема в том, что такие "случайные" переходы "по дереву" между страницами индекса, а потом еще и обращение к разным участкам файла таблицы, не слишком эффективны с точки зрения random read, поэтому читать лучше как можно меньше записей:

Index Only Scan [Backward]

Но если запись индекса и так "знает", что "вот там лежит запись со значением ключевого поля N", то саму запись таблицы можно уже и не извлекать, если кроме самого значения ключевого поля возвращать ничего не требуется - в этом случае в плане окажется узел Index Only Scan.

Тут важно помнить, что использование SELECT * в запросе почти всегда сделает использование Index Only Scan невозможным.

Основное отличие от Index Scan заключается в необходимости убедиться, что записи на странице, куда указывает индекс, "видны всем" - то есть доступны всем активным транзакциям в одинаковом состоянии. Это делается с помощью проверки всего лишь пары битов Visibility Map, что гораздо "дешевле" извлечения записей таблицы:

Причем, мы можем "дешево" вернуть из записи индекса значения не только ключевых полей, по которым можно искать, но и дополнительно "прицепленных" к ним директивой INCLUDE:

Heap Fetches

Но даже если мы создали подходящий индекс, в запросе написали только подходящие поля, в плане получили Index Only Scan - это вовсе не гарантия, что наш запрос выполнится быстро.

Если вдруг мы видим ненулевое значение в атрибутной строке Heap Fetches - значит, где-то VM оказалась в неактуальном состоянии, и нам уже после этой проверки пришлось дополнительно "лезть" в таблицу. В этом случае "эффективный" Index Only Scan может оказаться не просто "не лучше", а гораздо хуже, чем "обычный" Index Scan:

Order By

Еще одна атрибутная строка, которую можно увидеть у узла Index Only Scan для некоторых типов индексов, - условие индексного упорядочивания Order By:

Как правило, она возникает при использовании оператора <-> для поиска "ближайших соседей" в различных ГИС-системах:

"Нетипичное" получение данных

Дальше пойдут гораздо более редко встречающиеся "в дикой природе" варианты получающих данные узлов.

Sample Scan

Если вам надо прочитать из огромной таблицы "хоть что-то", примерно демонстрирующее распределение данных в ней - вам поможет конструкция TABLESAMPLE, которая порождает узел Sample Scan, атрибутная строка Sampling которого как раз и демонстрирует выбранный принцип отбора записей:

Фактически, TABLESAMPLE - это более эффективный вариант чтения записей по случайному вероятностному условию:

Table Function Scan

Вызов вот так нетрадиционно описываемой SQL-стандартом функции XMLTABLE (и пока вроде только для нее) порождает в плане узел Table Function Scan:

Foreign Scan

PostgreSQL имеет весьма обширную экосистему, поэтому имеет возможности для работы и со сторонними СУБД - при обращении к таким "внешним" БД в плане появляется узел Foreign Scan с атрибутной строкой Remote SQL, в которой можно увидеть реальный запрос, ушедший "наружу":

Например, для распределения нагрузки между физическими серверами мы можем использовать основной просто как "прокси", транслирующий запросы к разным таблицам на независимые узлы.

Чтобы иметь возможность работать с таблицей из "внешней" базы, нам необходимо...

1. описать реквизиты самой базы (имена сервера и БД, логин-пароль)

2. описать отображение внешней таблицы

3. выполнить запрос к локальным отображениям

В случае соединения в запросе сразу нескольких таблиц, находящихся в одной внешней БД, PostgreSQL может передать реализацию соединения самому внешнему серверу - в этом случае в узле Foreign Scan появится атрибутная строка Relations, показывающая это.

Async Foregn Scan

Но передать на подчиненные серверы только извлечение или соединение данных - мало. Хочется еще и уметь получать с них данные одновременно - это реализует узел Async Foreign Scan:

Но сделать он это сможет, только если при описании сервера указан ключ async_capable:

Named Tuplestore Scan

Крайне редкий узел Named Tuplestore Scan появляется в плане, когда мы внутри STATEMENT-триггера что-то делаем с NEW/OLD-проекциями:

В качестве примера попробуем написать триггер, который просто будет выводить NOTICE с количеством вставляемых в таблицу записей:

Теперь, если настроить максимально полное протоколирование всех планов в текущей сессии, и выполнить вставку в таблицу...

Custom Scan

Начиная с 11-12 версии PostgreSQL вы можете описать собственные методы доступа к файлам данных, используя стандартное API. Фактически, вы можете хранить и читать данные так, как вам хочется - за это отвечает узел Custom Scan, в скобочках которого будет как раз конкретный используемый для работы с таблицей метод-провайдер:

Такие планы обычно возникают при использовании различных расширений или "форков" - вроде Citus или TimescaleDB, иногда Greenplum. То есть, если вы хороший C-разработчик, то можете сделать и свой метод доступа.

Работа с битовыми картами

Битовые карты позволяют совместить преимущества скорости Seq Scan и избирательности Index Scan, но, в отличие от всех предыдущих вариантов получения данных, работа с ними уже требует наличия в плане нескольких узлов.

Bitmap: Index Scan | And/Or | Heap Scan

Сначала в узле Bitmap Index Scan база "отмечает" те страницы данных, в которых искомые значения могли бы находиться, согласно использованному индексу.

Затем, если использовалось несколько индексов, над полученными из них "картами" производится операция пересечения/объединения BitmapAnd/BitmapOr в зависимости от операции между условиями.

После этого в узле Bitmap Heap Scan отмеченные в "карте" страницы прочитываются и на них ищутся записи, подходящие под полную комбинацию условий:

Recheck Cond

Вся такая комбинация проверяемых условий как раз и выводится в строке Recheck Cond:

Rows Removed by Index Recheck

Поскольку сами карты имеют вероятностный характер, отражая лишь потенциальную возможность наличия подходящих записей, количество всех прочитанных, но не соответствующих полной комбинации условий, будет выведено в строке Rows Removed by Index Recheck:

Heap Blocks

При построении карты PostgreSQL сначала пытается сохранить "координаты" подходящих записей (exact), но если даже сама "карта" оказывается настолько велика, что не умещается в доступной памяти, то там остаются лишь номера страниц (lossy):

Понятно, что читать все записи со страницы при этом гораздо "дороже", чем проверить только конкретные. Если видите такую ситуацию в строке Heap Blocks, можете попробовать увеличить значение параметра work_mem.

---

На этом вторая часть лекции закончена, и дальше будет рассмотрение других видов операций над уже полученными данными.

Напоминаю, что на слайдах есть активные ссылки на документацию и мои статьи по темам.