Індексація масивів і операторів (`@>`, `<@`, `&&`) для швидкого пошуку

Масиви в PostgreSQL дають можливість зберігати купу значень в одній клітинці таблиці. Це нереально зручно, коли треба згрупувати пов’язані дані, наприклад, список тегів для статті або перелік категорій продукту. Але як тільки ми починаємо шукати, фільтрувати чи перетинати масиви, швидкодія може сильно просісти. Саме тому індексація масивів — це справжній порятунок. Індекси дозволяють прискорити такі операції, як:

• перевірка, чи містить масив конкретний елемент,
• пошук масивів, які містять задані елементи,
• перевірка на перетин масивів.

Оператори для роботи з масивами

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

@> (contains) — перевіряє, чи містить масив усі елементи з іншого масиву.

SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

Тут ми шукаємо курси, які містять тег "SQL".

<@ (is contained by) — перевіряє, чи міститься один масив в іншому.

SELECT *
FROM courses
WHERE ARRAY['PostgreSQL', 'SQL'] <@ tags;

Тут ми шукаємо курси, теги яких включають усі елементи масиву ARRAY['PostgreSQL', 'SQL'].

&& (overlap) — перевіряє, чи є перетин між масивами.

SELECT *
FROM courses
WHERE tags && ARRAY['NoSQL', 'Big Data'];

Цей запит знаходить курси, які містять хоча б один із тегів "NoSQL" або "Big Data".

Як допомагає індексація?

Уяви, що у нас є таблиця courses з мільйонами записів, і ти виконуєш запит з використанням одного з операторів вище. Без індексу PostgreSQL буде змушений послідовно перевіряти кожен рядок — операція, яка може тривати вічність (особливо, якщо у тебе терпіння, як у програміста, що чекає компіляції).

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

1. GIN (Generalized Inverted Index) — найкращий вибір для масивів.
2. BTREE — використовується для порівняння масивів повністю.

Приклад: Створюємо індекс для масивів

Створимо невеличку таблицю з масивами, щоб протестувати все на практиці.

CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    tags TEXT[] NOT NULL
);

Додаємо кілька записів:

INSERT INTO courses (name, tags)
VALUES
    ('Основи SQL', ARRAY['SQL', 'PostgreSQL', 'Бази даних']),
    ('Робота з Big Data', ARRAY['Hadoop', 'Big Data', 'NoSQL']),
    ('Розробка на Python', ARRAY['Python', 'Web', 'Дані']),
    ('Курс по PostgreSQL', ARRAY['PostgreSQL', 'Advanced', 'SQL']);

Ось як виглядатиме така табличка:

Без індексу: повільний пошук

Тепер уяви, що ми хочемо знайти всі курси, які містять тег SQL.

EXPLAIN ANALYZE
SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

Цей запит виконається, але якщо даних буде багато, він стане дуже повільним. PostgreSQL зробить так званий послідовний перебір (Sequential Scan), тобто перевірить кожен рядок таблиці.

Приблизний результат запиту:

Створення індексу GIN

Щоб прискорити пошук, створимо індекс типу GIN:

CREATE INDEX idx_courses_tags
ON courses USING GIN (tags);

Спробуємо виконати той самий запит:

EXPLAIN ANALYZE
SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

Тепер PostgreSQL буде використовувати створений індекс GIN, що суттєво скоротить час виконання запиту.

Якщо раніше використовувався послідовний перебір (Seq Scan), то тепер у плані виконання буде видно Bitmap Index Scan:

Як працюють оператори з індексами?

Приклад 1: Оператор @>

Запит:

SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

Індекс GIN ідеально підходить для цього оператора. Postgres швидко перевіряє, які рядки містять заданий елемент, і повертає результат.

Результат запиту:

Приклад 2: Оператор &&

Запит:

SELECT *
FROM courses
WHERE tags && ARRAY['NoSQL', 'Big Data'];

Цей оператор перевіряє перетин масивів: він поверне рядки, де масив tags перетинається хоча б з одним елементом із переданого масиву.

Індекс GIN знову робить свою магію — пошук відбувається швидко, навіть при великих обсягах даних.

Результат запиту:

Індексація та оптимізація

Працюючи з масивами, дотримуйся таких порад:

1. Використовуй GIN індекси для пошуку всередині масивів. Вони швидші, ніж послідовний перебір.
2. Додавай індекси тільки на стовпці, які реально часто використовуються в запитах. Індексація займає місце і сповільнює вставку даних, тому не треба індексувати все підряд.
3. Профілюй запити за допомогою EXPLAIN і EXPLAIN ANALYZE, щоб зрозуміти, чи дійсно використовується твій індекс.

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

Давай подивимось, як створювати індекси під конкретні типи операцій з масивами і навіщо це треба на практиці.

Індекс для оператора @>

Припустимо, у нас вже є така таблиця courses:

Щоб прискорити запити з оператором @> (масив містить елемент), створимо GIN-індекс:

CREATE INDEX idx_courses_tags_gin
ON courses USING GIN (tags);

Тепер виконаємо запит:

SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

Результат:

Індекс для операторів @>, <@, і &&

Початкова таблиця така ж, як у попередньому прикладі.

Оскільки оператори @>, <@ і && всі ефективно працюють з індексами типу GIN, можна створити один універсальний індекс, який буде прискорювати запити з будь-яким із цих операторів:

CREATE INDEX idx_tags
ON courses USING GIN (tags);

Приклади запитів і їх результати:

• @> — перевірка, чи містить масив вказані елементи:

SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

---

• <@ — перевірка, чи міститься масив в іншому масиві:

SELECT *
FROM courses
WHERE tags <@ ARRAY['SQL', 'PostgreSQL', 'Advanced', 'Big Data', 'NoSQL', 'Python'];

---

• && — перевірка перетину масивів:

SELECT *
FROM courses
WHERE tags && ARRAY['NoSQL', 'Big Data'];

Спробуємо щось складніше

Створимо запит, який поверне курси, де серед тегів є хоча б одне перетинання з заданим списком ['Python', 'SQL', 'NoSQL']:

SELECT *
FROM courses
WHERE tags && ARRAY['Python', 'SQL', 'NoSQL'];

Вихід:

З GIN індексом такий запит виконується миттєво, навіть якщо у таблиці мільйони записів.

Типові помилки при роботі з масивами

Індекс не використовується: якщо у виводі EXPLAIN ти бачиш Seq Scan, перевір, що індекс створено і що оператор, який ти використовуєш, дійсно підтримує індексацію.

Рідке використання масиву: якщо стовпець з масивами рідко бере участь у запитах або оновлюється, індекс може займати зайве місце і не приносити помітної користі.

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

Тепер у тебе є всі потрібні інструменти для ефективної роботи з масивами в PostgreSQL — прискорюй запити за допомогою операторів @>, <@, && і індексів GIN. Не соромся протестувати це самостійно на своїх даних!