Індексування масивів: створення GIN- та BTREE-індексів

Уяви, що в тебе є таблиця з мільйонами записів, і один зі стовпців зберігає масиви. Наприклад, у нас є таблиця products, і кожен продукт може належати до кількох категорій:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    categories TEXT[] -- Масив рядків для зберігання категорій продукту
);

Припустимо, ти хочеш знайти всі продукти, що належать до категорії electronics. Просте використання оператора @> для пошуку може призвести до повного сканування всієї таблиці:

SELECT *
FROM products 
WHERE categories @> ARRAY['electronics'];

Повне сканування (Seq Scan) — це повільно. Особливо, якщо таблиця величезна. Індекси приходять на допомогу, щоб перетворити це на більш швидкий пошук.

Типи індексів для масивів

PostgreSQL підтримує два основних типи індексів, які можна юзати для масивів:

1. GIN (Generalized Inverted Index) — ідеальний для пошуку, якщо треба швидко шукати елементи в масиві або перевіряти перетини.
2. BTREE (Binary Tree) — підходить для інших операцій, наприклад для точного порівняння масивів.

Розглянемо кожен з них детальніше.

1. Індекс GIN: зі швидкістю світла

GIN (Generalized Inverted Index) — це індекс, який круто підходить для роботи з операторами, такими як:

• @> (масив включає елемент або інший масив),
• <@ (масив включений в інший масив),
• && (масиви перетинаються).

Ось як можна створити GIN-індекс для нашого стовпця categories:

CREATE INDEX idx_categories_gin
ON products USING gin(categories);

Після створення індексу виконання запитів стане помітно швидше. Наприклад, запит:

SELECT *
FROM products 
WHERE categories @> ARRAY['electronics'];

буде використовувати твій GIN-індекс.

Цікавий факт: GIN індекс працює за принципом інвертованого списку — він зберігає, які елементи (наприклад, рядки) знаходяться в яких записах. Це схоже на зворотний покажчик, який ти бачиш у книжках, щоб знайти тему за номером сторінки. Зручно, правда?

2. Індекс BTREE: коли важливий порядок

BTREE (Binary Tree) — це стандартний індекс, який юзається в більшості баз даних. Він підходить для операцій, що вимагають точного порівняння масивів, наприклад:

• Перевірка рівності масиву =,
• Порівняння масивів за порядком елементів (>, <).

Створити BTREE-індекс для масиву можна так:

CREATE INDEX idx_categories_btree
ON products USING btree(categories);

Приклад запиту, який може використовувати BTREE-індекс:

SELECT *
FROM products
WHERE categories = ARRAY['electronics', 'gadgets'];

Але варто пам’ятати, що BTREE-індекси не підходять для операторів типу @> або <@. Для них краще юзати GIN.

Приклади використання індексів

Тепер давай поєднаємо теорію з практикою і розглянемо кілька прикладів.

1. Пошук перетину масивів

Припустимо, ми хочемо знайти всі продукти, які пов’язані з категоріями electronics і smartphones, використовуючи оператор && (перетин масивів):

SELECT *
FROM products
WHERE categories && ARRAY['electronics', 'smartphones'];

Для цього ідеально підійде GIN-індекс, який ти вже створив раніше:

CREATE INDEX idx_categories_gin
ON products USING gin(categories);

З таким індексом запит виконається значно швидше завдяки використанню інвертованого списку.

2. Порівняння масивів на рівність

Якщо тобі треба знайти продукти, які належать тільки категоріям electronics і gadgets (у такому порядку), то тут краще юзати BTREE-індекс:

SELECT *
FROM products
WHERE categories = ARRAY['electronics', 'gadgets'];

Створи відповідний індекс:

CREATE INDEX idx_categories_btree
ON products USING btree(categories);

Продуктивність індексів

Індекси допомагають прискорювати запити, але їх використання має і зворотний бік. Наприклад:

• Створення індексу займає час і ресурси. Якщо у тебе дуже велика таблиця, побудова індексу може бути досить довгим процесом.
• Оновлення таблиці. Кожного разу, коли ти вставляєш нові рядки або оновлюєш існуючі дані, індекси теж оновлюються. Це може сповільнити операції INSERT і UPDATE.

Але, у більшості випадків вигода від швидкого виконання запитів переважає ці витрати.

Як обрати: GIN чи BTREE?

Ось невеличка таблиця, яка допоможе тобі вибрати потрібний індекс для задач: