Разберите, почему предикат по третьему столбцу не сужает диапазон индексного чтения в этом запросе. Укажите, какие строки будут найдены через индекс, а какие условия проверятся после чтения.
CREATE INDEX ix_orders
ON orders (customer_id, created_at, status);
SELECT order_id, amount
FROM orders
WHERE customer_id = 42
AND created_at >= DATE '2025-01-01'
AND status = 'paid';
В типичном B-дереве индекс сможет позиционироваться по customer_id = 42 и затем читать диапазон по created_at >= DATE '2025-01-01'. Условие status = 'paid' обычно не сужает границы этого индексного диапазона: его значение меняется внутри уже выбранного диапазона, поэтому СУБД проверяет его для прочитанных индексных записей как дополнительный предикат.
Это не означает, что индекс бесполезен для status: он всё равно уменьшает область чтения за счёт первых двух столбцов. Но для эффективного поиска по статусу в данном запросе порядок ключей может быть неоптимальным.
B-деревья появились как структура, позволяющая хранить упорядоченные ключи на диске и быстро находить отдельные значения или диапазоны без полного чтения таблицы. Составной ключ упорядочивается лексикографически: сначала сравнивается первый столбец, затем второй при равенстве первого, затем третий.
Из-за такого порядка оптимизатор может задать непрерывный диапазон поиска только там, где значения ключа образуют пригодную границу. Равенства обычно фиксируют префикс ключа, а первое условие диапазона ограничивает дальнейшее позиционирование.
Пусть в индексе находится много заказов клиента 42, начиная с указанной даты, но только небольшая часть из них имеет статус paid. Индекс всё равно приведёт чтение ко всему диапазону клиента и даты, после чего значительная доля строк будет отброшена по status.
Ошибочно считать, что наличие всех трёх столбцов в индексе автоматически превращает три условия WHERE в одинаково эффективный поиск. Последствия могут включать лишние чтения страниц индекса, обращения к таблице за дополнительными столбцами и рост времени выполнения при расширении диапазона дат.
Ключ индекса имеет порядок (customer_id, created_at, status). Сначала СУБД находит начало группы customer_id = 42, затем ограничивает чтение нижней границей created_at. Получается диапазон, внутри которого записи дополнительно упорядочены по status, но равенство по нему не образует единственную непрерывную область поиска.
Упрощённо план можно представить так:
Причина в том, что при диапазоне по created_at записи с разными датами перемешивают участки разных значений status. Чтобы найти все paid, пришлось бы проверять множество фрагментов диапазона, поэтому оптимизатор часто читает общий диапазон и применяет фильтр позже. Конкретное отображение в плане зависит от СУБД и её оптимизатора: условие может называться residual predicate, filter или аналогичным термином.
Если запросы обычно имеют равенство по клиенту и статусу, а дату используют как диапазон, потенциально более подходящим может быть индекс (customer_id, status, created_at). Тогда равенства фиксируют префикс (customer_id, status), а диапазон по created_at ограничивает чтение внутри него.
Однако перестановка ключей не является универсальным рецептом. Нужно учитывать реальные запросы, распределение данных, необходимость сортировки, размер индекса и стоимость его поддержки при вставках и изменениях. Если запрос возвращает много строк или требует столбцов, отсутствующих в индексе, последующие чтения таблицы могут стать главным ограничением.
В таблице заказов одного клиента было несколько миллионов записей, из которых лишь около одного процента имели статус paid. Существовал индекс (customer_id, created_at, status), но отчёт за год читал почти все заказы клиента и фильтровал статус после индексного чтения.
Рассматривались варианты:
(customer_id, status, created_at). Он хорошо подходил для отчёта, но занимал дополнительное место и требовал обслуживания при изменении статуса.После проверки планов и нескольких типичных распределений данных выбрали (customer_id, status, created_at): запросы почти всегда задавали клиента и статус, а дату использовали диапазоном. Решение подтвердили нагрузочными тестами, потому что один замер на маленьком наборе данных не показывает стоимость чтения широкого диапазона.
1. Всегда ли третье условие полностью игнорируется оптимизатором?
Нет. Оно может применяться как фильтр к строкам, найденным индексом, и тем самым уменьшать количество строк, передаваемых дальше по плану. Вопрос в другом: оно не обязательно уменьшает сам диапазон чтения, поэтому затраты на просмотр уже найденных записей сохраняются.
2. Достаточно ли переставить столбцы и получить лучший план?
Нет. Новый индекс должен соответствовать реальным шаблонам запросов. Если запросы иногда фильтруют только по created_at, индекс с ведущим customer_id для них может быть мало полезен. Кроме того, более узкий индекс может быть предпочтительнее широкого, если он покрывает нужный запрос или уменьшает объём чтения.
3. Почему высокая селективность status не гарантирует эффективное использование этого столбца?
Селективность важна вместе с положением столбца в ключе. Даже очень редкий статус не помогает сузить позиционирование, если перед ним уже задан диапазон по предыдущему ключевому столбцу. Чтобы использовать селективность при навигации по B-дереву, статус обычно должен находиться до первого диапазонного столбца в подходящем префиксе индекса.