PostgreSQL / 系统课时

优化动态属性筛选

优化动态属性筛选

商品、设备或配置记录常把不固定属性放进 JSONB,而真实查询又会叠加类目、状态等稳定条件。本节用一组可直接运行的 SQL,演示如何让 B-tree 与 GIN 分担过滤,并用执行计划判断索引方案是否真正匹配查询形态。

学习目标

  • 为 JSONB 包含查询选择可匹配的 GIN 索引
  • 为高频固定路径设计表达式索引
  • 组合普通列条件与动态属性过滤,并用执行计划验证

先固定查询形态

假设 category 和 enabled 是稳定字段,颜色、尺寸和防水能力保存在 attributes。典型请求是“在 shoe 类目中查找启用、黑色且防水的商品”。稳定条件适合 B-tree,结构包含条件适合 GIN;优化器可能选择其中一个索引,也可能通过位图扫描组合多个索引,并不存在固定的“先查 B-tree、再查 GIN”顺序。

普通列与 JSONB 索引协同过滤路径

建立联合索引与 GIN 主实验

默认 jsonb_ops GIN 操作符类支持 @>,也支持键存在等更多 JSONB 操作符,适合作为通用起点。下面一次完成建表、造数、普通列联合索引、GIN 索引和主查询,确保整段可以直接执行。

sql
DROP TABLE IF EXISTS products;

CREATE TABLE products (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    category text NOT NULL,
    enabled boolean NOT NULL,
    attributes jsonb NOT NULL
);

INSERT INTO products (category, enabled, attributes)
VALUES
    ('shoe', true,  '{"color":"black","waterproof":true,"size":42}'),
    ('shoe', true,  '{"color":"white","waterproof":true,"size":41}'),
    ('shoe', false, '{"color":"black","waterproof":true,"size":43}'),
    ('bag',  true,  '{"color":"black","waterproof":true}');

CREATE INDEX idx_products_category_enabled
ON products (category, enabled);

CREATE INDEX idx_products_attributes_gin
ON products USING gin (attributes);

SELECT id, category, attributes->>'color' AS color
FROM products
WHERE category = 'shoe'
  AND enabled = true
  AND attributes @> '{"color":"black","waterproof":true}'::jsonb;

预期结果

text
DROP TABLE
CREATE TABLE
INSERT 0 4
CREATE INDEX
CREATE INDEX
 id | category | color
----+----------+-------
  1 | shoe     | black

@> 判断左侧 JSONB 是否包含右侧结构,适合多个动态属性组合筛选。生产环境是否同时使用普通列索引与 GIN,取决于数据分布、选择性、统计信息和成本估算,因此“两个索引都存在”不等于“两个索引一定同时参与”。

高频固定路径改用表达式索引

如果大量请求只比较一个固定路径,例如 attributes->>'color' = 'black',可以为该标量表达式建立 B-tree 索引。它比通用 GIN 更直接地服务等值比较,但查询表达式需要与索引表达式保持一致。

sql
CREATE INDEX idx_products_color
ON products ((attributes->>'color'));

SELECT id, category
FROM products
WHERE attributes->>'color' = 'black'
ORDER BY id;

预期结果

text
CREATE INDEX
 id | category
----+----------
  1 | shoe
  3 | shoe
  4 | bag

如果查询改成语义不同的 JSONPath 或 @> 包含表达式,就不能假定这个 B-tree 表达式索引仍会匹配。固定路径适合单独索引,动态组合条件仍应围绕实际使用的 JSONB 操作符设计。

选择 GIN 操作符类

当业务主要依赖 @>、@? 和 @@,且不需要 ?、?|、?& 这类键存在操作符时,可以评估 jsonb_path_ops。它覆盖的操作符更少,但索引通常更紧凑,对包含查询也更有针对性。

sql
DROP INDEX idx_products_attributes_gin;

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

SELECT id
FROM products
WHERE attributes @> '{"waterproof":true}'::jsonb
ORDER BY id;

预期结果

text
DROP INDEX
CREATE INDEX
 id
----
  1
  2
  3
  4

用执行计划验收

演示表只有 4 行,优化器很可能认为顺序扫描更便宜,因此不能用这个小数据集强行证明索引一定出现。生产验证应在接近真实规模和分布的环境中执行 EXPLAIN (ANALYZE, BUFFERS),重点检查扫描节点、估算行数与实际行数偏差,以及缓冲区访问量。

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM products
WHERE category = 'shoe'
  AND enabled = true
  AND attributes @> '{"color":"black"}'::jsonb;

预期结果

text
<QUERY PLAN>
Planning Time: <TIME> ms
Execution Time: <TIME> ms

实际计划节点会随 PostgreSQL 版本、数据量、统计信息和缓存状态变化,因此不固定伪造 Bitmap Index Scan 或 Seq Scan。动态属性筛选应按查询形态分层治理:稳定字段交给普通列与 B-tree,组合包含查询交给匹配的 GIN,高频固定路径再补表达式索引;每增加一个索引,都应对应一组明确而持续的业务查询。

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

目录与阅读设置

记录一个想法

优化动态属性筛选

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

登录哪都通