Порядок ключей, частичные индексы, выражения и дополнительные столбцы
B-tree — основной тип индекса PostgreSQL. Он хранит ключи в упорядоченной
древовидной структуре и подходит для равенства, диапазонов и сортировки:
=, <, <=, >, >=, BETWEEN, а также совместимого ORDER BY.
Порядок столбцов в составном индексе важен. Его выбирают по форме запроса.
Рассмотрим страницу заказов организации:
SELECT id, status, total_amount
FROM marketplace.orders
WHERE org_id = $1
AND created_at >= $2
ORDER BY created_at DESC, id DESC
LIMIT 50;Под неё подходит кандидат:
CREATE INDEX orders_feed_idx
ON marketplace.orders (org_id, created_at DESC, id DESC)
INCLUDE (status, total_amount);org_id стоит первым, потому что запрос задаёт точное равенство;created_at задаёт диапазон и нужный убывающий порядок;id разрешает одинаковое время и делает порядок полным;status и total_amount нужны только в результате, поэтому добавлены как
хранимые значения через INCLUDE.Правило «самый избирательный столбец всегда первый» слишком грубое. Важны равенство, диапазон, сортировка, разные варианты запроса и размер индекса.
Частичный индекс хранит только строки, подходящие под условие:
CREATE INDEX orders_pending_idx
ON marketplace.orders (org_id, created_at)
WHERE status = 'pending';Для очереди ожидающих заказов он меньше полного индекса и дешевле в
обслуживании. Но планировщик должен доказать, что условие запроса гарантирует
status = 'pending'.
Параметр status = $1 в общем подготовленном плане часто не даёт такого
доказательства: во время планирования значение неизвестно и может оказаться
любым.
Индекс может хранить не исходный столбец, а результат функции:
CREATE UNIQUE INDEX users_org_lower_email_key
ON marketplace.app_users (org_id, lower(email));Теперь сочетание организации и почты в нижнем регистре уникально. Условие запроса должно использовать эквивалентное выражение.
До такого решения определите бизнес-смысл: достаточно ли lower для символов
нужных языков, как работает сопоставление строк и считаются ли разные варианты
регистра одним адресом.
INCLUDE кладёт дополнительные значения в листовые записи индекса. Они не
участвуют в поисковом порядке и уникальности.
Index Only Scan возможен, когда все нужные значения есть в индексе и карта
видимости позволяет не проверять таблицу. В часто изменяемой таблице всё равно
будет много Heap Fetches. Тогда расширенный индекс займёт место и замедлит
запись без ожидаемой пользы.
Каждый индекс занимает диск и кеш, создаёт WAL и требует обновления при записи. Перед добавлением сохраните целевой запрос и его план. После добавления сравните:
В лаборатории 23 вы спроектируете индекс для ленты заказов, частичный индекс очереди и уникальность почты по выражению, а затем удалите индекс, польза которого не подтвердилась.
Подробнее: индексы PostgreSQL.
Вопросы ещё не добавлены
Вопросы для этой подтемы ещё не добавлены.
Далее: Другие индексы: GIN, GiST, SP-GiST, BRIN и Hash