Индексация массивов и операторов (`@>`, `<@`, `&&`) для быстрого поиска

Массивы в 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 оптимально подходит для этого оператора. Постгрес быстро проверяет, какие строки содержат заданный элемент, и возвращает результат.

Результат запроса:

Пример 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. Не стесняйтесь протестировать это самостоятельно на ваших данных!