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

У попередніх уроках ми розглядали використання індексів для лінійних запитів СУБД (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-Tree групують і жорстко класифікують 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;

Ваша черга

Вивчення умовних (часткових) індексів