Что такое составной индекс и как он работает

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

Читаете ли вы план запроса и понимаете ли, почему индекс не применился. Go-разработчик чаще всего пишет SQL руками, без прослойки ORM, поэтому спрашивают конкретно: какой порядок колонок выбрать и чем подтвердить, что выбор сработал.

Как отвечать

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

Правило левого префикса: индекс по (a, b) обслуживает условия по a и по a вместе с b. PostgreSQL формально способен взять такой индекс и для условия по одному b, но это будет чтение всего индекса целиком, и планировщик пойдёт на это, только когда так дешевле, чем читать таблицу. Закладываться на подобное нельзя.

Порядок колонок: равенство первым, затем то, по чему сортируем, диапазон последним.

CREATE INDEX idx_jobs_company_created ON jobs (company_id, created_at DESC)
  WHERE is_active;

Полезные приёмы: условие в определении делает индекс частичным и резко уменьшает его размер; INCLUDE кладёт в лист колонки, не участвующие в поиске, ради index only scan; постраничная выдача курсором по тем же колонкам вместо OFFSET.

Проверять выводом EXPLAIN (ANALYZE, BUFFERS), сверяя ожидаемое и фактическое число строк.

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

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

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

Забывают направление сортировки. Индекс по (a, b DESC) обслуживает ORDER BY a, b DESC и полностью обратный порядок, но не сортировку по обеим колонкам по возрастанию, и в плане внезапно появляется отдельный шаг сортировки.