GIN индексы в PostgreSQL

Сегодня хочу рассказать о GIN индексах в PostgreSQL. Это один из мощных инструментов, которые есть в БД PostgreSQL.

GIN (Generalized Inverted Index) – это инвертированный индекс, который предназначен для ускорения поиска в структурах данных, содержащих составные типы. Он имеет встроенную оптимизацию, позволяющую искать по элементам внутри сложных структур. По своей сути, это обратный индекс, где для каждого уникального элемента хранится список указателей на записи, в которых он встречается. Это дает возможность быстро находить записи, соответствующие запросу.

GIN-индексы особенно эффективны для следующих типов данных:

Массивы

Хранение списков значений
Быстрый поиск по элементам массива
Пример: теги, категории, списки ID

JSONB

Хранение полуструктурированных данных
Быстрый поиск по ключам и значениям
Поддержка сложных запросов к JSON-документам

Полнотекстовый поиск

Индексация текстовых полей
Быстрый поиск по словам и фразам
Поддержка различных языков

Рассмотрим несколько примеров, как использовать GIN индексы. Для начала подготовим табличку и данные, на примере которых посмотрим работу:

CREATE TABLE articles ( id SERIAL PRIMARY KEY, author TEXT, content TEXT, some_structure jsonb NOT NULL, tags text [] ); INSERT INTO articles (author, content, some_structure, tags) VALUES( 'Andrey Kazakov', 'Раздутие индексов Частые операции обновления, вставки и удаления данных в таблицах PostgreSQL приводит к тому, что индексы начинают занимать больше места на диске. Почему это происходит При обновлении, вставке, удалении строк база данных не просто меняет старые значения на новые. Вместо этого создаётся новая версия строки, а старая сохраняется для обеспечения целостности данных в рамках текущих транзакций. К чему это приводит - Индексы занимают больше места на диске - Происходит замедление запросов - необходимо обрабатывать неактуальные данные - Увеличивается нагрузка на память и процессор', '{"category": "post", "code": true}', ARRAY ['postgreSQL', 'sql', 'optimization'] ); CREATE EXTENSION IF NOT EXISTS pg_trgm; INSERT INTO articles (author, content, some_structure, tags) VALUES( 'Andrey Kazakov', 'Сегодня пробовал разрабатывать в Cursor (https://www.cursor.com/). Впечатления самые наилучшие. До этого пользовался VSCode с плагином TabNine. Cursor просто рвет в клочья его! Мощная фишка - конекст поднимается на основе открытого проекта. Автодополнение не так часто ошибается. Плюс интегрированное окно чата, которое видит в какой части файла ты работаешь и сразу предлагает заменить или вставить то, что ты спрашивал. В добавок к этому умная замена по коду, не просто ищет вхождения но и анализирует что помимо вхождений нужно поменять. Однозначно рекомендую!', '{"category": "post", "code": false}', ARRAY ['AI', 'IDE'] ); INSERT INTO articles (author, content, some_structure, tags) VALUES( 'Andrey Kazakov', 'Анализируем свой портфель инвестиций. Нужно было проанализировать свой портфель в Т-Инвестиции, к сожалению, текущий функционал их приложения показывает не очень информативно информацию по секторам и типам инвестиций. За пару вечеров набросал скрипт для анализа портфеля. Код проекта разместил на GitFlic (https://gitflic.ru/project/kazakov-andr-v/using-invest-api) - российский сервис для хранения исходного кода. Пробуем российские альтернативы github. https://telegra.ph/Analiziruem-portfel-s-pomoshchyu-python-i-T-Bank-invest-API-03-19 ', '{"category": "post", "code": true}', ARRAY ['python', 'investment','tbank'] );

Теперь рассмотрим несколько вариантов использования GIN индексов для оптимизации запросов.

Если наша таблица содержит массивы, например как в нашем случае массив тегов, то для того, чтобы ускорить поиск нужно создать GIN индекс на этом массиве:

CREATE INDEX idx_articles_tags ON articles USING GIN(tags);

После удачного создания, наши запросы поиска по записям содержащим определенные теги такого вида:

SELECT * FROM articles WHERE 'postgreSQL' = ANY(tags); SELECT * FROM articles WHERE tags && ARRAY['postgreSQL', 'IDE'];

будут выполняться гораздо эффективнее.

Допустим, необходимо получить записи, у которых в поле JSONB есть определенная информация, то есть json содержит определенные поля с некоторыми значениями. Для начала создадим GIN индекс на это поле:

CREATE INDEX idx_some_structure ON articles USING GIN(some_structure);

Теперь запросы поиска данных внутри структуры some_structure должны выполняться значительно быстрее, благодаря более эффективному поиску внутри JSONB:

SELECT * FROM articles WHERE some_structure @> '{"code": true}';

Есть возможность ускорить поиск по частичному совпадению строк, когда мы используем в запросах LIKE, ILIKE. Для этого, необходимо подключить модуль поддержки триграмм (pg_trgm) для поиска по частичному совпадению строк. Устанавливаем модуль:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

Далее создаем индекс на текстовую колонку :

CREATE INDEX idx_trgm_content ON articles USING GIN(content gin_trgm_ops);

В результате поиск по вхождению строки должен выполняться более эффективно:

SELECT * FROM articles WHERE content ILIKE '%запрос%';

Хотите добавить возможность быстрого поиска по контенту? Можно использовать сочетание GIN-индекса и функции полнотекстового поиска. Сначала добавим необходимые поля и индексы

ALTER TABLE articles ADD COLUMN search_vector tsvector GENERATED ALWAYS AS (to_tsvector('russian', content)) STORED; CREATE INDEX idx_articles_search ON articles USING GIN(search_vector);

И теперь можем искать записи по содержимому по ключевым словам более эффективно:

SELECT * FROM articles WHERE search_vector @@ to_tsquery('russian', 'портфель');

Эффективность поиска по структурам данных: Хорошо подходит для обработки массивов и структурированных данных типа JSONB. Позволяет быстро находить нужные строки даже среди миллионов записей. Хранит только уникальные элементы и их местоположение, в следствии этого более экономный по сравнению с полным сканированием.
Поддержка различных типов данных: Работает с различными типами - строки, числа, массивы, объекты JSONB и даже геопространственные данные.
Подходит для оптимизации полнотекстового поиска: Улучшает производительность запросов с использованием операторов @@ и функций вроде to_tsvector() и to_tsquery(). Особенно полезен там, где требуются операции пересечения (&&), включения (@>), проверки существования элементов массива (?, ?&) и другие специфические условия.

Обновление: Каждый раз, когда изменяется запись, содержащая поля, входящих в GIN индекс, индекс обновляется целиком. Это увеличивает нагрузку на систему при частых изменениях данных.
Больший размер: GIN индекс занимает больше места на диске по сравнению с традиционными B-tree индексами, так как хранит список всех значений, содержащихся в колонке.
Сортировка:: По умолчанию не поддерживает эффективные запросы с сортировками. Стоит учитывать при разработке, можно использовать решения в комбинации с другими индексами.

При работе с массивами, JSONB полями и полнотекстовым поиском стоит рассмотреть использование GIN индексов для данных полей. Это позволит повысить эффективность и производительность БД PostgreSQL. Но, в тоже время, стоит учитывать особенности его осблуживания и требования к системе. Очень аккуратно применять к часто изменяемым данным. Очень хорошая статья о GIN индексах https://habr.com/ru/companies/postgrespro/articles/340978/

Спасибо за внимание! Подписывайся на мой канал :) telegram

Введение

Что такое GIN индекс

Для каких типов данных используется GIN индексы

Примеры использования GIN индексов

Обработка массивов:

Работа с JSONB полями:

Поиск по частичному совпадению строк

Полнотекстовый поиск:

Преимущества GIN индексов

Недостатки GIN индексов

Заключение