Почему для использования индекса в фильтрах обязательно должно быть равенство?

Что проверяют

Услышите ли вы, что в самой формулировке ошибка. B-дерево прекрасно работает и на неравенствах, а «обязательно равенство» верно для хеш-индексов и для колонок составного индекса, стоящих после диапазонного условия. Уточняют обычно ровно про это: почему WHERE a > 1 AND b = 2 не использует индекс (a, b) целиком.

Как отвечать

• B-дерево: листья связаны в отсортированный список, поэтому работают =, <, >, BETWEEN, IN и ORDER BY: находим границу диапазона и читаем подряд. Ограничение не в операторе, а в объёме выборки;
• хеш-индекс: хранит хеш значения, порядок теряется полностью, отсюда единственный поддерживаемый оператор =;
• составной индекс (a, b, c): ключ упорядочен лексикографически. Пока по a и b идут равенства, c тоже участвует в поиске. Как только по a пришёл диапазон, внутри него b и c уже не отсортированы, и они превращаются в фильтр по прочитанным строкам. Отсюда рабочее правило: колонки с равенством ставим в начало индекса, диапазон последним;
• селективность: планировщик считает стоимость по статистике. Если условие выбирает заметную долю таблицы, последовательное чтение дешевле множества случайных переходов, и индекс не будет использован даже при равенстве.

Добавьте случаи, когда индекс мёртв и без диапазонов: функция над колонкой (WHERE lower(email) = ... требует индекса по выражению), несовпадение типов в сравнении, LIKE '%abc' с шаблоном в начале. Для PostgreSQL уместно назвать text_pattern_ops для LIKE 'abc%' вне C-локали, GIN для массивов и jsonb, BRIN для почти упорядоченных данных. Финал: спор решается через EXPLAIN (ANALYZE, BUFFERS), а не рассуждением.

Где спотыкаются

Соглашаются с посылкой вопроса и уверенно объясняют неверное утверждение. Интервьюер как раз проверяет, заметите ли вы подмену.

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

Путают порядок колонок в индексе с порядком условий в WHERE. Условия в запросе можно писать как угодно, значение имеет только порядок колонок в определении индекса.