EXPLAIN ANALYZE
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)
(actual time=3.423..3.666 rows=426 loops=1)
Sort Key: s.seat_no
Sort Method: quicksort Memory: 46kB
-> Nested Loop (cost=5.43..17.90 rows=149 width=59)
(actual time=0.236..0.993 rows=426 loops=1)
-> Seq Scan on aircrafts a (cost=0.00..1.11 rows=1 width=48)
(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 (cost=5.43..15.29 rows=149
width=15)
,→
(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 (cost=0.00..5.39
rows=149 width=0)
,→
(actual time=0.064..0.064 rows=142 loops=3)
Index Cond: (aircraft_code = a.aircraft_code)
Planning time: 0.554 ms
240
Execution time: 3.840 ms
(14 строк)
Как видно из плана, значение параметра loops для узла, выполняющего сканирова- ние таблицы seats по
индексу с построением битовой карты, равно трем. Это объяс- няется тем, что из таблицы aircrafts были фактически выбраны три строки, и
для каж-дойиз них выполняется поиск в таблице seats. Для подсчета общих затрат времени на выполнение операций сканирования по индексу за три цикла нужно значение па- раметра actual time умножить на значение параметра loops. Таким образом, для узла дерева плана Bitmap Index Scan получим:
0, 064 × 3 = 0, 192.
Подобные вычисления общих затрат времени на промежуточных уровнях дерева плана могут помочь выявить наиболее ресурсоемкие операции.
Попутно заметим, что, согласно этому плану, сортировка на верхнем уровне плана выполнялась в памяти с использованием метода quicksort:
Sort Method: quicksort Memory: 46kB
До сих пор мы рассматривали только выборки, т. е. такие запросы, которые не изме- няют хранимых данных. Однако, кроме выборок, есть такие операции, как вставка,
обновление и удаление строк. Нужно помнить, что хотя результаты выборки и не вы- водятся, тем не менее, она фактически все равно выполняется. Поэтому если требу- ется исследовать план выполнения запроса, модифицирующего данные, то для того,
чтобы изменения на самом деле произведены не были, нужно воспользоваться тран- закцией с откатом изменений.
BEGIN;BEGIN
EXPLAIN ANALYZEUPDATE aircraftsSET range = range + 100WHERE model '^Air';QUERY PLAN
-----------------------------------------------------------------
Update on aircrafts (cost=0.00..1.11 rows=1 width=58)
(actual time=0.299..0.299 rows=0 loops=1)
-> Seq Scan on aircrafts (cost=0.00..1.11 rows=1 width=58)
(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 строк)
ROLLBACK;ROLLBACK
241
В документации приводится важное предостережение о том, что нельзя экстраполи- ровать, т. е. распространять, пусть даже и с некоторыми поправками, оценки, полу-
ченные для таблиц небольшого размера, на таблицы большого размера. Это объясня- ется тем, что оценки, вычисляемые планировщиком, не являются линейными. Одна из причин заключается в том, что для таблиц разных размеров могут быть выбраны разные планы. Например, для маленькой таблицы может быть выбрано последова- тельное сканирование, а для большой — сканирование по индексу.
10.5 Оптимизация запросовМы рассмотрели базовые способы получения плана выполнения запроса и познако- мились с типичными компонентами плана. Эти знания призваны помочь в тех си- туациях, когда необходимо ускорить выполнение запроса. При принятии решения о том, что выполнение какого-либо запроса нужно оптимизировать, следует учиты- вать не только абсолютное время его выполнения, но и частоту его использования.
Запрос может выполняться, например, за несколько миллисекунд, но таких запросов могут быть сотни или тысячи.
В результате ресурсы сервера будут расходоваться очень интенсивно. Возможно, что в такой ситуации придется заняться ускорением выполнения этого запроса. А если запрос выполняется один раз в месяц, скажем, для получения итоговой картины по
продажам авиабилетов за этот период, то в этом случае бороться за ускорение на несколько миллисекунд, видимо, не имеет смысла.
Повлиять на скорость выполнения запроса можно различными способами, мы рас- смотрим некоторые из них:
– обновление статистики, на основе которой планировщик строит планы;
– изменение исходного кода запроса;
– изменение схемы данных, связанное с денормализацией: создание материализо- ванных представлений и временных таблиц, создание индексов, использование вычисляемых столбцов таблиц;
–
изменение параметров планировщика, управляющих выбором порядка соедине- ния наборов строк: использование общих табличных выражений (запросы с пред- ложением 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).
242
Необходимым условием для того, чтобы планировщик выбрал правильный план, яв- ляется наличие актуальной статистики. Если вы предполагаете, что планировщик опирается на неактуальную статистику, можно ее принудительно обновить с помо- щью команды ANALYZE. Например, обновить статистику для таблицы aircrafts мож- но, выполнив команду
ANALYZE aircrafts;
ANALYZE
В качестве примера ситуации, в которой оптимизация запроса представляется обос- нованной, рассмотрим следующую задачу. Предположим, что необходимо опреде- лить степень загруженности кассиров нашей авиакомпании в сентябре 2016 г. Для этого, в частности, требуется выявить распределение числа операций бронирования по числу билетов, оформленных в рамках этих операций. Другими словами, это озна- чает, что нужно подсчитать число операций бронирования, в которых был оформлен только один билет, число операций, в которых было оформлено два билета и т. д.
Эту задачу можно переформулировать так: для каждой строки, отобранной из таб- лицы «Бронирования» (bookings), нужно подсчитать соответствующие строки в таб- лице «Билеты» (tickets). Речь идет о строках, в которых значение поля book_ref такое же, что и в текущей строке таблицы bookings. Буквальное следование такой форму- лировке задачи приводит к получению запроса с коррелированным подзапросом в предложении SELECT. Но это еще не окончательное решение. Теперь нужно сгруппи- ровать полученный набор строк по значениям числа оформленных билетов.
Получаем такой запрос:
EXPLAIN
SELECT num_tickets, count( * ) AS num_bookings
FROM (
SELECT b.book_ref,
( SELECT count( * ) FROM tickets t
WHERE t.book_ref = b.book_ref
)
FROM bookings b
WHERE date_trunc( 'mon', book_date ) = '2016-09-01'
) AS count_tickets( book_ref, num_tickets )
GROUP by num_tickets
ORDER BY num_tickets DESC;
QUERY PLAN
------------------------------------------------------------------
GroupAggregate (cost=14000017.12..27994373.35 rows=1314 width=16)
Group Key: ((SubPlan 1))
-> Sort (cost=14000017.12..14000020.40 rows=1314 width=8)
Sort Key: ((SubPlan 1)) DESC
-> Seq Scan on bookings b
(cost=0.00..13999949.05 rows=1314 width=8)
Filter: (date_trunc('mon'::text, book_date) =
'2016-09-01 00:00:00+08'::timestamp with time zone)
SubPlan 1
-> Aggregate (cost=10650.17..10650.18 rows=1 width=8)
-> Seq Scan on tickets t
(cost=0.00..10650.16 rows=2 width=0)
243
Filter: (book_ref = b.book_ref)
(10 строк)
В этом плане получены очень большие оценки общей стоимости выполнения запро- са: cost=14000017.12..27994373.35. Универсальной зависимости между оценкой сто- имости и реальным временем выполнения запроса не существует. Не всегда можно даже приблизительно предположить, в какие затраты времени выльется та или иная оценка стоимости. Но, тем не менее, в тексте главы при рассмотрении других запро- сов оценок такого порядка еще не встречалось.
Планировщик предполагает, что из таблицы tickets в подзапросе будет извлекаться всего по две строки, и эту операцию нужно будет проделать 1314 раз: столько строк предположительно будет выбрано из таблицы bookings. Как видно из плана, для просмотра строк в таблице tickets исполь- зуется ее последовательное сканирование. В результате оценка стоимости этого узла плана получается высокой — cost=0.00..10650.16.
Если у вас не очень мощный компьютер, то время получения результата может выйти за разумные пределы, и вам придется прервать процесс с помощью клавишей Ctrl-
C.
Что можно сделать для ускорения выполнения запроса? Давайте создадим индекс для таблицы tickets по столбцу book_ref, по которому происходит поиск в ней.
CREATE INDEX tickets_book_ref_key ON tickets ( book_ref );CREATE INDEX
Повторим запрос, добавив параметр ANALYZE в команду EXPLAIN. Новый план, в ко- тором отражены и фактические результаты, будет таким:
QUERY PLAN
------------------------------------------------------------------
GroupAggregate (cost=22072.70..38484.52 rows=1314 width=16)
(actual time=3656.554..3787.562 rows=5 loops=1)
Group Key: ((SubPlan 1))
-> Sort (cost=22072.70..22075.99 rows=1314 width=8)
(actual time=3656.533..3726.969 rows=165534 loops=1)
Sort Key: ((SubPlan 1)) DESC
Sort Method: external merge Disk: 2912kB
-> Seq Scan on bookings b
(cost=0.00..22004.64 rows=1314 width=8)
(actual time=0.219..3332.162 rows=165534 loops=1)
Filter: (date_trunc('mon'::text, book_date) =
'2016-09-01 00:00:00+08'::timestamp with time zone)
Rows Removed by Filter: 97254
SubPlan 1
-> Aggregate (cost=12.46..12.47 rows=1 width=8)
(actual time=0.016..0.016 rows=1 loops=165534)
-> Index Only Scan using tickets_book_ref_key on tickets t
(cost=0.42..12.46 rows=2 width=0)
(actual time=0.013..0.014 rows=1 loops=165534)
Index Cond: (book_ref = b.book_ref)
Heap Fetches: 230699
Planning time: 0.290 ms
Execution time: 3788.690 ms
(15 строк)
244
Теперь планировщик использует индекс для поиска в таблице tickets. Причем, это поиск исключительно по индексу (Index Only Scan), поскольку нас интересует только число строк — count( * ), а не их содержание. Обратите внимание на различие пред- полагаемого и фактического числа извлекаемых строк. Тем не менее, запрос стал вы- полняться значительно — на порядок — быстрее.
Результат имеет такой вид:
num_tickets | num_bookings
-------------+--------------
5 |
13 4 |
536 3 |
7966 2 |
47573 1 |
109446
(5 строк)
Кроме создания индекса есть и другой способ: замена коррелированного подзапроса соединением таблиц.
EXPLAIN_SELECT_total_amount_FROM_bookings_ORDER_BY_total_amount_DESC_LIMIT_5;'>EXPLAIN_ANALYZE_SELECT_num_tickets,_count(_*_)_AS_num_bookings_FROM_(_SELECT_b.book_ref,_count(_*_)_FROM_bookings_b,_tickets_t'>EXPLAIN ANALYZE
SELECT num_tickets, count( * ) AS num_bookings
FROM (
SELECT b.book_ref, count( * )
FROM bookings b, tickets t
WHERE date_trunc( 'mon', b.book_date ) = '2016-09-01'
AND t.book_ref = b.book_ref
GROUP BY b.book_ref
) AS count_tickets( book_ref, num_tickets )
GROUP by num_tickets
ORDER BY num_tickets DESC;
QUERY PLAN
------------------------------------------------------------------
GroupAggregate (cost=16966.67..16978.53 rows=200 width=16)
(actual time=4092.258..4219.477 rows=5 loops=1)
Group Key: count_tickets.num_tickets
-> Sort (cost=16966.67..16969.96 rows=1314 width=8)
(actual time=4092.236..4161.294 rows=165534 loops=1)
Sort Key: count_tickets.num_tickets DESC
Sort Method: external merge Disk: 2912kB
-> Subquery Scan on count_tickets
(cost=16858.57..16898.61 rows=1314 width=8)
(actual time=3176.113..3862.133 rows=165534 loops=1)
-> GroupAggregate
(cost=16858.57..16885.47 rows=1314 width=15)
(actual time=3176.111..3765.157 rows=165534 loops=1)
Group Key: b.book_ref
-> Sort (cost=16858.57..16863.16 rows=1834 width=7)
(actual time=3176.098..3552.334 rows=230699 loops=1)
Sort Key: b.book_ref
Sort Method: external merge Disk: 3824kB
-> Hash Join (cost=5632.24..16759.16 rows=1834 width=7)
(actual time=498.701..1091.509 rows=230699 loops=1)
Hash Cond: (t.book_ref = b.book_ref)
245
-> Seq Scan on tickets t
(cost=0.00..9733.33 rows=366733 width=7)
(actual time=0.047..170.792 rows=366733 loops=1)
-> Hash (cost=5615.82..5615.82 rows=1314 width=7)
(actual time=498.624..498.624 rows=165534
loops=1)
,→
Buckets: 262144 (originally 2048)
Batches: 2 (originally 1)
Memory Usage: 3457kB
-> Seq Scan on bookings b
(cost=0.00..5615.82 rows=1314 width=7)
(actual time=0.019..267.728 rows=165534
loops=1)
,→
Filter: (date_trunc('mon'::text, book_date) =
'2016-09-01 00:00:00+08'::timestamp with time zone)
,→
Rows Removed by Filter: 97254
Planning time: 2.183 ms
Execution time: 4221.133 ms
(21 строка)
В данном плане используется соединение хешированием (Hash Join). При этом ин- декс по таблице tickets игнорируется: она просматривается последовательно (Seq
Scan on tickets t). Время выполнения модифицированного запроса оказывается несколько большим, чем в предыдущем случае, когда в запросе присутствовал корре- лированный подзапрос. Таким образом, можно заключить, что для ускорения работы оригинального запроса можно было либо создать индекс, либо модифицировать сам запрос, даже не создавая индекса.
Другие методы оптимизации выполнения запросов представлены в разделе «Кон- трольные вопросы и задания». Рекомендуем вам самостоятельно с ними ознакомить- ся и поэкспериментировать.
Перед выполнением упражнений нужно восстановить измененные значения пара- метров:
SET enable_hashjoin = on;SET
SET enable_nestloop = on;SET
Контрольные вопросы и задания1. Как вы думаете, почему при сканировании по индексу оценка стоимости ресур- сов,
требующихся для выдачи первых результатов, не равна нулю, хотя исполь- зуется индекс, совпадающий с порядком сортировки?
EXPLAIN SELECT * FROM bookings ORDER BY book_ref;246
QUERY PLAN
----------------------------------------------------------------
Index Scan using bookings_pkey on bookings (cost=0.42..8511.24
rows=262788 width=21)
,→
(1 строка)
2. Как вы думаете, если в запросе присутствует предложение ORDER BY, и создан индекс по тем столбцам, которые фигурируют в предложении ORDER BY, то всегда ли будет использоваться этот индекс или нет? Почему? Проверьте ваши предположения с помощью команды EXPLAIN.
3. Самостоятельно выполните команду EXPLAIN для запроса, содержащего общее табличное выражение (CTE). Посмотрите, на каком уровне находится узел пла- на, отвечающий за это выражение, как он оформляется. Учтите, что общие таб- личные выражения всегда материализуются, т. е. вычисляются однократно и ре- зультат их вычисления сохраняется в памяти, а затем все последующие обраще- ния в рамках запроса направляются уже к этому материализованному резуль- тату.
4. Прокомментируйте следующий план, попробуйте объяснить значения всех его узлов и параметров.
EXPLAIN
SELECT total_amount FROM bookings
ORDER BY total_amount DESC LIMIT 5;
QUERY PLAN
----------------------------------------------------------------
Limit (cost=8666.69..8666.71 rows=5 width=6)
-> Sort (cost=8666.69..9323.66 rows=262788 width=6)
Sort Key: total_amount DESC
-> Seq Scan on bookings (cost=0.00..4301.88 rows=262788
width=6)
,→
(4 строки)
5. В подавляющем большинстве городов только один аэропорт, но есть и такие го- рода, в которых более одного аэропорта. Давайте их выявим.
EXPLAIN
SELECT city, count( * ) FROM airports
GROUP BY city HAVING count( * ) > 1;
QUERY PLAN
----------------------------------------------------------------
HashAggregate (cost=3.82..4.83 rows=101 width=25)
Group Key: city
Filter: (count(*) > 1)
-> Seq Scan on airports (cost=0.00..3.04 rows=104 width=17)
(4 строки)
Для подсчета числа аэропортов в городах используется последовательное ска- нирование и формирование хеш-таблицы (HashAggregate), ключом которой яв- ляется столбец city. Затем из нее отбираются те записи, значения которых соот- ветствуют условию
Filter: (count(*) > 1)
247
Как вы думаете, чем
можно объяснить, что вторая оценка стоимости в парамет- ре cost для узла Seq Scan, равная 3.04, не совпадает с первой оценкой стоимости в параметре cost для узла HashAggregate?
6. Выполните команду EXPLAIN для запроса, в котором использована одна из оконных функций. Найдите в плане выполнения запроса узел с именем
WindowAgg. Попробуйте объяснить, почему он занимает именно этот уровень в плане.
7. Выполните анализ плана выполнения операций вставки и удаления строк. При- чем, сделайте это таким образом, чтобы данные в таблицах фактически изме- нены не были.
8.* Замена коррелированного подзапроса соединением таблиц является одним из способов повышения производительности.
Предположим, что мы задались вопросом: сколько маршрутов обслуживают са- молеты каждого типа? При этом нужно учитывать, что может иметь место такая ситуация, когда самолеты какого-либо типа не обслуживают ни одного марш- рута. Поэтому необходимо использовать не только представление «Маршруты»
(routes), но и таблицу «Самолеты» (aircrafts).
Это первый вариант запроса, в нем используется коррелированный подзапрос.
Скачать 0.9 Mb.