Индексирование БД: выбор столбцов и стоимость индекса

Прямой ответ

Индекс ускоряет выборку по фильтру, но замедляет запись, поэтому индекс создаётся под конкретный запрос, а не на каждый столбец таблицы.

CREATE INDEX CONCURRENTLY idx_orders_user_created
ON orders (user_id, created_at DESC);

Порядок столбцов определяет, какие условия запроса использует индекс, поэтому индекс с ведущим столбцом вне фильтра не применяется. Об устройстве индексов — в статье про индексы PostgreSQL.

Выбор столбцов под фильтр

Индекс применяется к условию фильтра, поэтому столбцы индекса выбираются по фактическим условиям запроса.

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

Селективность условия. Условие, оставляющее малую долю строк, выигрывает от индекса, поэтому порядок чтения сокращается.

Низкая селективность. Условие, оставляющее большую долю строк, не выигрывает от индекса, поэтому планировщик выбирает полный перебор.

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

Отдельный аспект — составной индекс. Порядок столбцов в составном индексе определяет, какие комбинации условий используют индекс, поэтому запрос с условием только по второму столбцу индекс не применяет.

Стоимость записи и число индексов

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

Обновление индекса. Запись в таблицу требует обновления каждого индекса, поэтому время записи растёт с числом индексов.

Объём хранения. Каждый индекс занимает место, поэтому объём базы растёт с числом индексов.

Неиспользуемые индексы. Индекс, к которому запросы не обращаются, увеличивает стоимость записи без пользы, поэтому удаляется по статистике использования.

Практическое следствие: таблица с большим числом индексов замедляет запись, поэтому индекс создаётся под запрос, а не на каждый столбец.

Проверка использования и эффект

Использование индекса проверяется по статистике, поэтому бесполезный индекс определяется по факту обращения.

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

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

Фактическое время. Время выполнения сравнивается до и после создания, поэтому ускорение подтверждается измерением.

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

О влиянии индекса на план запроса — в статье про оптимизацию запросов БД; об индексах для JSONB — в статье про JSONB запросы и индексы.

Частичный индекс и покрытие запроса

Частичный индекс охватывает долю строк таблицы, поэтому объём индекса сокращается, а покрытие запроса устраняет чтение самой таблицы.

Частичный индекс. Индекс строится по условию, поэтому в индекс попадают только строки, удовлетворяющие условию, а объём индекса сокращается.

Покрытие запроса. Индекс содержит столбцы, необходимые запросу, поэтому чтение выполняется из индекса без обращения к таблице.

Объём индекса. Сокращение объёма индекса уменьшает число страниц, читаемых при поиске, поэтому время чтения сокращается.

Практическое следствие: индекс, построенный по всей таблице при использовании только доли строк, занимает объём без соответствующей пользы.

Отдельный аспект — создание индекса без блокировки записи. Создание индекса с блокировкой останавливает запись в таблицу на время построения, поэтому операция выполняется в период низкой нагрузки либо без блокировки. Построение индекса на таблице большого объёма занимает заметное время, поэтому блокировка записи на этот период приводит к накоплению ожидающих запросов.

Практика: настройка индексирования

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

2. Учитывайте порядок столбцов в составном индексе. Порядок определяет, какие комбинации условий используют индекс.

3. Проверяйте селективность условия. Условие с низкой селективностью не выигрывает от индекса, поэтому планировщик выбирает перебор.

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

5. Проверяйте использование по статистике. Индекс без обращений увеличивает стоимость записи без пользы.

6. Удаляйте неиспользуемые индексы. Стоимость записи сокращается, поэтому время операции уменьшается.

7. Сравнивайте план до и после создания. Смена способа чтения подтверждает, что индекс применяется.

Рекомендации:

1. Не создавайте индекс на каждый столбец. Число индексов определяет стоимость записи, поэтому индекс создаётся под запрос.
2. Учитывайте селективность при выборе столбца. Столбец с малым числом значений не даёт ускорения.
3. Сопоставляйте порядок столбцов с условиями запроса. Ведущий столбец вне фильтра делает индекс неприменимым.
4. Фиксируйте время записи в метриках. Рост времени записи указывает на избыточное число индексов.
5. Пересматриваем индексы при изменении запросов. Новый запрос может требовать иного состава индекса.

Практический сценарий: запись замедлилась после добавления индексов

Контекст: время вставки выросло после добавления нескольких индексов, поэтому операция стала медленнее.

1. Диагностика. Проверка статистики использования показала, что два из шести индексов не применялись ни одним запросом. Индексы обновлялись при каждой вставке, поэтому время записи росло без ускорения чтения.
2. Причина. Индексы создавались на столбцы без проверки использования, поэтому стоимость записи выросла без пользы для чтения.
3. Действия. (а) Проверили статистику использования индексов и нашли неиспользуемые; (б) удалили индексы без обращений, чтобы стоимость записи сократилась; (в) сравнили время вставки до и после удаления и зафиксировали сокращение; (г) проверили план запросов и убедились, что используемые индексы продолжают применяться; (д) настроили метрику времени записи, чтобы рост стоимости обнаруживался.
4. Результат. Время записи сократилось, поэтому операция выполняется быстрее без потери ускорения чтения.

Вывод: каждый индекс обновляется при записи, поэтому неиспользуемый индекс увеличивает стоимость операции без пользы. Проверка использования по статистике определяет индексы, которые удаляются без потери ускорения.

FAQ

Как выбрать столбцы для индекса? По фактическим условиям фильтра запроса. Индекс применяется к условию сравнения, поэтому столбец вне фильтра не используется.

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

Как влияет число индексов на запись? Каждый индекс обновляется при записи, поэтому время операции растёт с числом индексов.

Как определить неиспользуемый индекс? По статистике обращений к индексу. Индекс без обращений увеличивает стоимость записи без ускорения чтения.

Источники: PostgreSQL indexes, PostgreSQL multicolumn indexes, Use The Index Luke — ссылки в шапке.