Индексы PostgreSQL на практике: когда работают, а когда нет

Я работаю над системой управления школой: ученики, посещаемость, оценки — таблицы растут быстро, таблица посещаемости за несколько месяцев набирает миллионы строк. Однажды страница отчёта по посещаемости стала открываться за 3-4 секунды. «Добавим индекс, и всё», — сказал я. Добавил — ничего не изменилось. Тогда я по-настоящему понял: наличие индекса и его работа — две разные вещи. В этой статье разберём на реальных примерах, когда индексы PostgreSQL работают, а когда просто занимают место.

Примеры будут на этой упрощённой схеме:

CREATE TABLE students (
    id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    class_id  int  NOT NULL,
    full_name text NOT NULL,
    email     text,
    status    text NOT NULL DEFAULT 'active'  -- 'active' | 'archived'
);

CREATE TABLE attendance (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    student_id  bigint NOT NULL REFERENCES students (id),
    lesson_date date   NOT NULL,
    status      text   NOT NULL  -- 'present' | 'absent' | 'late'
);

Как работает B-tree — минутная интуиция

Когда вы пишете CREATE INDEX, PostgreSQL по умолчанию строит B-tree. Представьте его как алфавитный указатель в конце книги: значения лежат в дереве в отсортированном виде, в каждом листе — значение и адрес строки в таблице. Поиск спускается от корня к листу за несколько прыжков — даже в таблице на миллион строк достаточно прочитать 3-4 страницы.

Отсюда один важный вывод: B-tree — отсортированная структура, поэтому он силён в =, <, >, BETWEEN, ORDER BY и поиске по префиксу. И бессилен в любом условии, которое не опирается на сортировку. Все «нерабочие» случаи ниже вытекают из этой одной причины.

Индекс есть, но не работает: четыре классических случая

1. Функция над колонкой. Самая частая ошибка:

-- на колонке email есть обычный индекс:
CREATE INDEX idx_students_email ON students (email);

-- этот запрос обходит индекс стороной:
SELECT * FROM students WHERE lower(email) = 'aziz@example.com';

Индекс отсортирован по значениям email, а не по lower(email) — для PostgreSQL это совершенно другое значение. Решение — expression index:

CREATE INDEX idx_students_email_lower ON students (lower(email));

Теперь выражение в запросе точно совпадает с выражением в индексе, и Index Scan работает.

2. LIKE с `%` в начале. full_name LIKE 'Aziz%' — поиск по префиксу, отсортированное дерево с ним справляется: все «начинающиеся на A-z-i-z» лежат в дереве рядом. Но LIKE '%aziz%' означает «встречается где угодно» — у такого поиска нет точки входа в дерево, и PostgreSQL уходит в полное сканирование. Если нужно искать ученика по фрагменту имени, решение — расширение pg_trgm и GIN-индекс.

3. Низкая селективность. Допустим, мы поставили индекс на students.status, но 95 процентов учеников — active. В запросе WHERE status = 'active' PostgreSQL считает по статистике: прочитать таблицу последовательно от начала до конца дешевле, чем через индекс обращаться к 95 процентам строк поодиночке. И выбирает Seq Scan. Это не ошибка, а правильное решение; отдельный индекс на такую колонку был не нужен вовсе.

4. Неявное приведение типов. Если ORM или драйвер отправил параметр не того типа, индекс молча отключается:

-- student_id — bigint, проиндексирован. Параметр пришёл как numeric:
EXPLAIN SELECT * FROM attendance WHERE student_id = 100234::numeric;
-- Filter: ((student_id)::numeric = '100234'::numeric)  →  Seq Scan

PostgreSQL приводит колонку к numeric — и это снова тот же случай «функция над колонкой». Признак: неожиданный :: рядом с колонкой в выводе EXPLAIN. Решение — привести тип параметра к типу колонки.

В составном индексе порядок колонок решает всё

Составной индекс похож на телефонную книгу: записи отсортированы по (фамилия, имя). Знаете фамилию — найдёте мгновенно, знаете только имя — придётся листать всю книгу.

CREATE INDEX idx_att_student_date ON attendance (student_id, lesson_date);

С этим индексом:

• WHERE student_id = 42 — работает (leftmost prefix);
• WHERE student_id = 42 AND lesson_date >= '2026-05-01' — работает полностью;
• WHERE lesson_date = '2026-05-10' — не работает: без первой колонки нет точки входа в дерево.

Если запрос «посещаемость всей школы за дату» нужен часто, под него заводится отдельный индекс (lesson_date). Практическое правило: колонки, фильтруемые равенством (=), — в начало индекса, колонку с диапазоном (>=, BETWEEN) — в конец.

Учимся читать EXPLAIN ANALYZE

Работает индекс или нет — не гадайте, а спросите:

EXPLAIN ANALYZE
SELECT * FROM attendance
WHERE student_id = 42 AND lesson_date >= date '2026-05-01';

Index Scan using idx_att_student_date on attendance
  (cost=0.43..12.10 rows=38 width=29)
  (actual time=0.031..0.058 rows=41 loops=1)
  Index Cond: ((student_id = 42) AND (lesson_date >= '2026-05-01'::date))
Planning Time: 0.210 ms
Execution Time: 0.089 ms

На что я смотрю в первую очередь:

• Seq Scan — таблица читается целиком. Для маленькой таблицы нормально, для отфильтрованного запроса по большой — сигнал.
• Index Scan — через дерево читаются только нужные строки.
• Bitmap Heap Scan — промежуточный вариант: из индекса собирается «карта» подходящих страниц, потом страницы читаются разом. При средней селективности это нормально.
• rows (оценка) vs actual rows — прогноз планировщика против реального результата. Если разница больше чем в 10 раз, статистика устарела — запустите ANALYZE attendance;, иначе планировщик продолжит выбирать неверные планы.

Обратите внимание и на строку Index Cond: ваше условие стоит там или свалилось в Filter ниже — это показывает, действительно ли индекс покрывает условие.

Partial и covering индексы

Partial индекс индексирует только нужную часть таблицы. В посещаемости ~95 процентов строк — present, но в отчётах нас почти всегда интересуют отсутствовавшие:

CREATE INDEX idx_att_absent ON attendance (lesson_date)
WHERE status = 'absent';

Этот индекс хранит только строки absent — он в двадцать раз меньше полного, дёшев на записи и идеален для запросов «кто не пришёл в этот день». Условие должно точно повторяться в запросе: WHERE status = 'absent' AND lesson_date = ....

Covering индекс (INCLUDE) добавляет в лист индекса дополнительные колонки, нужные запросу:

CREATE INDEX idx_att_report ON attendance (student_id, lesson_date)
INCLUDE (status);

Если запрос отчёта просит только эти три колонки, PostgreSQL делает Index Only Scan — вообще не трогает таблицу. У нас месячный отчёт по посещаемости заметно ускорился именно этим индексом. Одна оговорка: чтобы было Heap Fetches: 0, по таблице должен регулярно проходить VACUUM.

Лишние индексы тоже вредят

Индекс не бесплатен. Каждый INSERT и UPDATE обновляет каждый индекс таблицы. В attendance на каждом уроке приходят сотни записей — каждый лишний индекс на ней замедляет каждую запись и ест место на диске. Какие индексы лежат мёртвым грузом, подскажет статистика:

SELECT indexrelid::regclass AS index_name,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size,
       idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

Неуникальные индексы с idx_scan = 0 — кандидаты на удаление. Я прогоняю этот запрос раз в квартал.

Итоги

• Индекс — отсортированная структура: функция над колонкой, % в начале и неверный type cast его отключают.
• Seq Scan при низкой селективности — не ошибка, а правильное решение планировщика.
• Порядок в составном индексе: колонки с равенством вперёд, диапазон в конец; без leftmost prefix индекс не работает.
• Не гадайте — запускайте EXPLAIN ANALYZE и сравнивайте rows estimate с actual.
• Partial и INCLUDE индексы в правильном месте заметно эффективнее обычных.
• Раз в квартал проверяйте pg_stat_user_indexes и удаляйте мёртвые индексы.

Индекс — это не «добавил и забыл», а инструмент, который живёт вместе с вашими запросами. Подружитесь с EXPLAIN ANALYZE — и он сам расскажет вам все свои секреты.