Индексы PostgreSQL: как проверить пользу для чтения и цену для записи до внедрения

Быстрый запрос ещё не означает удачное изменение

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

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

Мы провели небольшой воспроизводимый опыт на PostgreSQL 16.15. В таблице было 200 тысяч синтетических строк; сравнивались одинаковые конфигурации с дополнительным B-tree по статусу и без него. Для редкого значения медиана серверного времени SELECT уменьшилась с 8,928 до 1,820 мс. При этом вставка 10 тысяч строк заняла 12,959 мс вместо 7,609 мс. Индекс не стал «плохим»: он изменил распределение работы. Именно это и нужно увидеть до принятия решения.

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

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

Данные важнее названия столбца

Фраза «на статус нужен индекс» не описывает нагрузку. Один статус может встречаться у каждой сотой строки, другой — почти у всех. В нашем опыте open занимал 1% таблицы, closed — 99%. Запросы различались только значением условия, но это означало совершенно разный объём результата до агрегирования: 2000 и 198000 строк.

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

Планировщик принимает решение по оценкам. Русская документация Postgres Professional о статистике PostgreSQL объясняет роль частых значений, распределений и статистики, собираемой ANALYZE. Если данные изменились, старое представление об их распределении может перестать соответствовать действительности. Для взаимосвязанных столбцов существует расширенная статистика; её применимость проверяют по конкретному расхождению оценок, а не включают как общее средство ускорения.

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

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

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

Как читать план без самообмана

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

Начните с узла, где предположение расходится с действительностью. Оценка rows описывает выход узла, а не обязательно все просмотренные строки. У агрегата, который вычисляет одну сумму, результат может быть одной строкой после обработки сотен тысяч записей. Если смотреть только на эту верхнюю единицу, основная работа исчезает из объяснения. При нескольких loops фактические значения узла также нельзя читать как единичное выполнение всего запроса.

Важен и адрес условия. Index Cond участвует в индексном поиске; Filter может отбрасывать уже полученные строки. В нашем базовом плане фильтр оставил 2000 записей и удалил из потока 198000. В варианте с кандидатом использовался Index Scan с условием по state. Путь изменился содержательно, а не только по названию строки в отчёте.

Для опыта мы использовали такой формат:

EXPLAIN (ANALYZE, BUFFERS, WAL, TIMING OFF, FORMAT JSON)
SELECT sum(amount) FROM orders WHERE state='open';

Документация команды EXPLAIN задаёт смысл этих параметров. BUFFERS показывает обращения к буферам, WAL — сведения о сформированном журнале, TIMING OFF убирает подробное измерение времени отдельных узлов. Общее Execution Time остаётся. Это уменьшает часть накладных расходов наблюдения, но не делает инструментирование бесплатным.

Cost не измеряется в миллисекундах. Shared hit не означает чтение диска и не является счётчиком уникальных страниц. В нашем прогретом SELECT физические чтения в отчёте отсутствовали: shared reads равнялись нулю. Поэтому объяснять выигрыш словами «индекс уменьшил дисковую задержку» было бы неверно.

У ANALYZE есть ещё одно буквальное свойство: изменяющая команда действительно изменяет данные. В этом материале UPDATE и INSERT исполнялись только в одноразовом синтетическом контейнере. Оборачивание запроса в откат не превращает произвольный production-запуск в безопасное наблюдение: остаются работа, блокировки и возможные внешние эффекты. Выбирайте среду испытания до измерительного инструмента.

Воспроизводимый опыт на 200 тысячах строк

Чтобы результат можно было проверить, зафиксируем установку. Использовался PostgreSQL 16.15 для aarch64 в Linux-контейнере, ограниченном двумя CPU и 1 GiB памяти. Shared buffers — 128 MiB; данные размещены в tmpfs. Запросы исполнял один клиент. Параллельное исполнение и JIT были выключены. Autovacuum отключён только для короткого управляемого опыта; перед чтением выполнялся VACUUM ANALYZE.

Это настройки исследовательского стенда, а не рекомендации для рабочего сервиса. На машине выполнялись и другие контейнеры, CPU не были выделены исключительно опыту. Поэтому мы сохраняем разброс измерений и не объявляем разницу соседних десятых миллисекунды доказательством.

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

CREATE TABLE orders (
  id integer PRIMARY KEY,
  state text NOT NULL,
  amount integer NOT NULL,
  payload text NOT NULL
) WITH (fillfactor=70);

INSERT INTO orders
SELECT g,
       CASE WHEN g%100=0 THEN 'open' ELSE 'closed' END,
       g%10000,
       repeat('x',80)
FROM generate_series(1,200000) g;

VACUUM (ANALYZE) orders;

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

CREATE INDEX orders_state_idx ON orders (state);

Чтение выполнялось запросом суммы отдельно для open и closed. Поле amount отсутствует в кандидате, поэтому индекс не покрывает весь запрос. Для каждой пары сначала запускался один прогрев, затем пять измерений SELECT. Их медиана становилась результатом пары. Всего было пять пар; итоговая величина — медиана пяти таких медиан. Порядок конфигураций чередовался: базовая первой в парах 1, 3 и 5, индекс первой в парах 2 и 4.

Запись проверялась отдельно. Один UPDATE менял статус первых 10 тысяч строк на противоположный. Один INSERT добавлял ещё 10 тысяч строк с тем же правилом распределения. Перед каждым испытанием записи восстанавливалась исходная таблица для соответствующей операции; перед изменением выполнялся CHECKPOINT. Вставка не наследовала результат предыдущего UPDATE. Изменения действительно исполнялись и завершались, а не многократно накладывались на всё более изменённую таблицу.

Для INSERT и UPDATE получено по пять выполнений каждой конфигурации. Их медианы нельзя считать p95 или прогнозом пропускной способности. Это короткие серверные измерения без клиентской сети и времени завершения COMMIT. Полный порядок, настройки и исходные JSON-планы сохранены в исследовательском пакете статьи.

Что показали чтение, запись и размер

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

×

Собственный синтетический опыт НТА: пять пар вариантов, 200 тысяч строк, прогретый кэш. Полосы показывают медианы, отметки — диапазоны; это серверное исполнение, не прогноз production.

Редкое чтение уверенно отделилось от базового по наблюдаемым диапазонам. Массовое — нет: диапазоны перекрываются, а план остался Seq Scan в обеих конфигурациях. Небольшую разницу медиан мы не называем ускорением. Индекс существует, но для этого условия планировщик его не выбрал; это не означает неисправность индекса.

Вставка во всех пяти измерениях с кандидатом была медленнее соответствующего диапазона базовой конфигурации. У UPDATE разброс заметнее, и диапазоны частично пересекаются. Здесь полезно не ограничиваться временем: отдельно зафиксированы WAL и HOT-обновления. Они показывают изменение выполненной работы, хотя не позволяют разложить каждую миллисекунду по причинам.

Размер нового индекса сразу после построения составил 1 400 832 байта, около 1,336 MiB. Размер heap был одинаковым — 38 109 184 байта. Все индексы вместе занимали 4 513 792 байта без кандидата и 5 914 624 с ним. Первичный ключ никуда не исчез: сравнивать весь объём индексов со словом «ноль» было бы ошибкой.

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

Не складывайте наши медианы в «общий процент экономии». Операции имеют разные частоты, могут выполняться одновременно и ждать разные ресурсы. Число миллисекунд отдельного SELECT не является готовой оценкой пропускной способности сервиса.

Что именно изменилось в плане

Возьмём один реальный план из первой пары, чтобы связать числа с механизмом. Без кандидата сервер просматривал таблицу последовательно. Из 200 тысяч строк фильтр оставлял 2000. После добавления B-tree запрос использовал Index Scan с Index Cond по state='open'. Затем оба варианта вычисляли ту же сумму. Результаты сравнивались во всех парах и совпали.

×

Аннотированные планы одного измерения НТА. Shared hits — обращения к буферам, не уникальные страницы и не чтения диска. В обоих вариантах shared reads равны нулю.

В показанном базовом плане оценка выхода сканирования была 1947 строк при фактических 2000. В индексном — 2153 при тех же 2000. Это не пример катастрофически ошибочной статистики. Мы не использовали его, чтобы доказать необходимость расширенной статистики или изменения параметров стоимости. В данном случае гипотеза касается другого пути к подходящим строкам.

Количество shared hits составило 4652 против 2004. Счётчик помогает увидеть разную работу, но его нельзя механически перевести в «сэкономленные уникальные страницы». Индексный проход тоже обращается к таблице: сумма требует amount, которого нет в ключе индекса. Поэтому иллюстрация не обещает, что индекс хранит весь ответ или заменяет таблицу.

Для массового значения путь не изменился: Seq Scan остался во всех пяти парах. Это важная контрольная точка. Если бы мы показали только редкий запрос, читатель увидел бы подтверждение выгодной гипотезы, но не увидел бы область, где тот же объект ничего не меняет в способе доступа.

Такой разбор полезнее требования «в плане обязательно должен быть Index Scan». У конкретного запроса может быть разумный последовательный проход. Задача проверки — установить соответствие объёма работы цели и данным. Принудительный выбор индекса ради нужного названия узла оставляет без ответа главный вопрос: стал ли необходимый сценарий лучше при допустимых затратах?

Почему запись становится дороже

Вставка новой строки должна учитывать дополнительную структуру доступа. В нашем INSERT сервер сформировал 2 862 188 байт WAL с кандидатом против 2 190 112 без него. Эти значения повторились во всех пяти парах. WAL — журнал предзаписи; здесь сравнивается его объём, а не время доставки на реплику или устойчивой записи на носитель.

Для UPDATE статуса получены 4 739 403 и 3 682 452 байта WAL соответственно. До каждого испытания выполнялся CHECKPOINT, а full-page writes были включены. Поэтому в объёме учитываются условия этого протокола, включая полностраничные образы. Нельзя взять разность и объявить её неизменной ценой каждой будущей операции в рабочей базе.

Есть и наблюдение о HOT. Документация PostgreSQL описывает Heap-Only Tuples как оптимизацию обновления при определённых условиях: индексируемые столбцы не меняются и на странице есть место для новой версии. Для обычного B-tree изменение его ключевого поля нарушает это условие. Исключения для суммарных индексов вроде BRIN не относятся к нашему кандидату.

Без индекса по state счётчики показали 4194 HOT-обновления из 10000. С кандидатом — ноль из 10000. Во всех пяти парах значения совпали. Мы меняли именно state, поэтому наблюдение согласуется с документированным механизмом. Оно не означает, что любой UPDATE любой колонки всегда одинаково обслуживает все индексы.

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

Долгая эксплуатация добавляет ещё одно измерение. Регулярная очистка PostgreSQL связана с повторным использованием места, статистикой и картой видимости. Наш короткий опыт на свежих таблицах не проверяет, как кандидат будет вести себя после недель обновлений. Из него нельзя вывести совет отключить autovacuum, увеличить fillfactor или назначить одинаковый режим обслуживания всем таблицам.

Как выбрать следующего кандидата

После простого индекса часто хочется добавить в него остальные поля запроса. Лучше сначала записать, какую именно работу предполагается убрать. В этой статье следующие варианты рассматриваются как технически обоснованные гипотезы. Мы не измеряли их наравне с orders_state_idx и не объявляем победителя.

Составной B-tree. Если запрос сочетает равенства, диапазон и сортировку, порядок ключей может менять доступный путь. Документация многоколоночных индексов PostgreSQL 16 объясняет роль ведущих столбцов и условий. Полезнее анализировать форму запроса, чем повторять правило «самый селективный столбец всегда первый». Другой набор фильтров или направление чтения может изменить полезность выбранного порядка.

Частичный индекс. Если рабочая задача относится к устойчивому подмножеству, кандидат может включать только его. Но планировщик должен установить, что условие запроса подходит предикату. Описание частичных индексов отдельно предупреждает о распознавании условия при планировании, в том числе при параметризации. Поэтому испытание буквального SQL в консоли не заменяет проверку той формы подготовленного запроса, которую отправляет приложение.

Покрывающий индекс с INCLUDE. Добавленные поля могут сделать данные запроса доступными из индекса, однако возможность index-only scan зависит также от видимости строк. Документация покрывающих индексов объясняет, почему наличие всех полей само по себе не гарантирует отсутствие обращений к heap. Проверяются Heap Fetches и реальный режим обновления; широкие дополнительные поля имеют собственную цену хранения.

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

Иногда правильный следующий шаг вообще не связан с новым индексом: уточнить форму запроса, вернуть актуальную статистику, сократить ненужный результат. В нашей методике это альтернативы для проверки, а не обещание, что любой из них обязательно быстрее. Основание выбора — найденная причина лишней работы.

Создание индекса — отдельное изменение

У кандидата есть две разные стоимости: постоянное присутствие и момент появления. На небольшом стенде создание заняло примерно 90–97 мс по клиентским часам, включая запуск psql через Docker. Мы не используем это число для планирования рабочего окна: оно даже не является чистым серверным временем CREATE INDEX.

Документация CREATE INDEX различает обычное и конкурентное построение. Обычное построение блокирует запись в таблицу. CONCURRENTLY позволяет не блокировать обычные изменения тем же способом, но требует дополнительной работы и ожиданий, имеет ограничения выполнения и может оставить невалидный индекс при ошибке. Слово «конкурентно» не означает «без влияния на сервис».

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

Объём также нужно считать явно. Функции размера PostgreSQL позволяют различать отдельное отношение, все индексы таблицы и совокупный размер. Для карточки изменения запишите размер кандидата после создания, затем наблюдайте его в согласованном цикле эксплуатации. Начальные 1,336 MiB из нашего опыта не описывают рост после длительной записи, расходы копирования и работу резервного контура.

Самостоятельный протокол руководителя может включать три решения до запуска: кто наблюдает изменение, какое событие останавливает попытку и какой подтверждённый путь возврата доступен. Это методическая рекомендация НТА, следующая из различия между свойствами готового индекса и процессом его построения. Конкретные команды, права и последовательность возврата определяются схемой и эксплуатационным регламентом организации.

Когда индекс оставить, изменить или удалить

Решение «оставить» требует положительного результата на нужном сценарии и допустимой цены для остальных. Для нашего учебного кандидата установлен выигрыш редкого чтения, но не измерена смешанная рабочая нагрузка. Корректное решение по этому опыту — передать гипотезу в проверку профиля конкретного сервиса. Разрешение менять production из него не следует.

Решение «изменить» уместно, когда механизм полезен, но форма кандидата не соответствует части обязательных запросов или цена слишком высока. Тогда фиксируется новая гипотеза: другой порядок полей, предикат, покрытие или изменение самого запроса. Меняют один объяснимый фактор и повторяют сравнение, сохраняя исходный вариант как точку отсчёта.

Удаление требует отдельной проверки роли объекта. Накопительная статистика PostgreSQL даёт счётчики использования, но их смысл зависит от окна наблюдения и сбросов. Нулевой idx_scan за короткий период не отвечает на вопрос о редком регламентном запросе. Кроме того, индекс может обеспечивать ограничение целостности. Его ценность не сводится к тому, заметил ли наблюдатель SELECT.

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

Для выбора запросов полезны агрегаты pg_stat_statements: вызовы, суммарное и среднее время помогают найти значимую нагрузку. Они не дают готовый p95 пользовательского пути. Если критерий приёмки задан через хвост задержек, нужны измерения распределения в приложении или нагрузочном испытании. Перекрашивание среднего времени в название p95 не улучшит наблюдаемость.

Протокол решения по индексу

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

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

Есть полезная проверка качества самого протокола. Передайте его инженеру, который не создавал индекс. Он должен восстановить запрос, данные, смысл сравнения и причину решения, не спрашивая «а что здесь запускали?». Такой подход согласуется с проверкой допуска изменения в поставку: происхождение предложения не заменяет проверяемое основание.

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

Тест понедельником

В ближайший рабочий день выберите один медленный запрос. Опишите, кто ждёт его результат и какой задержки достаточно для процесса. Найдите несколько характерных наборов параметров. Подготовьте синтетический профиль с нужным распределением и зафиксируйте версию СУБД.

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

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

Важное уведомление

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