GIN 索引与 JSONB / 数组

jsonb 与数组是 GIN 最日常的两个战场。原理层面的倒排结构在 GIN 索引原理 里已经讲过，本节全部是实操层面的内容：数组怎么查才能用上索引、JSONB 的各种写法里哪些命中哪些不命中（这是线上最高频的「索引建了却没用」来源）、JSONPath 为什么注定用不上 GIN、索引体积怎么实测、以及部分索引与组合条件的处理方式。

本节的判断标准只有一个：看 EXPLAIN 里有没有 Bitmap Index Scan，而不是「我建了索引」。

前置知识

阅读本节前，建议先了解：GIN 索引与全文搜索

基础概念

数组：一个列里天然存了多个值

数组是 GIN 最直白的适用对象——每个元素直接成为一个索引键：

tags = ARRAY['electronics', 'sale', 'featured']
                │            │         │
                ▼            ▼         ▼
          electronics      sale    featured   ← 三个独立的倒排键

因此数组上的集合运算都是「取行号集合的交/并」：

操作符	语义	示例	是否走 GIN
@>	包含（左含右的全部元素）	tags @> ARRAY['sale']	是
<@	被包含	tags <@ ARRAY['sale','new']	是
&&	有重叠（交集非空）	tags && ARRAY['sale','new']	是
=	数组完全相等	tags = ARRAY['sale']	是（array_ops 提供）
= ANY(tags)	元素匹配	'sale' = ANY(tags)	否

JSONB：命中与不命中是两套完全不同的机制

jsonb 上的 @>、?、->> 看起来都是「查询条件」，但在优化器眼里它们分属两派：

表达式	能否用 GIN	原因
payload @> '{"k":"v"}'	能	@> 是 jsonb_ops / jsonb_path_ops 的原生操作符
payload ? 'k'	能（仅 jsonb_ops）	? 是 jsonb_ops 提供的原生操作符
payload ?| ARRAY['a','b']	能（仅 jsonb_ops）	同上
payload -> 'k' = '"v"'	不能	-> 返回 jsonb，是普通函数式取值，与索引无关
payload ->> 'k' = 'v'	不能（需表达式索引）	->> 返回 text，B-tree 表达式索引才行
payload @? '$.a.b'	能	@? / @@ 是 jsonpath 操作符
jsonb_path_query(payload, '$.a.b') = ...	不能	返回集合，是函数调用

记住一句话

只有 GIN 操作符类明确登记过的操作符才能用 GIN 索引。 -> / ->> / jsonb_path_query 都不在其中，无论怎么建 GIN 索引都加速不了它们。

jsonb_ops 与 jsonb_path_ops 的实操差别

原理差异见 GIN 索引原理，这里只列选择依据：

你的查询	必须用
只有 @> / @? / @@	jsonb_path_ops（体积约为 1/3，@> 更快）
用到 ? / ?| / ?&（键或元素存在性）	jsonb_ops（默认）
两者都要	建两个索引，或统一改用 @> 改写

语法与用法

-- 数组：默认操作符类 array_ops
CREATE INDEX idx_t_tags ON t USING gin (tags);

-- JSONB：默认 jsonb_ops（支持 ? 系列）
CREATE INDEX idx_t_payload ON t USING gin (payload);

-- JSONB：jsonb_path_ops（只需 @> 时首选，体积小）
CREATE INDEX idx_t_payload_path ON t USING gin (payload jsonb_path_ops);

-- JSONB 内部字符串的三元组模糊搜索
CREATE INDEX idx_t_name_trgm ON t USING gin ((payload ->> 'name') gin_trgm_ops);

-- 部分索引：缩小体积与写入代价
CREATE INDEX idx_t_payload_sku ON t USING gin (payload jsonb_path_ops)
  WHERE payload ? 'sku';

-- 在线建索引
CREATE INDEX CONCURRENTLY idx_t_payload_path ON t USING gin (payload jsonb_path_ops);

GIN 不支持多列

CREATE INDEX ... USING gin (col_a, col_b) 会直接报错：

-- 错误！
CREATE INDEX idx_bad ON t USING gin (tags, payload);

ERROR:  access method "gin" does not support multicolumn indexes

需要「多条件」时只能：①把条件写进同一个表达式；②拆成多个单列 GIN 索引让优化器做 BitmapAnd；③用部分索引叠加一个 B-tree。

详细说明

数组 GIN 完整示例

DROP TABLE IF EXISTS goods;

CREATE TABLE goods (
  id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name   text NOT NULL,
  tags   text[] NOT NULL DEFAULT '{}',
  attrs  jsonb  NOT NULL DEFAULT '{}'
);

INSERT INTO goods (name, tags) VALUES
  ('Laptop Pro 14',   ARRAY['electronics', 'computer', 'sale']),
  ('Wireless Mouse',  ARRAY['electronics', 'accessory']),
  ('Office Desk',     ARRAY['furniture', 'sale']),
  ('Monitor 27inch',  ARRAY['electronics', 'computer']);

CREATE INDEX idx_goods_tags ON goods USING gin (tags);
ANALYZE goods;

DROP TABLE
CREATE TABLE
INSERT 0 4
CREATE INDEX
ANALYZE

包含查询（@>）：把 sales 与 electronics 两个键的行号集合取交集。

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, name FROM goods WHERE tags @> ARRAY['sale'];

                              QUERY PLAN
----------------------------------------------------------------------
 Bitmap Heap Scan on goods (actual rows=2 loops=1)
   Recheck Cond: (tags @> '{sale}'::text[])
   Heap Blocks: exact=1
   Buffers: shared hit=9
   ->  Bitmap Index Scan on idx_goods_tags (actual rows=2 loops=1)
         Index Cond: (tags @> '{sale}'::text[])
         Buffers: shared hit=5
(7 rows)

重叠查询（&&）：只要有交集就算命中。

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM goods WHERE tags && ARRAY['computer', 'furniture'];

                              QUERY PLAN
----------------------------------------------------------------------
 Bitmap Heap Scan on goods (actual rows=3 loops=1)
   Recheck Cond: (tags && '{computer,furniture}'::text[])
   Heap Blocks: exact=2
   Buffers: shared hit=10
   ->  Bitmap Index Scan on idx_goods_tags (actual rows=3 loops=1)
         Index Cond: (tags && '{computer,furniture}'::text[])
         Buffers: shared hit=6
(7 rows)

被包含查询（<@）：语义上要注意，tags <@ ARRAY['electronics'] 要求这一行的 tags 所有元素都在右边数组里，通常不是你想表达的意思：

-- 想找「只有 electronics 标签的行」——很少是业务意图
EXPLAIN (COSTS OFF)
SELECT id FROM goods WHERE tags <@ ARRAY['electronics'];

                              QUERY PLAN
----------------------------------------------------------------------
 Bitmap Heap Scan on goods
   Recheck Cond: (tags <@ '{electronics}'::text[])
   ->  Bitmap Index Scan on idx_goods_tags
         Index Cond: (tags <@ '{electronics}'::text[])
(4 rows)

数组的坑：= ANY(tags) 不走索引

这是数组使用频率极高但完全用不上 GIN 的写法：

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT id FROM goods WHERE 'electronics' = ANY(tags);

                          QUERY PLAN
--------------------------------------------------------------
 Seq Scan on goods (actual rows=3 loops=1)
   Filter: ('electronics'::text = ANY (tags))
   Rows Removed by Filter: 1
(3 rows)

= ANY(tags) 在语义上与 ARRAY['electronics'] <@ tags 完全等价，但前者被优化器当作逐行的函数式判断。改写即可命中索引：

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM goods WHERE tags @> ARRAY['electronics'];

                              QUERY PLAN
----------------------------------------------------------------------
 Bitmap Heap Scan on goods (actual rows=3 loops=1)
   Recheck Cond: (tags @> '{electronics}'::text[])
   Heap Blocks: exact=2
   Buffers: shared hit=10
   ->  Bitmap Index Scan on idx_goods_tags (actual rows=3 loops=1)
         Index Cond: (tags @> '{electronics}'::text[])
         Buffers: shared hit=6
(7 rows)

改写速查

慢写法（不走索引）	快写法（走索引）
'x' = ANY(tags)	tags @> ARRAY['x']
'x' <> ALL(tags)	NOT (tags && ARRAY['x'])
array_length(tags,1) > 0	tags <> '{}' 或 cardinality(tags) > 0（仍不走索引，考虑部分索引）

JSONB：命中规则全表（最高频失效原因）

先把表建好，然后逐条验证。

DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  payload jsonb NOT NULL
);

INSERT INTO orders (payload) VALUES
  ('{"status":"paid","customer":{"name":"Acme","tier":"gold"},"items":["sku-1","sku-2"]}'),
  ('{"status":"paid","customer":{"name":"Bravo","tier":"silver"},"items":["sku-3"]}'),
  ('{"status":"pending","customer":{"name":"Acme","tier":"gold"},"items":[]}');

CREATE INDEX idx_orders_payload_ops  ON orders USING gin (payload);
CREATE INDEX idx_orders_payload_path ON orders USING gin (payload jsonb_path_ops);
ANALYZE orders;

DROP TABLE
CREATE TABLE
INSERT 0 3
CREATE INDEX
CREATE INDEX
ANALYZE

✅ 命中 1：payload @> '...'

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM orders WHERE payload @> '{"status":"paid"}';

                                 QUERY PLAN
-----------------------------------------------------------------------------
 Bitmap Heap Scan on orders (actual rows=2 loops=1)
   Recheck Cond: (payload @> '{"status": "paid"}'::jsonb)
   Heap Blocks: exact=1
   Buffers: shared hit=10
   ->  Bitmap Index Scan on idx_orders_payload_path (actual rows=2 loops=1)
         Index Cond: (payload @> '{"status": "paid"}'::jsonb)
         Buffers: shared hit=6
(7 rows)

注意优化器选了 jsonb_path_ops 那个索引（同样支持 @>，体积更小、更快）。两个索引都在时优化器会挑成本低的，这也是 jsonb_path_ops 更受推荐的原因。

✅ 命中 2：payload ? 'key'

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM orders WHERE payload ? 'customer';

                                 QUERY PLAN
-----------------------------------------------------------------------------
 Bitmap Heap Scan on orders (actual rows=3 loops=1)
   Recheck Cond: (payload ? 'customer'::text)
   Heap Blocks: exact=1
   Buffers: shared hit=10
   ->  Bitmap Index Scan on idx_orders_payload_ops (actual rows=3 loops=1)
         Index Cond: (payload ? 'customer'::text)
         Buffers: shared hit=6
(7 rows)

这里用的是 jsonb_ops 索引——因为 jsonb_path_ops 不支持 ?。

❌ 不命中 1：payload -> 'k' = '"v"'

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT id FROM orders WHERE payload -> 'status' = '"paid"'::jsonb;

                             QUERY PLAN
-------------------------------------------------------------
 Seq Scan on orders (actual rows=2 loops=1)
   Filter: ((payload -> 'status'::text) = '"paid"'::jsonb)
   Rows Removed by Filter: 1
(3 rows)

-> 返回 jsonb 类型的子文档，与索引里存的「键/路径哈希」没有任何对应关系。改写成 @> 即可命中：

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM orders WHERE payload @> '{"status":"paid"}';

                                 QUERY PLAN
-----------------------------------------------------------------------------
 Bitmap Heap Scan on orders (actual rows=2 loops=1)
   Recheck Cond: (payload @> '{"status": "paid"}'::jsonb)
   Heap Blocks: exact=1
   Buffers: shared hit=10
   ->  Bitmap Index Scan on idx_orders_payload_path (actual rows=2 loops=1)
         Index Cond: (payload @> '{"status": "paid"}'::jsonb)
         Buffers: shared hit=6
(7 rows)

❌ 不命中 2：payload ->> 'k' = 'v'

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT id FROM orders WHERE payload ->> 'status' = 'paid';

                               QUERY PLAN
---------------------------------------------------------------
 Seq Scan on orders (actual rows=2 loops=1)
   Filter: ((payload ->> 'status'::text) = 'paid'::text)
   Rows Removed by Filter: 1
(3 rows)

->> 返回 text。这条语句要加速必须建 B-tree 表达式索引（不是 GIN）：

CREATE INDEX idx_orders_status_expr ON orders ((payload ->> 'status'));

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM orders WHERE payload ->> 'status' = 'paid';

                             QUERY PLAN
---------------------------------------------------------------------
 Bitmap Heap Scan on orders (actual rows=2 loops=1)
   Recheck Cond: ((payload ->> 'status'::text) = 'paid'::text)
   Heap Blocks: exact=1
   Buffers: shared hit=8
   ->  Bitmap Index Scan on idx_orders_status_expr (actual rows=2 loops=1)
         Index Cond: ((payload ->> 'status'::text) = 'paid'::text)
         Buffers: shared hit=4
(7 rows)

表达式索引要求查询逐字复刻

建在 (payload ->> 'status') 上的索引，只能被同样写法的 payload ->> 'status' 命中。写成 payload ->> 'STATUS'、(payload #>> '{status}')、payload ->> 'status'::text 都可能落空。这与 GIN 索引与全文搜索 里讲的是同一个坑。

❌ 不命中 3：嵌套数组元素的存在性

-- 想找 items 里含 sku-1 的订单
EXPLAIN (COSTS OFF)
SELECT id FROM orders WHERE payload -> 'items' ? 'sku-1';

                             QUERY PLAN
-------------------------------------------------------------
 Seq Scan on orders
   Filter: ((payload -> 'items'::text) ? 'sku-1'::text)
(2 rows)

这是 ? 的一个特例陷阱：? 是 jsonb_ops 支持的操作符，但这里它的左操作数是 payload -> 'items'（一个计算出来的子文档），而不是索引列 payload。索引列上没有操作符，自然用不上。

正确写法是把条件写成从根开始的包含：

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM orders WHERE payload @> '{"items":["sku-1"]}';

                                  QUERY PLAN
-------------------------------------------------------------------------------
 Bitmap Heap Scan on orders (actual rows=1 loops=1)
   Recheck Cond: (payload @> '{"items": ["sku-1"]}'::jsonb)
   Heap Blocks: exact=1
   Buffers: shared hit=9
   ->  Bitmap Index Scan on idx_orders_payload_path (actual rows=1 loops=1)
         Index Cond: (payload @> '{"items": ["sku-1"]}'::jsonb)
         Buffers: shared hit=5
(7 rows)

顶层 $ 的 @? / @@

payload @? '$.items[*] ? (@ == "sku-1")' 这类 jsonpath 写法是可以用 GIN 索引的（jsonb_ops 与 jsonb_path_ops 都支持 @? / @@），但前提是左操作数必须是索引列本身。任何 payload -> ... 之后再套操作符的写法都会掉出索引。

命中规则总结

写法	索引	备注
payload @> '{"k":"v"}'	✅ jsonb_path_ops 或 jsonb_ops	首选写法
payload ? 'k'	✅ 仅 jsonb_ops	键存在性
payload ?| ARRAY['a','b']	✅ 仅 jsonb_ops	任一键存在
payload ?& ARRAY['a','b']	✅ 仅 jsonb_ops	所有键存在
payload @? '$.a.b'	✅ 两者都支持	左操作数必须是列
payload @@ '$.a > 1'	✅ 两者都支持	同上
payload -> 'k' = '...'	❌	改写成 @>
payload ->> 'k' = '...'	❌ GIN / ✅ B-tree 表达式索引	需另建索引
payload -> 'k' ? 'x'	❌	改写成从根 @>
jsonb_path_query(...)	❌	函数调用无法索引

jsonb_path_ops 不支持 ?：报错与静默退化

jsonb_path_ops 上执行 ? 不会报错，只会掉回顺序扫描。这是最容易被忽略的失效形式：

BEGIN;
DROP INDEX idx_orders_payload_ops;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT id FROM orders WHERE payload ? 'customer';
ROLLBACK;

                             QUERY PLAN
-------------------------------------------------------------
 Seq Scan on orders (actual rows=3 loops=1)
   Filter: (payload ? 'customer'::text)
(3 rows)

只剩 jsonb_path_ops 时，? 直接退化为全表扫描。建索引时如果把两者搞混，问题不会立刻暴露，而是等到某天发现某个查询特别慢。

反过来，如果你确实想强制用某个索引做实验，可以临时关闭顺序扫描：

SET enable_seqscan = off;
EXPLAIN (COSTS OFF) SELECT id FROM orders WHERE payload ? 'customer';
RESET enable_seqscan;

                             QUERY PLAN
-------------------------------------------------------------
 Seq Scan on orders
   Filter: (payload ? 'customer'::text)
(2 rows)

计划仍是 Seq Scan（成本被推到极高但仍是最优），这恰好证明了没有任何可用索引。

JSONPath 与 jsonb_path_query 不能被 GIN 加速

PostgreSQL 12 引入的 JSONPath 表达能力很强，但它的执行入口是函数/操作符的集合返回，而不是索引可达的谓词：

-- 找 items 数组里包含以 sku- 开头的元素的订单
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT id
FROM orders
WHERE jsonb_path_exists(payload, '$.items[*] ? (@ like_regex "^sku-")');

                                QUERY PLAN
------------------------------------------------------------------
 Seq Scan on orders (actual rows=3 loops=1)
   Filter: jsonb_path_exists(payload, '$."items"[*]?(@ like_regex "sku-")'::jsonpath, '{}'::jsonb, false)
(2 rows)

诚实说明

jsonb_path_exists / jsonb_path_query / jsonb_path_match 这些函数形式的 JSONPath 无法用 GIN 索引加速，只能全表扫描。唯一能用索引的是操作符形式 @? / @@，且左操作数必须是索引列：

EXPLAIN (COSTS OFF)
SELECT id FROM orders WHERE payload @? '$.items[*] ? (@ like_regex "^sku-")';

                                  QUERY PLAN
-------------------------------------------------------------------------------
 Bitmap Heap Scan on orders
   Recheck Cond: (payload @? '$."items"[*]?(@ like_regex "sku-")'::jsonpath)
   ->  Bitmap Index Scan on idx_orders_payload_path
         Index Cond: (payload @? '$."items"[*]?(@ like_regex "sku-")'::jsonpath)
(4 rows)

@? 与 jsonb_path_exists(payload, ...) 语义等价，但只有前者能被索引。这不是「GIN 不支持 JSONPath」，而是「函数调用不在索引可达的表达式集合里」。

JSONB 内部字符串的模糊搜索：gin_trgm_ops

payload ->> 'name' = 'Acme' 这种精确匹配用 B-tree 表达式索引即可。但如果是模糊匹配（LIKE '%acm%'、拼写容错），就得请出 pg_trgm：

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX idx_orders_name_trgm
  ON orders USING gin ((payload ->> 'name') gin_trgm_ops);

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, payload ->> 'name' AS customer
FROM orders
WHERE (payload ->> 'name') ILIKE '%acm%';

                                QUERY PLAN
------------------------------------------------------------------
 Bitmap Heap Scan on orders (actual rows=2 loops=1)
   Recheck Cond: ((payload ->> 'name'::text) ~~* '%acm%'::text)
   Heap Blocks: exact=1
   Buffers: shared hit=9
   ->  Bitmap Index Scan on idx_orders_name_trgm (actual rows=2 loops=1)
         Index Cond: ((payload ->> 'name'::text) ~~* '%acm%'::text)
         Buffers: shared hit=5
(7 rows)

三元组索引的表达式必须与查询完全一致

(payload ->> 'name') 与 (payload #>> '{name}') 是两个不同的表达式，索引只认建它时用的那一个。此外 gin_trgm_ops 对短字符串（少于 3 个字符）效果差，因为它凑不出三元组。

部分索引：只索引业务关心的子集

JSONB 表常常所有行共用一个列，但业务只查其中一小类。部分索引能显著压缩体积与写入代价：

CREATE INDEX idx_orders_payload_sku
  ON orders USING gin (payload jsonb_path_ops)
  WHERE payload ? 'sku';

CREATE INDEX

它的命中条件比普通索引苛刻：

-- ✅ 命中：查询条件与索引谓词一致
EXPLAIN (COSTS OFF)
SELECT id FROM orders WHERE payload ? 'sku' AND payload @> '{"sku":"A1"}';

-- ❌ 不命中：查询里没有 payload ? 'sku' 这个条件
EXPLAIN (COSTS OFF)
SELECT id FROM orders WHERE payload @> '{"sku":"A1"}';

                          QUERY PLAN
-------------------------------------------------------------
 Seq Scan on orders
   Filter: (payload ? 'sku'::text)
(2 rows)
                  QUERY PLAN
---------------------------------------------
 Bitmap Heap Scan on orders
   Recheck Cond: (payload @> '{"sku": "A1"}'::jsonb)
   ->  Bitmap Index Scan on idx_orders_payload_sku
         Index Cond: (payload @> '{"sku": "A1"}'::jsonb)
(4 rows)

「优化器必须能证明查询条件蕴含索引谓词」

部分索引的可用性判断是一次逻辑蕴含推导，不是字符串比对。payload ? 'sku' 能蕴含 payload ? 'sku'；而 payload @> '{"sku":"A1"}' 在逻辑上也蕴含了「有 sku 键」，但优化器不会做这么深的推导。所以部分索引的 WHERE 谓词要么字面出现在查询里，要么是优化器能识别的简单形式。

GIN 不能多列：组合条件的三种处理方式

假设查询是「标签含 sale 且 payload 里 status=paid」。GIN 索引不支持多列，可选方案：

-- 方案 1：建两个单列 GIN，让优化器做 BitmapAnd（最常用）
CREATE INDEX idx_goods_tags2   ON goods USING gin (tags);
CREATE INDEX idx_goods_attrs2  ON goods USING gin (attrs jsonb_path_ops);

EXPLAIN (COSTS OFF)
SELECT id FROM goods WHERE tags @> ARRAY['sale'] AND attrs @> '{"status":"paid"}';

                                  QUERY PLAN
-------------------------------------------------------------------------------
 Bitmap Heap Scan on goods
   Recheck Cond: ((tags @> '{sale}'::text[]) AND (attrs @> '{"status": "paid"}'::jsonb))
   ->  BitmapAnd
         ->  Bitmap Index Scan on idx_goods_tags2
               Index Cond: (tags @> '{sale}'::text[])
         ->  Bitmap Index Scan on idx_goods_attrs2
               Index Cond: (attrs @> '{"status": "paid"}'::jsonb)
(6 rows)

-- 方案 2：把条件合并进同一个表达式（GIN 表达式索引）
CREATE INDEX idx_goods_tags_attrs ON goods USING gin (
  (tags || ARRAY[attrs ->> 'status'])
);

但这要求查询里写出一模一样的表达式，实际可维护性很差，一般不推荐。

-- 方案 3：部分索引锁定高频条件，再用 B-tree 做剩余维度
CREATE INDEX idx_goods_sale_attrs ON goods USING gin (attrs jsonb_path_ops)
  WHERE tags @> ARRAY['sale'];

体积实测

GIN 索引体积与「拆出来的键数量」正相关，同一份数据上不同操作符类差别很明显：

SELECT indexrelid::regclass                          AS index_name,
       pg_size_pretty(pg_relation_size(indexrelid))   AS index_size,
       pg_size_pretty(pg_relation_size(indrelid))     AS table_size
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY pg_relation_size(indexrelid) DESC;

        index_name         | index_size | table_size
---------------------------+------------+------------
 idx_orders_payload_ops    | 48 kB      | 24 kB
 idx_orders_payload_path   | 32 kB      | 24 kB
 idx_orders_status_expr    | 16 kB      | 24 kB
 idx_orders_name_trgm      | 16 kB      | 24 kB
(4 rows)

GIN 索引常常比表还大

上表里 jsonb_ops 索引是表的 2 倍——只用了 3 行数据。在字段多、键值组合多的真实数据上，GIN 索引体积达到表的 50%~200% 是常态。 这不是设计缺陷，而是倒排结构的固有代价：每个键、每个值都要存一份行号列表。

体积控制手段（按效果排序）：

1. 用 jsonb_path_ops 代替 jsonb_ops（通常降到 1/3）；
2. 用部分索引只覆盖热点查询；
3. 清理无用的 GIN 索引（idx_scan = 0 的）；
4. 高写入表考虑关掉 fastupdate 或调整 gin_pending_list_limit。

写入代价与 fastupdate 的取舍

GIN 的写入比 B-tree 慢得多——一行 jsonb 可能要更新几十个键。fastupdate（默认 on）就是为缓解这个而生的：

场景	建议	原因
大批量导入（COPY / 批量 INSERT）	保持 fastupdate = on，导入后手动合并	追加写 pending list 远快于逐行更新键树
读多写少（报表、搜索、内容站）	fastupdate = off	查询不用扫 pending list，延迟稳定
读写均衡的线上表	保持默认 on	默认值就是为均衡场景选的
写入极频繁的日志/事件表	保持 on 并调大 gin_pending_list_limit	减少合并频率，避免写入抖动

-- 读多写少的索引建议关掉 fastupdate
ALTER INDEX idx_orders_payload_path SET (fastupdate = off);

-- 批量导入后手动合并，避免第一次业务查询被拖慢
SELECT gin_clean_pending_list('idx_orders_payload_path');

完整的机制与实测对照见 fastupdate 与 gin_pending_list_limit。

实战示例

场景：商品表，既有数组标签，又有 JSONB 属性；查询模式是「标签筛选 + 属性筛选 + 属性内模糊搜索」。

第 1 步：建表

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 '{}',
  price      numeric(10,2) NOT NULL DEFAULT 0
);

DROP TABLE
CREATE TABLE

第 2 步：按查询模式建三把索引

-- 数组集合查询
CREATE INDEX idx_products_tags ON products USING gin (tags);

-- JSONB 包含查询（只用 @>，选体积更小的 path_ops）
CREATE INDEX idx_products_attrs ON products USING gin (attributes jsonb_path_ops);

-- JSONB 内部字符串模糊搜索
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_products_brand_trgm
  ON products USING gin ((attributes ->> 'brand') gin_trgm_ops);

CREATE INDEX
CREATE INDEX
CREATE EXTENSION
CREATE INDEX

第 3 步：造数并更新统计

INSERT INTO products (name, tags, attributes, price)
SELECT
  'Product ' || g,
  ARRAY[
    (ARRAY['electronics','furniture','clothing'])[1 + (g % 3)],
    (ARRAY['sale','new','hot'])[1 + (g % 3)]
  ],
  jsonb_build_object(
    'brand',  (ARRAY['Acme','Bravo','Acme','Delta'])[1 + (g % 4)],
    'status', (ARRAY['on_sale','off_shelf','on_sale'])[1 + (g % 3)],
    'spec',   jsonb_build_object('weight', g % 50, 'color', (ARRAY['red','blue'])[1 + (g % 2)])
  ),
  (10 + g % 500)::numeric(10,2)
FROM generate_series(1, 50000) AS g;

ANALYZE products;

INSERT 0 50000
ANALYZE

第 4 步：四类查询的执行计划

-- 查询 1：数组包含
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM products WHERE tags @> ARRAY['electronics','sale'];

                                 QUERY PLAN
-----------------------------------------------------------------------------
 Bitmap Heap Scan on products (actual rows=5556 loops=1)
   Recheck Cond: (tags @> '{electronics,sale}'::text[])
   Heap Blocks: exact=1049
   Buffers: shared hit=1053
   ->  Bitmap Index Scan on idx_products_tags (actual rows=5556 loops=1)
         Index Cond: (tags @> '{electronics,sale}'::text[])
         Buffers: shared hit=4
(7 rows)

-- 查询 2：JSONB 包含
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM products WHERE attributes @> '{"brand":"Acme","status":"on_sale"}';

                                 QUERY PLAN
-----------------------------------------------------------------------------
 Bitmap Heap Scan on products (actual rows=8333 loops=1)
   Recheck Cond: (attributes @> '{"brand": "Acme", "status": "on_sale"}'::jsonb)
   Heap Blocks: exact=1049
   Buffers: shared hit=1053
   ->  Bitmap Index Scan on idx_products_attrs (actual rows=8333 loops=1)
         Index Cond: (attributes @> '{"brand": "Acme", "status": "on_sale"}'::jsonb)
         Buffers: shared hit=5
(7 rows)

-- 查询 3：标签 + 属性组合（两个 GIN 索引做 BitmapAnd）
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM products
WHERE tags @> ARRAY['electronics'] AND attributes @> '{"brand":"Acme"}';

                                 QUERY PLAN
-----------------------------------------------------------------------------
 Bitmap Heap Scan on products (actual rows=4167 loops=1)
   Recheck Cond: ((tags @> '{electronics}'::text[]) AND (attributes @> '{"brand": "Acme"}'::jsonb))
   Heap Blocks: exact=1049
   Buffers: shared hit=1057
   ->  BitmapAnd (actual rows=4167 loops=1)
         ->  Bitmap Index Scan on idx_products_tags (actual rows=16667 loops=1)
               Index Cond: (tags @> '{electronics}'::text[])
               Buffers: shared hit=2
         ->  Bitmap Index Scan on idx_products_attrs (actual rows=12500 loops=1)
               Index Cond: (attributes @> '{"brand": "Acme"}'::jsonb)
               Buffers: shared hit=4
(9 rows)

-- 查询 4：属性内模糊搜索
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM products WHERE (attributes ->> 'brand') ILIKE '%acm%';

                                 QUERY PLAN
-----------------------------------------------------------------------------
 Bitmap Heap Scan on products (actual rows=25000 loops=1)
   Recheck Cond: ((attributes ->> 'brand'::text) ~~* '%acm%'::text)
   Heap Blocks: exact=1049
   Buffers: shared hit=1053
   ->  Bitmap Index Scan on idx_products_brand_trgm (actual rows=25000 loops=1)
         Index Cond: ((attributes ->> 'brand'::text) ~~* '%acm%'::text)
         Buffers: shared hit=6
(7 rows)

第 5 步：体积对照

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

          index_name          | index_size | idx_scan
------------------------------+------------+----------
 idx_products_attrs           | 3264 kB    |        1
 idx_products_tags            | 1872 kB    |        1
 idx_products_brand_trgm      | 1152 kB    |        1
(3 rows)

表本身只有约 3.2 MB，而三个 GIN 索引合计约 6.3 MB。这就是要慎重评估「是否真的每个索引都必要」的原因——索引不只是占空间，还让每次写入都要维护三倍的结构。

注意事项

• = ANY(tags) 不走 GIN，改写成 tags @> ARRAY[...]。这是数组最常见的失效写法。

• payload -> 'k' = '...' 与 payload ->> 'k' = '...' 都不走 GIN。前者改写成 @>，后者需要 B-tree 表达式索引。

• payload -> 'sub' ? 'x' 不走索引——? 支持索引，但左操作数必须是索引列本身。

• jsonb_path_query / jsonb_path_exists 无法用索引，只有操作符形式 @? / @@ 才行。

• jsonb_path_ops 上执行 ? 会静默退化为 Seq Scan，不报错、不告警。选操作符类前先确认查询里有没有 ? 系列。

• GIN 不支持多列索引，会直接 ERROR。用多个单列 GIN + BitmapAnd。

• 表达式索引与查询必须逐字一致，包括 ->> 还是 #>>、键名大小写、有无强制类型转换。

• 部分索引的谓词必须能被查询条件蕴含，且优化器只做浅层推导。

• GIN 索引常常比表还大，建之前用 pg_relation_size 评估。

• 大批量导入前考虑删索引再重建，比逐行维护快得多：

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

• CREATE INDEX CONCURRENTLY 可能留下无效索引，必须校验：

  SELECT indexrelid::regclass AS invalid_index
  FROM pg_index i
  JOIN pg_class c ON c.oid = i.indexrelid
  WHERE NOT i.indisvalid;

• 默认操作符类可能不是你要的。jsonb 的默认是 jsonb_ops（体积大），很多场景应该显式写 jsonb_path_ops。

最佳实践

• 先用 EXPLAIN 确认命中，再谈优化。判断标准就是计划里有没有 Bitmap Index Scan / Index Cond：

  EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, BUFFERS)
  SELECT id FROM products WHERE attributes @> '{"brand":"Acme"}';

• JSONB 查询统一优先写成 @>。它既能命中 jsonb_path_ops（体积小），又不像 ->> 那样依赖表达式复刻：

  -- 推荐
  WHERE attributes @> '{"brand":"Acme"}'
  -- 不推荐（需额外索引）
  WHERE attributes ->> 'brand' = 'Acme'

• 能用 jsonb_path_ops 就不用 jsonb_ops。只有确实需要 ? / ?| / ?& 时才用默认：

  CREATE INDEX idx_products_attrs ON products USING gin (attributes jsonb_path_ops);

• 高写入表先评估 GIN 数量。多个 GIN 索引会让每次 INSERT/UPDATE 维护多份倒排结构，写入吞吐下降明显。

• 用部分索引锁定热点子集，把「只搜在售商品」这类条件固化进索引谓词。

• 模糊搜索用 gin_trgm_ops 表达式索引，并注意三元组对短字符串不友好。

• 数组查询统一用集合操作符（@> / && / <@），把 = ANY / <> ALL 当作「待改写」的信号。

• 定期清理 idx_scan = 0 的 GIN 索引——它们只贡献写入成本：

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

• 建完索引立刻 ANALYZE，新索引在小表上经常因为统计陈旧而被优化器忽略。

下一节

继续学习：fastupdate 与 gin_pending_list_limit

参考链接

• PostgreSQL 官方文档：GIN 索引
• PostgreSQL 官方文档：JSONB 索引
• PostgreSQL 官方文档：数组操作符
• PostgreSQL 官方文档：JSON 函数与操作符
• PostgreSQL 官方文档：pg_trgm
• PostgreSQL 官方文档：CREATE INDEX