В каком порядке нужно перечеслять поля при индексации

Порядок полей при индексации — ESR правило

Порядок полей в индексе критически влияет на performance запросов. За 10+ лет работы я руководствуюсь ESR правилом (Equality, Sort, Range).

ESR правило (Equality → Sort → Range)

E (Equality) — поля в WHERE с точным совпадением:

Получают первый приоритет. Они фильруют данные перед сортировкой.

S (Sort) — поля в ORDER BY:

Берут второй приоритет. После фильтрации базовых данных индекс помогает избежать дополнительной сортировки.

R (Range) — поля в WHERE с диапазонами:

Помещаются в конец. Диапазон "разбивает" индекс — данные после него не отсортированы.

Практический пример

Запрос для поиска заказов пользователя за период в определённом регионе:

SELECT * FROM orders
WHERE user_id = 123          -- Equality
  AND region = 'EU'          -- Equality
  AND created_at >= '2025-01-01'  -- Range
ORDER BY amount DESC;        -- Sort

ПРАВИЛЬНЫЙ индекс:

CREATE INDEX idx_orders ON orders(
  user_id,        -- E: Фильтр по пользователю
  region,         -- E: Фильтр по региону
  amount DESC,    -- S: Сортировка по сумме
  created_at      -- R: Диапазон по дате
);

НЕПРАВИЛЬНЫЙ индекс (медленнее 10x):

CREATE INDEX idx_orders_bad ON orders(
  created_at,     -- R в начале - разбивает индекс!
  user_id,
  region,
  amount
);

Второй индекс заставит БД сканировать все строки с date >= '2025-01-01', а потом фильтровать по user_id и region.

Дополнительные правила

Включающие индексы (INCLUDE поля):

Для covering index добавляем в INCLUDE поля, которые не участвуют в WHERE/ORDER:

CREATE INDEX idx_orders_cover ON orders(
  user_id,
  region
) INCLUDE (amount, status);

БД сможет ответить на запрос только из индекса, не трогая основную таблицу (Index-Only Scan).

Направление сортировки (ASC/DESC):

-- Если нужна сортировка по убыванию
CREATE INDEX idx_score ON users(
  status,      -- E: точный поиск
  score DESC   -- S: сортировка вниз
);

Анализ с EXPLAIN

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE user_id = 123
  AND region = 'EU'
ORDER BY amount DESC;

Проверяю в выводе:

• Seq Scan — индекс не использован (неправильный порядок)
• Index Scan — хорошо
• Index Only Scan — идеально (никаких обращений к таблице)

Общие принципы

1. Высокая селективность в начале:

Полья с большим количеством уникальных значений раньше. Это быстрее отсекает данные.

-- Правильно
CREATE INDEX idx ON events(user_id, event_type, date);
-- user_id имеет 1M уникальных, event_type только 10

2. Не переусложняй:

Не создавай индекс на 10 полей. PostgreSQL использует один индекс за раз. Лучше иметь несколько узких индексов.

3. Проверяй через EXPLAIN ANALYZE:

Теория vs практика. Всегда валидируй через explain.

4. Частые изменения vs читаемость:

Каждый индекс замедляет INSERT/UPDATE/DELETE. Индексируй только необходимое.

Итог

ESR порядок (Equality → Sort → Range) даёт 10-100x ускорение запросов. Используй правило в 99% случаев — это универсально работает для PostgreSQL, MySQL, SQL Server.