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

Листинг 5.6 

Соединение по первичному и внешнему ключам без индекса
SELECT f.flight_no,
f.scheduled_departure,
model,
count(passenger_id) passengers
FROM flight f
JOIN booking_leg bl ON bl.flight_id = f.flight_id
JOIN passenger p ON p.booking_id=bl.booking_id
JOIN aircraft ac ON ac.code=f.aircraft_code
WHERE f.departure_airport = 'JFK'
AND f.arrival_airport = 'ORD'
AND f.scheduled_departure BETWEEN '2020-08-14' AND '2020-08-16'
GROUP BY 1,2,3
План выполнения этого запроса показан на рис. 5.1, и он довольно вну-
шительный.
Рис. 5.1 

План с последовательным сканированием небольшой таблицы
Вот единственная часть этого плана, которая нас сейчас интересует:
Hash (cost=1.12..1.12 rows=12 width=64)
-> Seq Scan on aircraft ac (cost=0.00..1.12 rows=12
Оптимизатор PostgreSQL обращается к статистике и может определить, 
что размер таблицы 
aircraft
мал, и индексный доступ не будет эффек-
тивным.

---

74

Короткие запросы и индексы
и
ндексы
 
и
 
неравенства
В главе 3 описана структура B-деревьев, рассказывается, как они устроены 
и как используются для поиска. Далее следует демонстрация их применения 
на практике.
Предыдущий раздел относится к простым B-деревьям. Как было отме-
чено в главе 3, они могут поддерживать поиск по отношениям равенства, 
«больше», «меньше» и 
between
: все это виды поиска, требующие сравнения 
и упорядочения. Большинство поисковых условий в OLTP-системах попада-
ют в эту категорию, но есть и нетривиальные случаи, когда критерии поиска 
более сложные.
Индексы и преобразования столбцов
Преобразование столбца происходит, когда в критерии поиска участвует не 
сам столбец, а как-либо измененное его значение. Например, 
lower(last_name
) 
(преобразование фамилии в нижний регистр) и 
update_ts::date
(приведение 
временной метки с часовым поясом к дате) являются преобразованиями 
столбцов.
Как преобразования столбцов влияют на использование индекса? Попрос-
ту говоря, B-деревья по преобразованному атрибуту использовать нельзя. 
Вы помните из главы 3, как строится B-дерево и как выполняется поиск по 
нему: в каждом узле значение атрибута сравнивается со значением в узле. 
Преобразованное значение нигде не записывается, поэтому его не с чем 
сравнивать. Пусть имеется индекс по фамилии:
CREATE INDEX account_last_name ON account (last_name);
Следующий запрос не сможет воспользоваться этим индексом:
SELECT * FROM account WHERE lower(last_name) = 'daniels';
Как решить эту проблему? Поиск такого типа может потребоваться, по-
тому что пассажиры могут вводить свои фамилии в разных регистрах. Если 
вы полагаете, что достаточно охватить наиболее распространенные случаи, 
то можно изменить критерий поиска следующим образом:
SELECT * FROM account
WHERE last_name = 'daniels'
OR last_name = 'Daniels'
OR last_name = 'DANIELS'
План выполнения этого запроса показан на рис. 5.2.
Однако лучше было бы создать (дополнительный) 
функциональный
индекс:
CREATE INDEX account_last_name_lower ON account (lower(last_name));
При создании функционального индекса PostgreSQL применяет функ-
цию к значениям столбца (или столбцов), а затем помещает эти значения 

---

Индексы и неравенства 

75
в B-дерево. Подобно обычному B-дереву, где узлы содержат значения столб-
ца, в функциональном индексе узлы содержат значения функции. В нашем 
случае это функция 
lower
. После создания индекса запрос № 1 в лис тинге 5.7 
вместо последовательного сканирования сможет использовать новый ин-
декс. Соответствующий план выполнения показан на рис. 5.3.