re:re:actable
Урок 073 минуты чтения

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

На этой странице
Кратко

Составной (multicolumn) индекс строится по нескольким столбцам и сортирует записи сначала по первому, внутри — по второму и так далее, как телефонный справочник «фамилия → имя». Отсюда правило левого префикса: индекс (a, b, c) эффективно работает для условий на a, a + b, a + b + c, но не для одного b или c (PostgreSQL 18 умеет skip scan, но это помогает, только когда у a мало различных значений). Порядок столбцов выбирают так: сначала столбцы с условием равенства, затем столбец диапазона или сортировки — после диапазона следующие столбцы уже не сужают поиск по индексу. Один хорошо спроектированный составной индекс часто заменяет несколько одиночных и может служить покрывающим.

Вопрос

Как работает составной индекс и как выбрать порядок столбцов?

Порядок записей

CREATE INDEX orders_user_status_created_idx ON orders (user_id, status, created_at);
(1, 'new',  10:00)
(1, 'new',  12:00)
(1, 'paid', 09:00)
(2, 'new',  08:00)
...

Правило левого префикса

УсловиеИспользует индекс эффективно
user_id = 1Да
user_id = 1 AND status = 'new'Да
user_id = 1 AND status = 'new' AND created_at > $1Да
user_id = 1 AND created_at > $1Частично: поиск по user_id, created_at фильтруется внутри
status = 'new'Нет (или skip scan в PG 18 при малом числе user_id)
created_at > $1Нет

Равенство → диапазон/сортировка

-- запрос
SELECT * FROM orders
WHERE user_id = $1 AND status = 'paid' AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC;
 
-- ✓ равенства первыми, диапазон и сортировка — последним
CREATE INDEX ON orders (user_id, status, created_at DESC);
 
-- ✗ диапазон в начале: внутри диапазона дат строки перемешаны по user_id
CREATE INDEX ON orders (created_at, user_id, status);

Миф о «самом селективном столбце первым»

Для условий равенства на всех столбцах порядок мало влияет на скорость поиска. Важнее:

  1. Какие запросы должен обслуживать индекс (левый префикс).
  2. Где стоит диапазон/сортировка — в конце.
  3. Можно ли переиспользовать индекс для FK (FK-столбец первым).

Один индекс вместо нескольких

-- вместо (user_id) и (user_id, created_at)
CREATE INDEX ON orders (user_id, created_at);   -- покрывает оба сценария

Индекс (user_id) становится избыточным — его можно удалить.

Составной или несколько одиночных

PostgreSQL умеет объединять одиночные индексы через BitmapAnd, но это обычно медленнее, чем один составной индекс под частый запрос. Одиночные индексы гибче для редких и разнообразных фильтров (админ-панели с множеством опциональных фильтров).

Частые ошибки

  • Порядок столбцов «как в таблице», а не как в запросах.
  • Диапазон или сортировка в начале индекса.
  • Дублирующие индексы: (a) при существующем (a, b).
  • Пять столбцов «на все случаи» — тяжёлый индекс, который не подходит никому.

Что важно помнить

  • Индекс (a, b, c) — сортировка по a, потом b, потом c.
  • Левый префикс определяет, какие запросы обслуживаются.
  • Равенства — сначала, диапазон и сортировка — в конце.

Мини-шпаргалка

  • CREATE INDEX ON t (eq1, eq2, range_or_sort).
  • (a, b) покрывает запросы по a.
  • Удаляйте индексы-префиксы, дублирующие составной.
  • Проверяйте EXPLAIN: Index Cond vs Filter.

Документация

PostgreSQL: Multicolumn Indexes, PostgreSQL: Combining Multiple Indexes.

Дочитали до конца?
Отметьте урок — прогресс сохранится в браузере.

Поиск

Поиск по урокам