Оптимизация схемы под запросы: как работает PostgreSQL 16

В PostgreSQL 16 список последних оплаченных заказов может читать таблицу целиком, даже если на ней уже есть несколько индексов. Чтобы схема помогала конкретному запросу, нужно сопоставить фильтры и сортировку с порядком столбцов в индексе, а затем проверить план и цену изменений для записи. Разберём это на одном запросе — без выдуманных замеров: покажем, что именно измерять на вашей базе.

Начинайте с запроса, а не с таблицы

Допустим, приложение показывает последние 50 оплаченных заказов конкретного клиента. Запрос выглядит так:

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

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

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

Что происходит внутри составного индекса

Порядок столбцов решает, какой диапазон можно найти

Для запроса с равенствами по customer_id и status и сортировкой по created_at можно проверить такой индекс:

CREATE INDEX orders_customer_status_created_idx
ON orders (customer_id, status, created_at DESC)
INCLUDE (id, total);

В B-tree записи упорядочены сначала по customer_id, затем внутри этого порядка — по status, а затем по created_at. Поэтому PostgreSQL может найти диапазон для одного клиента и статуса, получить записи в нужном порядке и остановиться после 50 строк. Последний столбец здесь не просто «ещё одно поле»: его позиция связывает поиск с сортировкой.

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

DESC задаёт направление сортировки в индексе. Для одного столбца PostgreSQL способен читать B-tree и в обратном направлении, поэтому явное DESC не всегда меняет результат. В составных индексах направление и сочетание порядков важнее: проверяйте его относительно конкретного ORDER BY, а не добавляйте наугад.

INCLUDE может убрать чтение таблицы, но не обещает этого

id и total добавлены через INCLUDE: они доступны из листовых записей индекса, но не участвуют в его сортировке. Это даёт плану возможность выполнить index-only scan и не обращаться к таблице за этими значениями. Возможность — ключевое слово. PostgreSQL должен ещё проверить видимость строк для транзакции; если карта видимости не позволяет этого сделать, обращения к таблице всё равно понадобятся.

Добавление полей в индекс увеличивает его размер. Большой индекс занимает больше места и может добавлять работу при обновлении строк. INCLUDE полезен, когда запрос часто читает эти столбцы и выигрыш от сокращения обращений к таблице подтверждён планом, а не предположением.

Индекс — это не бесплатная копия таблицы. Он ускоряет одни пути чтения ценой места на диске и дополнительной работы при изменении данных.

Сравните варианты на одном шаблоне нагрузки

Частичный вариант для проверки может выглядеть так:

CREATE INDEX orders_paid_customer_created_idx
ON orders (customer_id, created_at DESC)
INCLUDE (id, total)
WHERE status = 'paid';

Он не заменяет составной индекс во всех случаях. Если приложение запрашивает разные статусы, один индекс с предикатом paid не обслужит остальные значения тем же способом. А если статус передаётся параметром и план строится без известного значения, PostgreSQL может не суметь доказать, что предикат запроса соответствует предикату частичного индекса. Проверяйте фактический план именно для способа выполнения запроса в приложении.

Как проверить гипотезу без мнимых процентов

Сначала зафиксируйте исходный план, затем создайте один кандидатный индекс и повторите измерение на сопоставимых данных и параметрах. Для чтения плана используйте EXPLAIN; EXPLAIN ANALYZE выполняет запрос и добавляет фактические времена и число строк. На нагруженной рабочей базе такой запуск сам создаёт нагрузку, поэтому тяжёлый запрос сначала безопаснее проверять на копии или в согласованное окно.

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

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

• оценку планировщика и фактическое число строк на ключевых шагах: большое расхождение может указывать на неточную статистику;
• наличие лишнего Sort и сколько строк проходит через фильтр до LIMIT;
• shared hit и shared read в BUFFERS: это данные о страницах из буферного кеша и прочитанных страницах, а не универсальная оценка времени;
• фактическое время на повторяемых запусках и при похожем состоянии кеша, а не один удачный результат;
• размер нового индекса и влияние на характерные INSERT, UPDATE и DELETE.

EXPLAIN ANALYZE по одному запуску не доказывает, что изменение улучшило приложение в целом. Он показывает конкретное выполнение в конкретных условиях. Если после создания индекса планировщик всё ещё выбирает последовательное чтение, проверьте, насколько часто запрос встречается, сколько строк совпадает с условиями и актуальна ли статистика. Не пытайтесь заставить план использовать индекс только потому, что он существует.

Термин: селективность — доля строк, которую условие оставляет от всей таблицы. Условие по редкому customer_id обычно селективнее, чем условие по статусу, который есть у большей части заказов; реальное распределение нужно смотреть в своих данных.

Где такая оптимизация ломается

• Индекс хорошо выглядит на тестовой базе, но плохо — на реальной. Маленькая таблица и другая доля оплаченных заказов меняют стоимость плана. Проверяйте данные и параметры, похожие на рабочие.
• Порядок сортировки результата не совпадает с ожиданием приложения. Индекс не исправит запрос, если приложение добавляет другой ORDER BY или использует дополнительные условия. Сопоставьте полный SQL и индекс.
• Новый индекс ускоряет чтение, но замедляет запись. Особенно заметно это при нескольких дублирующих индексах и частых изменениях включённых столбцов. Перед добавлением проверьте список уже существующих индексов и наблюдайте за записью.
• Частичный индекс не используется. Проверьте, может ли планировщик вывести условие индекса из фактического запроса, включая параметры. Если нет, сравните с обычным составным индексом, не делая вывод по одному тесту.
• Ожидали index-only scan, а видите обращения к таблице. INCLUDE не отменяет проверку видимости строк. Смотрите фактический узел плана и число обращений, а не только наличие столбцов в индексе.

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

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

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

Здесь разобран PostgreSQL 16 и один B-tree-паттерн. Настройка статистики, секционирование и денормализация требуют отдельного анализа: они не являются автоматическим следующим шагом после создания индекса.