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 строки)
Дополнительный узел обозначен на плане символами «−>».
Хотя по столбцу aircraft_code создан индекс (для поддержки первичного ключа), пла- нировщик предпочел не использовать этот индекс, а прибегнуть к последовательно- му сканированию (Seq Scan) таблицы, о чем говорит нам нижний узел плана. На верх- нем узле выполняется сортировка выбранных строк. Поскольку для выполнения сор- тировки требуется время, отличное от нуля, то этот факт и отражен в первой числовой оценке — 1,23. Это оценка времени, которое потребуется для того, чтобы приступить к выводу отсортированных строк. Но времени непосредственно на саму сортировку потребуется меньше: ведь в оценку 1,23 входит и оценка стоимости получения вы- борки — 1,09.
Когда таблица очень маленькая, то обращение к индексу не даст выигрыша в скоро- сти, а лишь добавит к операциям чтения страниц, в которых хранятся строки таблиц,
еще и операции чтения страниц с записями индекса.
Обратимся к таблице «Бронирования» (bookings) для иллюстрации сканирования по
индексу
EXPLAIN SELECT * FROM bookings ORDER BY book_ref;
QUERY PLAN
----------------------------------------------------------------
Index Scan using bookings_pkey on bookings (cost=0.42..8511.24
rows=262788 width=21)
,→
(1 строка)
Поскольку выводимые строки плана в утилите psql могут быть очень длинными, мы будем вносить небольшие изменения в форматирование вывода при переносе плана в текст пособия.
232
Обратите внимание, что первая оценка стоимости в плане — не нулевая. Это объяс- няется тем, что, хотя индекс уже упорядочен, и дополнительная сортировка не требу- ется, но для того, чтобы найти в индексе первую строку в соответствии с требуемым порядком, тоже нужно некоторое время.
Если к
сортировке добавить еще и условие отбора строк, то это отразится в дополни- тельной строке верхнего (и единственного) узла плана.
EXPLAINSELECT * FROM bookingsWHERE book_ref > '0000FF' AND book_ref < '000FFF'ORDER BY book_ref;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.
Теперь проиллюстрируем метод
сканирования на основе битовой карты на при- мере таблицы «Места» (seats).
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 при создании битовой карты равно нулю,
поскольку сами строки на этом этапе еще не выбираются.
Если нам будет нужно выбрать только номера бронирований в каком-то диапазоне,
то обращения к таблице не потребуется: достаточно
сканирования исключительнопо индексуEXPLAINSELECT book_refFROM bookingsWHERE book_ref < '000FFF'ORDER BY book_ref;233
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. Здесь также первая оценка стоимо- сти не нулевая, т. к. отыскание в индексе наименьшего значения требует некоторого времени.
Посмотрим, как отражаются в планах выполнения запросов различные
агрегатныефункции. Начнем с простого подсчета строк.
EXPLAINSELECT count( * )FROM seatsWHERE 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), если никакие значения атрибутов не выбираются, а подсчитыва- ется лишь число этих строк? Казалось бы, достаточно использования только индек- са. Но это нужно для того, чтобы проверить видимость версий строк: ведь разные транзакции могут видеть разные версии строк,
поэтому при подсчете их числа нуж- но учитывать, какой транзакции они видны. Обратите еще внимание на тот факт, что собственно стадия агрегирования «стоит» не очень дорого. Ее можно приблизитель- но оценить как 0,24 (отняв от оценки 14,48 в узле Aggregate оценку 14,24 в узле Bitmap
Heap Scan).
А в этом примере агрегирование связано уже с вычислениями на основе значений конкретного столбца, а не просто с подсчетом строк.
EXPLAIN_SELECT_a.aircraft_code,_a.model,_s.seat_no,_s.fare_conditions_FROM_seats_s_JOIN_aircrafts_a_ON_s.aircraft_code_=_a.aircraft_code'>EXPLAIN_SELECT_avg(_total_amount_)_FROM_bookings;'>EXPLAIN SELECT avg( total_amount ) FROM bookings;QUERY PLAN
-----------------------------------------------------------------
Aggregate (cost=4958.85..4958.86 rows=1 width=32)
-> Seq Scan on bookings (cost=0.00..4301.88 rows=262788 width=6)
(2 строки)
234
10.3 Методы формирования соединений наборов строк
Теперь обратимся к методам, которые используются для формирования соедине- ний наборов строк. Начнем с метода вложенного цикла (nested loop). Для получе- ния списка мест в салонах самолетов Airbus с указанием класса обслуживания сфор- мируем запрос, в котором соединяются две таблицы: «Места» (seats) и «Самолеты»
(aircrafts).
EXPLAIN
SELECT a.aircraft_code, a.model,
s.seat_no, s.fare_conditions
FROM seats s
JOIN aircrafts a ON s.aircraft_code = a.aircraft_code
WHERE a.model '^Air'
ORDER BY s.seat_no;
QUERY PLAN
-----------------------------------------------------------------
Sort (cost=23.28..23.65 rows=149 width=59)
Sort Key: s.seat_no
-> Nested Loop (cost=5.43..17.90 rows=149 width=59)
-> Seq Scan on aircrafts a (cost=0.00..1.11 rows=1 width=48)
Filter: (model '^Air'::text)
-> Bitmap Heap Scan on seats s (cost=5.43..15.29 rows=149
width=15)
,→
Recheck Cond: (aircraft_code = a.aircraft_code)
-> Bitmap Index Scan on seats_pkey (cost=0.00..5.39
rows=149 width=0)
,→
Index Cond: (aircraft_code = a.aircraft_code)
(9 строк)
Узел Nested Loop, в котором выполняется соединение, имеет два дочерних узла:
внешний — Seq Scan и внутренний — Bitmap Heap Scan. Во внешнем узле последо- вательно сканируется таблица aircrafts с целью отбора строк согласно условию Filter:
(model ’^Air’::text). Для каждой из отобранных строк во внутреннем дочернем уз- ле (Bitmap Heap Scan) выполняется поиск в таблице seats по индексу с использо- ванием битовой карты. Она формируется в узле Bitmap Index Scan с учетом усло- вия Index Cond: (aircraft_code = a.aircraft_code), т. е. для текущего значения атрибута aircraft_code, по которому выполняется соединение. На верхнем уровне плана сфор- мированные строки сортируются по ключу (Sort Key: s.seat_no).
Следующий метод соединения наборов строк — соединение хешированием (hash join). Получим список маршрутов с указанием модели самолета, выполняющего рей- сы по этим маршрутам. Воспользуемся таблицами «Маршруты» (routes) и «Самолеты»
(aircrafts).
EXPLAIN
SELECT r.flight_no, r.departure_airport_name,
r.arrival_airport_name, a.model
FROM routes r
JOIN aircrafts a ON r.aircraft_code = a.aircraft_code
ORDER BY flight_no;
235
QUERY PLAN
-----------------------------------------------------------------
Sort (cost=24.25..24.31 rows=21 width=124)
Sort Key: r.flight_no
-> Hash Join (cost=1.20..23.79 rows=21 width=124)
Hash Cond: (r.aircraft_code = a.aircraft_code)
-> Seq Scan on routes r (cost=0.00..20.64 rows=464 width=108)
-> Hash (cost=1.09..1.09 rows=9 width=48)
-> Seq Scan on aircrafts a (cost=0.00..1.09 rows=9 width=48)
(7 строк)
На самом внутреннем уровне плана последовательно сканируется (Seq Scan) таблица aircrafts, и формируется хеш-таблица, ключами которой являются значения атрибу- та aircraft_code, т. к. именно по нему выполняется соединение таблиц. Затем после- довательно сканируется (Seq Scan) таблица routes, и для каждой ее строки выполня- ется поиск значения атрибута aircraft_code среди ключей хеш-таблицы: Hash Cond:
(r.aircraft_code = a.aircraft_code). Если такой поиск успешен, значит, формируется ком- бинированная результирующая строка выборки. На верхнем уровне плана сформи- рованные строки сортируются. Обратите внимание, что хеш-таблица создана на ос- нове той таблицы,
число строк в которой меньше, т. е. aircrafts. Таким образом, поиск в ней будет выполняться быстрее, чем если бы хеш-таблица была создана на основе таблицы routes.
Последний из методов соединения наборов строк —
соединение слиянием (merge join). Для иллюстрации воспользуемся простым запросом, построенным на основе таблиц «Билеты» (tickets) и «Перелеты» (ticket_flights). Он выбирает для каждого би- лета все перелеты, включенные в него. Конечно, это очень упрощенный запрос, в ре- альной ситуации он не представлял бы большой практической пользы, но в целях упрощения плана и повышения наглядности, воспользуемся им.
EXPLAINSELECT t.ticket_no, t.passenger_name,tf.flight_id, tf.amountFROM tickets tJOIN ticket_flights tf ON t.ticket_no = tf.ticket_noORDER BY t.ticket_no;QUERY PLAN
-----------------------------------------------------------------
Merge Join (cost=1.51..98276.90 rows=1045726 width=40)
Merge Cond: (t.ticket_no = tf.ticket_no)
-> Index Scan using tickets_pkey on tickets t (cost=0.42..17230.42
rows=366733 width=30)
,→
-> Index Scan using ticket_flights_pkey on ticket_flights tf
(cost=0.42..67058.74 rows=1045726 width=24)
,→
(4 строки)
Два внутренних узла дерева плана отвечают за сканирование таблиц tickets и ticket_flights по индексам (Index Scan). Таким образом, верхний узел (Merge Join) по- лучает наборы строк этих таблиц уже в отсортированном виде, поэтому не требуется отдельного узла для сортировки результирующих строк. Обратите внимание на оцен- ки стоимости выполнения всех трех операций: двух сканирований таблиц и резуль- тирующего соединения слиянием. Мы видим, что первая оценка в узле Merge Join
236
равна 1, 51, что значительно меньше вторых оценок, вычисленных планировщиком для двух нижних узлов, а именно: 17230, 42 и 67058, 74. Напомним, что первая оцен- ка говорит, сколько ресурсов будет затрачено (сколько времени, в
условных едини- цах, пройдет) до начала вывода первых результатов выполнения операции на данном уровне дерева плана. Вторая оценка показывает общее количество ресурсов, требу- ющихся для полного завершения операции на данном уровне дерева плана. Таким образом, можно заключить, что вывод результирующих строк начнется еще задолго до завершения сканирования исходных таблиц.
10.4 Управление планировщикомДля управления планировщиком предусмотрен целый ряд параметров. Их можно из- менить на время текущего сеанса работы с помощью команды SET. Конечно, изме- нять параметры в производственной базе данных следует только в том случае, когда вы
обоснованно считаете, что планировщик ошибается. Однако для того чтобы на- учиться видеть ошибки планировщика, нужен большой опыт. Поэтому следует рас- сматривать приведенные далее команды управления планировщиком лишь с пози- ции изучения потенциальных возможностей управления им, а не как рекомендацию к бездумному изменению этих параметров в реальной работе.
Например, чтобы запретить планировщику использовать метод соединения на осно- ве хеширования, нужно сделать так:
SET enable_hashjoin = off;Чтобы запретить планировщику использовать метод соединения слиянием, нужно сделать так:
SET enable_mergejoin = off;А для того чтобы запретить планировщику использовать соединение методом вло- женного цикла, нужно сделать так:
SET enable_nestloop = off;По умолчанию все эти параметры имеют значение «on» (включено).
Необходимо уточнить, что в результате выполнения вышеприведенных команд не накладывается полного запрета на использование конкретного метода соединения наборов строк. Методу просто назначается очень высокая стоимость, но планиров- щик все равно сохраняет возможность маневра, и даже такой «запрещенный» метод может быть использован. Более подробно этот вопрос рассматривается в одном из примеров в разделе «Контрольные вопросы и задания».
Давайте запретим планировщику использовать метод соединения слиянием:
SET enable_mergejoin = off;SET
Теперь повторим предыдущий запрос:
237
EXPLAINSELECT t.ticket_no, t.passenger_name,tf.flight_id, tf.amountFROM tickets tJOIN ticket_flights tf ON t.ticket_no = tf.ticket_noORDER BY t.ticket_no;QUERY PLAN
---------------------------------------------------------------
Sort (cost=226400.55..229014.87 rows=1045726 width=40)
Sort Key: t.ticket_no
-> Hash Join (cost=16824.49..64658.49 rows=1045726 width=40)
Hash Cond: (tf.ticket_no = t.ticket_no)
-> Seq Scan on ticket_flights tf (cost=0.00..18692.26
rows=1045726 width=24)
,→
-> Hash (cost=9733.33..9733.33 rows=366733 width=30)
-> Seq Scan on tickets t (cost=0.00..9733.33 rows=366733
width=30)
,→
(7 строк)
Теперь планировщик выбирает слияние хешированием. Полученные оценки стои- мости выполнения запроса будут значительно выше. При этом вывод результирую- щих
строк начнется значительно позднее, чем при использовании метода соедине- ния слиянием. На это указывает значение параметра cost для верхнего узла дерева плана — cost=226400.55..229014.87.
В команде EXPLAIN можно указать опцию ANALYZE, что позволит выполнить запрос и вывести на экран фактические затраты времени на выполнение запроса и число фактически выбранных строк. При этом, хотя запрос и выполняется, сами результи- рующие строки не выводятся.
Сначала разрешим планировщику использовать метод соединения слиянием:
SET enable_mergejoin = on;SET
Повторим предыдущий запрос с опцией ANALYZE.
EXPLAIN ANALYZESELECT t.ticket_no, t.passenger_name,tf.flight_id, tf.amountFROM tickets tJOIN ticket_flights tf ON t.ticket_no = tf.ticket_noORDER BY t.ticket_no;QUERY PLAN
-----------------------------------------------------------------
Merge Join (cost=1.51..98276.90 rows=1045726 width=40)
(actual time=0.087..10642.643 rows=1045726 loops=1)
Merge Cond: (t.ticket_no = tf.ticket_no)
-> Index Scan using tickets_pkey on tickets t
(cost=0.42..17230.42 rows=366733 width=30)
(actual time=0.031..762.460 rows=366733 loops=1)
-> Index Scan using ticket_flights_pkey on ticket_flights tf
(cost=0.42..67058.74 rows=1045726 width=24)
238
(actual time=0.006..7743.705 rows=1045726 loops=1)
Planning time: 122.347 ms
Execution time: 10948.791 ms
(6 строк)
Фактические затраты времени измеряются в миллисекундах, а оценки стоимости —
в условных единицах, поэтому плановые оценки и фактические затраты совпасть не могут. Важнее обратить внимание на то, насколько точно планировщик оценил чис- ло обрабатываемых строк, а также на фактическое число повторений того или иного узла дерева плана — это параметр loops. В данном запросе каждый узел плана был вы- полнен ровно один раз, поскольку выбор строк из обоих соединяемых наборов про- изводился по индексу, поэтому достаточно одного прохода по каждому набору. Чис- ло выбираемых строк было оценено точно, поскольку таблицы связаны по внешнему ключу, и в выборку включаются все их строки (нет предложения WHERE).
Кроме времени выполнения запроса выводится также и время формирования пла- на.
Необходимо учитывать, что фактические затраты времени на разных компьютерах будут различаться. Будет другим и фактическое время при повторном выполнении запроса на одном и том же компьютере, поскольку и в СУБД, и в операционной си- стеме используются буферизация и кэширование, а также с течением времени может изменяться фактическая нагрузка на сервер. Поэтому время выполнения повторно- го запроса может оказаться значительно меньше, чем
время выполнения первого, а время выполнения запроса в третий раз — немного больше, чем во второй.
Если модифицировать запрос, добавив предложение WHERE, то точного совпадения оценки числа выбираемых строк и фактического их числа уже не будет.
EXPLAIN ANALYZESELECT t.ticket_no, t.passenger_name,tf.flight_id, tf.amountFROM tickets tJOIN ticket_flights tf ON t.ticket_no = tf.ticket_noWHERE amount > 50000ORDER BY t.ticket_no;QUERY PLAN
-----------------------------------------------------------------
Merge Join (cost=27391.09..46664.80 rows=75126 width=40)
(actual time=2133.715..3117.200 rows=72647 loops=1)
Merge Cond: (t.ticket_no = tf.ticket_no)
-> Index Scan using tickets_pkey on tickets t
(cost=0.42..17230.42 rows=366733 width=30)
(actual time=0.009..318.517 rows=366733 loops=1)
-> Sort (cost=27390.66..27578.48 rows=75126 width=24)
(actual time=2132.781..2173.526 rows=72647 loops=1)
Sort Key: tf.ticket_no
Sort Method: external sort Disk: 2768kB
-> Seq Scan on ticket_flights tf
(cost=0.00..21306.58 rows=75126 width=24)
(actual time=0.351..332.313 rows=72647 loops=1)
Filter: (amount > '50000'::numeric)
Rows Removed by Filter: 973079 239
Planning time: 1.415 ms
Execution time: 3135.869 ms
(11 строк)
План выполнения запроса изменился. Метод соединения наборов строк остался прежним — слияние. Но выборка строк в нижнем узле дерева плана теперь выполня- ется с помощью последовательного сканирования и сортировки. Обратите внимание,
что при включении опции ANALYZE может выводиться дополнительная информация о фактически использовавшихся методах, о затратах памяти и др. В частности, сказа- но, что была использована внешняя сортировка на диске, приведены затраты памяти на ее выполнение, приведено число строк, удаленных при проверке условия их отбо- ра:
Sort Method: external sort Disk: 2768kB
Rows Removed by Filter: 973079
Фактическое число строк, выбранных из таблицы ticket_flights, и фактическое число результирующих строк запроса хотя и не совпали с оценками этих чисел, но оказались весьма близкими к ним. Фактические значения равны 72647, а оценки — 75126. Это хороший результат работы планировщика.
Обратимся еще раз к запросу, который мы уже рассматривали выше, и выполним его с опцией ANALYZE. В плане этого запроса нас будет интересовать фактический пара- метр loops.
Скачать 0.9 Mb.