Какие операции выигрывают от B-tree индекса, а какие нет

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

Понимаете ли вы, что B-tree это упорядоченная структура, и умеете ли выводить из этого границы применимости. Следом обычно дают конкретное условие вроде WHERE lower(email) = $1 или LIKE '%abc' и просят сказать, применится ли индекс.

Как отвечать

Начните с того, что выигрывает:

• равенство и диапазон: =, <, >, BETWEEN, IN по колонке;
• сортировка и пагинация: ORDER BY col LIMIT 20 берётся прямо из индекса, без отдельной сортировки;
• MIN и MAX: читается край индекса, без прохода по таблице;
• префиксный поиск: LIKE 'abc%' работает, но для локали, отличной от C, нужен класс операторов text_pattern_ops;
• соединения: merge join, проверка уникальности, внешние ключи;
• index-only scan: если все нужные колонки есть в индексе, включая добавленные через INCLUDE, и карта видимости свежая;
• составной индекс: работает по левому префиксу, (a, b) годится для условия по a и по паре, но не для одного b.

Дальше про то, что не помогает:

• LIKE '%abc', регулярные выражения, поиск по подстроке: нужен GIN с pg_trgm;
• выражение или приведение типа над колонкой: lower(email), date(created_at), id::text. Лечится индексом по выражению;
• низкая избирательность: <>, NOT IN, условие, отбирающее заметную долю таблицы. Планировщик выберет последовательное чтение, и это правильно;
• другие типы задач: полнотекстовый поиск, jsonb по содержимому, геометрия, поиск ближайших векторов. Там работают GIN, GiST и HNSW.

Отдельно скажите про запись: каждый индекс замедляет INSERT, UPDATE и DELETE, а лёгкое обновление HOT возможно только когда изменённые колонки не входят ни в один индекс.

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

Говорят, что B-tree ускоряет вставку и удаление. Ускоряется поиск строки, а сама запись становится дороже: каждый индекс надо обновить.

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

Не смотрят план: раз индекс создан, значит используется. EXPLAIN (ANALYZE, BUFFERS) регулярно показывает Seq Scan из-за приведения типа, устаревшей статистики или того, что запрос выбирает половину таблицы.