Учебное пособие СанктПетербург бхвпетербург
 Скачать 1.88 Mb.
|
|
ANALYZE aircrafts; ANALYZE В качестве примера ситуации, в которой оптимизация запроса представляется обос- нованной, рассмотрим следующую задачу. Предположим, что необходимо опреде- лить степень загруженности кассиров нашей авиакомпании в сентябре 2016 г. Для этого, в частности, требуется выявить распределение числа операций бронирования по числу билетов, оформленных в рамках этих операций. Другими словами, это озна- чает, что нужно подсчитать число операций бронирования, в которых был оформлен только один билет, число операций, в которых было оформлено два билета и т. д. Эту задачу можно переформулировать так: для каждой строки, отобранной из табли- цы «Бронирования» (bookings), нужно подсчитать соответствующие строки в таб- лице «Билеты» (tickets). Речь идет о строках, в которых значение поля book_ref такое же, что и в текущей строке таблицы bookings. Буквальное следование такой формулировке задачи приводит к получению запроса с коррелированным подзапро- сом в предложении SELECT. Но это еще не окончательное решение. Теперь нужно сгруппировать полученный набор строк по значениям числа оформленных билетов. 312 10.5. Оптимизация запросов Получаем такой запрос: 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) Filter: (book_ref = b.book_ref) (10 строк) В плане получены очень большие оценки общей стоимости выполнения запроса: cost=14000017.12..27994373.35 Универсальной зависимости между оценкой стоимости и реальным временем вы- полнения запроса не существует. Не всегда можно даже приблизительно предполо- жить, в какие затраты времени выльется та или иная оценка стоимости. Но, тем не менее, при рассмотрении других запросов оценок такого порядка нам еще не встре- чалось. Планировщик предполагает, что из таблицы tickets в подзапросе будет извлекаться всего по две строки, и эту операцию нужно будет проделать 1 314 раз: столько строк предположительно будет выбрано из таблицы bookings. Как видно 313 Глава 10. Повышение производительности из плана, для просмотра строк в таблице tickets используется ее последовательное сканирование. В результате оценка стоимости этого узла плана получается высокой: cost=0.00..10650.16 Если у вас не очень мощный компьютер, то время получения результата может выйти за разумные пределы, и вам придется прервать процесс с помощью клавиш < Ctrl>+ Что можно сделать для ускорения выполнения запроса? Давайте создадим индекс для таблицы 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 строк) 314 10.5. Оптимизация запросов Теперь планировщик использует индекс для поиска в таблице tickets. Причем это поиск исключительно по индексу (Index Only Scan), поскольку нас интересует толь- ко число строк — count( * ), а не их содержание. Обратите внимание на различие предполагаемого и фактического числа извлекаемых строк. Тем не менее запрос стал выполняться значительно — на порядок — быстрее. Результат имеет такой вид: num_tickets | num_bookings -------------+-------------- 5 | 13 4 | 536 3 | 7966 2 | 47573 1 | 109446 (5 строк) Кроме создания индекса есть и другой способ: замена коррелированного подзапроса соединением таблиц. 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) 315 Глава 10. Повышение производительности -> 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) -> 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). Время выполнения модифицированного запроса оказыва- ется несколько большим, чем в предыдущем случае, когда в запросе присутствовал коррелированный подзапрос. Таким образом, можно заключить, что для ускорения работы оригинального запроса можно было либо создать индекс, либо модифициро- вать сам запрос, даже не создавая индекса. Другие методы оптимизации выполнения запросов представлены в разделе «Кон- трольные вопросы и задания». Рекомендуем вам самостоятельно с ними ознакомить- ся и поэкспериментировать. 316 Контрольные вопросы и задания Контрольные вопросы и задания Перед выполнением упражнений нужно восстановить измененные значения пара- метров: SET enable_hashjoin = on; SET SET enable_nestloop = on; SET 1. Как вы думаете, почему при сканировании по индексу оценка стоимости ресур- сов, требующихся для выдачи первых результатов, не равна нулю, хотя исполь- зуется индекс, совпадающий с порядком сортировки? EXPLAIN_SELECT_total_amount_FROM_bookings_ORDER_BY_total_amount_DESC_LIMIT_5;'>EXPLAIN_SELECT_*_FROM_bookings_ORDER_BY_book_ref;'>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 строка) 2. Как вы думаете, если в запросе присутствует предложение ORDER BY, и создан индекс по тем столбцам, которые фигурируют в предложении ORDER BY, то все- гда ли будет использоваться этот индекс или нет? Почему? Проверьте ваши предположения с помощью команды EXPLAIN. 3. Самостоятельно выполните команду EXPLAIN для запроса, содержащего общее табличное выражение (CTE). Посмотрите, на каком уровне находится узел пла- на, отвечающий за это выражение, как он оформляется. Учтите, что общие таб- личные выражения всегда материализуются, т. е. вычисляются однократно и результат их вычисления сохраняется в памяти, а затем все последующие об- ращения в рамках запроса направляются уже к этому материализованному ре- зультату. 4. Прокомментируйте следующий план, попробуйте объяснить значения всех его узлов и параметров. 317 Глава 10. Повышение производительности 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) Как вы думаете, чем можно объяснить, что вторая оценка стоимости в парамет- ре cost для узла Seq Scan, равная 3,04, не совпадает с первой оценкой стоимо- сти в параметре cost для узла HashAggregate? 318 Контрольные вопросы и задания 6. Выполните команду EXPLAIN для запроса, в котором использована какая- нибудь из оконных функций. Найдите в плане выполнения запроса узел с име- нем WindowAgg. Попробуйте объяснить, почему он занимает именно этот уро- вень в плане. 7. Проанализируйте план выполнения операций вставки и удаления строк. При- чем сделайте это таким образом, чтобы данные в таблицах фактически измене- ны не были. 8.* Замена коррелированного подзапроса соединением таблиц является одним из способов повышения производительности. Предположим, что мы задались вопросом: сколько маршрутов обслуживают са- молеты каждого типа? При этом нужно учитывать, что может иметь место такая ситуация, когда самолеты какого-либо типа не обслуживают ни одного марш- рута. Поэтому необходимо использовать не только представление «Маршруты» (routes), но и таблицу «Самолеты» (aircrafts). Это первый вариант запроса, в нем используется коррелированный подзапрос. EXPLAIN ANALYZE SELECT a.aircraft_code AS a_code, a.model, ( SELECT count( r.aircraft_code ) FROM routes r WHERE r.aircraft_code = a.aircraft_code ) AS num_routes FROM aircrafts a GROUP BY 1, 2 ORDER BY 3 DESC; А в этом варианте коррелированный подзапрос раскрыт и заменен внешним соединением: EXPLAIN ANALYZE SELECT a.aircraft_code AS a_code, a.model, count( r.aircraft_code ) AS num_routes FROM aircrafts a LEFT OUTER JOIN routes r ON r.aircraft_code = a.aircraft_code GROUP BY 1, 2 ORDER BY 3 DESC; 319 Глава 10. Повышение производительности Причина использования внешнего соединения в том, что может найтись мо- дель самолета, не обслуживающая ни одного маршрута, и если не использовать внешнее соединение, она вообще не попадет в результирующую выборку. Исследуйте планы выполнения обоих запросов. Попытайтесь найти объясне- ние различиям в эффективности их выполнения. Чтобы получить усредненную картину, выполните каждый запрос несколько раз. Поскольку таблицы, участ- вующие в запросах, небольшие, то различие по абсолютным затратам времени выполнения будет незначительным. Но если бы число строк в таблицах было большим, то экономия ресурсов сервера могла оказаться заметной. Предложите аналогичную пару запросов к базе данных «Авиаперевозки». Про- ведите необходимые эксперименты с вашими запросами. 9. Одним из способов повышения производительности является изменение схе- мы данных, связанное с денормализацией, а именно: создание материализо- ванных представлений. В главе 5 было описано такое материализованное пред- ставление — «Маршруты» (routes). Команда для его создания была приведена в главе 6. Проведите эксперимент: сначала выполните выборку из готового представле- ния, а затем ту выборку, которая это представление формирует. EXPLAIN ANALYZE SELECT * FROM routes; EXPLAIN ANALYZE WITH f3 AS ( SELECT f2.flight_no, ... Поскольку второй запрос очень громоздкий, то можно поступить таким обра- зом: сначала сохраните его в текстовом файле, а затем выполните с помощью команды \i утилиты psql. Вы увидите, что затраты времени отличаются практически на два порядка. Ко- нечно, нужно помнить, что материализованные представления необходимо пе- риодически обновлять, чтобы их содержимое было актуальным. 10.* Одним из способов повышения производительности является изменение схе- мы данных, связанное с денормализацией, а именно: использование вычисля- емых столбцов. Для примера рассмотрим таблицу «Бронирования» (bookings). В ней столбец «Полная сумма бронирования» (total_amount) является вычис- ляемым. Мы не будем сейчас говорить о том, каким образом его значения син- хронизируются с данными в таблице «Перелеты» (ticket_flifgts), а лишь 320 Контрольные вопросы и задания рассмотрим два запроса, возвращающие полные суммы бронирований. Пред- положим, что указанного столбца в таблице bookings не было бы. Тогда запрос, возвращающий полные суммы бронирований, выглядел бы так: EXPLAIN ANALYZE SELECT b.book_ref, sum( tf.amount ) FROM bookings b, tickets t, ticket_flights tf WHERE b.book_ref = t.book_ref AND t.ticket_no = tf.ticket_no GROUP BY 1 ORDER BY 1; Но благодаря наличию вычисляемого столбца total_amount те же сведения можно получить с гораздо меньшими затратами ресурсов: EXPLAIN ANALYZE SELECT book_ref, total_amount FROM bookings ORDER BY 1; Попробуйте предложить еще какой-нибудь вычисляемый столбец для одной из таблиц базы данных «Авиаперевозки». Проведите эксперименты, подтвержда- ющие эффективность вашего решения. 11.* Одним из способов повышения производительности является изменение схемы данных, а именно: использование временных таблиц. Предположим, что нам предстоит сделать много выборок из представления «Рейсы» (flights_v), в та- ком случае имеет смысл подумать о создании временной таблицы: CREATE TEMP TABLE flights_tt AS SELECT * FROM flights_v; Сформируйте планы для получения простой выборки из представления и из временной таблицы и сравните полученные результаты. Как вы думаете, поче- му план, сформированный для получения даже простой выборки из представ- ления, многоуровневый? EXPLAIN ANALYZE SELECT * FROM flights_v; EXPLAIN ANALYZE SELECT * FROM flights_tt; 321 Глава 10. Повышение производительности Выполните более сложные запросы к представлению и временной таблице и сравните полученные результаты. Чтобы увидеть фактические затраты време- ни, включайте в команду EXPLAIN опцию ANALYZE. Подумайте, при выполнении каких запросов к базе данных «Авиаперевозки» было бы целесообразно создать временную таблицу. Создайте ее и проведите эксперименты, подтверждающие эффективность ее использования. 12. Одним из способов повышения производительности является изменение схемы данных, связанное с денормализацией, а именно: создание индексов. Выполните следующий простой запрос к таблице «Билеты»: EXPLAIN ANALYZE SELECT count( * ) FROM tickets WHERE passenger_name = 'IVAN IVANOV'; Создайте индекс по столбцу passenger_name: CREATE INDEX passenger_name_key ON tickets ( passenger_name ); Теперь повторите запрос и сравните полученные планы и фактические резуль- таты. Предложите какой-нибудь запрос к базе данных «Авиаперевозки», для выпол- нения которого было бы целесообразно создать индекс. Создайте индекс и по- вторите запрос. Изучите полученный план, посмотрите, используется ли индекс планировщиком. 13.* В самом конце главы мы выполняли оптимизацию запроса путем создания ин- декса и модификации текста запроса. Был сформирован такой запрос: |