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

В предыдущих уроках мы рассмотрели использование индексов для линейных запросов СУБД (WHERE country = 'Italy'). А как насчет того, когда мы ищем на двух комбинированных фронтах?

Например, в реальной электронной коммерции нам может потребоваться найти заказы, принадлежащие определенному идентификатору клиента И, попадающие в заданный диапазон времени: WHERE customer_id = 90 AND placed_at >= '2025-01-01'.

Если у вас есть только базовый индекс customer_id, Postgres мгновенно найдет 50 заказов клиента, используя двоичное дерево, но затем ему придется перебирать все 50 из них вручную и последовательно, лениво фильтруя даты на глаз.

Композитные индексы

Мы можем указать СУБД упорядочивать файлы «аналитического словаря» не по одному ключевому измерению, а по нескольким.

CREATE INDEX idx_orders_customer_date ON orders(customer_id, placed_at);

«Составной» индекс очень важен для порядка своих параметров: здесь узлы B-дерева группируются и жестко классифицируются customer_id в блоке (наиболее разборчивом для разделения); внутри них находится поддерево, разветвляющееся с различными значениями времени placed_at в идеально отсортированном порядке.

Эта структура грубо уничтожает предложения WHERE с несколькими вложенными предикатами.

Частичные индексы (фильтрованные индексы)

Каждый индекс требует дискового пространства и ресурсов ЦП для потоков пользовательского ввода. Что, если электронная коммерция требует постоянной сортировки и поиска фильтра, но только для меньшинства записей? (Например: быстрый опрос проверки непрочитанных уведомлений.) Создание индекса «is_read» для всех 10 000 000 уведомлений невероятно утомительно, если «Непрочитанные» представляют только 5 из них.

CREATE INDEX idx_unread_notifs ON notifications(user_id)
  WHERE is_read = false; -- Questa aggiuntiva è l'"Indice Parziale"

База данных создаст молниеносное B-дерево, в котором будут храниться ключи быстрого доступа к БД ТОЛЬКО, если запись непрочитана! Диск полюбит индекс в 5 строк (3 байта) вместо индекса в 10 миллионов. Быстрые SELECT в полной мере выигрывают:

SELECT * FROM notifications WHERE user_id = 2 AND is_read = false;

Ваша очередь

Изучение условных (частичных) индексов