PostgreSQL / 系统课时
比较不同索引方案
比较不同索引方案
同一个 JSONB 查询可以被多种索引“部分加速”,但候选行数量、索引体积和维护成本并不相同。本课固定一条业务查询,依次比较无索引、整列 GIN、普通表达式索引和部分表达式索引,重点观察计划节点、候选行与过滤位置,而不是只比较一次运行时间。
学习目标
- 会用同一数据集和同一 SQL 公平比较多种 JSONB 索引方案。
- 能从执行计划判断索引命中了哪个条件、还剩多少行需要过滤。
- 能根据查询形态选择 GIN、表达式索引或部分索引,而不是机械追求“走索引”。
固定查询与数据分布
实验表有 10 万行:约 20% 的商品为 active,约 10% 为 red,并让所有 red 商品都处于 active。业务查询要求同时满足状态与颜色条件。
DROP TABLE IF EXISTS product_catalog;
CREATE TABLE product_catalog (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
attributes jsonb NOT NULL
);
INSERT INTO product_catalog (attributes)
SELECT jsonb_build_object(
'status', CASE WHEN i % 5 = 0 THEN 'active' ELSE 'inactive' END,
'color', CASE WHEN i % 10 = 0 THEN 'red' ELSE 'black' END
)
FROM generate_series(1, 100000) AS g(i);
ANALYZE product_catalog;
EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM product_catalog
WHERE attributes @> '{"status":"active"}'
AND attributes->>'color' = 'red';预期输出
Seq Scan on product_catalog (cost=<VALUE> rows=<VALUE> width=<VALUE>) (actual time=<TIME>.. <TIME> rows=10000 loops=1)
Filter: ((attributes @> '{"status": "active"}'::jsonb) AND ((attributes ->> 'color'::text) = 'red'::text))
Rows Removed by Filter: 90000
Buffers: shared hit=<VALUE>
Execution Time: <TIME> ms无索引时必须检查整张表。后面每次只保留一种候选索引,查询文本保持完全不变,这样才能比较“索引本身帮助了什么”。
比较 GIN 与表达式索引
先建立整列 GIN。它能服务 @> 包含查询,因此可先找出约 2 万条 active 候选行,再对 color='red' 做剩余过滤。
CREATE INDEX product_attributes_gin
ON product_catalog USING gin (attributes);
ANALYZE product_catalog;
EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM product_catalog
WHERE attributes @> '{"status":"active"}'
AND attributes->>'color' = 'red';
DROP INDEX product_attributes_gin;
CREATE INDEX product_color_idx
ON product_catalog ((attributes->>'color'));
ANALYZE product_catalog;
EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM product_catalog
WHERE attributes @> '{"status":"active"}'
AND attributes->>'color' = 'red';预期输出
-- GIN 方案:通常先用 status 条件得到约 20000 条候选行
Bitmap Heap Scan on product_catalog ... actual ... rows=10000
Recheck Cond: (attributes @> '{"status": "active"}'::jsonb)
Filter: ((attributes ->> 'color'::text) = 'red'::text)
-> Bitmap Index Scan on product_attributes_gin ... rows=<VALUE>
-- 表达式索引方案:通常先按 color 找到约 10000 条候选行
Bitmap Heap Scan on product_catalog ... actual ... rows=10000
Recheck Cond: ((attributes ->> 'color'::text) = 'red'::text)
Filter: (attributes @> '{"status": "active"}'::jsonb)
-> Bitmap Index Scan on product_color_idx ... rows=<VALUE>具体可能出现 Index Scan 或 Bitmap Heap Scan,由成本模型、缓存和数据规模决定。这里应比较的是候选集合:GIN 先利用 status,约需复查 2 万行;表达式索引先利用更有选择性的 color,候选集合约 1 万行,因此在这组数据上通常更直接。
用部分索引贴合稳定谓词
如果生产查询几乎总是限定 status='active',可以把这个稳定条件放进部分索引谓词,只为活跃商品维护颜色索引。查询中的谓词必须能让规划器证明满足索引条件。
DROP INDEX product_color_idx;
CREATE INDEX product_active_color_idx
ON product_catalog ((attributes->>'color'))
WHERE attributes @> '{"status":"active"}';
ANALYZE product_catalog;
EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM product_catalog
WHERE attributes @> '{"status":"active"}'
AND attributes->>'color' = 'red';预期输出
Bitmap Heap Scan on product_catalog ... actual ... rows=10000
Recheck Cond: ((attributes ->> 'color'::text) = 'red'::text)
-> Bitmap Index Scan on product_active_color_idx ... rows=<VALUE>
Execution Time: <TIME> ms部分索引只覆盖 active 子集,因此索引通常比全量表达式索引更小,写入维护也只发生在满足谓词的行上。代价是适用范围更窄:查询若没有等价的 active 条件,规划器不能把它当作通用颜色索引使用。
不要只看 Execution Time
一次 Execution Time 很容易受缓存预热、并发负载和硬件状态影响。比较方案时优先看计划是否稳定命中目标条件、索引返回多少候选行、Heap 访问量与 Buffers 是否下降,再在相同缓存策略下做多轮基准测试。
本例四种方案的核心差异可以概括为:无索引扫描 10 万行;GIN 擅长通用 JSONB 包含条件,但可能先得到较大的候选集;表达式索引针对固定提取路径,适合频繁按某个键做等值或范围过滤;部分表达式索引在稳定业务谓词下进一步缩小索引覆盖范围。真正的选择标准是查询形态和数据分布,而不是索引类型的名字。
清理实验对象
实验结束后删除表即可同时清理索引。
DROP TABLE product_catalog;预期结果
DROP TABLE在生产环境决定索引方案前,应在接近真实数据分布的副本上重复 EXPLAIN (ANALYZE, BUFFERS),并同时评估索引大小、写放大和查询覆盖面。执行计划更短不等于方案更好,能以最低总体成本稳定服务真实查询才是目标。