Индексы в SQL: типичные грабли и как их избежать

Запрос фильтрует строки по трём условиям, а индекс есть только на одном столбце — и база всё равно читает большую часть таблицы. Добавить ещё несколько индексов наугад кажется быстрым решением, но каждый из них занимает место и добавляет работу при записи. Ниже — типичные грабли с индексами на примере PostgreSQL 16: как выбрать форму индекса под запрос и проверить, что он действительно помогает.

Сначала запрос, потом индекс

Индекс ускоряет не абстрактный столбец, а конкретный способ найти и упорядочить строки. Перед созданием выпишите запрос целиком: условия в WHERE, сортировку, ограничение LIMIT и то, как часто запрос запускается. Индекс для фильтрации по одному полю может не помочь запросу, который одновременно фильтрует по другому полю и сортирует результат.

Например, интернет-магазин часто показывает последние оплаченные заказы конкретного клиента:

SELECT id, created_at
FROM orders
WHERE customer_id = 42
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

Для такого запроса в PostgreSQL 16 может подойти составной B-tree-индекс по (customer_id, status, created_at). Первые два столбца помогают отобрать строки, последний — получить их в нужном порядке. Но это не универсальная формула: если у вас другие запросы, данные или сортировка, порядок и набор полей могут быть другими.

Четыре грабли при проектировании

1. Поставить нужные поля не в том порядке

Для B-tree важен порядок столбцов в составном индексе. Обычно наиболее полезны ограничения на первые поля, а затем — на следующие. Индекс (customer_id, status, created_at) рассчитан на фильтрацию по клиенту, затем по статусу. Если запрос ищет только по status, не рассчитывайте, что этот индекс окажется для него равноценен индексу, начинающемуся со status.

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

2. Индексировать всё подряд

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

Если запрос возвращает большую долю таблицы, последовательное чтение может оказаться выгоднее: базе не всегда стоит идти по индексу и затем отдельно доставать множество строк. Наличие индекса — не команда использовать его, а один из вариантов плана.

3. Не совпасть с выражением в запросе

Обычный индекс на email не обязательно поможет поиску, который применяет к полю функцию, например lower(email). В PostgreSQL можно создать индекс на выражение:

CREATE INDEX users_lower_email_idx
ON users (lower(email));

Он рассчитан на запросы с тем же выражением:

SELECT id
FROM users
WHERE lower(email) = lower('Person@example.com');

Индекс на выражение добавляет работу при записи: базе нужно вычислять значение и поддерживать его в индексе. Создавайте его, когда такой поиск действительно важен, а не «на всякий случай».

4. Ждать пользы от индекса там, где поиск устроен иначе

B-tree подходит многим запросам на равенство, диапазон и сортировку, но не любой форме поиска. Например, условие LIKE '%shop%' начинается с шаблонного символа: обычный B-tree-индекс на текстовом столбце, как правило, не решает задачу такого поиска. Для поиска по подстроке в PostgreSQL используют другие средства, в частности расширение pg_trgm; выбор требует отдельной проверки под вашу схему и запросы.

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

Какой индекс выбрать

Это не взаимозаменяемые рецепты: выбирайте вариант по форме запроса и проверяйте план.

Частичный индекс в PostgreSQL 16 можно задать так:

CREATE INDEX orders_open_created_idx
ON orders (created_at)
WHERE status = 'open';

Он не индексирует всю таблицу, а хранит записи, подходящие под условие. Это может сократить размер индекса, но не поможет запросу, который ищет другой статус. С параметризованными запросами и планами для подготовленных выражений поведение стоит проверять отдельно: планировщик должен суметь связать условие запроса с предикатом индекса.

Проверяйте план, а не наличие индекса

В PostgreSQL 16 для разбора конкретного запроса используйте EXPLAIN. С ANALYZE база выполнит запрос, поэтому начинайте с безопасного чтения и учитывайте, что тестовая среда может отличаться от рабочей.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at
FROM orders
WHERE customer_id = 42
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

Смотрите не только на то, появился ли Index Scan. Сопоставьте оценку числа строк с фактическим результатом, время выполнения и чтение блоков. Если оценки сильно расходятся, планировщику может не хватать актуальной статистики. Если план не использует индекс, это не автоматически ошибка: последовательное чтение иногда дешевле.

Для честного сравнения проверяйте несколько характерных значений, а не один удобный пример. Маленькая таблица, редкое значение и выборка из большой части таблицы дают разные планы. Зафиксируйте условия теста и измеряйте в похожей на production среде: результаты зависят от объёма и распределения данных, кеша и конкурирующей нагрузки.

Хороший индекс — не тот, который красиво выглядит в схеме, а тот, который улучшает важный запрос и оправдывает цену при записи.

Когда что выбирать и что ломается

• Один частый фильтр по полю — начните с проверки простого B-tree-индекса.
• Фильтр плюс сортировка или несколько устойчивых условий — проверьте составной индекс в порядке реального запроса.
• Запрос постоянно применяет функцию к полю — проверьте индекс на выражение или возможность переписать условие.
• Запрос работает только с постоянным подмножеством строк — рассмотрите частичный индекс и убедитесь, что планировщик распознаёт условие.

Если после создания индекс не используется, проверьте, совпадает ли он с условием и сортировкой, сколько строк возвращает запрос и насколько актуальна статистика. Если ускорился чтение, но просела запись, проверьте число индексов и стоимость обновления таблицы. Не удаляйте индекс только из-за одного плана: сначала проверьте нагрузку и остальные запросы, которые могут на него опираться.

Сноска. Селективность — доля строк, которую условие оставляет после фильтрации. Чем меньше подходящих строк, тем чаще индексный поиск может быть выгоден; это не единственный фактор, который учитывает планировщик.

Что делать дальше

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

Здесь разобраны базовые формы индексов и проверка плана. Выбор специализированного индекса для полнотекстового поиска или поиска по подстроке — отдельная задача.