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

Почему PostgreSQL игнорирует мой индекс?
Иногда случается неприятность: подходящий индекс создан, но по какой-то 
причине PostgreSQL его не использует. В этот момент разработчику базы 
данных может сильно не хватать подсказок оптимизатору, если он имеет 
опыт работы с системами, которые поддерживают такие подсказки. Однако 
в большинстве случаев для разочарования нет причин. Оптимизатор Post-
greSQL – один из лучших и в большинстве случаев поступает правильно. Так 
что, скорее всего, существует веская причина не использовать индекс, и ее 
можно найти, изучив план выполнения.
Рассмотрим пример. Здесь, а также в некоторых примерах из последующих 
глав используются крупные таблицы, которые не включены в дистрибутив 
postgres_air
из-за своего размера. Эти таблицы необходимы, чтобы проил-
люстрировать случаи, которые происходят в реальной жизни и с которыми 
вы можете столкнуться. Здесь используется таблица 
boarding_pass_large
, у ко-
торой та же структура, что и у таблицы 
boarding_pass
, но она содержит в три 
раза больше строк – свыше 75 000 000 посадочных талонов. Чтобы создать 
крупную таблицу для экспериментов, вы можете вставить каждую строку 
таблицы 
boarding_pass
по три раза.
В базе данных 
postgres_air
текущая дата – 17 августа 2020 г. Запросим вы-
борку из ста пассажиров, которые зарегистрировались за последнюю неделю:
SELECT *
FROM boarding_pass_large
WHERE update_ts::date BETWEEN '2020-08-10' AND '2020-08-17'
LIMIT 100
Как и следовало ожидать, план выполнения, представленный на рис. 5.24, 
показывает последовательное сканирование.

---

Когда индексы не используются 

95
Рис. 5.24 

Последовательное сканирование по причине преобразования столбца
Мы рассказали, как избежать этой проблемы, так что вместо приведения 
типов используем интервал:
SELECT *
FROM boarding_pass_large
WHERE update_ts BETWEEN '2020-08-10' AND '2020-08-18'
LIMIT 100
Однако проверка показывает, что план выполнения по-прежнему исполь-
зует последовательное сканирование!
Почему избавление от преобразования столбца не привело к использо-
ванию индекса? Ответ на рис. 5.25: это сочетание относительно высокой 
селективности индекса для большой таблицы и наличие оператора 
LIMIT
. 
Планировщик запросов вычисляет, что при указанном условии фильтрации 
будет выбрано более 700 000 строк, которые, напомним, могут потребовать 
вдвое больше дисковых операций ввода-вывода. Так как требуется всего 
100 строк и порядок не указан, быстрее использовать последовательное ска-
нирование таблицы. Так больше шансов, что сто записей, удовлетворяющих 
этому критерию, будут найдены быстрее.
Рис. 5.25 

Последовательное сканирование из-за высокой селективности индекса
Ситуация была бы иной, если бы эти сто записей нужно было выбрать 
в определенном порядке. Если требуется сортировка не по индексирован-
ному атрибуту, PostgreSQL потребуется выбрать 
все
записи, прежде чем он 
сможет понять, какие из них идут первыми.
Изменим команду 
SELECT
, чтобы добавить упорядочивание:
SELECT *
FROM boarding_pass_large
WHERE update_ts::date BETWEEN '2020-08-10' AND '2020-08-17'
ORDER BY 1
LIMIT 100

---