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 строки)
10.3. Методы формирования соединений наборов строк
Теперь обратимся к методам, которые используются для формирования соединений наборов строк. Начнем с метода вложенного цикла (nested loop). Для получения списка мест в салонах самолетов Airbus с указанием класса обслуживания сформиру- ем запрос, в котором соединяются две таблицы: «Места» и «Самолеты».
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 строк)
302
10.3. Методы формирования соединений наборов строк
Узел 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). Получим список маршрутов с указанием модели самолета, выполняющего рей- сы по этим маршрутам. Воспользуемся таблицами «Маршруты» и «Самолеты».
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;
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 среди ключей хеш-таблицы:
303
Глава 10. Повышение производительностиHash Cond: (r.aircraft_code = a.aircraft_code)
Если такой поиск успешен, значит, формируется комбинированная результирующая строка выборки.
На верхнем уровне плана сформированные строки сортируются. Обратите внимание,
что хеш-таблица создана на основе той таблицы, число строк в которой меньше, т. е.
aircrafts. Таким образом,
поиск в ней будет выполняться быстрее, чем если бы хеш-таблица была создана на основе таблицы routes.
Последний из методов соединения наборов строк —
соединение слиянием (merge join). Для иллюстрации воспользуемся простым запросом, построенным на основе таблиц «Билеты» и «Перелеты». Он выбирает для каждого билета все перелеты, вклю- ченные в него. Конечно, это очень упрощенный запрос, в реальной ситуации он не представлял бы большой практической пользы, но в целях упрощения плана и повы- шения наглядности воспользуемся им.
EXPLAIN SELECT 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 равна 1,51, что значительно меньше вторых оценок, вычисленных планиров- щиком для двух нижних узлов, а именно: 17230,42 и 67058,74.
304
10.4. Управление планировщикомНапомним, что первая оценка говорит, сколько ресурсов будет затрачено (сколько времени, в условных единицах, пройдет) до начала вывода первых результатов вы- полнения операции на данном уровне дерева плана. Вторая оценка показывает об- щее количество ресурсов, требующихся для полного завершения операции на данном уровне дерева плана. Таким образом, можно заключить, что вывод результирующих строк начнется еще задолго до завершения сканирования исходных таблиц.
10.4. Управление планировщикомДля управления планировщиком предусмотрен целый ряд параметров. Их можно из- менить на время текущего сеанса работы с помощью команды SET. Конечно, изме- нять параметры в производственной
базе данных следует только в том случае, когда вы
обоснованно считаете, что планировщик ошибается. Однако для того чтобы на- учиться видеть ошибки планировщика, нужен большой опыт. Поэтому следует рас- сматривать приведенные далее команды управления планировщиком лишь с пози- ции изучения потенциальных возможностей управления им, а не как рекомендацию к бездумному изменению этих параметров в реальной работе.
Например, чтобы запретить планировщику использовать метод соединения на осно- ве хеширования, нужно сделать так:
SET enable_hashjoin = off;Чтобы запретить планировщику использовать метод соединения слиянием, нужно сделать так:
SET enable_mergejoin = off;А для того чтобы запретить планировщику использовать соединение методом вло- женного цикла, нужно сделать так:
SET enable_nestloop = off;По умолчанию все эти параметры имеют значение on (включено).
Необходимо уточнить, что в результате выполнения вышеприведенных команд не накладывается полного запрета на использование конкретного метода соединения наборов строк. Методу просто назначается очень высокая стоимость, но планиров- щик все равно сохраняет возможность маневра, и даже такой «запрещенный» метод может быть использован. Более подробно этот вопрос рассматривается в одном из примеров в разделе «Контрольные вопросы и задания».
305
Глава 10. Повышение производительности
Давайте запретим планировщику использовать метод соединения слиянием:
SET enable_mergejoin = off;
SET
Теперь повторим предыдущий запрос:
EXPLAIN SELECT t.ticket_no,
t.passenger_name,
tf.flight_id,
tf.amount
FROM tickets t
JOIN ticket_flights tf ON t.ticket_no = tf.ticket_no
ORDER 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
306
10.4. Управление планировщикомПовторим предыдущий запрос с опцией 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)
(actual time=0.006..7743.705 rows=1045726 loops=1)
Planning time: 122.347 ms
Execution time: 10948.791 ms
(6 строк)
Фактические затраты времени измеряются в миллисекундах, а оценки стоимости —
в условных единицах, поэтому плановые оценки и фактические затраты совпасть не могут. Важнее обратить внимание на то, насколько точно
планировщик оценил число обрабатываемых строк, а также на фактическое число повторений того или иного уз- ла дерева плана — это параметр loops. В данном запросе каждый узел плана был выполнен ровно один раз, поскольку выбор строк из обоих соединяемых наборов производился по индексу, поэтому достаточно одного прохода по каждому набору.
Число выбираемых строк было оценено точно, поскольку таблицы связаны по внеш- нему ключу, и в выборку включаются все их строки (нет предложения WHERE).
Кроме времени выполнения запроса выводится также и время формирования плана.
Необходимо учитывать, что фактические затраты времени на разных компьютерах будут различаться. Будет другим и фактическое время при повторном выполнении запроса на одном и том же компьютере, поскольку и в СУБД, и в операционной си- стеме используются буферизация и кэширование, а также с течением времени может
307
Глава 10. Повышение производительности
изменяться фактическая нагрузка на сервер. Поэтому время выполнения повторно- го запроса может оказаться значительно меньше, чем время выполнения первого,
а время выполнения запроса в третий раз — немного больше, чем во второй.
Если модифицировать запрос, добавив предложение WHERE, то точного совпадения оценки числа выбираемых строк и фактического их числа уже не будет.
EXPLAIN ANALYZE
SELECT t.ticket_no,
t.passenger_name,
tf.flight_id,
tf.amount
FROM tickets t
JOIN ticket_flights tf ON t.ticket_no = tf.ticket_no
WHERE amount > 50000
ORDER 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
Planning time: 1.415 ms
Execution time: 3135.869 ms
(11 строк)
План выполнения запроса изменился. Метод соединения наборов строк остался прежним — слияние. Но выборка строк в нижнем узле дерева плана теперь выполня- ется с помощью последовательного сканирования и сортировки. Обратите внимание,
что при включении опции ANALYZE может выводиться дополнительная информация
308
10.4. Управление планировщикомо
фактически использовавшихся методах, о затратах памяти и др. В частности, сказа- но, что была использована внешняя сортировка на диске (Sort Method), приведены затраты памяти на ее выполнение, приведено число строк, удаленных при проверке условия их отбора (Rows Removed by Filter).
Фактическое число строк, выбранных из таблицы ticket_flights, и фактическое число результирующих строк запроса хотя и не совпали с оценками этих чисел, но оказались весьма близкими к ним. Фактические значения равны 72 647, а оценки —
75 126. Это хороший результат работы планировщика.
Обратимся еще раз к запросу, который мы уже рассматривали выше, и выполним его с опциями ANALYZE и COSTS OFF (для сокращения вывода). В плане этого запроса нас будет интересовать фактический параметр loops.
EXPLAIN (ANALYZE, COSTS OFF)SELECT a.aircraft_code,a.model,s.seat_no,s.fare_conditionsFROM seats sJOIN aircrafts a ON s.aircraft_code = a.aircraft_codeWHERE a.model '^Air'ORDER BY s.seat_no;QUERY PLAN
---------------------------------------------------------------------
Sort (actual time=3.423..3.666 rows=426 loops=1)
Sort Key: s.seat_no
Sort Method: quicksort Memory: 46kB
-> Nested Loop (actual time=0.236..0.993 rows=426 loops=1)
-> Seq Scan on aircrafts a (actual time=0.100..0.112 rows=3
loops=1)
Filter: (model '^Air'::text)
Rows Removed by Filter: 6
-> Bitmap Heap Scan on seats s (actual time=0.080..0.154
rows=142 loops=3)
Recheck Cond: (aircraft_code = a.aircraft_code)
Heap Blocks: exact=6
-> Bitmap Index Scan on seats_pkey
(actual time=0.064..0.064 rows=142 loops=3)
Index Cond: (aircraft_code = a.aircraft_code)
Planning time: 0.554 ms
Execution time: 3.840 ms
(14 строк)
309
Глава 10. Повышение производительности
Как видно из плана, значение параметра loops для узла, выполняющего сканиро- вание таблицы seats по индексу с построением битовой карты, равно трем. Это объясняется тем, что из таблицы aircrafts были фактически выбраны три стро- ки, и для каждой из них выполняется поиск в таблице seats. Для подсчета общих затрат времени на выполнение операций сканирования по индексу за три цикла нуж- но значение параметра actual time умножить на значение параметра loops. Таким образом, для узла дерева плана Bitmap Index Scan получим 0,064 × 3 = 0,192.
Подобные вычисления общих затрат времени на промежуточных уровнях дерева плана могут помочь выявить наиболее ресурсоемкие операции. Попутно заметим,
что, согласно этому плану, сортировка на верхнем уровне плана выполнялась в па- мяти с использованием метода quicksort:
Sort Method: quicksort Memory: 46kB
До сих пор мы рассматривали только выборки, т. е. такие запросы, которые не изме- няют хранимых данных. Однако, кроме выборок, есть такие операции, как вставка,
обновление и удаление строк. Нужно помнить, что хотя результаты выборки и не вы- водятся, тем не менее, она фактически все равно выполняется. Поэтому если требу- ется исследовать план выполнения запроса, модифицирующего данные, то для того,
чтобы изменения на самом деле произведены не были, нужно воспользоваться тран- закцией с откатом изменений.
BEGIN;
BEGIN
EXPLAIN (ANALYZE, COSTS OFF)
UPDATE aircrafts
SET range = range + 100
WHERE model '^Air';
QUERY PLAN
-----------------------------------------------------------------------
Update on aircrafts (actual time=0.299..0.299 rows=0 loops=1)
-> Seq Scan on aircrafts (actual time=0.111..0.121 rows=3 loops=1)
Filter: (model '^Air'::text)
Rows Removed by Filter: 6
Planning time: 0.235 ms
Execution time: 0.414 ms
(6 строк)
310
10.5. Оптимизация запросовROLLBACK;ROLLBACK
В документации
приводится важное предостережение о том, что нельзя экстраполи- ровать, т. е. распространять, пусть даже и с некоторыми поправками, оценки, полу- ченные для таблиц небольшого размера, на таблицы большого размера. Это объясня- ется тем, что оценки, вычисляемые планировщиком, не являются линейными. Одна из причин заключается в том, что для таблиц разных размеров могут быть выбраны разные планы. Например, для маленькой таблицы может быть выбрано последова- тельное сканирование, а для большой — сканирование по индексу.
10.5. Оптимизация запросовМы рассмотрели базовые способы получения плана выполнения запроса и познако- мились с типичными компонентами плана. Эти знания призваны помочь в тех си- туациях, когда необходимо ускорить выполнение запроса. При принятии решения о том, что выполнение какого-либо запроса нужно оптимизировать, следует учиты- вать не только абсолютное время его выполнения, но и частоту его использования.
Запрос может выполняться, например, за несколько миллисекунд, но таких запросов могут быть сотни или тысячи.
В результате ресурсы сервера будут расходоваться очень интенсивно. Возможно, что в такой ситуации придется заняться ускорением выполнения этого запроса. А
если запрос выполняется один раз в месяц, скажем, для получения итоговой картины по продажам авиабилетов за этот период, то в этом случае бороться за ускорение на несколько миллисекунд, видимо, не имеет смысла.
Повлиять на скорость выполнения запроса можно различными способами, мы рас- смотрим некоторые из них:
– обновление статистики, на основе которой планировщик строит планы;
– изменение исходного кода запроса;
– изменение схемы данных, связанное с денормализацией: создание материализо- ванных представлений и временных таблиц, создание индексов, использование вычисляемых столбцов таблиц;
311
Глава 10. Повышение производительности–
изменение параметров планировщика, управляющих выбором порядка соедине- ния наборов строк: использование общих табличных выражений (запросы с пред- ложением WITH), использование фиксированного порядка соединения (параметр join_collapse_limit = 1), запрет раскрытия подзапросов и преобразования их в соединения таблиц (параметр from_collapse_limit = 1);
– изменение параметров планировщика, управляющих выбором метода досту- па к данным (enable_seqscan, enable_indexscan, enable_indexonlyscan,
enable_bitmapscan) и способа соединения наборов строк (enable_nestloop,
enable_hashjoin, enable_mergejoin);
– изменение параметров планировщика, управляющих использованием ряда опе- раций: агрегирование на основе хеширования (enable_hashagg), материализа- ция временных наборов строк (enable_material), выполнение явной сортиров- ки при наличии других возможностей (enable_sort).
Необходимым условием для того, чтобы планировщик выбрал правильный план, яв- ляется наличие актуальной статистики. Если вы предполагаете, что планировщик опирается на неактуальную статистику, можно ее принудительно обновить с помо- щью команды ANALYZE. Например, обновить статистику для таблицы aircrafts можно, выполнив команду
Скачать 1.88 Mb.