Заметки

Индексы в PostgreSQL: что помогает на практике

Базы данных · 28 сентября 2026 · ~8 минут

Самая частая ошибка, которую я видел в чужих схемах — «давайте проиндексируем всё». Индекс стоит места и замедляет запись, а половина из созданных не используется вообще. Ниже то, что на самом деле влияет на скорость.

Содержание
  1. Сначала — используется ли индекс
  2. Кардинальность решает
  3. Порядок колонок в составном индексе
  4. Частичные и покрывающие индексы
  5. Как читать EXPLAIN ANALYZE
  6. Цена индекса

1. Узнаём, используются ли индексы

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

SELECT relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Индексы с нулевым idx_scan — кандидаты на удаление. Единственная поправка: счётчик сбрасывается при перезапуске сервера, поэтому смотреть имеет смысл через недели работы, а не через час.

2. Кардинальность решает всё

Индекс по колонке со значениями status (пять вариантов) почти бесполезен: планировщик обходит половину таблицы по любому, и дешевле сделать Seq Scan. Индекс по user_id на 10 миллионах строк, где на пользователя приходится десяток записей, — напротив, работает отлично.

Грубое правило: если условие отсекает больше 15–20% таблицы, планировщик с высокой вероятностью выберет последовательное сканирование. Это не баг — так действительно быстрее.

3. Порядок колонок важнее самого факта наличия индекса

Составной индекс (a, b, c) работает для условий вида a = ?, a = ? AND b = ?, a = ? AND b = ? AND c = ?. Он не работает для b = ? без a — это как искать в телефонной книге по имени, если она отсортирована по фамилии.

Практический порядок — от наиболее селективного и наиболее частого в запросах:

-- часто: выборка заказов пользователя за период
CREATE INDEX ON orders (user_id, created_at DESC);

-- запрос: WHERE user_id = ? ORDER BY created_at DESC LIMIT 50
-- сортировка уже внутри индекса, отдельного Sort шага нет

Обратите внимание на DESC: для запросов с ORDER BY ... DESC направление в индексе избавляет от переворота. Составной индекс вида (user_id, created_at DESC) особенно хорош для «последние N записей пользователя».

4. Частичные и покрывающие индексы

Если запросы почти всегда смотрят только активные записи, индекс можно сузить:

CREATE INDEX CONCURRENTLY idx_tasks_open
  ON tasks (created_at)
  WHERE status = 'open';

Он раз в пять меньше полного, а WHERE status = 'open' планировщик понимает и подставляет его точно.

Покрывающий индекс (INCLUDE) прячет данные прямо в индексе — тогда обращения к самой таблице не происходит вовсе:

CREATE INDEX idx_orders_cover ON orders (user_id) INCLUDE (total, created_at);
CONCURRENTLY Обычный CREATE INDEX ставит блокировку на запись. На живой таблице используйте CONCURRENTLY — дольше, но не кладёт продакшен. Если создание упало, остаётся недостроенный индекс, который надо удалить перед повторной попыткой.

5. Читаем EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 50;

На что смотреть в выводе:

ПризнакЧто означает
Seq Scan on orders (rows=10000000)Индекс не выбран — либо его нет, либо он не селективен
Index Scan vs Index Only ScanВторой — данные целиком из индекса, быстрее
Sort Method: external mergeСортировка не влезла в память — ушла на диск, почти наверняка нужен индекс с нужным порядком
rows=50 против actual rows=1Плохая статистика — пора ANALYZE или кастомную селективность
shared read блоки в BUFFERSРеальное число обращений к страницам — главный показатель стоимости

Важная привычка: EXPLAIN без ANALYZE показывает только план, а не факт. Иногда план красивый, а фактическое время — три секунды.

6. Цена, которую платим за индексы

Каждый индекс — это дополнительная запись при INSERT/UPDATE и место на диске. Практический чек-лист перед созданием:

Полезная команда для поиска дублей: pg_index + сравнение indkey — но быстрее обычно глазами по \di в psql при десятке индексов.