PostgreSQL / 系统课时
使用部分索引控制规模
使用部分索引控制规模
JSONB 表往往只有一部分数据真正需要某个索引。本课从真实查询条件反推部分索引范围,重点解决两件事:只为值得加速的数据维护索引,以及确认查询谓词能够被 PostgreSQL 证明满足索引谓词。
学习目标
- 根据稳定的业务过滤条件设计 PostgreSQL 部分索引
- 判断查询谓词能否满足部分索引的使用条件
- 验证索引定义,并识别不适合使用部分索引的场景
从业务查询反推索引范围
假设事件表长期保存历史数据,但线上接口只查询未归档事件,而且只有包含 priority 属性的数据才参与告警筛选。若直接为整张表建立表达式索引,已归档数据和缺少该字段的数据也会产生索引空间与写入维护成本。
可以把这两个长期稳定的条件直接写入部分索引谓词:
CREATE TABLE event_log (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
archived_at timestamptz,
payload jsonb NOT NULL
);
CREATE INDEX idx_event_log_active_priority
ON event_log ((payload ->> 'priority'))
WHERE archived_at IS NULL
AND payload ? 'priority';预期结果
CREATE TABLE
CREATE INDEX这里的 payload ? 'priority' 是合法的 JSONB 键存在判断,检查顶层是否存在 priority 键。只有同时满足“未归档”和“字段存在”的行才进入索引,因此当历史归档数据持续增长时,索引规模不必与整张表等比例增长。
让查询谓词满足索引条件
部分索引并不是查询使用了相同索引表达式就一定可用。规划器还必须在规划阶段证明查询条件蕴含索引的 WHERE 谓词,也就是查询需要的行不会落在部分索引覆盖范围之外。
下面的查询明确保留 archived_at IS NULL 和 payload ? 'priority',因此具备使用该部分索引的条件:
EXPLAIN (COSTS OFF)
SELECT id, payload
FROM event_log
WHERE archived_at IS NULL
AND payload ? 'priority'
AND payload ->> 'priority' = 'high';预期结果
Index Scan using idx_event_log_active_priority on event_log
Index Cond: ((payload ->> 'priority'::text) = 'high'::text)实际计划节点、类型转换展示和缩进会随 PostgreSQL 版本、数据量与统计信息变化;数据量很小时也可能选择顺序扫描。因此验证重点不是强求出现某一种扫描节点,而是确认查询条件与部分索引谓词一致,并在具有代表性的数据量下检查计划选择。
如果去掉 archived_at IS NULL,查询还可能返回已归档行,而这些行并不一定存在于该部分索引中,规划器就不能只依赖这个索引。类似地,如果索引只覆盖某个固定业务状态,而查询需要同时读取其他状态,该部分索引也不能完整满足查询范围。
验证数据库中的索引定义
创建完成后,可以从系统视图读取数据库实际保存的定义,确认索引表达式与部分谓词都符合预期:
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'event_log'
AND indexname = 'idx_event_log_active_priority';预期结果
indexname | indexdef
------------------------------------+------------------------------------------------------------
idx_event_log_active_priority | CREATE INDEX idx_event_log_active_priority ON public.event_log USING btree (((payload ->> 'priority'::text))) WHERE ((archived_at IS NULL) AND (payload ? 'priority'::text))不同 PostgreSQL 版本可能调整括号、类型转换和 schema 的显示形式,但定义中应同时包含表达式 payload ->> 'priority'、条件 archived_at IS NULL,以及合法的键存在判断 payload ? 'priority'。
参数化查询与适用边界
部分索引适合目标数据只占总体一部分,而且过滤条件长期稳定的场景,例如只索引未删除、处理中、未归档或包含某个 JSONB 字段的数据。如果绝大多数行都满足谓词,节省的磁盘空间和写入维护成本通常有限。
参数化查询还需要额外注意:PostgreSQL 必须在生成计划时证明查询条件蕴含部分索引谓词。若决定索引覆盖范围的条件本身被参数化,尤其是在需要生成可复用通用计划时,规划器可能无法证明任意参数值都满足谓词。例如把固定的业务状态改成参数,就可能失去使用相应部分索引的条件。
相比之下,本例把 archived_at IS NULL 和 payload ? 'priority' 保留为固定条件,只把 priority 的匹配值参数化,不会改变这两个部分谓词限定的数据集合。部分索引的关键不是简单给索引增加一个 WHERE,而是用稳定的真实查询条件划定值得承担索引成本的数据范围,再通过索引定义和执行计划持续验证。