null

Составные индексы и INCLUDE в PostgreSQL

7 March 2025 0 Daria Zuban

Индексы в СУБД существенно ускоряют поиск и фильтрацию данных. Если запрос использует несколько колонок в WHERE или ORDER BY, обычные одноколоночные индексы могут оказаться неэффективными.

В таких ситуациях стоит рассмотреть составные индексы (Composite Index). В документации PostgreSQL чаще используется термин multi-column indexes, а composite indexes — более общее название, которое также используется в других СУБД.

Составные индексы (Composite Index)

Составной индекс охватывает несколько колонок сразу, позволяя одновременно ускорить поиск по нескольким полям.

Порядок колонок в составном индексе критичен, и его необходимо учитывать при составлении фильтра в запросе.

В PostgreSQL составной индекс реализован на основе B-Tree:

  • Хранит данные в отсортированном виде.
  • Разбивает значения на страницы (pages) для быстрого поиска.
  • Строит иерархическую структуру, где корневые узлы указывают на диапазоны значений в листьях.
  • При построении составного индекса сначала сортируется первая колонка, затем вторая и так далее.

Пример

Допустим, что в таблице users часто идут запросы по name и age:

CREATE INDEX idx_users_name_age ON users(name, age);

Как это работает?

Индекс отсортирован по name, затем по age.

  1. Поиск по обеим колонкам – name и age – очень быстрый, так как индекс используется полностью:

SELECT * FROM users WHERE name = 'Ivan' AND age = 30;

  1. Поиск по обеим колонкам, но в обратном порядке, age и name, менее эффективен:

SELECT * FROM users WHERE age = 30 AND name = 'Ivan';

PostgreSQL не может быстро найти все записи с age = 30, потому что индекс отсортирован по name, а не по age. 
Он будет сканировать весь индекс, проверяя age = 30 для каждой строки с разными значениями name.

  1. Поиск только по name использует индекс:

SELECT * FROM users WHERE name = 'Ivan';

  1. Поиск только по age не использует этот индекс!

SELECT * FROM users WHERE age = 30.

INCLUDE в индексах

Обычно индексы содержат только те колонки, которые участвуют в поиске или сортировке. Однако иногда требуется добавить дополнительные колонки, которые не участвуют в сортировке, но могут понадобиться для SELECT. В этом случае используется INCLUDE.

Как это работает?

INCLUDE позволяет добавить в индекс дополнительные колонки, которые PostgreSQL может использовать при выборе стратегии индексного покрытия (Index-Only Scan), но без участия в сортировке.

Пример индекса с INCLUDE:

CREATE INDEX idx_users_name_age_include ON users(name) INCLUDE (age, email);

В этом случае:

  • Индекс отсортирован по name, но содержит также age и email.
  • Запросы, использующие name, могут быть полностью обслужены индексом, так как age и email уже находятся в структуре индекса.

Пример запроса, который использует индексное покрытие:

SELECT name, age, email FROM users WHERE name = 'Ivan;

Когда использовать INCLUDE?

  • таблица большая, и Index-Only Scan может существенно ускорить выполнение запроса.
  • нужно избежать обращения к таблице, если все необходимые данные уже содержатся в индексе.
  • колонки используются в SELECT, но не участвуют в WHERE или ORDER BY.

Вывод

Составные индексы и INCLUDE в PostgreSQL — мощные инструменты оптимизации запросов:

  • Используйте составные индексы, если запрос фильтрует или сортирует по нескольким колонкам.
  • Порядок колонок в индексе критичен — сначала идут самые селективные поля.
  • INCLUDE позволяет избежать обращения к таблице, улучшая производительность SELECT.

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

Next