Продолжаю публикацию расширенных транскриптов лекционного курса "PostgreSQL для начинающих", подготовленного мной в рамках "Школы backend-разработчика" в "Тензоре".
В первой части лекции мы узнали, что такое план выполнения запроса, как и зачем его читать (и почему это совсем непросто), и о каких проблемах с производительностью базы он может сигнализировать. В этой - разберем, что такое Seq Scan, Bitmap Heap Scan, Index Scan и почему Index Only Scan бывает нехорош.
Как обычно, для предпочитающих смотреть и слушать, а не читать - доступна видеозапись (часть 1, часть 2) и слайды:
Краткий путеводитель:
Анализ запросов (часть 1)
Индексы
Транзакции
Блокировки
Содержание текущей лекции:
"Нематериальные" источники данных
Function Scan (
Filter,Rows Removed by Filter)
Index Scan [Backward] (
Index Cond)Index Only Scan [Backward] (
Heap Fetches,Order By)
Sample Scan (
Sampling)Foreign Scan (
Remote SQL,Relations)
Bitmap Index Scan
BitmapAnd / BitmapOr
Bitmap Heap Scan (
Recheck Cond,Rows Removed by Index Recheck,Heap Blocks)
Наконец, мы добрались до "основного блюда" нашей лекции - перечня тех операций, про которые план как раз может нам рассказать:
И самые важные из них - операции получения данных - основной функционал, ради которого мы с СУБД и работаем. Поэтому их видов больше всего - наверное, даже больше, чем всех остальных вместе взятых.
"Нематериальные" источники данных
Result
Первое, с чего мы начнем - узел Result, который вы можете увидеть в плане самого простого запроса:
Result: константаОн появляется, когда вы просто хотите получить в столбце результирующей выборки какое-то константное значение (например, SELECT 1) или результат вычисления простой функции:
Result: функция в поле"Простой" не в смысле сложности производимых ей операций, конечно, а возвращающей некоторое единичное значение, а не целую SETOF-выборку.
Третий вариант, когда может возникнуть Result-узел - "однострочная" команда VALUES, о которой мы говорили на второй лекции:
Result: однострочный VALUESValues Scan
Тут как раз важно, чтобы строка была единственная. Потому что когда присутствует несколько строк внутри VALUES, уже возникает узел Values Scan, в rows-атрибуте у которого будет как раз количество этих самых строк:
Values Scan: многострочный VALUESВ качестве источника данных будет указано имя "*VALUES*" или какой-то из его "номерных" вариантов типа "*VALUES*_1", отличать которые друг от друга можно как раз по количеству возвращаемых ими записей - потому что на момент планирования выполнения запроса база прекрасно знает, сколько конкретно их там будет, поскольку вы их все в тело SQL-запроса переписали, и больше им взяться неоткуда.
Function Scan
Выше мы видели результат обращения к простой функции в поле, а если указать генерирующую выборку функцию и во FROM-часть запроса, как, например, generate_series, которую рассматривали в лекции про "сложные SELECT"?.. Тогда мы увидим в плане узел Function Scan:
Function Scan: выборка из функцииТут, дополнительно к имени самой вызываемой функции, из запроса в узел будет странслирован и указанный для нее алиас, по которым можно сопоставлять узлы с разными точками вызова одних и тех же функций в теле SQL.
Атрибуты фильтрации (Filter / Rows Removed by Filter)
Раз мы дошли до узлов, которые что-то берут из базы (конкретно тут мы ничего еще пока не взяли из таблиц, но уже взяли из функции), то мы можем в запросе наложить некоторое условие. Раз у нас есть FROM-часть в запросе, то может присутствовать и WHERE-условие:
Filter: условие отбора записейВ этом случае у узла мы получим атрибутную строку Filter, в которой и будет записано наше условие отбора записей. Точнее, почти оно - в том виде, в который его преобразует PostgreSQL для конкретного узла: например, n IN (2, 3, 5) будет преобразовано в n = ANY('{2,3,5}'::integer[]), а сложное условие по нескольким функциям или таблицам может "расползтись" частями на несколько узлов плана.
А раз есть условие отбора, то при фактическом исполнении запроса (или при вызове EXPLAIN ANALYZE) могут возникнуть и строки, которые этот отбор не прошли - их количество выводится в атрибутной строке Rows Removed by Filter:
Rows Removed by Filter: количество отброшенных строкВ нашем примере, очевидно, условию будет соответствовать только лишь первая строка. Но база об этом ничего не знает, поэтому честно генерирует все 4 строки, 3 отбрасывает (Rows Removed by Filter: 3), а 1 - возвращает нам (rows=1).
Фактически, уменьшение значения RRbF - непроизводительной работы базы, когда записи сначала генерируются/вычитываются, а потом отбрасываются - один из способов оптимизации эффективности выполнения SQL-запросов. В труднодостижимом идеальном состоянии, этой атрибутной строки у вас вообще не должно быть.
ProjectSet
Но если мы обратимся к генерирующей функции в поле, то вместо Function Scan получим совсем другой узел - ProjectSet:
ProjectSet: SETOF-функция в полеПо сути, в этот момент происходит "запоминание" результата вычисления всего набора результирующих значений.
То есть, в зависимости от того что это за функция (возвращающая единственное значение или целую выборку) и где мы ее вызвали (во FROM-части запроса или в качестве столбца результирующей выборки), мы можем получить 3 варианта узлов: Result, Function Scan и ProjectSet.
Subquery Scan
Раз мы научились что-то из функций извлекать, давайте попробуем как-то с этим результатом повзаимодействовать. И когда мы начинаем это делать, завернув саму основную выборку во вложенный запрос, в плане возникает узел Subquery Scan:
Subquery Scan: вложенная выборкаПричем у нас действительно должно быть взаимодействие со столбцами этой выборки, иначе, например, если мы в этом примере убрать условие, от всего плана останется единственный Result, поскольку последние версии PostgreSQL достаточно умны для упрощения и запроса, и порожденного им плана, избавляясь от бесполезных "SELECT-от-SELECT".
CTE Scan/CTE
А вот если вложенную выборку поместить не в подзапрос, а в WITH-часть, о которой говорили на третьей лекции, то появится возможность делать обращения к Common Table Expression, "запоминающих" результат выборки:
CTE Scan: обращение к Common Table ExpressionДо версии PostgreSQL 12 наличие обращения к CTE однозначно приводило к появлению в плане узлов CTE Scan, где мы обращаемся к выборке, и узлов CTE, где эта выборка формируется.
Но, уже начиная с v12, в случае однократного нерекурсивного обращения и неиспользования ключевого слова MATERIALIZED, узел CTE Scan в плане будет заменен на ее непосредственное вычисление без генерации CTE.
Recursive Union/WorkTable Scan
Но CTE бывают еще и рекурсивные, мы с их помощью в прошлой лекции вычисляли ряд чисел Фибоначчи. В этом случае в плане мы увидим узлы Recursive Union, который реализует добавление найденного на текущем шаге к набранному на предыдущих итерациях, и WorkTable Scan - итеративного обращения к предыдущему сегменту накапливаемых данных:
Recursive Union/WorkTable Scan: рекурсивный запросInitPlan/SubPlan
Раз мы упомянули такие "ничего не делающие" узлы плана как CTE, лишь описывающие свое тело, то стоит рассказать еще про два подобных узла (в плане они выводятся "без стрелочек") - InitPlan и SubPlan, относящихся к вложенным запросам.
Между ними лишь одно, очень тонкое, отличие. Если вложенный запрос ни от чего "снаружи" не зависит, то у вас будет узел InitPlan и его однократное выполнение:
InitPlan: независимый вложенный запросВ этом примере мы для каждой записи последовательности вычислили random() и умножили на значение. А в этом - умножение на значение мы сделали внутри подзапроса, и получили узел SubPlan:
SubPlan: зависящий от внешнего вложенный запросМало того, что мы получаем разные планы - так еще и результаты принципиально различны! В случае с InitPlan random() выполняется лишь раз, и все значения пропорциональны первому, то в варианте с SubPlan, вычисление функции происходит каждый раз:
InitPlan - лишь раз, SubPlan - каждый раз
InitPlan - все значения пропорциональны первому, SubPlan - все независимыЭтот момент, безусловно, стоит учитывать, когда вы пишете SQL с вложенным запросом - сколько раз "на самом деле" он у вас выполняется .
Чтение из таблицы
Наконец-то мы закончили с узлами, получающими данные "из воздуха", без обращения к каким-то "физическим" контейнерам, и дальше уже будем смотреть, как PostgreSQL умеет извлекать данные из файлов таблиц/индексов.
Подробно этого вопроса я касался в статье о поиске избыточно занятого места, а если вкратце, то таблица/индекс хранится в одном контейнере, который "физически" делится на файлы-сегменты по 1GB, которые, в свою очередь, на логические страницы данных по 8KB:
Чтение каждой страницы - это и есть "+1" в соответствующее значение buffers, о которых мы говорили в первой части лекции - в зависимости от того, была ли она уже спроецирована куда-то в память, или нам приходится читать ее с носителя.
На каждой такой странице отдельно лежат указатели на начало каждой из записей и отдельно их двоичные представления:
TID Scan
Поэтому самый быстрый и простой способ обращения к одной конкретной записи - прямая адресация, представленная узлом TID Scan - буквально, "прочитай со страницы #2 запись #3".
TID Scan: чтение записи по "физическому" расположению (pageNo, tupleNo)Его можно использовать для такой вещи как идентификация строк в таблице, где уникальные ключи в принципе отсутствуют (типа таблиц логов или значений метрик мониторинга), но поработать с конкретными экземплярами записей хочется - например, чтобы удалить лишние дубли.
TID ScanSeq Scan
Следующий, самый простой и, наверное, наиболее часто встречающийся узел - Seq Scan - последовательный просмотр страниц таблицы. То есть "указатель" чтения встает в начало файла таблицы, или может, и в середину - как базе покажется "удобнее" - и начинает читать страницу за страницей:
Seq Scan: читаем все подрядЭтот способ не только не требует какой-то дополнительной настройки и доступен для любой таблицы всегда, но и наиболее быстр как при чтении с любого носителя, так и при доступе к памяти - поскольку последовательный доступ получает все преимущества упреждающего кэширования.
Если вы читаете из небольшой таблицы, то никакой другой вариант доступа быстрее заведомо не будет. Если у вас в самой таблице 3 страницы, да еще и в индексе те же 3 - какой смысл читать тогда плюсом индекс?..
Seq Scan: самый быстрый, доступен всегдаИли если у вас на таблице нет никаких подходящих к условиям запроса индексов - Seq Scan выручит всегда: надо - пофильтруем, надо - потом отсортируем.
Но если у вас в плане Seq Scan возвращает или фильтрует много-много записей (rows/RRbF) - это неудачный план, и такой подход становится объективным злом.
Index Scan [Backward]
Чтобы это зло лишний раз не тревожить, люди придумали индексы.
Ведь понятно, что "самый быстрый" не означает "самый эффективный" - Seq Scan прочитает быстро-быстро, но все-все записи из всех страниц, которые ему попадутся, и, наверняка, большинство из них отбросит как неподходящие по условиям.
Индекс - это дополнительная, как правило, древовидная логически упорядоченная структура, которая хранится аналогично модели хранения таблицы. В индексе мы всегда знаем, к какой его следующей странице надо двигаться, чтобы дойти до конкретной записи, соответствующей условиям в запросе - за это "движение" как раз и отвечает узел Index Scan - чтение записей таблицы в порядке, заданном индексом:
Index Scan: упорядоченный доступ к записямОднако, упорядоченным этот доступ может быть только лишь по тем ключам и условиям, которые были заранее заданы при создании индекса. В этом случае в атрибутной строке Index Cond мы увидим как раз то условие, которое используется для поиска записей.
Если такие условия обратимы с точки зрения линейного порядка ("справа - налево" вместо "слева - направо") следования записей в индексе (например, x > y и x <= y), то при "обратном" чтении узел будет Index Scan Backward.
Но проблема в том, что такие "случайные" переходы "по дереву" между страницами индекса, а потом еще и обращение к разным участкам файла таблицы, не слишком эффективны с точки зрения random read, поэтому читать лучше как можно меньше записей:
Index Scan: быстро, если читать немногоIndex Only Scan [Backward]
Но если запись индекса и так "знает", что "вот там лежит запись со значением ключевого поля N", то саму запись таблицы можно уже и не извлекать, если кроме самого значения ключевого поля возвращать ничего не требуется - в этом случае в плане окажется узел Index Only Scan.
Тут важно помнить, что использование
SELECT *в запросе почти всегда сделает использованиеIndex Only Scanневозможным.
Основное отличие от Index Scan заключается в необходимости убедиться, что записи на странице, куда указывает индекс, "видны всем" - то есть доступны всем активным транзакциям в одинаковом состоянии. Это делается с помощью проверки всего лишь пары битов Visibility Map, что гораздо "дешевле" извлечения записей таблицы:
Index Only Scan: пробуем не заглядывать в таблицуПричем, мы можем "дешево" вернуть из записи индекса значения не только ключевых полей, по которым можно искать, но и дополнительно "прицепленных" к ним директивой INCLUDE:
Index Only Scan: добавление неключевых полейHeap Fetches
Но даже если мы создали подходящий индекс, в запросе написали только подходящие поля, в плане получили Index Only Scan - это вовсе не гарантия, что наш запрос выполнится быстро.
Если вдруг мы видим ненулевое значение в атрибутной строке Heap Fetches - значит, где-то VM оказалась в неактуальном состоянии, и нам уже после этой проверки пришлось дополнительно "лезть" в таблицу. В этом случае "эффективный" Index Only Scan может оказаться не просто "не лучше", а гораздо хуже, чем "обычный" Index Scan:
Heap Fetches: отрабатываем "промах" сквозь VMOrder By
Еще одна атрибутная строка, которую можно увидеть у узла Index Only Scan для некоторых типов индексов, - условие индексного упорядочивания Order By:
Order By: доступно для некоторых индексов и операцийКак правило, она возникает при использовании оператора <-> для поиска "ближайших соседей" в различных ГИС-системах:
Order By: ближайшие 10 точек из миллиона"Нетипичное" получение данных
Дальше пойдут гораздо более редко встречающиеся "в дикой природе" варианты получающих данные узлов.
Sample Scan
Если вам надо прочитать из огромной таблицы "хоть что-то", примерно демонстрирующее распределение данных в ней - вам поможет конструкция TABLESAMPLE, которая порождает узел Sample Scan, атрибутная строка Sampling которого как раз и демонстрирует выбранный принцип отбора записей:
Sample Scan: читаем "кусочек" таблицыФактически, TABLESAMPLE - это более эффективный вариант чтения записей по случайному вероятностному условию:
TABLESAMPLE - замена random() < XTable Function Scan
Вызов вот так нетрадиционно описываемой SQL-стандартом функции XMLTABLE (и пока вроде только для нее) порождает в плане узел Table Function Scan:
Table Function Scan: редкий гостьForeign Scan
PostgreSQL имеет весьма обширную экосистему, поэтому имеет возможности для работы и со сторонними СУБД - при обращении к таким "внешним" БД в плане появляется узел Foreign Scan с атрибутной строкой Remote SQL, в которой можно увидеть реальный запрос, ушедший "наружу":
Foreign Scan: чтение из "сторонней" БДНапример, для распределения нагрузки между физическими серверами мы можем использовать основной просто как "прокси", транслирующий запросы к разным таблицам на независимые узлы.
Чтобы иметь возможность работать с таблицей из "внешней" базы, нам необходимо...
описать реквизиты самой базы (имена сервера и БД, логин-пароль)
описать отображение внешней таблицы
выполнить запрос к локальным отображениям
Relations: соединение таблиц на внешнем сервереВ случае соединения в запросе сразу нескольких таблиц, находящихся в одной внешней БД, PostgreSQL может передать реализацию соединения самому внешнему серверу - в этом случае в узле Foreign Scan появится атрибутная строка Relations, показывающая это.
Async Foregn Scan
Но передать на подчиненные серверы только извлечение или соединение данных - мало. Хочется еще и уметь получать с них данные одновременно - это реализует узел Async Foreign Scan:
Async Foreign Scan: одновременное получение "внешних" данныхНо сделать он это сможет, только если при описании сервера указан ключ async_capable:
async_capable разрешает асинхронное выполнение
Async Foreign Scan: проброс запросаNamed Tuplestore Scan
Крайне редкий узел Named Tuplestore Scan появляется в плане, когда мы внутри STATEMENT-триггера что-то делаем с NEW/OLD-проекциями:
Named Tuplestore Scan: работа с NEW/OLD в триггереВ качестве примера попробуем написать триггер, который просто будет выводить NOTICE с количеством вставляемых в таблицу записей:
Теперь, если настроить максимально полное протоколирование всех планов в текущей сессии, и выполнить вставку в таблицу...
Custom Scan
Начиная с 11-12 версии PostgreSQL вы можете описать собственные методы доступа к файлам данных, используя стандартное API. Фактически, вы можете хранить и читать данные так, как вам хочется - за это отвечает узел Custom Scan, в скобочках которого будет как раз конкретный используемый для работы с таблицей метод-провайдер:
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 отмеченные в "карте" страницы прочитываются и на них ищутся записи, подходящие под полную комбинацию условий:
Bitmap-операцииRecheck Cond
Вся такая комбинация проверяемых условий как раз и выводится в строке Recheck Cond:
Recheck Cond: проверка условийRows Removed by Index Recheck
Поскольку сами карты имеют вероятностный характер, отражая лишь потенциальную возможность наличия подходящих записей, количество всех прочитанных, но не соответствующих полной комбинации условий, будет выведено в строке Rows Removed by Index Recheck:
Rows Removed by Index Recheck: количество отфильтрованных строкHeap Blocks
При построении карты PostgreSQL сначала пытается сохранить "координаты" подходящих записей (exact), но если даже сама "карта" оказывается настолько велика, что не умещается в доступной памяти, то там остаются лишь номера страниц (lossy):
Heap Blocks: влезла ли "карта"Понятно, что читать все записи со страницы при этом гораздо "дороже", чем проверить только конкретные. Если видите такую ситуацию в строке Heap Blocks, можете попробовать увеличить значение параметра work_mem.
На этом вторая часть лекции закончена, и дальше будет рассмотрение других видов операций над уже полученными данными.
Напоминаю, что на слайдах есть активные ссылки на документацию и мои статьи по темам.
