При каких условиях индексы ускоряют или не помогают при join

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

Понимаете ли вы, что индекс ускоряет не JOIN сам по себе, а конкретный способ соединения, который выбрал планировщик. Бэкендеру на Python это нужно постоянно: запросы порождает ORM, и разбираться, почему они медленные, приходится по плану выполнения.

Как отвечать

Начните со способов соединения. Nested loop идёт по внешнему набору и для каждой строки ищет совпадение во внутренней таблице: здесь индекс по ключу соединения решает всё. Hash join строит хэш-таблицу по одной стороне, индекс по ключу ему не нужен, зато помогает индекс под условия фильтрации. Merge join требует отсортированных входов, и индекс может отдать готовый порядок без сортировки.

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

Отдельно назовите то, что молча выключает индекс: несовпадение типов и приведение на лету, функция поверх колонки без подходящего индекса, разные правила сортировки у текстовых колонок. И напомните, что в PostgreSQL индекс автоматически появляется под первичным ключом и уникальным ограничением, но не под внешним. Соединение по внешнему ключу без индекса это классическая причина долгих запросов.

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

Первое: предлагают навесить индексы на все колонки соединения. Каждый индекс замедляет вставку и обновление, занимает место и иногда уводит планировщик в худший план. Правильный порядок звучит как «сначала план запроса, потом индекс под него».

Второе: забывают, что индекс нужен на той стороне, по которой идёт поиск. При nested loop индекс на внешнем наборе бесполезен. Составной индекс работает слева направо: если первая колонка в условии не задана, поиска по нему не будет.

Третье: измеряют не там. На пустой базе разработчика планировщик всегда выберет последовательное чтение, потому что так дешевле, и вывод переносят на продакшен. Смотреть надо EXPLAIN (ANALYZE, BUFFERS) на сопоставимых объёмах и сверять оценку строк с фактической: расхождение в разы означает проблему со статистикой, а не отсутствие индекса.