Составные и частичные индексы

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

Рассмотрим PostgreSQL 17.11 и снимок catalog-db/lesson-16 из архива серии. В нём шесть курсов. SQL и планы не запускались, ускорение не измерялось. Два кандидата создаются в отдельных транзакциях с ROLLBACK; после каждого этапа временная структура исчезает. Reset восстанавливает учебную схему отдельно, он не является частью обычного чтения.

Порядок ключей следует сценарию

Запрос страницы фронтенда выглядит так:

SELECT id, slug, published_at
FROM catalog.courses
WHERE topic_id = 1 AND status = 'published'
ORDER BY published_at DESC, id DESC
LIMIT 2;

Ожидаются JavaScript, затем HTML. Черновая производительность не проходит отбор. Кандидат составного B-tree начинается с темы, по которой задано равенство, и продолжает выбранным порядком:

BEGIN;
CREATE INDEX courses_topic_time_idx
ON catalog.courses (topic_id, published_at DESC, id DESC);
SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'catalog' AND tablename = 'courses';
ROLLBACK;

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

В PostgreSQL 17 ведущие колонки составного B-tree особенно важны для ограничения просматриваемого диапазона. Равенство по первой колонке и дальнейшие условия следует рассматривать по правилам составных индексов этой версии. Мы не переносим в урок механизмы новых версий и не обещаем одинаковую эффективность любого условия на любой позиции ключа.

Сравните глобальную ленту без темы: ORDER BY published_at DESC, id DESC. Индекс сначала упорядочивает по topic_id, поэтому его внутренние группы тем не образуют автоматически нужный общий порядок времени. Он может оставаться доступной структурой для других действий, но соответствие сценарию уже другое. Один индекс не обязан одинаково хорошо обслуживать страницу темы и общую ленту.

Направление и равные моменты

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

B-tree допускает обратный проход, однако он обращает порядок ключей согласованно. Это не возможность независимо перевернуть одну колонку и оставить любую другую без изменения. Поэтому индексный кандидат следует сверять с полным ORDER BY, а не только с именем первого временного поля.

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

Частичный набор

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

BEGIN;
CREATE INDEX courses_published_topic_time_idx
ON catalog.courses (topic_id, published_at DESC, id DESC)
WHERE status = 'published';
SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'catalog' AND tablename = 'courses';
ROLLBACK;

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

Чтобы использовать частичный индекс как подходящий доступ, планировщик должен доказать, что условие запроса подразумевает условие индекса. Наш SELECT содержит явное status = 'published', поэтому смысловое совпадение понятно. Детали доказательства и ограничения описаны в документации partial indexes PostgreSQL.

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

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

Параметры и доказуемость

В предыдущих уроках мы передавали значения отдельно от SQL. Параметризация остаётся правильным способом работы со значениями, но универсальный подготовленный план может не знать, что параметр статуса всегда равен published. Если условие записано как status = $1, общий план обязан быть корректным и для draft. Он не может безусловно опереться только на опубликованные строки.

В конкретном плане, где значение известно при планировании, решение может отличаться. Поэтому неверно утверждать ни «параметры всегда мешают частичному индексу», ни «параметры никогда не влияют». Следует различать вид плана и ту часть условия, которую нужно доказать. Никакие подобные планы в этой главе не получены фактическим запуском.

Параметр темы сам по себе не разрушает постоянное условие публикации. Запрос с topic_id = $1 AND status = 'published' сохраняет явный предикат индекса. При этом выбор метода и итоговая стоимость всё ещё зависят от данных. Не заменяйте параметризацию пользовательского значения склейкой текста только ради предполагаемого использования структуры.

Читаем кандидата и откат

В lesson.sql после каждого CREATE INDEX находится EXPLAIN без ANALYZE и чтение pg_indexes. Затем выполняется ROLLBACK. В финальном списке должны остаться только индексы ограничений исходной схемы; два учебных кандидата не сохраняются. Поэтому повторение создания после завершённого отката не должно конфликтовать с собственным прежним именем.

Если ручная работа остановилась до ROLLBACK, сначала завершите состояние сессии, а не добавляйте IF NOT EXISTS, чтобы спрятать непонятную структуру. Такая оговорка могла бы оставить индекс с другим определением под знакомым именем. В самостоятельном снимке известное начало и явное завершение важнее бесшумного пропуска команды.

Полный и частичный индексы имеют стоимость хранения и изменений. Частичный кандидат может сократить область, но переход курса между draft и published меняет принадлежность структуре. Решение оценивают по конкретной нагрузке, а не по популярности слова «частичный». На шести строках мы доказали только связь определения с запросом, не быстродействие.

Принадлежность частичному индексу меняется

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

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

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

Оглавление курса.