PostgreSQL / 系统课时

识别 JSONB 扫描路径

识别 JSONB 扫描路径

面对 JSONB 慢查询,第一步不是立即重建索引,而是确认 PostgreSQL 实际选择了什么扫描路径。本课用 EXPLAIN (ANALYZE, BUFFERS) 识别顺序扫描、普通索引扫描和位图扫描,并重点理解两个 Bitmap 节点怎样协作缩小候选范围、读取 heap。

学习目标

  • 从执行计划判断 JSONB 条件采用了哪种扫描方式
  • 理解 Bitmap Index Scan 与 Bitmap Heap Scan 的职责分工
  • 使用实际行数、缓冲区访问和执行时间判断索引是否真正缩小扫描范围

准备可观察的 JSONB 查询

下面用商品动态属性构造主示例。attributes 保存颜色、品牌等字段,并创建默认 jsonb_ops GIN 索引。示例插入 10000 行,其中约十分之一的商品颜色为红色,使包含条件既有明显命中,又不会接近全表。

sql
DROP TABLE IF EXISTS product_catalog;

CREATE TABLE product_catalog (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku text NOT NULL,
    attributes jsonb NOT NULL
);

INSERT INTO product_catalog (sku, attributes)
SELECT
    'SKU-' || g,
    jsonb_build_object(
        'color', CASE WHEN g % 10 = 0 THEN 'red' ELSE 'blue' END,
        'brand', 'brand-' || (g % 50)
    )
FROM generate_series(1, 10000) AS g;

CREATE INDEX idx_product_attributes_gin
ON product_catalog USING gin (attributes);

ANALYZE product_catalog;

预期结果

text
DROP TABLE
CREATE TABLE
INSERT 0 10000
CREATE INDEX
ANALYZE

若表此前不存在,客户端还可能显示一条 NOTICE;这不影响实验。关键验收点是 INSERT 0 10000,随后 GIN 索引创建成功并完成统计信息收集。

从计划节点判断扫描方式

默认 GIN 索引可以支持 attributes @> '{"color":"red"}' 这样的 JSONB 包含查询。ANALYZE 让执行计划包含真实行数和耗时,BUFFERS 则显示执行阶段访问了多少缓冲区页面。

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, sku
FROM product_catalog
WHERE attributes @> '{"color":"red"}'::jsonb;

预期结果(常见形态,具体成本与节点仍由当前环境的成本模型决定)

text
Bitmap Heap Scan on product_catalog  (actual rows=1000 loops=1)
  Recheck Cond: (attributes @> '{"color": "red"}'::jsonb)
  Heap Blocks: exact=<VALUE>
  Buffers: shared hit=<VALUE>
  ->  Bitmap Index Scan on idx_product_attributes_gin  (actual rows=1000 loops=1)
        Index Cond: (attributes @> '{"color": "red"}'::jsonb)
        Buffers: shared hit=<VALUE>
Planning Time: <TIME> ms
Execution Time: <TIME> ms

实际成本、缓冲区数量和耗时会随 PostgreSQL 版本、机器环境和缓存状态变化。即使索引可用,优化器也可能因为表较小、命中比例偏高或成本参数不同而选择 Seq Scan,所以排查时应以当前实际计划为准,而不是只检查索引是否存在。

Bitmap Index Scan 与 Bitmap Heap Scan 的数据访问关系

理解两个 Bitmap 节点的分工

Bitmap Index Scan 读取索引并生成候选 tuple 位置的位图。它负责定位候选位置,不会把 id、sku 等业务列直接返回给上层;因此看到这个节点,只能说明索引参与了候选集合的缩小。

Bitmap Heap Scan 根据位图集中访问 heap 页面并取得实际记录。与“每命中一个索引项就立即访问一次 heap”相比,这种方式可以把同一页面上的命中项集中处理,在候选记录较多时减少随机页面访问。若位图因为内存压力退化为页级记录,或索引访问方法要求再次确认条件,Recheck Cond 会在读取 tuple 后重新判断。

区分常见扫描路径

JSONB 查询排查时,可以先按扫描节点建立第一层判断,再结合 actual rows、Buffers 和最终执行时间判断访问范围是否合理。

计划节点 含义 常见判断
Seq Scan 顺序读取表并逐行判断条件 没有合适索引,或优化器认为顺序扫描成本更低
Bitmap Index Scan + Bitmap Heap Scan 索引生成候选位图,再集中读取 heap GIN 条件命中一定数量记录时很常见
Index Scan 按索引结果逐步访问记录 常见于可直接支持该访问方式的 B-tree 等索引
Index Only Scan 查询所需列主要由索引提供 JSONB GIN 包含查询通常不应期待它直接返回 id、sku 等业务列

不要只看到计划中出现 Index 就判定查询已经高效。还应比较估算行数与 actual rows 是否偏差明显,观察 heap 页面访问量是否被有效压缩,并确认 Execution Time 是否达到优化目标。

用 planner 开关做对照实验

为了理解优化器的备选路径,可以只在当前会话关闭位图扫描,再执行同一个查询。这个实验回答的是“移除 Bitmap 路径后优化器会选什么”,不能作为生产环境的长期优化配置。

sql
SET enable_bitmapscan = off;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, sku
FROM product_catalog
WHERE attributes @> '{"color":"red"}'::jsonb;

RESET enable_bitmapscan;

预期结果

text
SET
<NON_BITMAP_PLAN>
Planning Time: <TIME> ms
Execution Time: <TIME> ms
RESET

其中 <NON_BITMAP_PLAN> 代表当前环境实际选择的非 Bitmap 计划主体,例如 Seq Scan 或 Parallel Seq Scan。关闭 enable_bitmapscan 并不等于“强制顺序扫描”,剩余可用路径仍由优化器成本模型决定;验收时只需确认计划不再采用 Bitmap 扫描,并在 RESET 后恢复默认 planner 行为。

识别 JSONB 扫描路径时,应按“扫描节点是什么 → 索引是否参与 → heap 如何读取 → 实际读取了多少数据”的顺序分析。Bitmap Index Scan 与 Bitmap Heap Scan 成对出现时,通常表示 PostgreSQL 先利用索引缩小候选范围,再集中读取实际表页。

按自己的节奏,确认真正理解。

目录与阅读设置

记录一个想法

识别 JSONB 扫描路径

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通