Индексы и EXPLAIN: измеряем путь запроса

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

Структура рядом с данными

Индекс хранит дополнительный путь поиска. Часто используется B-tree: ключи упорядочены, внутренние узлы направляют поиск, листья содержат доступ к значениям/строкам. Это помогает равенству и диапазонам при подходящем порядке ключей. Одна найденная ссылка ещё может требовать чтения самой таблицы и проверки видимости строки. Индекс не является копией всей бизнес-логики.

Составной индекс (member_id, borrowed_on, id) естественно группирует выдачи участника, затем упорядочивает их по дате и ID. Запрос только по borrowed_on имеет другой рисунок доступа: ведущий member_id больше не фиксирован. Движок может иметь дополнительные способы использовать структуру; правило ведущих полей объясняет обычный выбор, а не запрещает все другие планы.

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

Увеличиваем данные контролируемо

Файл indexes.sql из комплекта создаёт loan_search с 100 тысячами строк, снимает план, добавляет индекс и повторяет тот же запрос. Всё выполняется в транзакции и откатывается. Временная таблица принадлежит текущей сессии; имя collision приводит к ошибке, а не к переиспользованию чужого объекта.

BEGIN;
CREATE TEMP TABLE loan_search(
  id bigint PRIMARY KEY,
  member_id bigint NOT NULL,
  borrowed_on date NOT NULL
);
INSERT INTO loan_search
SELECT n,n%1000,DATE '2026-01-01'+(n%200)::integer
FROM generate_series(1,100000) AS s(n);
ANALYZE loan_search;
SELECT COUNT(*) FROM loan_search WHERE member_id=17;

Ожидаемый COUNT равен 100: остаток 17 встречается сто раз. generate_series создаёт числа для теста; модуль % определяет распределение. Фиксированные формулы позволяют проверить ответ без скачивания большого dataset. ANALYZE собирает статистику для оценки планов; это не команда «обязательно используй индекс».

EXPLAIN (ANALYZE,BUFFERS)
SELECT id,borrowed_on FROM loan_search
WHERE member_id=17
ORDER BY borrowed_on,id LIMIT 10;

Запрос реально выполняется. EXPLAIN без ANALYZE показывает предполагаемый план, ANALYZE добавляет наблюдения исполнения, BUFFERS — работу с буферами. Для SELECT этот опыт не изменяет строки; для UPDATE/DELETE ANALYZE исполнил бы изменение. Поэтому нельзя запускать незнакомую изменяющую команду под видом безвредного просмотра плана.

Читаем план как объяснение

Узел Seq Scan означает просмотр таблицы, Index Scan использует индексный путь, Sort упорядочивает результат, Limit завершает после нужного количества. План дерева выполняет узлы с выбранными алгоритмами; логическое объяснение SELECT не задаёт физический порядок всех действий. Estimated rows — оценка, actual rows — наблюдение для конкретного запуска. Большое расхождение может указывать на статистику или распределение, но требует проверки причины.

Стоимость cost измеряется внутренними условными единицами, а не миллисекундами вашего SLA. Время узлов и loops тоже нельзя механически складывать как независимые этапы: часть измерений включает работу дочерних узлов. Читайте определение полей и проверяйте общий execution time. Прогретая память, нагрузка и различие cache hit/read влияют на повторную пробу.

CREATE INDEX loan_search_member_date
ON loan_search(member_id,borrowed_on,id);
ANALYZE loan_search;
EXPLAIN (ANALYZE,BUFFERS)
SELECT id,borrowed_on FROM loan_search
WHERE member_id=17
ORDER BY borrowed_on,id LIMIT 10;
ROLLBACK;

После добавления результат не должен измениться. Первые ID для member_id=17: 17,1017,2017 и далее с шагом 1000, потому что в нашей формуле все они имеют одинаковую дату 2026-01-18. Для LIMIT 10 последний ID равен 9017. План может воспользоваться упорядоченным индексом и уменьшить отдельную сортировку, но конкретное название узла не является обязательным обещанием лаборатории.

Запись платит за ускорение чтения

Индекс обновляется при изменении затронутых данных, занимает память/диск и может создавать WAL. Десять индексов не делают INSERT бесплатным. Уникальный индекс дополнительно проверяет правило; partial индекс хранит только подходящие строки. Наш индекс активных выдач уже служит correctness, поэтому удалить его ради ускорения записи означает изменить гарантию.

Index-only scan требует условий хранения нужных полей и видимости; наличие covering структуры не всегда устраняет обращение к таблице. Запрос, который возвращает почти все строки, может разумно выбрать последовательное чтение. Функция над индексируемым полем либо несовпадение типов может изменить доступный путь: вместо слепого индексирования сначала проверьте выражение запроса.

Самостоятельная лаборатория

Отведите 30–40 минут. Запустите файл, сохраните оба плана и те же десять строк результата. Назовите фильтр, сортировку, оценку/факт rows и объём работы буферов. Затем измените условие на выбор большинства member_id: обоснован ли прежний индекс? Не делайте вывод по одной миллисекунде с ноутбука.

Критерии: COUNT=100; обе версии запроса дают ID 17..9017 с шагом 1000; ученик отличает cost от времени; изменение схемы откатилось; ускорение сформулировано через наблюдаемый путь.

Разбор

Индекс группирует нужные сто строк и помогает порядку, но массовый ответ меняет цену. Решение пересматривают при изменении фильтров, размера результата, конкуренции записи или working set.

Первичные источники

PostgreSQL: Using EXPLAIN, Multicolumn Indexes, CMU: Indexes and Query Planning.