Пошук уроків, статей та іншого контенту
Порівняєте GIN і GiST та визначите, які типи даних і оператори найкраще підтримує кожен метод.
GIN та GiST — це методи доступу PostgreSQL, які використовуються для створення індексів. Вони призначені не для звичайного порівняння значень, а для спеціальних типів даних і операторів.
Індекс створюють із конкретним класом операторів. Саме клас операторів визначає:
які значення може індексувати метод;
які оператори підтримуються;
як PostgreSQL шукає збіги;
які компроміси між швидкістю пошуку, розміром індексу та швидкістю оновлень виникають.
Загальний синтаксис:
CREATE INDEX назва_індексу
ON таблиця
USING gin_або_gist (вираз opclass);Наприклад:
CREATE INDEX documents_tags_idx
ON documents
USING gin (tags);Тут gin — метод доступу, а PostgreSQL підбирає клас операторів за типом колонки.
GIN розшифровується як Generalized Inverted Index, тобто узагальнений інвертований індекс.
Звичайний B-tree пов’язує одне значення з рядком таблиці:
значення → рядокGIN ефективний, коли одне поле містить багато компонентів, а пошук виконується за окремими компонентами:
компонент → рядки, у яких він зустрічаєтьсяНаприклад, для масиву тегів:
"postgresql" → рядки 1, 4, 8
"sql" → рядки 1, 2, 8Тому GIN особливо добре підходить для:
масивів;
jsonb;
повнотекстового пошуку через tsvector.
Для масивів GIN підтримує оператори:
@> — масив містить інший масив;
<@ — масив міститься в іншому масиві;
= — рівність масивів;
&& — масиви мають спільні елементи.
Приклад:
CREATE TABLE articles (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title text NOT NULL,
tags text[] NOT NULL DEFAULT '{}'
);
INSERT INTO articles (title, tags) VALUES
('Індекси PostgreSQL', ARRAY['postgresql', 'indexes', 'sql']),
('Робота з JSONB', ARRAY['postgresql', 'jsonb']),
('Основи JavaScript', ARRAY['javascript', 'web']);
CREATE INDEX articles_tags_gin_idx
ON articles
USING gin (tags);
-- Знайти статті, які мають одночасно два теги
SELECT title
FROM articles
WHERE tags @> ARRAY['postgresql', 'indexes'];
-- Знайти статті, у яких є хоча б один із цих тегів
SELECT title
FROM articles
WHERE tags && ARRAY['jsonb', 'javascript'];Для оператора @> важливо, що умова записана над колонкою:
WHERE tags @> ARRAY['postgresql']GIN не індексує довільну логіку над елементами масиву. Він використовує оператори, для яких відповідний клас операторів має індексну підтримку.
jsonbДля jsonb PostgreSQL має два поширені класи операторів:
jsonb_ops — клас за замовчуванням;
jsonb_path_ops — спеціалізований клас для частини операцій містить.
jsonb_opsCREATE TABLE products (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL,
attributes jsonb NOT NULL
);
INSERT INTO products (name, attributes) VALUES
('Ноутбук', '{"brand": "A", "ram": 16, "tags": ["office", "portable"]}'),
('Монітор', '{"brand": "B", "size": 27, "tags": ["office"]}'),
('Клавіатура', '{"brand": "A", "layout": "US"}');
CREATE INDEX products_attributes_gin_idx
ON products
USING gin (attributes);Клас jsonb_ops підтримує, зокрема:
@> — містить;
<@ — міститься в;
? — має ключ або елемент;
?| — має хоча б один із перелічених ключів або елементів;
?& — має всі перелічені ключі або елементи.
-- Об'єкт містить задану пару ключ-значення
SELECT name
FROM products
WHERE attributes @> '{"brand": "A"}';
-- Об'єкт має ключ "ram"
SELECT name
FROM products
WHERE attributes ? 'ram';
-- Об'єкт має хоча б один із цих ключів
SELECT name
FROM products
WHERE attributes ?| ARRAY['ram', 'size'];
-- Об'єкт має всі ці ключі
SELECT name
FROM products
WHERE attributes ?& ARRAY['brand', 'tags'];GIN не робить довільний шлях у JSONB автоматично швидким. Наприклад, умова:
WHERE attributes ->> 'brand' = 'A'може потребувати окремого індексу на вираз:
CREATE INDEX products_brand_idx
ON products ((attributes ->> 'brand'));
SELECT name
FROM products
WHERE attributes ->> 'brand' = 'A';Це вже не індексування всього документа через GIN, а індексування конкретного виразу.
jsonb_path_opsjsonb_path_ops створює зазвичай менший і спеціалізованіший індекс:
CREATE INDEX products_attributes_path_gin_idx
ON products
USING gin (attributes jsonb_path_ops);Він оптимізований для операторів містить:
SELECT name
FROM products
WHERE attributes @> '{"brand": "A"}';На відміну від jsonb_ops, jsonb_path_ops не підтримує оператори перевірки ключів ?, ?| і ?&.
Отже:
потрібні різні оператори над ключами та структурами JSONB — зазвичай обирають jsonb_ops;
основний сценарій — перевірки @> — можна розглядати jsonb_path_ops;
вибір потрібно перевіряти на реальних запитах і даних.
GIN часто використовують для індексу tsvector:
CREATE TABLE documents (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title text NOT NULL,
body text NOT NULL,
search_vector tsvector GENERATED ALWAYS AS (
to_tsvector('simple', coalesce(title, '') || ' ' || coalesce(body, ''))
) STORED
);
INSERT INTO documents (title, body) VALUES
('Індекси PostgreSQL', 'GIN ефективний для масивів і повнотекстового пошуку.'),
('Реляційні бази даних', 'SQL використовується для роботи з таблицями.'),
('JSONB у PostgreSQL', 'JSONB зберігає структуровані документи.');
CREATE INDEX documents_search_gin_idx
ON documents
USING gin (search_vector);
SELECT title
FROM documents
WHERE search_vector @@ plainto_tsquery('simple', 'GIN PostgreSQL');Оператор @@ перевіряє, чи відповідає tsvector пошуковому запиту tsquery.
GIN добре працює для запитів, де потрібно знайти рядки за компонентами складного значення. Проте він має особливості:
індекс може бути великим;
вставки та оновлення можуть бути повільнішими, ніж для B-tree;
оновлення поля з великою кількістю елементів може змінювати багато індексних записів;
GIN не є універсальною заміною B-tree.
GIN варто обирати, коли поле містить набір компонентів і запит шукає наявність, перетин або включення цих компонентів.
GiST розшифровується як Generalized Search Tree, тобто узагальнене дерево пошуку.
GiST — це не один конкретний алгоритм для одного типу даних. Це каркас, який дозволяє реалізувати індекси для різних структур і операторів.
GiST часто використовують для:
діапазонів;
геометричних типів;
пошуку найближчих значень;
обмежень виключення (EXCLUDE);
спеціалізованих типів даних та розширень.
На відміну від GIN, GiST зазвичай зберігає узагальнені представлення значень у дереві. Під час пошуку PostgreSQL може перевірити більше кандидатів, ніж потрібно, а потім відфільтрувати їх точно. Тому GiST допускає хибні збіги на етапі індексу, які виправляються перевіркою самих рядків.
Вбудовані діапазонні типи PostgreSQL мають оператори:
&& — діапазони перетинаються;
@> — діапазон містить значення або інший діапазон;
<@ — діапазон міститься в іншому;
-|- — діапазони суміжні;
<< і >> — один діапазон повністю ліворуч або праворуч від іншого.
GiST є типовим вибором для індексування діапазонів.
CREATE TABLE room_bookings (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
room text NOT NULL,
booked_during tstzrange NOT NULL
);
INSERT INTO room_bookings (room, booked_during) VALUES
('A', '[2026-09-10 10:00+00, 2026-09-10 12:00+00)'),
('A', '[2026-09-10 14:00+00, 2026-09-10 16:00+00)'),
('B', '[2026-09-10 11:00+00, 2026-09-10 13:00+00)');
CREATE INDEX room_bookings_during_gist_idx
ON room_bookings
USING gist (booked_during);
-- Знайти бронювання, які перетинаються із заданим періодом
SELECT room, booked_during
FROM room_bookings
WHERE booked_during &&
tstzrange(
'2026-09-10 11:30+00',
'2026-09-10 15:00+00',
'[)'
);Позначення [) означає:
нижня межа включена;
верхня межа не включена.
Це корисно для часових інтервалів: бронювання, що закінчується о 12:00, не конфліктує з бронюванням, яке починається рівно о 12:00.
GiST може підтримувати пошук найближчих значень за допомогою оператора <->, якщо відповідний тип і клас операторів його підтримують.
Для геометричних даних приклад може виглядати так:
CREATE TABLE places (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL,
location point NOT NULL
);
INSERT INTO places (name, location) VALUES
('Центр', point(0, 0)),
('Північ', point(0, 10)),
('Схід', point(10, 0));
CREATE INDEX places_location_gist_idx
ON places
USING gist (location);
-- Повернути місця у порядку відстані до заданої точки
SELECT name, location
FROM places
ORDER BY location <-> point(2, 1)
LIMIT 2;Точна підтримка операторів залежить від типу даних і класу операторів. Тому для конкретного типу потрібно перевіряти його документацію та план виконання.
GiST застосовують разом із EXCLUDE, коли потрібно заборонити конфліктні комбінації значень.
Наприклад, можна заборонити два бронювання однієї кімнати в періоди, що перетинаються:
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE unique_room_bookings (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
room text NOT NULL,
booked_during tstzrange NOT NULL,
CONSTRAINT no_overlapping_bookings
EXCLUDE USING gist (
room WITH =,
booked_during WITH &&
)
);
INSERT INTO unique_room_bookings (room, booked_during)
VALUES (
'A',
'[2026-09-10 10:00+00, 2026-09-10 12:00+00)'
);
-- Ця операція завершиться помилкою через перетин періодів
INSERT INTO unique_room_bookings (room, booked_during)
VALUES (
'A',
'[2026-09-10 11:00+00, 2026-09-10 13:00+00)'
);btree_gist потрібне тут для того, щоб тип text міг брати участь у GiST-обмеженні з оператором рівності =.
GIN:
інвертований індекс;
один компонент пов’язаний із багатьма рядками;
особливо ефективний для наборів елементів у значенні.
GiST:
узагальнене дерево пошуку;
вузли містять узагальнені області або представлення значень;
підходить для просторових, діапазонних і спеціалізованих операцій.
| Метод | Типові типи даних | Приклади операторів | |---|---|---| | GIN | масиви | @>, <@, &&, = | | GIN | jsonb з jsonb_ops | @>, <@, ?, ?|, ?& | | GIN | jsonb з jsonb_path_ops | переважно @> | | GIN | tsvector | @@ | | GiST | діапазони | &&, @>, <@, -|-, <<, >> | | GiST | геометричні типи | просторові оператори та часто <-> | | GiST | типи з відповідним operator class | оператори, визначені цим класом |
Таблиця описує типові сценарії, але остаточна підтримка залежить від конкретного типу та operator class.
GIN часто добре оптимізує пошук за елементами великих наборів, але оновлення індексу може бути дорогим.
GiST зазвичай має інший компроміс:
часто краще підходить для діапазонів і просторового пошуку;
може підтримувати пошук найближчих значень;
зазвичай має менший або простіший для оновлення індекс, ніж GIN для подібних складних значень;
якість пошуку залежить від того, наскільки добре тип даних розділяється на області.
Не слід робити висновок лише з назви методу. Реальна швидкість залежить від:
кількості рядків;
розподілу значень;
вибірковості умови;
частоти вставок та оновлень;
розміру значень;
конкретного operator class.
Скористайтеся таким практичним правилом:
Масиви та пошук за їхніми елементами — почніть із GIN.
jsonb і перевірки містить — зазвичай GIN.
jsonb і перевірка наявності ключів — GIN із jsonb_ops.
Повнотекстовий пошук за tsvector — зазвичай GIN.
Діапазони та перевірка перетинів — GiST.
Геометричні дані та просторовий пошук — GiST.
Пошук найближчих значень — перевірте GiST і підтримку <->.
Заборона перетинних діапазонів — EXCLUDE USING gist.
Типове створення індексів:
-- Масиви
CREATE INDEX idx_tags_gin
ON articles USING gin (tags);
-- JSONB із широким набором операторів
CREATE INDEX idx_attributes_gin
ON products USING gin (attributes);
-- JSONB, якщо основний запит використовує @>
CREATE INDEX idx_attributes_path_gin
ON products USING gin (attributes jsonb_path_ops);
-- Діапазони
CREATE INDEX idx_period_gist
ON room_bookings USING gist (booked_during);Наявність індексу не гарантує, що PostgreSQL використає його в кожному запиті. Оптимізатор може обрати послідовне сканування, якщо таблиця маленька або умова повертає значну частину рядків.
Перевірити план можна за допомогою:
EXPLAIN (ANALYZE, BUFFERS)
SELECT title
FROM articles
WHERE tags @> ARRAY['postgresql'];Під час перевірки звертайте увагу на:
чи використовується Bitmap Index Scan або інший індексний вузол;
чи відповідає індекс оператору в умові;
скільки рядків очікував оптимізатор;
скільки рядків було знайдено фактично;
чи не повертає умова надто багато результатів.
Для коректних оцінок таблиця має містити реалістичний обсяг даних, а статистика — бути актуальною:
ANALYZE articles;
ANALYZE products;
ANALYZE room_bookings;jsonbGIN допомагає для операторів, які підтримує відповідний клас операторів. Умова з ->> або складним виразом може потребувати індексу на конкретний вираз.
Неправильне припущення:
CREATE INDEX products_attributes_gin_idx
ON products USING gin (attributes);
-- Не кожен такий запит автоматично використовує цей індекс
SELECT *
FROM products
WHERE attributes ->> 'brand' = 'A';Для цього сценарію доречним може бути:
CREATE INDEX products_brand_idx
ON products ((attributes ->> 'brand'));jsonb_path_ops для операторів ?jsonb_path_ops спеціалізований і не підтримує всі оператори jsonb_ops. Якщо потрібна перевірка ключів через ?, ?| або ?&, слід використовувати сумісний клас, зазвичай jsonb_ops.
GIN добре працює з підтримуваними операторами на кшталт @> і &&. Довільні функції, перетворення та умови можуть не мати відповідної індексної підтримки.
Для масивів типовим вибором є GIN. GiST можна застосовувати лише тоді, коли для конкретного типу та потрібних операторів є відповідний клас операторів і це виправдано вимірюваннями.
GIN може бути дорогим для таблиць із частими змінами великих масивів, JSONB-документів або tsvector. Індекс потрібно оцінювати не тільки за швидкістю SELECT, а й за впливом на INSERT, UPDATE, розмір бази та обслуговування.
EXCLUDE з UNIQUEUNIQUE забороняє однакові значення. EXCLUDE може забороняти конфлікти між операціями, наприклад перетин часових діапазонів. Для діапазонів і просторових умов EXCLUDE часто використовує GiST.
GIN — інвертований індекс для пошуку за компонентами складного значення.
Найтиповіші випадки для GIN — масиви, jsonb і повнотекстовий пошук.
GiST — узагальнений індекс для структур, які можна організувати як дерево областей або узагальнених представлень.
Найтиповіші випадки для GiST — діапазони, геометричні дані, пошук найближчих значень і EXCLUDE.
Підтримка операторів визначається не лише методом, а й конкретним класом операторів.
Для jsonb потрібно свідомо вибирати між jsonb_ops і jsonb_path_ops.
Правильний індекс потрібно перевіряти через EXPLAIN (ANALYZE, BUFFERS) на реальних даних.