PHP小码农

Appearance

GIN 索引原理 ​

前面三节讲的 B-Tree 与哈希索引有一个共同前提：一个列值就是一个整体。但它们都回答不了「这个 jsonb 文档里是否包含 brand=Acme」「这个数组里是否有 '5g'」「这段正文里是否出现过 postgres」这类问题——因为答案不在「值本身」，而在「值内部拆出来的一个个元素」里。

GIN（Generalized Inverted Index，通用倒排索引）就是为这种「一个列里存多个值」的场景而生的索引类型。本节先讲清它的倒排结构与三个组成部分，再逐个过一遍它支持的操作符类，重点讲透 jsonb_ops 与 jsonb_path_ops 的取舍，最后说明它的空间与写入代价，以及它不适合哪些场景。

前置知识

阅读本节前，建议先了解：哈希索引与 B-Tree 对比

基础概念 ​

倒排索引：把「行 → 值」翻过来 ​

正排（forward）结构描述的是「这一行有哪些值」；倒排（inverted）结构描述的是「这个值出现在哪些行」。二者互为转置：

text
正排（表数据）                       倒排（GIN 索引）
row 1 → {a, b}                       a → {1, 3}
row 2 → {c}            ⇄             b → {1}
row 3 → {a, c}                       c → {2, 3}
  • 查询 包含 a 的行：查倒排表里的 a，直接得到 {1, 3}。
  • 查询 包含 a 且包含 c 的行：取两个行号集合的交集 → {3}。

这就是「多个值同时命中」能被快速回答的原因：交集比逐个扫描快得多。B-Tree 做不到，因为它只知道「整行的值」，不知道这一行内部有哪几个元素。

GIN 的三个组成部分 ​

组成部分英文作用
键树entry tree一颗 B-Tree，存放排好序的「键」（如 JSONB 的键、词位、数组元素）
行号列表posting list每个键下挂一个有序的行号（TID）列表，就是倒排表
待处理列表pending listfastupdate 开启时，新写入的项先暂存在这里，攒够再批量合并进键树
text
                    ┌──────────────────────────────┐
   entry tree       │  'acme'      →  posting list │
  (B-Tree 存键)     │  'blue'      →  posting list │
                    │  'phone'     →  posting list │
                    └──────────────────────────────┘
                             ▲
                             │ 批量合并（超过 gin_pending_list_limit 时触发）
                             │
                    ┌──────────────────────────────┐
                    │  pending list（无序的待处理项）│
                    └──────────────────────────────┘
                             ▲
                             │ INSERT / UPDATE
                             │
                    ┌──────────────────────────────┐
                    │  ≤ 8KB 或列值可 TOAST 压缩时    │
                    │  走「单个元组」优化，不进 pending │
                    └──────────────────────────────┘

GIN 的第四个隐藏部分

对「不超过 8KB 且可压缩」的列值，GIN 会把整行的索引项存成一张紧凑的位图（GIN tuple），直接塞进键树，不写进 pending list。这是小数组、小 JSONB 写入开销远低于直觉的原因。

为「一个列里存多个值」而生 ​

GIN 的典型使用场景完全由「元素可枚举」这一点决定：

数据类型拆出来的「键」是什么内置操作符类
text[] / int[] 等数组每个数组元素array_ops
jsonb文档里每个键、每个值jsonb_ops
jsonb（路径哈希）每个「路径 + 值」组合jsonb_path_ops
tsvector每个词位tsvector_ops
text（三元组）每个 3 字符组gin_trgm_ops（pg_trgm）
hstore每个键与每个值hstore_ops
范围类型范围边界range_ops（一般用 GiST 更合适）

一个常见误解

「一个值」和「一个元素」不是一回事。上面的 {"brand": "Acme"} 在 jsonb_ops 下会拆成 brand、Acme 两个索引项——键也建项，值也建项。这是 jsonb_ops 体积大的直接原因。

fastupdate 机制 ​

写入 GIN 的最大开销来自「动态更新一棵 B-Tree」：每插入一行，可能要往键树里插入或更新若干个键。为此 PostgreSQL 引入了 fastupdate（默认 on）：

状态写入路径查询代价
fastupdate = off每行直接更新键树，写慢只扫键树，查快且稳定
fastupdate = on（默认）先写 pending list（追加写，写快）键树 + pending list 都要扫，查慢

代价是明确的：写快换查慢。关掉之后写入变慢，但查询响应时间稳定。这个取舍在 fastupdate 与 gin_pending_list_limit 里会完整展开。

GIN 支持的操作符类 ​

操作符类适用类型支持的操作符备注
array_ops（默认）任意数组@> <@ && == 之外都是集合语义
jsonb_ops（默认）jsonb@> @? @@ ? ?| ?&功能最全，体积最大
jsonb_path_opsjsonb@> @? @@只索引路径哈希，体积小、@> 更快
tsvector_opstsvector@@全文检索主力
gin_trgm_opstext 等LIKE ILIKE ~ %需 pg_trgm 扩展
hstore_opshstore@> ? ?| ?&需 hstore 扩展

语法与用法 ​

text
-- 最简形式：使用类型的默认操作符类
CREATE [ UNIQUE ] INDEX 索引名 ON 表名 USING gin (列名);

-- 显式指定操作符类（jsonb 的两种、trgm 等必须这样写）
CREATE INDEX 索引名 ON 表名 USING gin (列名 操作符类名);

-- 表达式索引（全文检索常见写法）
CREATE INDEX 索引名 ON 表名 USING gin (to_tsvector('english', 列名));

-- 存储参数
CREATE INDEX 索引名 ON 表名 USING gin (列名) WITH (fastupdate = off, gin_pending_list_limit = 16MB);

-- 部分索引
CREATE INDEX 索引名 ON 表名 USING gin (列名) WHERE 条件;

-- 在线建索引（不阻塞读写）
CREATE INDEX CONCURRENTLY 索引名 ON 表名 USING gin (列名);

-- 修改已有索引的参数
ALTER INDEX 索引名 SET (fastupdate = off);
ALTER INDEX 索引名 SET (gin_pending_list_limit = '32MB');

-- 手动合并待处理列表
SELECT gin_clean_pending_list('索引名');

最小可运行示例：

sql
CREATE TABLE demo_gin (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tags text[] NOT NULL
);

CREATE INDEX idx_demo_gin_tags ON demo_gin USING gin (tags);
text
CREATE TABLE
CREATE INDEX

详细说明 ​

数组上的倒排项长什么样 ​

先看一个能直接观察倒排结构的例子：

sql
DROP TABLE IF EXISTS assets;

CREATE TABLE assets (
  id   integer PRIMARY KEY,
  tags text[] NOT NULL
);

INSERT INTO assets (id, tags) VALUES
  (1, ARRAY['a', 'b']),
  (2, ARRAY['c']),
  (3, ARRAY['a', 'c']);

CREATE INDEX idx_assets_tags ON assets USING gin (tags);
ANALYZE assets;
text
DROP TABLE
CREATE TABLE
INSERT 0 3
CREATE INDEX
ANALYZE

现在查「同时包含 a 和 c 的行」，GIN 做的事情是取两个行号集合的交集：

sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM assets WHERE tags @> ARRAY['a', 'c'];
text
                              QUERY PLAN
----------------------------------------------------------------------
 Bitmap Heap Scan on assets (actual rows=1 loops=1)
   Recheck Cond: (tags @> '{a,c}'::text[])
   Heap Blocks: exact=1
   Buffers: shared hit=9
   ->  Bitmap Index Scan on idx_assets_tags (actual rows=1 loops=1)
         Index Cond: (tags @> '{a,c}'::text[])
         Buffers: shared hit=5
(7 rows)

Index Cond 里是 tags @> '{a,c}'，索引项就是 a 与 c 两把键，交集给出第 3 行。

jsonb_ops vs jsonb_path_ops（本节最重要的一节） ​

这是 GIN 在 JSONB 上的核心选择点，必须讲透。

jsonb_ops（默认） 为文档里的每个键、每个值各建一个索引项：

sql
DROP TABLE IF EXISTS docs;

CREATE TABLE docs (
  id      integer PRIMARY KEY,
  payload jsonb NOT NULL
);

INSERT INTO docs (id, payload) VALUES
  (1, '{"brand": "Acme", "screen": {"type": "OLED"}}'),
  (2, '{"brand": "Acme", "screen": {"type": "LCD"}}'),
  (3, '{"brand": "Other"}');

CREATE INDEX idx_docs_ops ON docs USING gin (payload);
ANALYZE docs;

对第 1 行，jsonb_ops 会建出这些键：brand、Acme、screen、type、OLED。注意 brand 这个「键」也单独成项——这正是它能支持 ? 系列存在性查询的原因，也是它体积大的原因。

jsonb_path_ops 只为「从根到叶的完整路径 + 值」的组合哈希建项。同样是第 1 行，它建的是 brand=Acme、screen.type=OLED 这样的组合，而不会单独为 brand 建项。因此：

对比项jsonb_ops（默认）jsonb_path_ops
索引内容每个键、每个值各一项每个「路径 + 值」组合一项
@>支持支持，更快
? / ?| / ?&支持不支持
@? / @@（jsonpath）支持支持
索引体积大约为前者的 1/3
查询速度较慢较快
适用场景需要键存在性判断只做包含 / 路径判断

用一段 SQL 把体积差异量化：

sql
CREATE INDEX idx_docs_path_ops ON docs USING gin (payload jsonb_path_ops);

SELECT
  indexrelid::regclass AS index_name,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE relname = 'docs'
ORDER BY pg_relation_size(indexrelid) DESC;
text
      index_name      | index_size
----------------------+------------
 idx_docs_ops         | 32 kB
 idx_docs_path_ops    | 16 kB
(2 rows)

数据只有 3 行时已经是 2 倍差距；在字段多、键值组合多的生产数据上，jsonb_path_ops 通常只占 jsonb_ops 的三分之一左右。

选择依据一句话总结：查询里用到了 ? / ?| / ?& 就必须用 jsonb_ops；只用 @> 就用 jsonb_path_ops。

再强调一次这个最容易踩的坑——jsonb_path_ops 上执行 ? 会静默退化为顺序扫描：

sql
-- 有 jsonb_path_ops 索引，但 ? 用不上它
EXPLAIN (COSTS OFF)
SELECT id FROM docs WHERE payload ? 'screen';
text
             QUERY PLAN
-------------------------------------
 Seq Scan on docs
   Filter: (payload ? 'screen'::text)
(2 rows)

优化器不会报错，只是不用索引。实操层面的完整对照见 GIN 索引与 JSONB / 数组。

全文检索上的 GIN ​

tsvector 是「词位集合」，天然就是倒排结构，因此 GIN 是全文检索的首选索引：

sql
DROP TABLE IF EXISTS notes;

CREATE TABLE notes (
  id      integer PRIMARY KEY,
  content text NOT NULL,
  tsv     tsvector GENERATED ALWAYS AS (to_tsvector('english', content)) STORED
);

INSERT INTO notes (id, content) VALUES
  (1, 'PostgreSQL provides full text search'),
  (2, 'GIN index accelerates full text search');

CREATE INDEX idx_notes_tsv ON notes USING gin (tsv);
ANALYZE notes;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM notes WHERE tsv @@ to_tsquery('english', 'search');
text
                              QUERY PLAN
----------------------------------------------------------------------
 Bitmap Heap Scan on notes (actual rows=2 loops=1)
   Recheck Cond: (tsv @@ '''search'''::tsquery)
   Heap Blocks: exact=1
   Buffers: shared hit=10
   ->  Bitmap Index Scan on idx_notes_tsv (actual rows=2 loops=1)
         Index Cond: (tsv @@ '''search'''::tsquery)
         Buffers: shared hit=6
(7 rows)

Recheck 必须存在

全文检索的 tsvector 带权重与位置，GIN 只按词位粗过滤（可能返回「包含该词位但不满足短语/权重约束」的假阳性），所以计划里一定有 Recheck Cond。这不是性能问题，而是正确性保证。

fastupdate 与写入代价 ​

GIN 的写入瓶颈是结构性的：一个列值会拆成多个索引项，插入一行就是多次 B-Tree 更新。

数据形态一行产生的索引项数
tags text[] 含 3 个元素3
payload 含 10 个键值对约 20（键 + 值）
一篇 500 词的正文数百个词位

对比 B-Tree：一行只产生 1 个索引项。所以 GIN 的写入通常是 B-Tree 的数倍到数十倍代价，索引体积也常常比数据本身还大。

fastupdate 的作用就是把「同步更新键树」改成「追加到 pending list + 批量合并」：

sql
-- 查看索引当前的存储参数
SELECT
  c.relname AS index_name,
  pg_size_pretty(pg_relation_size(c.oid)) AS index_size
FROM pg_class c
WHERE c.relname = 'idx_notes_tsv';
text
   index_name    | index_size
-----------------+------------
 idx_notes_tsv   | 16 kB
(1 row)
sql
-- 关闭 fastupdate（写入变慢，查询变稳定）
ALTER INDEX idx_notes_tsv SET (fastupdate = off);
text
ALTER INDEX

pending list 的容量由 gin_pending_list_limit 控制（默认 4MB），可全局设置也可索引级设置：

sql
ALTER INDEX idx_notes_tsv SET (gin_pending_list_limit = '16MB');
text
ALTER INDEX

GIN 的空间与维护代价 ​

GIN 是最容易膨胀的索引类型之一，原因同样是倒排结构：

现象原因
索引比数据大一个值产生多个索引项，且 posting list 有额外元数据
删除不立即回收删除行只是从 posting list 摘除 TID，页内空间不一定能被复用
频繁更新后膨胀同一行的项反复增删，留下不可复用的空洞
pgstatindex 不适用它只支持 B-Tree，GIN 要看 pgstattuple 或 pg_relation_size

膨胀的观察与清理：

sql
-- 需要 pgstattuple 扩展
CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT * FROM pgstatginindex('idx_notes_tsv');
text
 version | pending_pages | pending_tuples
---------+---------------+----------------
       2 |             0 |              0
(1 row)
sql
-- 重建索引（在线）
REINDEX INDEX CONCURRENTLY idx_notes_tsv;
text
REINDEX

VACUUM 负责清理 GIN 里的 dead entries，但不会主动合并 pending list；手动合并要用 gin_clean_pending_list()：

sql
SELECT gin_clean_pending_list('idx_notes_tsv') AS cleaned_pages;
text
 cleaned_pages
---------------
             0
(1 row)

GIN 不适合的场景 ​

选错索引类型和没建索引一样无效。以下场景不要用 GIN：

场景为什么不适合应该用什么
高选择率的普通等值查询倒排项多、体积大且不必要B-Tree
需要范围扫描（> < BETWEEN）GIN 无全序，无法做区间扫B-Tree
需要按索引排序（ORDER BY）GIN 输出无序，且无 KNNB-Tree / GiST
需要前缀匹配（LIKE 'x%'）默认 opclass 不支持B-Tree + text_pattern_ops
需要最近邻（<->）GIN 不支持 KNNGiST
列值只有单个标量没有「多个值」可拆B-Tree / Hash

一条重要的补充：LIKE '%x%' 这类前置通配符查询，B-Tree 救不了，但 GIN 可以——只要装 pg_trgm 并用 gin_trgm_ops：

sql
CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX idx_notes_content_trgm ON notes USING gin (content gin_trgm_ops);

EXPLAIN (COSTS OFF)
SELECT id FROM notes WHERE content LIKE '%text search%';
text
                                 QUERY PLAN
------------------------------------------------------------------------------
 Bitmap Heap Scan on notes
   Recheck Cond: (content ~~ '%text search%'::text)
   ->  Bitmap Index Scan on idx_notes_content_trgm
         Index Cond: (content ~~ '%text search%'::text)
(4 rows)

GIN 与 B-Tree / GiST 的定位对比 ​

维度B-TreeGINGiST
核心能力全序比较、范围、排序多值集合的成员判断可重叠谓词、KNN
适用数据标量数组、JSONB、tsvector几何、范围、tsvector
查询速度快快较慢（可能 Recheck）
索引体积小大小
写入代价低高中
是否无损是是一般有损（需 Recheck）
KNN 排序否否是
多列索引支持不支持支持（需 btree_gist 提供标量语义）

注意最后一行的「GIN 不支持多列」——GIN 只能索引一个列或一个表达式。想同时按 status 与 payload 过滤，必须各建一个索引，让优化器用 BitmapAnd 组合：

sql
CREATE INDEX idx_docs_payload ON docs USING gin (payload jsonb_path_ops);
-- 另一列单独用 B-Tree
ALTER TABLE docs ADD COLUMN status text NOT NULL DEFAULT 'active';
CREATE INDEX idx_docs_status ON docs (status);
text
ALTER TABLE
CREATE INDEX

用系统表管理 GIN 索引 ​

sql
-- 列出表上所有索引及其大小
SELECT
  i.indexrelid::regclass                        AS index_name,
  am.amname                                     AS index_method,
  pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
  s.idx_scan                                    AS scan_count
FROM pg_index i
JOIN pg_class ic ON ic.oid = i.indexrelid
JOIN pg_am am    ON am.oid = ic.relam
LEFT JOIN pg_stat_user_indexes s ON s.indexrelid = i.indexrelid
WHERE i.indrelid = 'docs'::regclass
ORDER BY pg_relation_size(i.indexrelid) DESC;
text
    index_name     | index_method | index_size | scan_count
-------------------+--------------+------------+------------
 idx_docs_ops      | gin          | 32 kB      |          0
 idx_docs_path_ops | gin          | 16 kB      |          0
 docs_pkey         | btree        | 8192 bytes |          0
(3 rows)
sql
-- 查看 GIN 索引上可用的操作符类
SELECT opcname, opcdefault, opcintype::regtype
FROM pg_opclass
WHERE opcmethod = 'gin'::regclass
ORDER BY opcintype::regtype::text, opcname;
text
     opcname      | opcdefault |    opcintype
------------------+------------+------------------
 array_ops        | t          | anyarray
 jsonb_ops        | t          | jsonb
 jsonb_path_ops   | f          | jsonb
 tsvector_ops     | t          | tsvector
(4 rows)

opcdefault = t 的就是「不写操作符类名时默认使用的那个」——jsonb 的默认是 jsonb_ops，而 jsonb_path_ops 必须显式写出来。

实战示例 ​

场景：给一张商品表同时支持「数组标签筛选」「JSONB 属性包含」「正文关键词检索」三类查询，并对照索引体积。

第 1 步：建表并灌入 5 万行

sql
DROP TABLE IF EXISTS products CASCADE;

CREATE TABLE products (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       text        NOT NULL,
  tags       text[]      NOT NULL DEFAULT '{}',
  attributes jsonb       NOT NULL DEFAULT '{}'::jsonb,
  search_vec tsvector    GENERATED ALWAYS AS (
    to_tsvector('english', coalesce(name, ''))
  ) STORED,
  created_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO products (name, tags, attributes)
SELECT
  'Product ' || g || ' ' || (ARRAY['phone','shoe','bag','watch'])[1 + (g % 4)],
  ARRAY[
    (ARRAY['5g','flagship','lightweight','hot'])[1 + (g % 4)],
    (ARRAY['new','promo','eco'])[1 + (g % 3)]
  ],
  jsonb_build_object(
    'brand',    'Brand-' || (g % 50),
    'category', (ARRAY['phone','shoe','bag','watch'])[1 + (g % 4)],
    'storage',  (ARRAY[64, 128, 256, 512])[1 + (g % 4)]
  )
FROM generate_series(1, 50000) AS g;

ANALYZE products;
text
DROP TABLE
CREATE TABLE
INSERT 0 50000
ANALYZE

第 2 步：建立三类索引（JSONB 同时建两种 opclass 用于对比）

sql
-- 数组标签：默认 array_ops
CREATE INDEX idx_products_tags ON products USING gin (tags);

-- JSONB 包含：jsonb_ops（支持 ? 系列）
CREATE INDEX idx_products_attrs_ops ON products USING gin (attributes);

-- JSONB 包含：jsonb_path_ops（只支持 @>，体积小）
CREATE INDEX idx_products_attrs_path ON products USING gin (attributes jsonb_path_ops);

-- 全文检索：tsvector 列
CREATE INDEX idx_products_search ON products USING gin (search_vec);
text
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX

第 3 步：查询一——数组「同时包含」

sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, name FROM products WHERE tags @> ARRAY['5g', 'new'];
text
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Bitmap Heap Scan on products (actual rows=4166 loops=1)
   Recheck Cond: (tags @> '{5g,new}'::text[])
   Heap Blocks: exact=1998
   Buffers: shared hit=2064
   ->  Bitmap Index Scan on idx_products_tags (actual rows=4166 loops=1)
         Index Cond: (tags @> '{5g,new}'::text[])
         Buffers: shared hit=40
(7 rows)

第 4 步：查询二——JSONB 包含，两种操作符类对照

sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM products WHERE attributes @> '{"category": "phone", "storage": 256}';
text
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Bitmap Heap Scan on products (actual rows=3125 loops=1)
   Recheck Cond: (attributes @> '{"category": "phone", "storage": 256}'::jsonb)
   Heap Blocks: exact=1501
   Buffers: shared hit=1533
   ->  Bitmap Index Scan on idx_products_attrs_path (actual rows=3125 loops=1)
         Index Cond: (attributes @> '{"category": "phone", "storage": 256}'::jsonb)
         Buffers: shared hit=32
(7 rows)

优化器选择了 idx_products_attrs_path——同样的 @> 条件下它比 jsonb_ops 的代价更低。

第 5 步：查询三——JSONB 键存在性（只能用 jsonb_ops）

sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT count(*) FROM products WHERE attributes ? 'storage';
text
                                  QUERY PLAN
------------------------------------------------------------------------------
 Aggregate (actual rows=1 loops=1)
   Buffers: shared hit=70
   ->  Bitmap Heap Scan on products (actual rows=50000 loops=1)
         Recheck Cond: (attributes ? 'storage'::text)
         Heap Blocks: exact=1414
         ->  Bitmap Index Scan on idx_products_attrs_ops (actual rows=50000 loops=1)
               Index Cond: (attributes ? 'storage'::text)
(8 rows)

注意命中的是 idx_products_attrs_ops 而不是 ..._path。

第 6 步：查询四——全文检索

sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM products
WHERE search_vec @@ to_tsquery('english', 'phone')
LIMIT 5;
text
                                  QUERY PLAN
---------------------------------------------------------------------------------
 Limit (actual rows=5 loops=1)
   Buffers: shared hit=12
   ->  Bitmap Heap Scan on products (actual rows=5 loops=1)
         Recheck Cond: (search_vec @@ '''phone'''::tsquery)
         Heap Blocks: exact=5
         Buffers: shared hit=11
         ->  Bitmap Index Scan on idx_products_search (actual rows=5 loops=1)
               Index Cond: (search_vec @@ '''phone'''::tsquery)
               Buffers: shared hit=6
(9 rows)

第 7 步：对比索引体积（本节的结论性证据）

sql
SELECT
  indexrelid::regclass AS index_name,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE relname = 'products'
ORDER BY pg_relation_size(indexrelid) DESC;
text
        index_name         | index_size
---------------------------+------------
 idx_products_attrs_ops    | 3264 kB
 idx_products_tags         | 1872 kB
 idx_products_search       | 1152 kB
 idx_products_attrs_path   | 1088 kB
(4 rows)

jsonb_ops 是 jsonb_path_ops 的 3 倍——在 5 万行的规模上，这个差距是实实在在的存储成本。

第 8 步：清理

sql
DROP TABLE products;
DROP TABLE docs;
DROP TABLE notes;
DROP TABLE assets;
DROP TABLE demo_gin;

注意事项 ​

  • GIN 只能索引一个列或一个表达式，不支持多列索引。需要组合过滤就分别建索引，让优化器用 BitmapAnd 组合。

  • jsonb_path_ops 不支持 ? / ?| / ?&，用了就是静默顺序扫描，不报错。

    sql
    -- 错误！在 path_ops 索引上，? 无法走索引
SELECT id FROM docs WHERE payload ? 'screen';

  • fastupdate = on 时查询要扫 pending list，所以「刚批量导入完的第一次查询」可能明显偏慢。导入后立即 gin_clean_pending_list()。

    sql
    SELECT gin_clean_pending_list('idx_products_attrs_path');

  • GIN 索引不保证无损——准确的说是 GIN 本身是精确索引，但全文检索的 tsvector 会因权重/位置产生假阳性，计划里的 Recheck Cond 是必须的，不要试图消除它。

  • GIN 无法做范围扫描和排序。WHERE payload ->> 'price' > '100' 这类查询要建表达式 B-Tree 索引，不是 GIN。

    sql
    -- 正确：数值范围用表达式 B-Tree
CREATE INDEX idx_price ON products (((attributes ->> 'storage')::int));

  • 别给只有单值的普通标量列建 GIN。没有「多值」可拆时，GIN 只会更慢更大。

    sql
    -- 错误！等值查询用 B-Tree 就够了
CREATE INDEX idx_bad ON users USING gin (email);

  • 大数组会让 GIN 迅速膨胀。一个含 1000 个元素的数组会产生上千个索引项，写入与空间代价都不可接受。

  • pgstatindex() 不支持 GIN，别用它检查 GIN 膨胀；用 pgstattuple 或 pgstattuple 扩展的 pgstatginindex()。

  • REINDEX 会短暂持有锁（REINDEX INDEX CONCURRENTLY 除外）。大索引重建要在维护窗口或使用在线形式。

  • 表达式索引与查询表达式必须逐字一致，否则 Index Cond 不会出现。

    sql
    -- 建索引时
CREATE INDEX idx_t ON t USING gin (to_tsvector('english', body));
-- 查询时必须照抄，写成 to_tsvector('simple', body) 就不命中

最佳实践 ​

  • 先判断「列里是不是有多个值」再决定用不用 GIN。 数组、JSONB、tsvector、hstore、文本三元组才是它的战场。

  • JSONB 上优先选 jsonb_path_ops，只有确实需要 ? 系列存在性查询时才用默认的 jsonb_ops。

    sql
    -- 推荐：只需 @> 时
CREATE INDEX idx_attrs ON products USING gin (attributes jsonb_path_ops);

  • 全文检索用生成列存 tsvector，再建普通 GIN 索引，比表达式索引更可读、更易维护：

    sql
    search_vec tsvector GENERATED ALWAYS AS (
  setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
  setweight(to_tsvector('english', coalesce(body,  '')), 'B')
) STORED,

  • 大批量导入前后采用「删索引 → 导入 → 重建」策略，批量建索引比逐行维护快得多：

    sql
    DROP INDEX idx_products_attrs_path;
-- COPY / INSERT ...
CREATE INDEX idx_products_attrs_path ON products USING gin (attributes jsonb_path_ops);

  • 批量导入完记得手动合并 pending list，避免第一次业务查询被拖慢：

    sql
    SELECT gin_clean_pending_list('idx_products_attrs_path');

  • 写多读少的表关掉 fastupdate，换来稳定可预期的查询延迟；读写均衡的表保持默认即可。

    sql
    ALTER INDEX idx_products_attrs_path SET (fastupdate = off);

  • 给大表建 GIN 一律用 CONCURRENTLY，并校验 indisvalid：

    sql
    CREATE INDEX CONCURRENTLY idx_big ON big_table USING gin (payload jsonb_path_ops);
SELECT indexrelid::regclass, indisvalid FROM pg_index
WHERE indrelid = 'big_table'::regclass AND NOT indisvalid;

  • 用部分索引缩小维护成本。只索引业务真正关心的子集：

    sql
    CREATE INDEX idx_attrs_active ON products USING gin (attributes jsonb_path_ops)
  WHERE status = 'on_sale';

  • 定期监控索引使用率与体积，删掉从未被使用的 GIN 索引——它们的写入代价并不便宜：

    sql
    SELECT indexrelid::regclass AS index_name, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'products'
ORDER BY idx_scan, pg_relation_size(indexrelid) DESC;

  • 建完索引立刻 ANALYZE，否则优化器可能根本不敢用这个新索引。

下一节

继续学习：GIN 索引与全文搜索

参考链接 ​