Глава 10
Повышение производительности
Заставить PostgreSQL работать быстро — эта задача может возникнуть с ростом объема данных. Мы покажем лишь самые простые методы ее решения.
10.1. Основные понятия
Для понимания материала этой главы необходимо сначала познакомиться с такими важными понятиями, как метод доступа и способ соединения наборов строк.
Метод доступа
характеризует тот способ, который используется для просмотра таб- лиц и извлечения только тех строк, которые соответствуют критерию отбора. Су- ществуют различные методы доступа: последовательный просмотр (sequential scan),
при котором индекс не используется, и группа методов, основанных на использова- нии индекса. К ней относятся: просмотр по индексу (index scan), просмотр исклю- чительно на основе индекса (index only scan) и просмотр на основе битовой карты
(bitmap scan).
Поскольку и таблицы, и индексы хранятся на диске, то для работы с ними эти объ- екты считываются в память, в которой они представлены разбитыми на отдельные фрагменты, называемые страницами. Эти страницы имеют специальную структуру.
Размер страниц по умолчанию составляет 8 кбайт.
При выполнении последовательного просмотра (sequential scan) обращения к ин- дексам не происходит, а строки извлекаются из табличных страниц в соответствии с критерием отбора. В том случае, когда в запросе нет предложения WHERE, тогда из- влекаются все строки таблицы. Данный метод применяется, когда требуется выбрать все строки таблицы или значительную их часть, т. е. когда так называемая селектив-
ность
выборки низка. В таком случае обращение к индексу не ускорит процесс про- смотра, а возможно даже и замедлит.
Просмотр на основе индекса
(index scan) предполагает обращение к индексу, со- зданному для данной таблицы. Поскольку в индексе для каждого ключевого значе- ния содержатся уникальные идентификаторы строк в таблицах, то после отыскания в
293
Глава 10. Повышение производительностииндексе нужного ключа производится обращение к соответствующей странице таб- лицы и извлечение искомой строки по ее идентификатору. При этом нужно учиты- вать, что хотя записи в индексе упорядочены, но обращения к страницам таблицы происходят хаотически, поскольку строки в таблицах не упорядочены. В таком
случае при низкой селективности выборки, т. е. когда из таблицы отбирается значительное число строк, использование индексного поиска может не только не давать ускорения работы, но даже и снижать производительность.
Просмотр исключительно на основе индекса(index only scan), как следует из на- звания метода, не должен, казалось бы, требовать обращения к строкам таблицы,
поскольку все данные, которые нужно получить с помощью запроса, в этом случае присутствуют в индексе. Однако в индексе нет информации о
видимости строк тран- закциям — нельзя быть уверенным, что данные, полученные из индекса, видны те- кущей транзакции.
Поэтому сначала выполняется обращение к карте видимости (visibility map), которая существует для каждой таблицы. В ней одним битом отмечены страницы, на кото- рых содержатся только те версии строк, которые видны всем без исключения тран- закциям. Если полученная из индекса версия строки находится на такой странице,
значит, эта строка видна текущей транзакции и обращаться к самой таблице не тре- буется. Поскольку размер карты видимости очень мал, то в результате сокращается объем операций ввода/вывода. Если же строка находится на странице, не отмеченной в карте видимости, тогда происходит обращение и к таблице; в результате никакого выигрыша по быстродействию в сравнении с обычным индексным поиском не до- стигается. Просмотр исключительно на основе индекса особенно эффективен, когда выбираемые данные изменяются редко. Он может применяться, когда в предложе- нии SELECT
указаны только имена столбцов, по которым создан индекс.
Просмотр на основе битовой карты(bitmap scan) является модификацией про- смотра на основе индекса. Данный метод позволяет оптимизировать индексный по- иск за счет того, что сначала производится поиск в индексе для всех искомых строк и формирование так называемой битовой карты, в которой указывается, в каких страницах таблицы эти строки содержатся. После того как битовая карта сформиро- вана, выполняется извлечение строк из страниц таблицы, но при этом обращение к каждой странице производится
только один раз.
Другим важным понятием является
способ соединения наборов строк (join). Набор строк может быть получен из таблицы с помощью одного их методов доступа, опи- санных выше. Набор строк может быть получен не только из одной таблицы, а может быть результатом соединения других наборов. Важно различать способ соединения таблиц (JOIN) и способ соединения наборов строк. Первое понятие относится к языку
294
10.1. Основные понятияSQL и является высокоуровневым, логическим, оно не касается вопросов реализа- ции. А второе относится именно к реализации, это — механизм непосредственного выполнения соединения наборов строк. Принципиально важным является то, что за один раз соединяются только два набора строк.
Существует три способа соединения: вложенный цикл (nested loop), хеширование
(hash join) и слияние (merge join). Они имеют свои особенности, которые PostgreSQL
учитывает при выполнении конкретных запросов.
Суть способа
«вложенный цикл» в том, что перебираются строки из «внешнего» на- бора и для каждой из них выполняется поиск соответствующих строк во «внутрен- нем» наборе. Если соответствующие строки найдены, то выполняется их соединение со строкой из «внешнего» набора. При этом способы выбора строк из обоих наборов могут быть различными. Метод поддерживает соединения как на основе равенства значений атрибутов (эквисоединения), так и любые другие виды условий. Поскольку он не требует подготовительных действий, то способен быстро приступить к непо- средственной выдаче результата. Метод эффективен для небольших выборок.
При
соединении хешированием строки
одного набора помещаются в хеш-таблицу,
содержащуюся в памяти, а строки из второго набора перебираются, и для каждой из них проверяется наличие соответствующих строк в хеш-таблице. Ключом хеш- таблицы является тот столбец, по которому выполняется соединение наборов строк.
Как правило, число строк в том наборе, на основе которого строится хеш-таблица,
меньше, чем во втором наборе. Это позволяет уменьшить ее размер и ускорить про- цесс обращения к ней. Данный метод работает только при выполнении эквисоедине- ний, поскольку для хеш-таблицы имеет смысл только проверка на равенство прове- ряемого значения одному из ее ключей. Метод эффективен для больших выборок.
Соединение методом слиянияпроизводится аналогично сортировке слиянием.
В этом случае оба набора строк должны быть предварительно отсортированы по тем столбцам, по которым производится соединение. Затем параллельно читаются стро- ки из обоих наборов и сравниваются значения столбцов, по которым производится соединение. При совпадении значений формируется результирующая строка. Этот процесс продолжается до исчерпания строк в обоих наборах. Этот метод, как и ме- тод соединения хешированием, работает только при выполнении эквисоединений.
Он пригоден для работы с большими наборами строк.
295
Глава 10. Повышение производительности10.2. Методы просмотра таблицТеперь мы можем перейти к рассмотрению планов выполнения запросов.
Прежде чем приступить к непосредственному выполнению запроса, PostgreSQL фор- мирует
план его выполнения. Чтобы достичь хорошей производительности, этот план должен учитывать свойства данных. Планированием занимается специальная под- система — планировщик (planner). Просмотреть план выполнения любого запроса можно с помощью команды EXPLAIN. Для детального понимания планов выполне- ния сложных запросов требуется опыт. Мы изложим лишь основные приемы работы с этой командой.
Структура плана запроса представляет собой дерево, состоящее из так называемых
узлов плана(plan nodes). Узлы на нижних уровнях дерева отвечают за просмотр и вы- дачу строк таблиц, которые осуществляются с помощью методов доступа, описанных выше. Если конкретный запрос требует выполнения операций агрегирования, соеди- нения таблиц, сортировки, то над узлами выборки строк будут располагаться допол- нительные узлы дерева плана. Например, для соединения наборов строк будут ис- пользоваться способы, которые мы только что рассмотрели. Для каждого узла дерева плана команда EXPLAIN
выводит по одной строке, при этом выводятся также оцен- ки стоимости выполнения операций на каждом узле, которые делает планировщик.
В случае необходимости для конкретных узлов могут выводиться дополнительные строки. Самая первая строка плана содержит общую оценку стоимости выполнения данного запроса.
Запустите утилиту psql и введите простой запрос:
EXPLAIN SELECT *FROM aircrafts;В ответ получим план выполнения запроса:
QUERY PLAN
---------------------------------------------------------
Seq Scan on aircrafts (cost=0.00..1.09 rows=9 width=52)
(1 строка)
Поскольку в этом запросе нет предложения WHERE, он должен просмотреть все строки таблицы, поэтому планировщик выбирает
последовательный просмотр (sequential scan). В скобках приведены важные параметры плана.
296
10.2. Методы просмотра таблицПервое число означает оценку ресурсов, требуемых для того, чтобы приступить к вы- воду данных. В нашем примере эта оценка равна нулю, поскольку никакие дополни- тельные операции с выбранными строками не предполагаются, и PostgreSQL может сразу же выводить прочитанные строки.
Второе число — это оценка общей стоимости выполнения запроса. Формируя эту оценку, планировщик исходит из предположения, что данный узел плана запроса вы- полняется до конца, т. е. извлекаются все имеющиеся строки таблицы. Однако в ряде случаев на практике это может оказаться и не так, если узел-родитель прекратит свою работу досрочно, например, в случае использования в запросе SELECT предложения
LIMIT, которое ограничивает выборку записей из таблицы конкретным их числом.
Обе оценки стоимости выполнения выражаются в неких
условных единицах, которые вычисляются на основе ряда параметров сервера баз данных. При этом не важно,
в каких конкретно единицах производится измерение стоимости: важны соотноше- ния стоимостей. Для каждого запроса планировщик формирует несколько планов.
При сравнении различных вариантов плана, как правило, для выполнения выбира- ется тот, который имеет наименьшую общую стоимость выполнения запроса. Однако при работе с курсорами этот принцип можно изменить с помощью специального па- раметра планировщика cursor_tuple_fraction (курсоры в учебном пособии не рассматриваются).
Далее в выводе идет общее число строк, которые должны быть извлечены (возвра- щены) на данном узле плана, также при условии выполнения этого узла до полного завершения. В нашем примере число строк равно 9. Это
число является оценкой, ко- торую планировщик получает на основе статистики, накапливаемой в специальных системных таблицах.
Последним параметром узла плана идет оценка среднего размера строк, которые вы- водятся на данном узле плана запроса. В нашем примере размер (ширина) строки данных оценивается в 52 байта.
В том случае, когда нас не интересуют численные оценки, можно воспользоваться параметром COSTS OFF:
EXPLAIN ( COSTS OFF ) SELECT *FROM aircrafts;QUERY PLAN
-----------------------
Seq Scan on aircrafts
(1 строка)
297
Глава 10. Повышение производительности
Сформируем запрос с предложением WHERE:
EXPLAIN SELECT *
FROM aircrafts
WHERE model 'Air';
QUERY PLAN
---------------------------------------------------------
Seq Scan on aircrafts (cost=0.00..1.11 rows=1 width=52)
Filter: (model 'Air'::text)
(2 строки)
Поскольку наложено дополнительное условие на строки, выбираемые из таблицы,
то ниже узла плана, отвечающего за их последовательную выборку, добавляется еще один узел, описывающий критерий отбора строк.
Filter: (model 'Air'::text)
Поскольку наложено условие отбора строк, то оценка их числа изменилась с 9 на 1.
В данном случае планировщик неточно оценил число выбираемых строк — фактиче- ски их будет три.
Обратите внимание, что по своей форме вывод команды EXPLAIN также является вы- боркой, поэтому в конце выборки, как обычно, выводится информация о числе строк в ней, т. е. в дереве плана. Это не число строк, которые будут выбраны из таблицы.
В данном случае это
(2 строки)
Теперь усложним запрос, добавив в него сортировку данных:
EXPLAIN SELECT *
FROM aircrafts
ORDER BY aircraft_code;
QUERY PLAN
-------------------------------------------------------------
Sort (cost=1.23..1.26 rows=9 width=52)
Sort Key: aircraft_code
-> Seq Scan on aircrafts (cost=0.00..1.09 rows=9 width=52)
(3 строки)
298
10.2. Методы просмотра таблицДополнительный узел обозначен на плане символами «->».
Хотя по столбцу aircraft_code создан индекс (для поддержки первичного ключа),
планировщик предпочел не использовать этот индекс, а прибегнуть к последователь- ному сканированию (Seq Scan) таблицы, о чем говорит нам нижний узел плана.
На верхнем узле выполняется сортировка выбранных строк. Поскольку для выполне- ния сортировки требуется время, отличное от нуля, то этот факт и отражен в первой числовой оценке — 1,23. Это оценка времени,
которое потребуется для того, чтобы приступить к выводу отсортированных строк. Но времени непосредственно на саму сортировку потребуется меньше: ведь в оценку 1,23 входит и оценка стоимости по- лучения выборки — 1,09.
Когда таблица очень маленькая, то обращение к индексу не даст выигрыша в скоро- сти, а лишь добавит к операциям чтения страниц, в которых хранятся строки таблиц,
еще и операции чтения страниц с записями индекса.
Обратимся к таблице «Бронирования» для иллюстрации
сканирования по индексу.
EXPLAIN SELECT *FROM bookingsORDER BY book_ref;QUERY PLAN
----------------------------------------------------------------
Index Scan using bookings_pkey on bookings (cost=0.42..8511.24
rows=262788 width=21)
(1 строка)
Поскольку выводимые строки плана в утилите psql могут быть очень длинными, мы будем вносить небольшие изменения в форматирование вывода при переносе плана в текст пособия.
Обратите внимание, что первая оценка стоимости в плане — не нулевая. Это объяс- няется тем, что, хотя индекс уже упорядочен, и дополнительная сортировка не требу- ется, но для того, чтобы найти в индексе первую строку в соответствии с требуемым порядком, тоже нужно некоторое время.
Если к сортировке добавить еще и условие отбора строк, то это отразится в дополни- тельной строке верхнего (и единственного) узла плана.
EXPLAIN SELECT *FROM bookingsWHERE book_ref > '0000FF' AND book_ref < '000FFF'ORDER BY book_ref;299
Глава 10. Повышение производительности
QUERY PLAN
---------------------------------------------------------------------
Index Scan using bookings_pkey on bookings (cost=0.42..9.50 rows=54
width=21)
Index Cond: ((book_ref > '0000FF'::bpchar) AND (book_ref <
'000FFF'::bpchar))
(2 строки)
Обратите внимание, что поскольку столбец, по которому производится отбор строк,
является индексируемым, то их отбор реализуется не через Filter, а через Index
Cond.
Теперь проиллюстрируем метод сканирования на основе битовой карты на при- мере таблицы «Места».
EXPLAIN SELECT *
FROM seats
WHERE aircraft_code = 'SU9';
QUERY PLAN
------------------------------------------------------------------------
Bitmap Heap Scan on seats (cost=5.03..14.24 rows=97 width=15)
Recheck Cond: (aircraft_code = 'SU9'::bpchar)
-> Bitmap Index Scan on seats_pkey (cost=0.00..5.00 rows=97 width=0)
Index Cond: (aircraft_code = 'SU9'::bpchar)
(4 строки)
В этом плане в нижнем узле строится битовая карта, а в верхнем узле с помощью этой карты сканируются страницы таблицы seats. Здесь также для отбора строк в со- ответствии с предложением WHERE используется индекс — Index Cond. Обратите внимание, что значение параметра width при создании битовой карты равно нулю,
поскольку сами строки на этом этапе еще не выбираются.
Если нам будет нужно выбрать только номера бронирований в каком-то диапазоне,
то обращения к таблице не потребуется: достаточно сканирования исключительно
по индексу
EXPLAIN SELECT book_ref
FROM bookings
WHERE book_ref < '000FFF'
ORDER BY book_ref;
300
10.2. Методы просмотра таблиц
QUERY PLAN
-------------------------------------------------------------------------
Index Only Scan using bookings_pkey on bookings (cost=0.42..9.42 rows=57
width=7)
Index Cond: (book_ref < '000FFF'::bpchar)
(2 строки)
В этом плане только один узел — Index Only Scan. Здесь также первая оценка стои- мости не нулевая, т. к. отыскание в индексе наименьшего значения требует некото- рого времени.
Посмотрим, как отражаются в планах выполнения запросов различные агрегатные
функции
. Начнем с простого подсчета строк.
EXPLAIN SELECT count( * )
FROM seats
WHERE aircraft_code = 'SU9';
QUERY PLAN
--------------------------------------------------------------------
Aggregate (cost=14.48..14.49 rows=1 width=8)
-> Bitmap Heap Scan on seats (cost=5.03..14.24 rows=97 width=0)
Recheck Cond: (aircraft_code = 'SU9'::bpchar)
-> Bitmap Index Scan on seats_pkey (cost=0.00..5.00 rows=97
width=0)
Index Cond: (aircraft_code = 'SU9'::bpchar)
(5 строк)
В верхнем узле плана выполняется агрегирование — Aggregate. А в нижних узлах подготавливаются строки с помощью сканирования на основе формирования бито- вой карты.
Но возникает вопрос: зачем вообще выполняется обращение к страницам таблицы
(Bitmap Heap Scan), если никакие значения атрибутов не выбираются, а подсчиты- вается лишь число этих строк? Казалось бы, достаточно использования только ин- декса. Но это нужно для того, чтобы проверить видимость версий строк: ведь разные транзакции могут видеть разные версии строк, поэтому при подсчете их числа нуж- но учитывать, какой транзакции они видны. Обратите еще внимание на тот факт, что собственно стадия агрегирования «ст´oит» не очень дорого. Ее можно приблизительно оценить как 0,24 (отняв от оценки 14,48 в узле Aggregate оценку 14,24 в узле Bitmap
Heap Scan).
301
Глава 10. Повышение производительностиА в этом примере агрегирование связано уже с вычислениями на основе значений конкретного столбца, а не просто с подсчетом строк.
Скачать 1.88 Mb.