9.3 表达式、部分与覆盖索引

普通索引把表列作为 key；expression、partial 与 covering index 分别回答三个更精确的问题：

expression → 查询真正比较的是否是一个规范化表达式？
partial    → 是否只有一个可在 planning time 证明的稳定子集值得索引？
INCLUDE    → 定位完成后，是否值得复制少量 payload 来避免 heap visit？

三者可以组合，但每加一层都扩大合同：查询语义必须吻合，写入必须维护更多内容，验证必须覆盖更多失效条件。

9.3.1 表达式必须与查询语义一致

索引表达式与查询表达式要能被 planner 对应

大小写无关的登录查找可以写成：

CREATE UNIQUE INDEX account_email_ci_uidx
ON account (lower(email));

SELECT account_id
FROM account
WHERE lower(email) = lower($1);

索引 key 是 lower(email)，不是原始 email。下面的查询有不同语义，不能因为“都在处理邮箱”就期待复用：

WHERE email = $1
WHERE upper(email) = upper($1)
WHERE trim(lower(email)) = trim(lower($1))
WHERE lower(email) COLLATE "C" = lower($1) COLLATE "C"

planner 能识别一些等价变换，但不会证明任意业务函数、cast 或字符串处理“效果一样”。设计时应让规范化规则只有一个权威表达：

在 SQL 与索引中复用同一表达式；
或把它做成 generated column，再查询和索引该列；
若它定义身份唯一性，明确原值能否保留多个展示形式；
通过真实 parameter、collation 与 locale 做 correctness 测试。

UNIQUE(lower(email)) 表达“规范化后不得重复”，这已是数据约束，不再只是性能。不能按 unused index 清理。

volatility 是正确性边界

PostgreSQL 要求 index definition 中用到的函数和操作符是 IMMUTABLE。原因很直接：同一行的 index key 必须在未来仍表示同一个值。依赖当前时间、会话时区、配置、外部表或可变环境的函数不能安全成为 key。

典型陷阱是：

-- placed_at 为 timestamptz；结果会受会话 TimeZone 影响
CREATE INDEX bad_daily_idx
ON orders (date(placed_at));

服务器会拒绝非 immutable 表达式。正确方案不是把自定义函数随手标成 IMMUTABLE，而是先固定业务语义：

“自然日”到底是 UTC、租户时区还是订单发生时记录的当地日期？
时区规则未来变化时，历史归属要不要变化？

如果合同是固定 UTC 日，可以用明确、可验证的 UTC 派生值；如果每租户时区不同，往往应在写入时保存业务日期或按租户和 UTC range 查询。错误声明 volatility 会让 planner 相信一个并不成立的不变量，结果可能是漏行，而不仅是变慢。

还要检查：

collation 版本升级后的排序/相等语义；
ICU/libc locale 差异；
extension 或自定义函数升级；
implicit cast 是否改变 operator/opclass；
expression 的返回类型和长度；
函数 schema qualification 与受控 search_path。

表达式通常只在插入及非 HOT 更新时计算，读取可直接用已保存 key；代价因此从读侧转移到写侧。复杂表达式要同时测 CPU、WAL、index size 与 build 时间。

expression 不能修复错误的数据模型

下面这些候选要先问是否应该改模型：

lower(trim(email))
(payload ->> 'tenant_id')::bigint
date_trunc('hour', occurred_at)
coalesce(deleted_at, 'infinity')

若 JSONB key 实际是高频连接键，生成强类型列或普通列通常比反复 cast 更可审计；若“未删除”是稳定热点子集，partial predicate 可能比把 infinity 混入 key 更清晰。expression index 是精确工具，不是把所有 schema 欠账藏进 planner 的办法。

9.3.2 部分索引的谓词蕴含与参数陷阱

查询必须在 planning time 蕴含 index predicate

部分索引只保存满足 predicate 的行：

CREATE INDEX open_ticket_customer_idx
ON ticket (customer_id, created_at DESC)
WHERE state = 'open';

它有资格服务：

WHERE customer_id = $1
  AND state = 'open'

因为查询条件明确蕴含 state='open'。PostgreSQL 能处理完全匹配及少量简单不等式蕴含，例如 x < 1 可蕴含 x < 2；它没有通用定理证明器，也不会在 runtime 取到值以后再重新证明 arbitrary predicate。

因此这些看似接近的条件可能不能使用同一 partial index：

WHERE state IN ('open', 'retry')
WHERE lower(state) = 'open'
WHERE state = current_setting('app.state')
WHERE state = $1                 -- generic plan 时未知

设计 partial index 时，把 predicate 连同 query text、parameterization 和 plan mode 一起写入合同。只保存一个手工 literal 的 EXPLAIN 不够。

generic parameter 为什么是确定性反例

本章候选：

CREATE INDEX ch09_order_placed_cover_idx
ON shop_private.ch09_order_probe
    (customer_id, placed_at DESC)
INCLUDE (order_no, amount_minor)
WHERE order_status = 'placed';

literal query 可以证明 predicate：

WHERE customer_id = 42
  AND order_status = 'placed'

实验随后准备带两个参数的语句：

PREPARE ch09_order_lookup(bigint, text) AS
SELECT order_no, placed_at, amount_minor
FROM shop_private.ch09_order_probe
WHERE customer_id = $1
  AND order_status = $2
ORDER BY placed_at DESC
LIMIT 20;

在 force_custom_plan 下，planner 为本次 EXECUTE (42, 'placed') 看见具体值，可以证明并使用 partial index；在 force_generic_plan 下，它必须生成适用于任意 $2 的计划，无法假设所有值都是 placed，因此不能使用该 partial index。

psql -X -w \
  --dbname='service=pg36-admin' \
  --set=plan_mode=force_custom_plan \
  --file=static/labs/ch09/order-parameter.sql

psql -X -w \
  --dbname='service=pg36-admin' \
  --set=plan_mode=force_generic_plan \
  --file=static/labs/ch09/order-parameter.sql

force_* 只用于构造确定性 A/B，不是生产修复。生产是否得到 custom/generic plan 还受 prepared statement 执行历史、driver/pool 协议和 planner 判断影响。可选方案按语义权衡：

让稳定状态保留为 SQL literal，只参数化 customer；
使用 custom plan，但要比较 planning cost 和所有参数桶；
建普通索引，接受索引更大、写成本更高；
为不同状态使用明确的 query family；
若状态集合与生命周期已成为数据分区问题，重新审视 schema/partitioning。

不要通过伪造 IMMUTABLE 函数、强制全局 plan mode 或复制大量近似 partial index 绕过合同。

partial index 也会漂移

“只索引 5% 活跃行”今天很划算，若状态分布变成 70%，大小和维护成本会完全不同。定期观察：

SELECT
    c.relname,
    pg_size_pretty(pg_relation_size(c.oid)) AS index_size,
    i.indisvalid,
    pg_get_expr(i.indpred, i.indrelid) AS predicate,
    pg_get_indexdef(i.indexrelid) AS definition
FROM pg_index AS i
JOIN pg_class AS c
  ON c.oid = i.indexrelid
WHERE i.indpred IS NOT NULL;

partial unique index 还能表达“只在满足条件的行中唯一”，例如每个用户最多一个 active token。这是业务约束，必须给并发写入做失败测试。partial index 不是 partition：它不提供 retention、独立 vacuum、partition pruning 或 detach/drop 生命周期。

9.3.3 `INCLUDE`、index-only scan 与可见性图

covering 是查询与索引的共同属性

考虑：

SELECT order_no, amount_minor, placed_at
FROM orders
WHERE customer_id = $1
ORDER BY placed_at DESC
LIMIT 20;

候选：

CREATE INDEX orders_customer_time_cover_idx
ON orders (customer_id, placed_at DESC)
INCLUDE (order_no, amount_minor);

customer_id, placed_at 是 search/order key；order_no, amount_minor 只是 payload：

它们不参与 B-tree 定位或排序；
unique index 的唯一性只作用于 key，不包括 INCLUDE；
payload 可以是 access method 不理解的类型，因为只需原样保存；
query 若再读取一个未保存列，便不再 covered。

PostgreSQL 14–18 中 B-tree 总能支持 index-only scan；GiST/SP-GiST 只在部分 opclass 上能重建原值，GIN 不能。INCLUDE 本身只受支持它的 access method 接受，不能把“有 INCLUDE”与“本次一定 Index Only Scan”等同。

为什么仍可能访问 heap

MVCC 可见性信息不保存在每个 index tuple 中。执行器必须确认当前 snapshot 下 heap tuple 是否可见；只有对应 heap page 的 visibility map all-visible bit 已设置，才能跳过 heap。

所以 index-only scan 有两层条件：

query 所需值都能从 index 得到
AND
目标 heap page 对当前机制可由 visibility map 证明 all-visible

EXPLAIN (ANALYZE, BUFFERS) 中的：

Heap Fetches: 0

是这次执行没有回 heap 的证据，不是索引永久保证。INSERT/UPDATE/DELETE 会清除相关 page 的 all-visible bit，VACUUM 在满足条件后再设置。高 churn 表即使 covered，也可能频繁 heap fetch；为一次 benchmark 手动 VACUUM 只能证明静态上限，不能模拟生产稳态。

本章在候选创建后执行受控 VACUUM (ANALYZE)，订单和库存 after plan 都要求 Index Only Scan + Heap Fetches=0。这条断言只属于确定性 fixture；生产验收要在真实写入、autovacuum 和 snapshot 条件下看 heap fetch 比例。

payload 不是免费的

增加 INCLUDE 会：

复制 payload，增大 leaf tuple、index size 与 cache footprint；
增加 INSERT/UPDATE 的 WAL 和维护；
更新 included column 时需要维护该索引，也会影响 HOT；
让 build、backup、restore、replication 和 vacuum 多付成本；
宽值可能超过 index tuple 大小上限，导致写入失败；
B-tree 只要有 non-key column，就不会使用 deduplication。

虽然 B-tree upper level 会移除 non-key payload，使导航层保持较小，leaf 层成本仍真实存在。不要 INCLUDE (*)，也不要为了“可能以后少一次 heap visit”复制 JSON、正文或频繁变化的状态。

一个可保留的 covering candidate 应同时满足：

declared query 高频且返回列稳定、窄；
定位 key 与 ordering 已正确；
实际 plan 使用 index-only，而非只在理论上可用；
真实 VM/all-visible 状态下 heap fetch 明显减少；
size/cache/write/WAL/HOT 代价可接受；
payload 变化不会让维护成本压过读取收益；
不与另一个更短索引形成无意义重叠。

如果表频繁更新或 query 本来就要访问 heap 中的宽列，普通短索引往往更好。

延伸阅读

上一节：从谓词、连接与排序推导索引 · 返回本章目录 · 下一节：索引也有写入和生命周期成本 · 查看全书目录 · 查看索引中心

最后更新于 2026-07-30

9.2 从谓词、连接与排序推导索引 9.4 索引也有写入和生命周期成本