Краткий ответ

Если условие вычисляет функцию над индексируемым столбцом, оптимизатор часто не может преобразовать его в диапазон исходных значений и выполнить эффективный поиск по индексу. В результате возможны сканирование индекса или таблицы и проверка функции для множества строк. Исключение составляют случаи, когда СУБД поддерживает и использует индекс по выражению либо заранее вычисляемый индексируемый столбец.

Исторический контекст

Обычные B-деревья создавались для быстрого поиска по упорядоченным значениям исходного столбца, а не по результатам произвольных вычислений. Индекс хранит ключи в порядке исходных значений, поэтому оптимизатор может быстро найти начало и конец диапазона без чтения всей таблицы.

Когда фильтрация стала включать преобразования, возникла проблема: порядок результатов функции не обязан совпадать с порядком исходных ключей. Поэтому СУБД нужны специальные механизмы — индексы по выражениям, функциональные индексы или индексируемые вычисляемые столбцы.

Постановка проблемы

Допустим, в таблице есть индекс по моменту создания записи, но условие отбирает строки по дню, полученному из этого момента. Для каждой даты существует много возможных значений времени, и простая проверка функции над столбцом не всегда представляется оптимизатору как обычный диапазон.

Неверное ожидание состоит в том, что наличие индекса автоматически ускорит любое условие с этим столбцом. На практике запрос может прочитать большую часть индекса, выполнить вычисление для каждой найденной строки и оказаться медленнее полного сканирования таблицы.

Подробное решение

Сравним два логически эквивалентных подхода:

-- Преобразование применяется к столбцу
SELECT * FROM orders
WHERE DATE(created_at) = DATE '2025-01-15';

-- Диапазон сохраняет столбец неизменённым
SELECT * FROM orders
WHERE created_at >= TIMESTAMP '2025-01-15 00:00:00'
  AND created_at <  TIMESTAMP '2025-01-16 00:00:00';

Во втором варианте условие задаёт непрерывный диапазон исходных ключей. Оптимизатор обычно может применить операцию индексного поиска, перейти к началу диапазона и читать записи до его конца, не вычисляя функцию для каждой строки.

В первом варианте точное поведение зависит от СУБД, типа индекса и оптимизатора. Она может выполнить сканирование индекса, использовать дополнительную оптимизацию или применить индекс по выражению, если такой индекс существует и выражение совпадает с условием.

Перенос преобразования на константу или параметр часто сохраняет индексируемый столбец неизменённым. Однако границы диапазона должны быть рассчитаны корректно: для временных данных важно учитывать точность, часовые пояса и переходы летнего времени.

Индекс по выражению устраняет проблему для конкретного вычисления, но требует дополнительного места и времени на поддержание при вставках и обновлениях. Он также может не использоваться при другом, хотя и похожем, выражении или при несовпадении типов и семантики преобразования.

Ситуация из практики

В журнале событий был обычный индекс по времени события. Запрос ежедневного отчёта применял преобразование времени к каждой строке и после роста таблицы начал читать почти весь индекс.

Рассматривались три варианта. Полное сканирование было простым, но плохо масштабировалось. Индекс по выражению ускорял именно этот отчёт, однако увеличивал стоимость записи и зависел от конкретной СУБД. Переписывание фильтра в полуоткрытый диапазон не требовало нового индекса и сохраняло совместимость.

Выбрали диапазон от начала дня до начала следующего дня. После проверки плана выполнения запрос стал выполнять индексный поиск, а не широкое сканирование; при этом нужно было отдельно проверить корректность часового пояса и границ интервала.

Что кандидаты часто упускают

1. Всегда ли функция над столбцом запрещает использование индекса?

Нет. СУБД может использовать функциональный индекс или индекс по выражению, если выражение индексируется и условие сопоставляется с ним. Некоторые оптимизаторы также умеют преобразовывать отдельные выражения в индексируемые предикаты.

Поэтому корректный вывод — не категорическое запрет использование индекса, а необходимость проверить фактический план выполнения и поддержку конкретной СУБД. Наличие индекса по исходному столбцу само по себе не гарантирует такой оптимизации.

2. Почему преобразование границ диапазона не всегда эквивалентно преобразованию столбца?

Эквивалентность зависит от типа данных и семантики функции. Для времени ошибка в точности или часовой зоне может включить лишние строки либо исключить нужные; безопасный шаблон часто использует левую включительную и правую исключительную границу.

Кроме того, не всякая функция монотонна. Для неё нельзя без потери смысла заменить условие над результатом функции простым диапазоном исходных значений.

3. Может ли индекс использоваться, если проблема вызвана не функцией, а неявным приведением типов?

Иногда да, но это зависит от направления приведения и типов. Если СУБД приводит сам индексируемый столбец к другому типу, эффективный поиск может стать невозможным; если приводится параметр к типу столбца, индекс обычно сохраняет возможность поиска.

На практике нужно задавать параметры в правильном типе и смотреть план выполнения. Неявное приведение также может изменить сравнение, привести к ошибкам или ухудшить оценку селективности, поэтому полагаться на автоматическое поведение без проверки нельзя.