PostgreSQL — JSONB 查询触发全表扫描

让 PostgreSQL 对 JSONB 包含与路径查询使用索引，在 GIN 默认与 jsonb_path_ops 运算符间选择，并把热点谓词改列为生成列。

症状

　　执行计划把暴力写得明明白白：Seq Scan 配上 Rows Removed by Filter: 9312765 与上百万缓冲读写——九百万行被反序列化然后丢弃，只为一个高选择性谓词。修复空间就是一张“运算符类对照表”：@> 需要 GIN；->> ‘key’ = ‘value’ 需要对提取表达式建索引；? 存在运算符只有默认 jsonb_ops 能服务，jsonb_path_ops 不行。动态属性设计（EAV 式 jsonb）随表增长变慢，正是缺这张表的表现。

诊断思路

　　先跑 EXPLAIN (ANALYZE, BUFFERS) 确认全扫与被丢弃的行数；再查 pg_indexes 的 indexdef 看现有索引的运算符类与谓词运算符是否匹配；最后在真实数据上建候选索引对比，Bitmap Index Scan 出现、缓冲数坍缩就是匹配的直接证据。

根因

　　JSONB 没有可供普通 btree 利用的内在顺序与统计，包含运算符需要以 jsonb 结构为键的 GIN 倒排索引。谓词中实际使用的运算符没有匹配的 GIN 运算符类时，规划器无索引可用，退化为扫描并反序列化每一行。

修复与验证

　　按运算符配索引：纯包含热路径用 jsonb_path_ops（索引更小更快）；->> 等值字段建表达式索引；最热的字段提升为 GENERATED ALWAYS AS (payload->>‘type’) STORED 生成列配 btree——真实统计、真实索引、纯 SQL 手感，这是长期最优解。JSONB 留给真正动态的尾部，按包含索引。验证时重跑同一查询：Bitmap Index Scan 取代 Seq Scan、估算行数贴近实际、缓冲读下降两个数量级；再在生产体量与多种 payload 形态下复测，jsonb_path_ops 的索引体积优势与生成列的统计驱动规划都要实测确认。GIN 比 btree 更大更慢更新，重写表要为每次插入付账。

代码示例

EXPLAIN ANALYZE SELECT * FROM events
WHERE payload @> '{"type": "signup"}';

Seq Scan on events  (cost=0.00..284712.00 rows=120 width=987)
  Filter: (payload @> '{"type":"signup"}'::jsonb)
  Rows Removed by Filter: 9312765
Buffers: shared hit=1482112 read=882110

-- pure containment, the most common hot path:
CREATE INDEX events_payload_pathops
  ON events USING gin (payload jsonb_path_ops);

-- equality on a hot extracted field:
CREATE INDEX events_source
  ON events ((payload->>'source'));

-- promote the hottest field to a real column:
ALTER TABLE events
  ADD COLUMN event_type text
  GENERATED ALWAYS AS (payload->>'type') STORED;
CREATE INDEX ON events (event_type);
ANALYZE events;