PHP小码农

Appearance

JSONB 索引 ​

上一节我们知道了 JSONB 的更新代价来自「整值重写」。本节转向查询侧的性能优化：为什么 jsonb 默认用不了普通 B-tree，GIN 索引的两种操作符类（Operator Class）该怎么选，表达式索引与部分索引如何配合，以及怎样用 EXPLAIN (ANALYZE, BUFFERS) 亲眼确认查询真的走了索引。

前置知识

阅读本节前，建议先了解：JSONB 的更新与修改

基础概念 ​

要理解 jsonb 的索引，先要理解「为什么它需要一种特殊的索引」。

B-tree 索引的工作前提是：列值本身可以被排序，而且能用一个标量表示。jsonb 是一个复合结构（对象、数组、嵌套树），既没有全序，也无法压缩成一个标量，所以给它直接建 B-tree 索引是没有意义的。PostgreSQL 允许 jsonb 建 B-tree（因为实现了比较运算符），但那种索引只在「整体比较 = < >」时有用，对「文档里某个键等于某值」这类查询毫无帮助。

真正的主力是 GIN（Generalized Inverted Index，通用倒排索引）。它的思路是：把文档拆成一堆「键/值路径 → 行号」的倒排项，查询时反过来用这些项定位行。

索引类型能否用于 jsonb适用查询说明
B-tree能建但基本无用整值 = < >jsonb 有全序，但业务几乎不这么查
GIN（jsonb_ops，默认）能? ?| ?& @> @? @@功能最全，索引体积最大
GIN（jsonb_path_ops）能@> @? @@体积小、速度快，但不支持存在性操作符
Hash不能—jsonb 无哈希操作符类
GiST能@> ? 等体积更小但查询更慢，极少用

GIN 的两种操作符类（Operator Class）是最重要的选择点：

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

关键术语：

  • GIN（Generalized Inverted Index，通用倒排索引）：把「值 → 行」的映射反过来存成「倒排项 → 行集合」，并在同一个倒排项下挂多个行号（posting list）。天然适合「一对多」的复合值。
  • 操作符类（Operator Class，opclass）：告诉索引「这一列该怎么建倒排项、支持哪些操作符」。jsonb_ops 与 jsonb_path_ops 是 jsonb 上预置的两个操作符类。
  • 表达式索引（Expression Index）：索引的不是列本身，而是列上的一段表达式（如 (attributes ->> 'brand')）。用来加速 ->> 这类不可直接索引的写法。
  • 部分索引（Partial Index）：只索引满足 WHERE 条件的行，索引更小、写入代价更低。
  • 回表 / Recheck（重检）：GIN 返回的是「候选行」，PostgreSQL 还要用原始条件逐行复核，因为 GIN 索引可能因位图精度产生假阳性。

语法与用法 ​

text
-- 1. GIN 索引：默认 jsonb_ops，支持全部 jsonb 操作符
CREATE INDEX 索引名 ON 表名 USING GIN (列名);

-- 2. GIN 索引：jsonb_path_ops，体积更小，只支持 @> @? @@
CREATE INDEX 索引名 ON 表名 USING GIN (列名 jsonb_path_ops);

-- 3. 表达式索引：对 ->> 取出的文本建 B-tree
CREATE INDEX 索引名 ON 表名 ((列名 ->> '键名'));
CREATE INDEX 索引名 ON 表名 ((列名 -> '键名'));          -- 取出的仍是 jsonb
CREATE INDEX 索引名 ON 表名 (((列名 -> 'a') ->> 'b'));   -- 多层路径

-- 4. 类型转换要显式写进表达式
CREATE INDEX 索引名 ON 表名 (((列名 ->> 'price')::numeric));

-- 5. 部分索引：只索引满足条件的行
CREATE INDEX 索引名 ON 表名 USING GIN (列名) WHERE 条件;

-- 6. 组合索引：GIN + B-tree 配合
CREATE INDEX idx_1 ON 表名 USING GIN (列名);
CREATE INDEX idx_2 ON 表名 (状态列, 创建时间);

先看一个走索引与不走索引的最小对照：

sql
-- 走索引：@> 左侧是索引列本身
SELECT sku FROM products WHERE attributes @> '{"brand": "Acme"}';

-- 不走索引：左侧是表达式，GIN 索引用不上
SELECT sku FROM products WHERE attributes -> 'brand' = '"Acme"'::jsonb;

详细说明 ​

为什么 jsonb 用不了普通 B-tree ​

jsonb 确实实现了比较运算符，所以语法上允许建 B-tree：

sql
CREATE TABLE t_btree (id int, doc jsonb);
CREATE INDEX idx_doc_btree ON t_btree USING btree (doc);
text
CREATE TABLE
CREATE INDEX

但这条索引只在整值排序/比较时生效。原因是 jsonb 的排序规则是「先按内部字节序比较键的个数与内容」，与业务字段完全无关。

sql
-- PostgreSQL 能比较 jsonb，但顺序是按内部字节序，不是按 brand
SELECT
  doc AS v,
  doc > '{"brand": "B"}'::jsonb AS gt_b
FROM (VALUES
  ('{"brand": "A"}'::jsonb),
  ('{"brand": "B"}'::jsonb)
) AS t(doc);
text
       v        | gt_b
----------------+------
 {"brand": "A"} | f
 {"brand": "B"} | f
(2 rows)

{"brand": "B"} 大于它自己也只是 f（相等不算大于）。这种顺序对「筛选 brand 为某值的商品」毫无价值。

再看按字段筛选的真实需求：

sql
-- 按字段筛选：B-tree 索引完全用不上
EXPLAIN (COSTS OFF)
SELECT * FROM t_btree WHERE doc ->> 'brand' = 'Acme';
text
              QUERY PLAN
--------------------------------------
 Seq Scan on t_btree
   Filter: ((doc ->> 'brand'::text) = 'Acme'::text)
(2 rows)

结果必然是顺序扫描。B-tree 只认「列值本身」，不认「列内部某个字段」。

GIN 索引基础 ​

GIN 的建法很直接：

sql
DROP TABLE IF EXISTS products;

CREATE TABLE products (
  id          uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
  sku         varchar(32) NOT NULL UNIQUE,
  name        varchar(128) NOT NULL,
  price       numeric(12,2) NOT NULL,
  attributes  jsonb       NOT NULL DEFAULT '{}'::jsonb,
  created_at  timestamptz NOT NULL DEFAULT now()
);

INSERT INTO products (sku, name, price, attributes) VALUES
  ('PHONE-001', '智能手机 A', 3999.00, '{
     "brand": "Acme",
     "screen": {"size": 6.7, "type": "OLED"},
     "storage": 256,
     "colors": ["black", "blue"],
     "tags": ["5g", "flagship"]
   }'::jsonb),
  ('SHOE-001', '跑鞋 B', 599.00, '{
     "brand": "RunFast",
     "size_range": [38, 39, 40, 41, 42],
     "material": "mesh",
     "tags": ["running", "lightweight"]
   }'::jsonb),
  ('PHONE-002', '智能手机 C', 1999.00, '{
     "brand": "Acme",
     "screen": {"size": 6.1, "type": "LCD"},
     "storage": 128,
     "colors": ["white"],
     "tags": ["5g"]
   }'::jsonb);
text
DROP TABLE
CREATE TABLE
INSERT 0 3
sql
-- 默认 jsonb_ops，功能最全
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);
text
CREATE INDEX
sql
-- 走索引：@> 左侧就是索引列
EXPLAIN (ANALYZE, BUFFERS)
SELECT sku, name FROM products WHERE attributes @> '{"brand": "Acme"}';
text
                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on products  (cost=16.00..25.00 rows=2 width=40) (actual time=0.030..0.032 rows=2 loops=1)
   Recheck Cond: (attributes @> '{"brand": "Acme"}'::jsonb)
   Heap Blocks: exact=1
   Buffers: shared hit=6
   ->  Bitmap Index Scan on idx_products_attributes  (cost=0.00..16.00 rows=2 width=0) (actual time=0.020..0.020 rows=2 loops=1)
         Index Cond: (attributes @> '{"brand": "Acme"}'::jsonb)
         Buffers: shared hit=3
 Planning:
   Buffers: shared hit=24
 Planning Time: 0.300 ms
 Execution Time: 0.060 ms
(10 rows)

看到 Bitmap Index Scan on idx_products_attributes 与 Index Cond: 就说明走索引了。注意还有 Recheck Cond（重检条件）——GIN 返回的是候选行集合，PostgreSQL 必须用原始条件再复核一次，因为倒排索引的位图可能包含假阳性。

jsonb_ops 与 jsonb_path_ops 的取舍 ​

两种操作符类的差别在于索引项怎么组织：

  • jsonb_ops：为文档里的每个键、每个值单独建索引项。因此支持「查某个键/值存在吗」（? 系列）。
  • jsonb_path_ops：为每个「从根到叶的完整路径 + 值」组合建索引项。路径哈希后存储，因此无法回答「某个键单独存在吗」，但 @> 查询更精准、更快、更小。
sql
-- 两种操作符类在同一个表上并存，用于对比体积
CREATE INDEX idx_products_ops      ON products USING GIN (attributes);
CREATE INDEX idx_products_path_ops ON products USING GIN (attributes jsonb_path_ops);
text
CREATE INDEX
CREATE INDEX
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 index_name;
text
       index_name        | index_size
-------------------------+------------
 idx_products_attributes | 32 kB
 idx_products_ops        | 32 kB
 idx_products_path_ops   | 16 kB
(3 rows)

在数据量小时差距是 2 倍；数据量大、键值组合多时，jsonb_path_ops 通常能省下一半到三分之二的空间。

功能差异是决定性的：

sql
-- jsonb_ops 支持 ?：查顶层是否存在某键
SELECT count(*) FROM products WHERE attributes ? 'storage';

-- jsonb_path_ops 不支持 ?：会退化成顺序扫描！
EXPLAIN (COSTS OFF)
SELECT count(*) FROM products WHERE attributes ? 'storage';
text
 count
-------
     2
(1 row)

           QUERY PLAN
--------------------------------
 Aggregate
   ->  Seq Scan on products
         Filter: (attributes ? 'storage'::text)
(3 rows)

这是最容易踩的坑：即便建立了 jsonb_path_ops 索引，? 仍然走顺序扫描——因为该操作符类没有 ? 对应的索引项。此时优化器不会报错，只会静默退化。

操作符支持对照表（务必记住）：

操作符含义jsonb_opsjsonb_path_ops
@>包含支持支持
<@被包含支持（反向用 @> 改写更快）支持（同左）
?键/元素存在支持不支持
?|任一存在支持不支持
?&全部存在支持不支持
@? / @@jsonpath 判断支持支持

<@ 是个例外：两种操作符类都「支持」，但优化器通常会把 a <@ b 改写成 b @> a 才能用上索引，所以写在左侧的应该是那个常量。

sql
-- 推荐：常量在左，列在右，直接命中 @>
SELECT sku FROM products WHERE '{"brand": "Acme"}'::jsonb <@ attributes;

-- 等价但优化器要改写才能用索引
SELECT sku FROM products WHERE attributes @> '{"brand": "Acme"}'::jsonb;

表达式索引：征服 ->> 的利器 ​

GIN 只解决「结构化包含」查询。而「取某个字段值再比较」——例如 attributes ->> 'brand' = 'Acme'——GIN 索引完全无能为力，因为 ->> 的结果不在索引里。

sql
-- attributes 上有 GIN，但这个查询不走它
EXPLAIN (ANALYZE, COSTS OFF, BUFFERS)
SELECT sku FROM products WHERE attributes ->> 'brand' = 'Acme';
text
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Seq Scan on products (actual time=0.008..0.009 rows=2 loops=1)
   Filter: ((attributes ->> 'brand'::text) = 'Acme'::text)
   Rows Removed by Filter: 1
   Buffers: shared hit=1
(4 rows)

解决办法是把这段表达式本身建成 B-tree 索引：

sql
-- 对 ->> 的文本结果建 B-tree
CREATE INDEX idx_products_brand ON products ((attributes ->> 'brand'));
text
CREATE INDEX
sql
EXPLAIN (ANALYZE, COSTS OFF, BUFFERS)
SELECT sku FROM products WHERE attributes ->> 'brand' = 'Acme';
text
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Index Scan using idx_products_brand on products (actual time=0.010..0.011 rows=2 loops=1)
   Index Cond: ((attributes ->> 'brand'::text) = 'Acme'::text)
   Buffers: shared hit=4
(3 rows)

表达式必须与索引定义逐字一致（包括括号），否则用不上：

sql
-- 错误！COALESCE 包裹后表达式不等于索引定义
EXPLAIN (COSTS OFF)
SELECT sku FROM products WHERE COALESCE(attributes ->> 'brand', '') = 'Acme';
text
             QUERY PLAN
-------------------------------------
 Seq Scan on products
   Filter: (COALESCE((attributes ->> 'brand'::text), ''::text) = 'Acme'::text)
(2 rows)

多层路径与取出的类型都要注意：

表达式返回类型索引类型说明
(attributes ->> 'brand')textB-tree最常用，字符串等值/范围查询
(attributes -> 'brand')jsonbB-tree结果仍是 jsonb，比较需带 JSON 引号
((attributes -> 'screen') ->> 'type')textB-tree多层下钻，括号要与查询一侧完全一致
((attributes ->> 'storage')::int)integerB-tree务必显式转换
(attributes ->> 'price')textB-tree慎用：文本比较 '100' < '99' 是错的

类型转换的坑值得单独看：

sql
-- 文本比较：按字典序，'1000' < '999' 为真，与数值语义相反
SELECT ('1000' < '999') AS text_compare, (1000 < 999) AS num_compare;
text
 text_compare | num_compare
--------------+-------------
 t            | f
(1 row)

所以数值字段一定要在索引里转换：

sql
CREATE INDEX idx_products_storage ON products (((attributes ->> 'storage')::int));
text
CREATE INDEX
sql
-- 查询侧也要写成同样的表达式，才能命中
EXPLAIN (COSTS OFF)
SELECT sku FROM products WHERE (attributes ->> 'storage')::int > 200;
text
                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Index Scan using idx_products_storage on products
   Index Cond: (((attributes ->> 'storage'::text))::integer > 200)
(2 rows)

@> 查询走索引的严格条件 ​

@> 要命中 GIN 索引，必须满足两个条件：

  1. 左侧必须是索引列本身，不能是表达式、函数返回值或子查询结果。
  2. 右侧必须是 jsonb 常量或参数化值，能被优化器在规划期当成「已知常量」处理。
sql
-- 正确：左侧是列，右侧是常量
SELECT sku FROM products WHERE attributes @> '{"brand": "Acme"}';

-- 错误！左侧是关键表达式，索引列在右边，无法用 GIN 索引
SELECT sku FROM products WHERE attributes -> 'screen' @> '{"type": "OLED"}'::jsonb;

-- 错误！条件里对列做了函数包装，索引失效
SELECT sku FROM products WHERE jsonb_typeof(attributes) = 'object' AND attributes @> '{"brand": "Acme"}';

第二、三种写法分别应该改写成：

sql
-- 改写一：把路径表达进右侧的 jsonb 常量里
SELECT sku FROM products WHERE attributes @> '{"screen": {"type": "OLED"}}'::jsonb;

-- 改写二：结构判断交给约束，别放在查询条件里
SELECT sku FROM products WHERE attributes @> '{"brand": "Acme"}';

再验证一次改写后的执行计划：

sql
EXPLAIN (ANALYZE, COSTS OFF, BUFFERS)
SELECT sku FROM products WHERE attributes @> '{"screen": {"type": "OLED"}}'::jsonb;
text
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Bitmap Heap Scan on products (actual time=0.021..0.022 rows=1 loops=1)
   Recheck Cond: (attributes @> '{"screen": {"type": "OLED"}}'::jsonb)
   Heap Blocks: exact=1
   Buffers: shared hit=6
   ->  Bitmap Index Scan on idx_products_attributes (actual time=0.014..0.014 rows=1 loops=1)
         Index Cond: (attributes @> '{"screen": {"type": "OLED"}}'::jsonb)
         Buffers: shared hit=3
(8 rows)

jsonb_path_ops 对这类嵌套包含尤其高效，因为它正是按「路径 + 值」建项的，能直接定位到 screen.type = OLED 这一项。

部分索引（Partial Index）与 jsonb 的结合 ​

部分索引只索引满足 WHERE 条件的行，在 jsonb 场景下非常实用，因为同一个字段在不同行里结构不同，但某一类行的结构是稳定的。

sql
-- 只给「上架」商品的属性建索引：下架商品的查询少，不值当
ALTER TABLE products ADD COLUMN status varchar(16) NOT NULL DEFAULT 'on_sale';
text
ALTER TABLE
sql
CREATE INDEX idx_products_attrs_active
  ON products USING GIN (attributes)
  WHERE status = 'on_sale';
text
CREATE INDEX
sql
-- 查询条件里必须带上 status = 'on_sale'，优化器才敢用部分索引
EXPLAIN (ANALYZE, COSTS OFF, BUFFERS)
SELECT sku FROM products
WHERE status = 'on_sale'
  AND attributes @> '{"brand": "Acme"}';
text
                                       QUERY PLAN
----------------------------------------------------------------------------------------------
 Bitmap Heap Scan on products (actual time=0.025..0.026 rows=2 loops=1)
   Recheck Cond: (attributes @> '{"brand": "Acme"}'::jsonb)
   Filter: ((status)::text = 'on_sale'::text)
   Buffers: shared hit=5
   ->  Bitmap Index Scan on idx_products_attrs_active (actual time=0.018..0.018 rows=2 loops=1)
         Index Cond: (attributes @> '{"brand": "Acme"}'::jsonb)
(7 rows)

jsonb 上的部分索引常见用法：

场景部分索引条件收益
只查活跃/上架数据WHERE is_active索引体积大幅缩小
只查某种数据形态WHERE jsonb_typeof(col) = 'object'排除异常行
只索引非空扩展字段WHERE col ? 'tags'避免为「没有该字段」的行建项
只查近期数据WHERE created_at > '2026-01-01'配合冷热分离

索引体积与写入代价的权衡 ​

GIN 不是免费的。每插入一行，PostgreSQL 要解析 jsonb 并为文档里每一个键和值插入倒排项；更新时还要增删项。索引项越多，写入越慢。

因素对索引体积的影响对写入速度的影响
文档越大、键值越多索引项线性增长每次写入的项更多，更慢
用 jsonb_path_ops 而非 jsonb_ops减少约 50%~70%略快
用部分索引按比例缩小只维护命中条件的行
数组元素很多每个元素建一项，膨胀快明显变慢
fastupdate 开启（默认）—写入快，但查询要先合并 pending list

fastupdate 是 GIN 的一个重要机制，值得了解：

sql
-- 查看 GIN 索引的 fastupdate 状态
SELECT
  indexrelid::regclass AS index_name,
  recheck,
  pg_relation_size(indexrelid) AS size
FROM pg_index
JOIN pg_class ON pg_class.oid = pg_index.indexrelid
WHERE indrelid = 'products'::regclass;
text
       index_name        | recheck |  size
-------------------------+---------+-------
 idx_products_attributes | f       | 32768
(1 row)

fastupdate = on（默认）时，新插入的项先攒在一块「待处理列表（pending list）」里，写满才批量合并进主索引。好处是写入快，坏处是查询时要先扫一遍待处理列表。写入量极大且查询较少的表可以保持默认；写入中等、查询敏感的表可以关掉：

sql
ALTER INDEX idx_products_attributes SET (fastupdate = off);
text
ALTER INDEX

用 pgstattuple 扩展可以精确观察 GIN 的内部结构（需要安装扩展）：

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

SELECT * FROM pgstatginindex('idx_products_attributes');
text
 version | pending_pages | pending_tuples
---------+---------------+----------------
       2 |             0 |              0
(1 row)

pending_pages 与 pending_tuples 长期居高不下，说明待处理列表积压，查询会变慢，此时可以考虑手动合并：

sql
VACUUM products;   -- 触发 GIN 待处理列表合并

用 EXPLAIN 验证是否走索引 ​

判断索引是否生效，看三处：Bitmap Index Scan on 索引名、Index Cond:、以及 Buffers 里 shared hit 是否远小于表的块数。下面把「走索引」与「不走索引」两种情况并排对照。

走索引的情况：

sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT sku FROM products WHERE attributes @> '{"brand": "Acme"}';
text
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Bitmap Heap Scan on products (actual rows=2 loops=1)
   Recheck Cond: (attributes @> '{"brand": "Acme"}'::jsonb)
   Heap Blocks: exact=1
   Buffers: shared hit=6
   ->  Bitmap Index Scan on idx_products_attributes (actual rows=2 loops=1)
         Index Cond: (attributes @> '{"brand": "Acme"}'::jsonb)
         Buffers: shared hit=3
(7 rows)

不走索引的情况：

sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT sku FROM products WHERE attributes ->> 'brand' = 'Acme';
text
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Seq Scan on products (actual rows=2 loops=1)
   Filter: ((attributes ->> 'brand'::text) = 'Acme'::text)
   Rows Removed by Filter: 1
   Buffers: shared hit=1
(4 rows)

两种计划的判读要点：

计划节点含义结论
Map Index Scan / Bitmap Index Scan用到了索引好
Index Cond:索引真正下推的条件检查是否包含你的关键条件
Recheck Cond:GIN 候选行的复核正常，GIN 总会有
Filter: + Rows Removed by Filter索引没覆盖，逐行判断需要优化
Seq Scan 出现在大表上顺序扫描索引未命中
Buffers: shared hit 很小只读了少量页索引生效的旁证

必须用 ANALYZE：只有真实执行才能拿到 actual rows，也才能看出「估算 1 行、实际 10 万行」这类统计信息不准的问题。COSTS OFF 只是为了让输出更易读，排查时可保留 cost。

实战示例 ​

下面为「商品多属性查询」设计一套完整索引方案，覆盖三类查询：结构化包含、单字段精确匹配、单字段范围筛选。

第一步：建表并灌入 1 万行数据

sql
DROP TABLE IF EXISTS products;

CREATE TABLE products (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku         varchar(32) NOT NULL UNIQUE,
  name        varchar(128) NOT NULL,
  price       numeric(12,2) NOT NULL,
  status      varchar(16) NOT NULL DEFAULT 'on_sale',
  attributes  jsonb       NOT NULL DEFAULT '{}'::jsonb,
  created_at  timestamptz NOT NULL DEFAULT now()
);

INSERT INTO products (sku, name, price, status, attributes)
SELECT
  'SKU-' || lpad(g::text, 6, '0'),
  '商品 ' || g,
  round((random() * 5000 + 10)::numeric, 2),
  CASE WHEN g % 10 = 0 THEN 'off_sale' ELSE 'on_sale' END,
  jsonb_build_object(
    'brand',    'Brand-' || (g % 20),
    'category', (ARRAY['phone','shoe','bag','watch'])[1 + (g % 4)],
    'storage',  (ARRAY[64, 128, 256, 512])[1 + (g % 4)],
    'colors',   jsonb_build_array('c' || (g % 5), 'c' || ((g + 1) % 5)),
    'tags',     jsonb_build_array(
                  (ARRAY['5g','flagship','lightweight','hot'])[1 + (g % 4)],
                  (ARRAY['new','promo','eco'])[1 + (g % 3)]
                )
  )
FROM generate_series(1, 10000) AS g;
text
DROP TABLE
CREATE TABLE
INSERT 0 10000

第二步：分析统计信息（重要，否则优化器可能选错计划）

sql
ANALYZE products;
text
ANALYZE

第三步：建立组合索引

sql
-- 结构化包含查询：GIN + jsonb_path_ops（本项目不用 ? 系列，选体积小的）
CREATE INDEX idx_products_attrs_path
  ON products USING GIN (attributes jsonb_path_ops);

-- 单字段精确匹配：表达式索引
CREATE INDEX idx_products_brand
  ON products ((attributes ->> 'brand'));

CREATE INDEX idx_products_category
  ON products ((attributes ->> 'category'));

-- 单字段范围筛选：转换类型后再建
CREATE INDEX idx_products_storage
  ON products (((attributes ->> 'storage')::int));

-- 核心列普通 B-tree（状态 + 时间，用于分页）
CREATE INDEX idx_products_status_created
  ON products (status, created_at DESC);

-- 部分索引：只索引上架商品的属性
CREATE INDEX idx_products_attrs_active
  ON products USING GIN (attributes jsonb_path_ops)
  WHERE status = 'on_sale';
text
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX

第四步：查询一——结构化包含（多属性组合）

sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT sku, name
FROM products
WHERE attributes @> '{"category": "phone", "storage": 256}'
  AND status = 'on_sale'
ORDER BY created_at DESC
LIMIT 10;
text
                                          QUERY PLAN
----------------------------------------------------------------------------------------------
 Limit (actual rows=10 loops=1)
   Buffers: shared hit=42
   ->  Sort (actual rows=10 loops=1)
         Sort Key: created_at DESC
         Sort Method: top-N heapsort  Memory: 25kB
         ->  Bitmap Heap Scan on products (actual rows=235 loops=1)
               Recheck Cond: (attributes @> '{"category": "phone", "storage": 256}'::jsonb)
               Filter: ((status)::text = 'on_sale'::text)
               Heap Blocks: exact=32
               Buffers: shared hit=38
               ->  Bitmap Index Scan on idx_products_attrs_path (actual rows=250 loops=1)
                     Index Cond: (attributes @> '{"category": "phone", "storage": 256}'::jsonb)
                     Buffers: shared hit=6
(12 rows)

@> 命中了 jsonb_path_ops 索引，把 1 万行缩到 250 个候选，再按 status 过滤。

第五步：查询二——单字段精确匹配

sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT sku, name, (attributes ->> 'brand') AS brand
FROM products
WHERE attributes ->> 'brand' = 'Brand-7';
text
                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Index Scan using idx_products_brand on products (actual rows=500 loops=1)
   Index Cond: ((attributes ->> 'brand'::text) = 'Brand-7'::text)
   Buffers: shared hit=513
(3 rows)

第六步：查询三——单字段范围筛选

sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT sku, (attributes ->> 'storage')::int AS storage
FROM products
WHERE (attributes ->> 'storage')::int >= 256
ORDER BY (attributes ->> 'storage')::int DESC
LIMIT 5;
text
                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Limit (actual rows=5 loops=1)
   Buffers: shared hit=15
   ->  Index Scan using idx_products_storage on products (actual rows=5 loops=1)
         Index Cond: (((attributes ->> 'storage'::text))::integer >= 256)
         Buffers: shared hit=12
(5 rows)

第七步：对比索引体积与写入代价

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_brand         | 1040 kB
 idx_products_attrs_path    | 552 kB
 idx_products_attrs_active  | 480 kB
 idx_products_category      | 464 kB
 idx_products_storage       | 384 kB
 idx_products_status_created| 320 kB
(6 rows)

第八步：清理

sql
DROP TABLE products;
DROP TABLE t_btree;
text
DROP TABLE
DROP TABLE

注意事项 ​

  • jsonb 上面的 B-tree 索引基本没用。它能建、语法不报错，但不解决「按字段筛选」的问题，白占空间与写入开销：

    sql
    -- 错误！按字段筛选时这个索引完全不会被用到
CREATE INDEX idx_doc_btree ON t_btree USING btree (doc);

  • ? ?| ?& 在 jsonb_path_ops 索引上会静默退化为顺序扫描。优化器不报错，只是不用索引。需要存在性判断就必须用默认的 jsonb_ops。

  • 左侧是表达式时 GIN 失效。attributes -> 'screen' @> '...' 不能走索引用；要把路径并进右侧常量：attributes @> '{"screen": {...}}'。

  • 表达式索引的表达式必须与查询逐字一致。多一层 COALESCE、少一对括号、漏掉 ::int，都会导致 Index Cond 消失。

  • 文本比较数值是错的。(attributes ->> 'price') > '1000' 是字符串比较，必须显式 ::numeric，索引表达式也要同步转换。

  • 函数包装列会让索引失效：

    sql
    -- 错误！jsonb_typeof 包住列，索引无法下推
WHERE jsonb_typeof(attributes) = 'object' AND attributes @> '{"brand": "Acme"}';

  • 部分索引必须在查询里写全条件。查询没带 status = 'on_sale' 时，WHERE status = 'on_sale' 的部分索引不会被选中。

  • GIN 索引会放大写放大。每个键值对都是一个索引项，更新 jsonb 列（整值重写）会同时增删所有受影响的项。jsonb 列上索引越多，写入越慢。

  • 数组元素很多时索引膨胀极快。一个含 1000 个元素的数组会生成上千个索引项。大数组不适合建 GIN。

  • EXPLAIN 不带 ANALYZE 只看估算。有些计划在真实执行时才会切换节点（如估算行数偏差导致）。排查性能问题务必加 ANALYZE。

  • 索引不会自动「越用越好」。统计信息过时会让优化器选错计划，大量增删改后要跑 ANALYZE，必要时 REINDEX。

  • 不要给每个 jsonb 字段都建表达式索引。表达式索引是 B-tree，只覆盖「一个字段一种用法」，字段多了索引数量爆炸，反而拖慢写入。

最佳实践 ​

  • 先想清楚查询模式再建索引。按「包含查询 → GIN」「单字段等值 → 表达式索引」「单字段范围 → 带转换的表达式索引」三档分别处理：

    sql
    CREATE INDEX idx_attrs     ON products USING GIN (attributes jsonb_path_ops);   -- 包含
CREATE INDEX idx_brand     ON products ((attributes ->> 'brand'));              -- 等值
CREATE INDEX idx_storage   ON products (((attributes ->> 'storage')::int));     -- 范围

  • 能用 jsonb_path_ops 就别用默认的 jsonb_ops。除非确实需要 ? ?| ?&，否则 jsonb_path_ops 体积更小、@> 更快。

  • 把高频筛选的字段提升为独立列。这是最有效的优化：B-tree 索引远比 jsonb 表达式索引小、快、可组合：

    sql
    brand      varchar(64),      -- 从 attributes 提升出来
category   varchar(32),
attributes jsonb NOT NULL DEFAULT '{}'::jsonb   -- 只放真正的动态属性

  • 给 jsonb 列写查询时保持「左侧是列」的形态：

    sql
    -- 推荐
WHERE attributes @> '{"tags": ["promo"]}'
-- 次优（不走 GIN）
WHERE attributes -> 'tags' ? 'promo'

  • 给 jsonb 的 jsonpath 查询加 GIN 索引。@? 与 @@ 同样吃 GIN 索引，写 jsonpath 时不要退化成函数调用形式：

    sql
    -- 走索引
WHERE attributes @? '$.tags[*] ? (@ == "promo")'
-- 不走索引（函数包装）
WHERE jsonb_path_exists(attributes, '$.tags[*] ? (@ == "promo")')

  • 用部分索引缩小维护成本。给「只查活跃数据」的场景加 WHERE is_active，索引体积和维护代价都按比例下降。

  • 定期用 EXPLAIN (ANALYZE, BUFFERS) 复查关键查询。数据分布会变，上个月有效的计划这个月可能失效：

    sql
    EXPLAIN (ANALYZE, BUFFERS) SELECT ... ;

  • 写入密集的表关闭 fastupdate 或减少 jsonb 索引数量。索引维护是写入延迟的主要来源之一。

  • 批量导入前先删索引，导入后重建。CREATE INDEX 比逐行维护索引快得多，大批量导入时差异可能是数倍：

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

  • 监控索引的真实使用率，把从没被用过的索引删掉：

    sql
    SELECT indexrelid::regclass AS index_name, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE relname = 'products'
ORDER BY idx_scan;

  • 上线前用 ANALYZE 刷新统计信息。尤其是刚灌完大批数据时，没有统计信息优化器只能瞎猜。

下一节

继续学习：JSONB 包含与存在性

参考链接 ​