GIN и индексы выражений для JSONB
Выбирайте между индексами GIN с jsonb_path_ops и целевыми индексами выражений с учётом формы запросов.
«GIN и индексы выражений для JSONB» — бесплатный урок PostgreSQL Performance & Query Optimization на CoddyKit. Это урок 2 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения PostgreSQL Performance & Query Optimization, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс PostgreSQL Performance & Query Optimization содержит 4 уроков всего.
Части этого урока еще не переведены и отображаются на английском.
Two Ways to Index JSONB
When you store data in a jsonb column, an unindexed query forces PostgreSQL to read and parse every row. There are two very different tools to fix this:
- GIN index — a general inverted index over the whole document, great for flexible containment and key/value lookups.
- Expression (B-tree) index — a targeted index on one extracted scalar, great for a specific known query shape.
This lesson is about choosing the right one for your query patterns.
The Sample Table
Imagine an events table where each row carries a flexible JSON payload. We will index its data column.
Notice the payload mixes a few common keys (type, user_id) with arbitrary extras.
CREATE TABLE events (
id bigserial PRIMARY KEY,
data jsonb NOT NULL
);
INSERT INTO events (data) VALUES
('{"type": "login", "user_id": 42, "ip": "10.0.0.1"}'),
('{"type": "logout", "user_id": 42}'),
('{"type": "login", "user_id": 99, "mfa": true}');The Default GIN: jsonb_ops
A plain GIN index uses the default jsonb_ops operator class. It indexes every key AND every value as separate entries.
This supports the widest set of operators: containment @>, key existence ?, ?|, and ?&.
The cost: it is larger on disk and slower to build/update because it stores far more entries per row.
CREATE INDEX idx_events_data_gin
ON events USING gin (data);
-- Supports key existence AND containment:
-- WHERE data ? 'mfa'
-- WHERE data @> '{"type":"login"}'The Leaner GIN: jsonb_path_ops
If you only ever use the containment operator @> (and the JSONPath operators @? / @@), prefer the jsonb_path_ops operator class.
- It hashes whole key→value paths into single entries.
- Result: noticeably smaller index and faster containment lookups.
- Trade-off: it does NOT support the key-existence operators
?,?|,?&.
CREATE INDEX idx_events_data_pathops
ON events USING gin (data jsonb_path_ops);
-- Great for:
SELECT id FROM events
WHERE data @> '{"type": "login"}';How Containment Uses the GIN Index
The @> operator asks "does the left document contain the right one?" Both GIN operator classes accelerate it.
Containment is structural: it matches nested keys and values, not just top-level ones. This is why a single GIN index can serve many different filter combinations.
-- Match by one key:
SELECT * FROM events WHERE data @> '{"user_id": 42}';
-- Match by two keys at once (same index):
SELECT * FROM events
WHERE data @> '{"type": "login", "user_id": 42}';
-- Match a nested shape:
SELECT * FROM events WHERE data @> '{"flags": {"beta": true}}';When GIN Falls Short: Range & Sort
GIN is built for equality-style containment. It canNOT help with:
- Range comparisons on an extracted value (
>,<,BETWEEN). - Ordering by a JSON field (
ORDER BY ... LIMIT). - Prefix / pattern matching on a text value.
For these shapes you want a B-tree, and on JSONB that means an expression index.
-- GIN can't accelerate this range filter on an inner number:
SELECT * FROM events
WHERE (data ->> 'user_id')::int > 50
ORDER BY (data ->> 'user_id')::int
LIMIT 10;Building an Expression Index
An expression index stores the result of an expression, not the raw column. You extract one scalar from the JSON and index that as a normal B-tree.
Two operators matter here:
->returnsjsonb.->>returnstext— usually what you cast and index.
-- B-tree on user_id extracted as an integer:
CREATE INDEX idx_events_user_id
ON events (((data ->> 'user_id')::int));
-- Now ranges, sorts and equality all use it:
SELECT * FROM events
WHERE (data ->> 'user_id')::int BETWEEN 40 AND 99
ORDER BY (data ->> 'user_id')::int;Match the Index Expression Exactly
The planner only uses an expression index when the query expression matches the indexed expression token for token, including the cast.
If you index (data ->> 'user_id')::int but query (data ->> 'user_id') as plain text, the index is ignored.
Keep the extraction + cast identical everywhere.
-- Indexed expression:
-- ((data ->> 'user_id')::int)
-- USES the index:
WHERE (data ->> 'user_id')::int = 42
-- IGNORES the index (text vs int mismatch):
WHERE (data ->> 'user_id') = '42'Reading EXPLAIN to Confirm
Never guess which index wins — ask the planner. Use EXPLAIN (ANALYZE, BUFFERS) and look at the node type:
- Bitmap Heap Scan +
Bitmap Index Scan on ...gin→ your GIN index is serving containment. - Index Scan / Index Only Scan on the expression index → your B-tree is serving the range/sort.
- Seq Scan → nothing matched; revisit the expression or operator.
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events
WHERE data @> '{"type": "login"}';
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events
WHERE (data ->> 'user_id')::int = 42;Partial Expression Indexes
If queries only ever target a subset of rows, add a WHERE clause to the index. A partial expression index is smaller and cheaper to maintain because it only stores the rows you actually search.
Here we index user_id only for login events — perfect when that is the only query shape that needs it.
CREATE INDEX idx_events_login_user
ON events (((data ->> 'user_id')::int))
WHERE data @> '{"type": "login"}';Choosing: A Quick Decision Guide
Pick by the shape of your queries, not by habit:
- Flexible filters on many different keys, or key-existence (
?) → GIN jsonb_ops. - Only containment
@>/ JSONPath, want it lean and fast → GIN jsonb_path_ops. - One known field with ranges, sorting, or equality on a scalar → expression B-tree index.
- That field queried on a narrow slice of rows → partial expression index.
It is common and correct to keep BOTH a GIN and one or two expression indexes on the same column.
Quick Check
Test your understanding of the GIN vs expression decision.
Recap
You learned to choose JSONB indexes by query shape:
- GIN jsonb_ops — widest operator support including key existence
?; largest. - GIN jsonb_path_ops — leaner and faster, containment
@>and JSONPath only. - Expression B-tree — one extracted, casted scalar for ranges, sorts, and equality; the query expression must match the index expression exactly.
- Partial expression index — same idea, scoped to a row subset for a smaller footprint.
Always confirm with EXPLAIN (ANALYZE, BUFFERS), and don't hesitate to keep a GIN and one or two expression indexes side by side.
Изучай SQL с ИИ-репетитором — бесплатно
Пиши и запускай код прямо в браузере, получай мгновенную помощь от ИИ-репетитора 24/7 и продолжи учиться на сайте или в приложении.
- Курсы
- 22
- Уроки
- 88
Часто задаваемые вопросы
Урок «GIN и индексы выражений для JSONB» бесплатный?
Да — полный текст урока «GIN и индексы выражений для JSONB» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс PostgreSQL Performance & Query Optimization, подпишись на CoddyKit PRO. Курс PostgreSQL Performance & Query Optimization содержит 4 уроков всего.
Чему я научусь в уроке «GIN и индексы выражений для JSONB»?
Выбирайте между индексами GIN с jsonb_path_ops и целевыми индексами выражений с учётом формы запросов. Ты практикуешь PostgreSQL Performance & Query Optimization с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.
Нужен ли мне опыт, чтобы начать PostgreSQL Performance & Query Optimization?
Предыдущий опыт не требуется. PostgreSQL Performance & Query Optimization на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 2 из 4.
Сколько времени занимает урок «GIN и индексы выражений для JSONB»?
Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.
Можно ли писать и запускать код в этом уроке PostgreSQL Performance & Query Optimization?
Да. Каждый урок PostgreSQL Performance & Query Optimization включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.
Все уроки этого курса
- Операторы JSONB и запросы на вхождение
- GIN и индексы выражений для JSONB
- Запросы к JSONB с помощью JSONPath
- Когда следует нормализовать данные из JSONB