Руководство по созданию эффективных запросов

Листинг 6.25 

Сложное условие с OR переписано, используя UNION ALL
SELECT 'Late group 1' AS grouping,
flight_id,
count(*) AS num_passengers
FROM boarding_pass bp
JOIN booking_leg bl USING (booking_leg_id)
JOIN booking b USING (booking_id)
JOIN flight f USING (flight_id)
WHERE departure_airport = 'FRA'
AND actual_departure > scheduled_departure + interval '1 hour'
AND bp.update_ts > scheduled_departure + interval '30 minutes'
AND actual_departure > '2020-07-01'
GROUP BY 1,2
UNION ALL
SELECT 'Late group 2' AS grouping,
flight_id,
count(*) AS num_passengers
FROM boarding_pass bp
JOIN booking_leg bl USING(booking_leg_id)
JOIN booking b USING (booking_id)
JOIN flight f USING (flight_id)
WHERE departure_airport = 'FRA'
AND actual_departure > scheduled_departure + interval '30 minute'
AND actual_departure <= scheduled_departure + interval '1 hour'
AND actual_departure > '2020-07-01'
GROUP BY 1,2

---

128

Длинные запросы и полное сканирование
Стоит отметить, что с большими запросами всегда нужно учитывать, какой 
оперативной памятью вы располагаете. Скорость выполнения как соедине-
ний хешированием, так и операций со множествами значительно умень-
шается, если участвующие наборы данных не помещаются в оперативную 
память.
и
зБегаем
 
многократного
 
сканирования
Еще одна причина медленного выполнения запросов – это многократные 
сканирования таблиц. Эта распространенная проблема является прямым 
результатом неудачного проектирования схемы данных. Схему теоретически 
можно исправить. Но поскольку часто мы оказываемся в ситуациях, когда 
не можем этого сделать, мы предложим способы написания эффективных 
запросов даже при несовершенной схеме.
Ситуация, которую мы моделируем в нашей схеме 
postgres_air
, не ред-
кость в реальном мире. Система уже запущена и работает, и внезапно нам 
потребовалось сохранять некую дополнительную информацию для объектов, 
которые уже присутствуют в базе данных.
В течение последних 30 лет самым простым решением в таких случаях 
было использование таблицы 
сущность–атрибут–значение
(entity-attrib-
ute-value, EAV), в которой могут храниться произвольные атрибуты – те, 
которые нужны сейчас, и те, которые понадобятся когда-нибудь. В схеме 
postgres_air
этот шаблон реализован в таблице 
custom_field
. Здесь хранят-
ся номер паспорта каждого пассажира, срок действия паспорта и название 
страны, выдавшей его. Атрибуты соответственно именуются 
passport_num
, 
passport_exp_date
и 
passport_country
.
Эта таблица не входит в дистрибутив postgres_air. Чтобы запустить при-
мер локально, выполните следующий скрипт из репозитория postgres_air 
на GitHub: 
https://github.com/hettie-d/postgres_air/blob/main/tables/custom_field.sql
.
Теперь представьте, что требуется отчет, показывающий имена пассажи-
ров и их паспортные данные. Листинг 6.26 представляет собой типичное 
предлагаемое решение: таблица 
custom_field
сканируется трижды! Во избе-
жание сброса промежуточных данных во временные файлы количество пас-
сажиров ограничено первыми пятью миллионами, что позволяет показать 
истинное соотношение времен выполнения. План выполнения на рис. 6.21 
подтверждает три сканирования таблицы, а время выполнения этого запроса 
составляет 5 минут.
Трехкратное сканирование этой таблицы напоминает такой способ разло-
жить яблоки, апельсины и лимоны из черного ящика по ведрам, при котором 
сначала в одно ведро откладываются все яблоки (а апельсины с лимонами 
остаются в ящике), затем в другое ведро откладываются апельсины, и нако-
нец в третье ведро из ящика перекладываются лимоны. Более эффективный 

---

Избегаем многократного сканирования 

129
способ выполнить эту работу – поставить все три ведра перед собой и, вы-
нимая фрукт из черного ящика, сразу класть его в правильное ведро.