B-tree не умеет искать jsonb @> '{"tag":"t17"}', слово в tsvector и пересечение диапазонов, а на таблице-логе индекс по дате в 43 МБ можно заменить файлом в 32 КБ. Для этого есть GIN, GiST, BRIN и hash: каждый хранит не отсортированные ключи, а другую структуру, и выигрывает только на своей задаче. Устройство B-tree разобрано в статье про B-tree, виды индексов и CREATE INDEX — в основе про индексы.
Стенд: 2 млн строк и семь индексов
Одна таблица, на ней по индексу каждого вида и два B-tree для сравнения. Строк 2 млн: вся база стенда занимает 725 МБ, индексы собираются за секунды.
docker run -d --name dba-gin-gist-brin-hash-when -e POSTGRES_PASSWORD=pg postgres:18
docker exec -i dba-gin-gist-brin-hash-when psql -U postgresCREATE EXTENSION pageinspect;
CREATE EXTENSION pgstattuple;
CREATE EXTENSION pg_walinspect;
CREATE TABLE ev (id int, created timestamptz, doc jsonb, tsv tsvector, span tstzrange, email text);
INSERT INTO ev
SELECT g,
timestamptz '2026-01-01' + g * interval '10 seconds',
jsonb_build_object('user', g % 50000, 'tag', 't' || (g % 1000), 'ok', g % 2 = 0),
to_tsvector('simple', 'w' || (g % 5000) || ' w' || (g % 777) || ' k' || (g % 10)),
tstzrange(timestamptz '2026-01-01' + g * interval '10 seconds',
timestamptz '2026-01-01' + g * interval '10 seconds' + interval '1 min' * (g % 90 + 1)),
'user' || g || '@mail.ru'
FROM generate_series(1, 2000000) g;
VACUUM ANALYZE ev;
\timing on
CREATE INDEX ev_doc_gin ON ev USING gin (doc);
CREATE INDEX ev_tsv_gin ON ev USING gin (tsv);
CREATE INDEX ev_span_gist ON ev USING gist (span);
CREATE INDEX ev_created_brin ON ev USING brin (created);
CREATE INDEX ev_created_btree ON ev (created);
CREATE INDEX ev_email_hash ON ev USING hash (email);
CREATE INDEX ev_email_btree ON ev (email);
\timing off
SELECT c.relname, am.amname, pg_size_pretty(pg_relation_size(c.oid)) AS size
FROM pg_class c JOIN pg_am am ON am.oid = c.relam
WHERE c.relname LIKE 'ev\_%' AND c.relkind = 'i' ORDER BY pg_relation_size(c.oid) DESC;Таблица заняла 356 МБ (45 577 страниц), pg_stats.correlation по created равна 1: строки лежат в порядке времени. Время сборки по \timing: GIN по doc 2,18 с, GIN по tsv 0,86 с, GiST 3,14 с, BRIN 0,10 с, B-tree по created 0,51 с, hash 2,58 с, B-tree по email 2,09 с.
relname | amname | size
------------------+--------+--------
ev_span_gist | gist | 114 MB
ev_email_btree | btree | 77 MB
ev_email_hash | hash | 64 MB
ev_created_btree | btree | 43 MB
ev_doc_gin | gin | 33 MB
ev_tsv_gin | gin | 29 MB
ev_created_brin | brin | 32 kBРазброс размеров в три порядка: от 32 КБ у BRIN до 114 МБ у GiST. Дальше каждый вид — на своей задаче.
GIN: ключ → список адресов, и pending list
По gin/README (строки 95–105) GIN состоит из метастраницы, B-tree ключей, при необходимости «posting tree» для ключей со множеством адресов и, при включённом fastupdate, списка страниц «pending» с ещё не слитыми записями. Один ключ (слово, ключ или значение из jsonb) указывает на много строк таблицы: индекс создан для случаев, когда значений в строке много и одни и те же встречаются во многих строках. Запрос по jsonb, второй прогон, сокращено до узлов, буферов и времени:
SET max_parallel_workers_per_gather = 0;
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF) SELECT count(id) FROM ev WHERE doc @> '{"tag":"t17"}';
SET enable_bitmapscan = off; SET enable_indexscan = off; -- тот же запрос без индекса Bitmap Heap Scan on ev (actual time=4.806..5.956 rows=2000.00 loops=1)
-> Bitmap Index Scan on ev_doc_gin (actual time=4.648..4.648 rows=2000.00 loops=1)
Buffers: shared hit=538
Execution Time: 6.051 ms
Seq Scan on ev (actual time=0.016..249.095 rows=2000.00 loops=1)
Execution Time: 249.289 ms2 000 строк из 2 млн: 6 мс против 249 мс. Запрос tsv @@ to_tsquery('simple', 'w4321 & k1') (400 строк) с индексом по tsv ответил за 0,65 мс, без него за 188 мс. Цена — размер (33 и 29 МБ) и вставка. Чтобы не обновлять дерево ключей на каждый INSERT, GIN при fastupdate = on пишет новые записи в pending list и сливает их пачкой. Вот условие слива в ginHeapTupleFastInsert (ginfast.c, строки 448–460, дословно):
/*
* Force pending list cleanup when it becomes too long. And,
* ginInsertCleanup could take significant amount of time, so we prefer to
* call it when it can do all the work in a single collection cycle. In
* non-vacuum mode, it shouldn't require maintenance_work_mem, so fire it
* while pending list is still small enough to fit into
* gin_pending_list_limit.
*
* ginInsertCleanup() should not be called inside our CRIT_SECTION.
*/
cleanupSize = GinGetPendingListCleanupSize(index);
if (metadata->nPendingPages * GIN_PAGE_FREESIZE > cleanupSize * (Size) 1024)
needCleanup = true;Слив запускает тот INSERT, который переполнил лимит (по умолчанию gin_pending_list_limit = 4MB), а также вызов gin_clean_pending_list, VACUUM и автоанализ таблицы (документация). Читать pending list приходится линейно: README (строки 99–101) прямо говорит, что страницы списка сканируются подряд. Опыт на отдельной таблице: 100 000 строк, автовакуум выключен, чтобы он не слил список раньше времени.
CREATE TABLE g (doc jsonb) WITH (autovacuum_enabled = false);
CREATE INDEX g_gin ON g USING gin (doc);
INSERT INTO g SELECT jsonb_build_object('t' || (n % 1000), n) FROM generate_series(1, 100000) n;
ANALYZE g;
SELECT * FROM pgstatginindex('g_gin');
SET enable_seqscan = off;
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF) SELECT count(*) FROM g WHERE doc ? 't17';
RESET enable_seqscan;
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF) SELECT count(*) FROM g WHERE doc ? 't17';
SELECT gin_clean_pending_list('g_gin');
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF) SELECT count(*) FROM g WHERE doc ? 't17'; version | pending_pages | pending_tuples
---------+---------------+----------------
2 | 491 | 100000
Bitmap Index Scan on g_gin (actual time=5.603..5.603 rows=100.00 loops=1)
Buffers: shared hit=493
Execution Time: 5.714 ms
Seq Scan on g (actual time=0.007..7.731 rows=100.00 loops=1)
Execution Time: 7.758 ms
gin_clean_pending_list = 491
Bitmap Index Scan on g_gin (actual time=0.019..0.019 rows=100.00 loops=1)
Buffers: shared hit=4
Execution Time: 0.176 msВсе 100 000 строк лежали в pending list, 491 страница. Индексный скан, включённый принудительно (enable_seqscan = off), прочитал 493 буфера при 491 странице списка, 5,7 мс. Без принуждения планировщик выбрал Seq Scan и 7,8 мс: ginCostEstimate (selfuncs.c, строки 8498–8502) начинает оценку скана с числа страниц списка:
/*
* Compute cost to begin scan, first of all, pay attention to pending
* list.
*/
entryPagesFetched = numPendingPages;После gin_clean_pending_list те же 100 строк индекс находит за 4 буфера и 0,18 мс, и план снова индексный. Сам fastupdate выигрывает на вставке. 100 000 одиночных INSERT в таблицу с одним GIN-индексом, три пары прогонов подряд (CALL ins(...) — процедура с циклом из одиночных INSERT):
fastupdate = on : 872 ms, 1056 ms, 1055 ms
fastupdate = off: 1154 ms, 1202 ms, 1334 msБез pending list вставка дороже в 1,14–1,32 раза: обычный GIN-индекс обновляет дерево ключей на каждую строку.
GiST: области, а не порядок
GiST не сортирует ключи: каждая внутренняя запись хранит значение, описывающее все записи ниже (для диапазона — объемлющий диапазон), и поиск спускается только в те страницы, чья запись «согласована» с запросом (gist/README, строки 67–73). Поэтому он работает там, где у значений нет линейного порядка: пересечение диапазонов, точки на карте, ближайшие соседи. Пересечение получасового окна с 2 млн диапазонов:
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT count(id) FROM ev WHERE span && tstzrange('2026-03-01 12:00', '2026-03-01 12:30'); Bitmap Index Scan on ev_span_gist (actual time=0.127..0.127 rows=452.00 loops=1)
Buffers: shared hit=7
Execution Time: 0.533 ms
Seq Scan on ev (actual time=49.173..190.379 rows=452.00 loops=1)7 буферов индекса и 0,53 мс против 190 мс без индекса. Цена другая, чем у GIN: индекс 114 МБ, в 3,5 раза больше GIN по doc, и самая долгая сборка из семи (3,14 с). B-tree по span такой запрос бы не выполнил: у диапазона нет порядка, пригодного для &&.
BRIN: min и max на диапазон страниц
BRIN хранит не значения строк, а для каждых 128 страниц таблицы (по умолчанию) пару «минимум и максимум». Вот ветка обновления минимума в brin_minmax_add_value (brin_minmax.c, строки 99–108, дословно):
cmpFn = minmax_get_strategy_procinfo(bdesc, attno, attr->atttypid,
BTLessStrategyNumber);
compar = FunctionCall2Coll(cmpFn, colloid, newval, column->bv_values[0]);
if (DatumGetBool(compar))
{
if (!attr->attbyval)
pfree(DatumGetPointer(column->bv_values[0]));
column->bv_values[0] = datumCopy(newval, attr->attbyval, attr->attlen);
updated = true;
}Новое значение сравнивается с хранимым минимумом (ниже сравнение с максимумом) и при необходимости заменяет его. Запрос отбирает диапазоны, чьи min .. max пересекают условие, и читает их страницы целиком. Первые записи индекса по created:
SELECT blknum, value FROM brin_page_items(get_raw_page('ev_created_brin', 2), 'ev_created_brin') ORDER BY itemoffset LIMIT 2; blknum | value
--------+----------------------------------------------------
0 | {2026-01-01 00:00:10+00 .. 2026-01-01 16:11:30+00}
128 | {2026-01-01 16:11:40+00 .. 2026-01-02 08:11:30+00}Диапазоны идут встык и не пересекаются: именно поэтому выигрывает BRIN. Теперь сутки данных тремя способами: B-tree по created, BRIN, и BRIN на копии таблицы, где строки перемешаны (ORDER BY random(), корреляция −0,003). Второй прогон каждого:
CREATE TABLE ev_shuf AS SELECT id, created FROM ev ORDER BY random();
CREATE INDEX ev_shuf_brin ON ev_shuf USING brin (created);
VACUUM ANALYZE ev_shuf;
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF) SELECT count(id) FROM ev WHERE created >= '2026-03-01' AND created < '2026-03-02';
-- DROP INDEX ev_created_btree; и тот же запрос ещё раз: теперь по BRIN
SET enable_seqscan = off;
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF) SELECT count(id) FROM ev_shuf WHERE created >= '2026-03-01' AND created < '2026-03-02'; Index Scan using ev_created_btree on ev (actual time=0.008..0.818 rows=8640.00 loops=1)
Buffers: shared hit=226
Execution Time: 1.116 ms
Bitmap Heap Scan on ev (actual time=0.124..0.680 rows=8640.00 loops=1)
Heap Blocks: lossy=256
Buffers: shared hit=259
Execution Time: 0.944 ms
Bitmap Heap Scan on ev_shuf (actual time=0.126..110.767 rows=8640.00 loops=1)
Rows Removed by Index Recheck: 1991360
Heap Blocks: lossy=10816
Buffers: shared hit=5251 read=5567 written=2892
Execution Time: 111.156 msНа упорядоченной таблице BRIN отобрал два диапазона (256 страниц), прочитал 259 буферов и ответил за 0,94 мс, как B-tree (226 буферов, 1,1 мс), при размере индекса 32 КБ против 43 МБ. На перемешанной таблице в каждом диапазоне оказались строки со всеми датами, индекс не отсёк ни одного диапазона: прочитаны все 10 816 страниц, 111 мс. Без принуждения планировщик на этой таблице выбрал Seq Scan, 10 816 буферов и 56 мс: честнее индекса, который читает то же самое с накладными расходами.
GIN и BRIN на одной таблице: 33 МБ против 32 КБ
Hash: только равенство
Hash-индекс хранит хеш значения, а не само значение, поэтому умеет одно: =. Диапазон, сортировка и LIKE 'abc%' ему недоступны. Поиск одной почты в 2 млн строк, второй прогон: сначала по hash, потом (hash удалён и пересоздан) по B-tree по email, затем диапазонное условие после удаления B-tree:
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF) SELECT id FROM ev WHERE email = 'user1234567@mail.ru';
DROP INDEX ev_email_hash; -- и то же самое по B-tree
DROP INDEX ev_email_btree;
EXPLAIN (COSTS OFF) SELECT id FROM ev WHERE email > 'user1234567@mail.ru' AND email < 'user1234569'; Index Scan using ev_email_hash on ev (actual time=0.002..0.002 rows=1.00 loops=1)
Buffers: shared hit=2
Index Scan using ev_email_btree on ev (actual time=0.017..0.017 rows=1.00 loops=1)
Buffers: shared hit=4
Seq Scan on ev
Filter: ((email > 'user1234567@mail.ru'::text) AND (email < 'user1234569'::text))Hash: 2 буфера и индекс 64 МБ, B-tree: 4 буфера и 77 МБ (индекс по email из этого же стенда). Выигрыш — несколько буферов на поиск и 13 МБ места ценой потери диапазонов: на >/< hash-индекс не используется, остался Seq Scan. Надёжность hash проверяется журналом. Тысяча вставок в таблицу с hash-индексом, записи WAL по менеджерам ресурсов через pg_walinspect:
CREATE TABLE h (k text);
CREATE INDEX h_hash ON h USING hash (k);
SELECT pg_current_wal_lsn() AS lsn0 \gset
INSERT INTO h SELECT 'k' || n FROM generate_series(1, 1000) n;
SELECT resource_manager, count(*) AS records FROM pg_get_wal_records_info(:'lsn0', pg_current_wal_lsn())
WHERE resource_manager IN ('Hash', 'Heap') GROUP BY 1 ORDER BY 1; resource_manager | records
------------------+---------
Hash | 1000
Heap | 10001000 записей Hash на 1000 строк: индекс журналируется и переживает сбой (документация PostgreSQL 18 называет hash-индексы «fully crash recoverable»). Устройство журнала — в статье про WAL.
Что из этого следует
GIN: когда в строке много ключей. jsonb @>, массивы, полнотекстовый поиск. 6 мс против 249 мс на 2 000 строках из 2 млн. Цена — размер и pending list: после массовой вставки без VACUUM индекс читал 493 буфера вместо 4, и планировщик предпочитал Seq Scan.
fastupdate — выбор между вставкой и чтением. on дешевле на вставке в 1,14–1,32 раза, но читатель платит за каждую страницу списка. Если важна задержка чтения, ставь fastupdate = off или следи, чтобы VACUUM успевал.
GiST: когда у значений нет порядка. Пересечения, геометрия, ближайшие соседи. Индекс в 3,5 раза больше GIN и самый долгий в сборке.
BRIN: журналы и временные ряды. Таблица, где новые строки дописываются в конец, а запросы режут по времени. Условие одно: корреляция в pg_stats близка к ±1.
Hash: только =. Выигрыш — 13 МБ и пара буферов. Диапазона и ORDER BY он не даст, поэтому для обычных ключей B-tree остаётся запасным ответом на «а вдруг понадобится диапазон».
Чек-лист
- Выбирая GIN, сразу спроси, как часто идут вставки:
SELECT * FROM pgstatginindex('имя')показываетpending_pages,gin_clean_pending_list('имя')сливает список. - Для BRIN сначала смотри
SELECT correlation FROM pg_stats WHERE tablename = '...' AND attname = '...'. Далеко от ±1: BRIN не нужен. - Для GiST и GIN замеряй размер индекса до выкладки:
pg_relation_size. Он решает, помещается ли индекс вshared_buffers. - Hash выбирай, только если запросы точно ограничатся равенством.
Контейнер удаляется командой docker rm -f -v dba-gin-gist-brin-hash-when.