Поиск по городу и дате отличается от поиска только по дате. На примере справочника разбираем составной индекс PostgreSQL и ограничения правила о первом поле.
Когда одного признака недостаточно
Представим условный сервис доставки: оператор часто ищет отправления определённого города за выбранный день. У таблицы есть город, дата отправки и номер заказа. Указатель только по городу помогает найти его отправления, но внутри выбранной группы ещё предстоит выделить нужный день. Возникает вопрос: можно ли заранее упорядочить сведения по двум признакам?
Составной индекс охватывает несколько полей одной таблицы. Его назначение удобно понимать через справочник, где действует последовательность правил расположения. Ниже рассматривается распространённый тип индекса PostgreSQL — B-дерево. У других типов устройство и роль порядка полей могут отличаться, поэтому переносить правило на любой индекс без уточнения нельзя.
Сначала город, затем дата внутри города
Вообразим бумажный каталог отправлений. Сначала карточки разбиты по городам, а внутри каждого города расположены по датам. Чтобы найти Казань за понедельник, человек открывает соответствующий раздел и нужный участок дней. Такой порядок удобен именно потому, что запрос согласуется с устройством справочника.
В документации PostgreSQL о составных индексах объясняется значение ведущих полей для B-дерева. Ограничения по первым полям помогают сузить просматриваемый участок. Порядок — это организация ключа поиска, а не заявление, что первое поле всегда важнее второго для бизнеса. Условия задачи определяют полезность такого расположения.
Если известна только дата
Теперь оператор хочет увидеть все отправления понедельника независимо от города. В бумажном каталоге нужный день находится внутри разных городских разделов. Придётся просмотреть несколько участков вместо одного. Если же сначала расположить карточки по дате, а затем по городу, такой вопрос становится удобнее, зато меняется картина для поиска только по городу.
Это объясняет, почему одинаковый набор полей в разном порядке не означает одинаковую пользу. Однако утверждать «без первого поля индекс вообще не используется» неверно. В текущем руководстве PostgreSQL описаны условия, при которых база может применять пропускающий поиск по группам ведущего поля. Выбор зависит от данных, версии системы и оценки затрат.
Составной указатель и два отдельных — разные варианты
Можно представить два независимых бумажных указателя: один по городу, другой по дате. Они не превращаются автоматически в общий справочник с порядком «город, затем дата». Но результаты поиска по отдельным указателям иногда можно сопоставить и получить записи, удовлетворяющие обоим условиям.
PostgreSQL умеет комбинировать несколько индексов. В документации этот вариант рассматривается наряду с составным индексом, с собственными затратами и ограничениями. Поэтому формула «два поля — обязательно один составной индекс» слишком груба. Полезно учитывать частоту разных вопросов: только город, только дата или их сочетание.
Цена лишних указателей
Индекс занимает место и требует сопровождения при изменении данных. Дополнительный справочник может ускорить подходящий поиск, но увеличивает объём поддерживаемой информации. Если добавить все мыслимые сочетания полей, выигрыш на отдельных вопросах ещё не докажет, что системе в целом стало лучше.
В учебном сервисе предложим записать три частых сценария поиска и посмотреть на их долю в работе. Затем обсуждать порядок полей и способы проверки результата. Нельзя обещать конкретное ускорение только по названию индекса. Нужно учитывать число разных значений, объём таблицы, характер изменений и то, какую работу реально выбирает система.
Какие вопросы задать до изменения базы
Начните с понятного описания: что ищут пользователи, какие признаки известны одновременно и какие результаты нужны в порядке дат? Затем уточните тип индекса и действующую версию базы. Поиск по одинаковому сочетанию признаков в большой и маленькой таблице может иметь разную цену. Полезно сравнивать наблюдения на сопоставимых условиях, а не один случайный запуск.
Общее назначение указателей объясняем в статье об индексах, а правила связанного изменения сведений — в материале о транзакциях. Изучать устройство таблиц и осмысленный поиск можно на программе SQL инженер. Для начала достаточно объяснить словами, почему каталог «город, затем дата» отвечает на разные вопросы с разным удобством.
