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

236

Динамический SQL
и
сПользование
 
динамического
 sQL 
в
 
Помощь
 
оПтимизатору
Поскольку вся эта глава целиком посвящена способам повышения производи-
тельности запросов с помощью динамического SQL, заголовок может вызвать 
некоторое недоумение. Но в этом разделе мы разбираемся с проблемами про-
изводительности другого рода. Здесь динамический SQL будет использоваться 
не с целью построения разных запросов для разных условий, а для того, чтобы 
подтолкнуть оптимизатор к выбору более подходящего плана выполнения.
Если внимательно присмотреться ко всем примерам из предыдущего раз-
дела, то можно заметить, что одна комбинация критериев поиска работает 
очень плохо, даже несмотря на небольшое результирующее множество: это 
случай, когда поиск ведется по адресу электронной почты и аэропорту вы-
лета. Даже в тех случаях, когда электронная почта достаточно ограничивает 
выборку, оптимизатор не может использовать индекс для 
booking_id
во вто-
ром соединении. Если мы выполним запрос из лис тинга 12.13, то план вы-
полнения покажет соединения хешированием – см. рис. 12.3.
Рис. 12.3 

План выполнения для запроса из лис тинга 12.13
с соединениями хешированием

---

Использование динамического SQL в помощь оптимизатору 

237
Время выполнения этого запроса составляет около семи секунд, а резуль-
тат содержит всего 224 строки, так что это небольшой запрос, и время вы-
полнения должно быть меньше.
Листинг 12.13 

Выбор бронирования по электронной почте и аэропорту вылета
SELECT DISTINCT
b.booking_id,
b.booking_ref,
b.booking_name,
b.email
FROM booking b
JOIN booking_leg bl USING (booking_id)
JOIN flight f USING (flight_id)
WHERE lower(email) LIKE 'lawton510%'
AND departure_airport = 'JFK'
Причина такого неоптимального плана уже упоминалась ранее – оптими-
затор PostgreSQL неправильно оценивает размер промежуточных наборов 
результатов. Фактическое количество строк, отфильтрованных по шаблону 
почты, составляет 3941, а оценка в плане – 28 216.
Методика оптимизации этого запроса в буквальном смысле помогает 
оптимизатору выполнять свою работу и устраняет необходимость оцени-
вать размер результирующего множества. Как этого достичь? Сначала най-
дем идентификаторы бронирований, соответствующие указанному адресу 
элект ронной почты, а затем передадим этот список идентификаторов ос-
новному запросу. Обратите внимание: функция, которую мы используем, 
чтобы проиллюстрировать этот случай, очень специфична и используется 
только как пример (лис тинг 12.14). Функция более общего вида, которая 
могла бы использоваться в промышленной системе, получилась бы значи-
тельно больше.
Почему это работает? Мы знаем, что поиск по электронной почте будет 
довольно ограничительным, потому что передается почти весь адрес или, 
по крайней мере, его существенная часть. Итак, на первом этапе выбирается 
относительно небольшое количество бронирований с этим адресом элект-
ронной почты, и их идентификаторы сохраняются в текстовой переменной 
v_booking_ids
. Затем создается запрос с явным списком идентификаторов.
Выполнение этой новой функции занимает от 0,5 до 0,6 секунды:
SELECT * FROM select_booking_email_departure('lawton510','JFK')
Изучив вывод команды 
EXPLAIN
для сгенерированного SQL, вы увидите 
план выполнения в том виде, в каком он представлен на рис. 12.4.
Даже с несколькими тысячами идентификаторов доступ на основе индекса 
оказывается более эффективным.

---