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

LLMS 索引： [llms.txt](/llms.txt)

---

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

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

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

## 9.3.1 表达式必须与查询语义一致 {#item-9-3-1}

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

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

```sql
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`。下面的查询有不同语义，不能因为“都在处理邮箱”就期待复用：

```sql
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。

典型陷阱是：

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

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

```text
“自然日”到底是 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 不能修复错误的数据模型

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

```sql
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 部分索引的谓词蕴含与参数陷阱 {#item-9-3-2}

### 查询必须在 planning time 蕴含 index predicate

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

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

它有资格服务：

```sql
WHERE customer_id = $1
  AND state = 'open'
```

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

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

```sql
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 为什么是确定性反例

本章候选：

```sql
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：

```sql
WHERE customer_id = 42
  AND order_status = 'placed'
```

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

```sql
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。

```bash
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%，大小和维护成本会完全不同。定期观察：

```sql
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 与可见性图 {#item-9-3-3}

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

考虑：

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

候选：

```sql
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 有两层条件：

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

`EXPLAIN (ANALYZE, BUFFERS)` 中的：

```text
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 应同时满足：

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

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

## 延伸阅读

- [PostgreSQL 18：Indexes on Expressions](https://www.postgresql.org/docs/18/indexes-expressional.html)
- [PostgreSQL 18：Partial Indexes](https://www.postgresql.org/docs/18/indexes-partial.html)
- [PostgreSQL 18：Index-Only Scans and Covering Indexes](https://www.postgresql.org/docs/18/indexes-index-only-scans.html)
- [PostgreSQL 18：Function Volatility Categories](https://www.postgresql.org/docs/18/xfunc-volatility.html)
- [PostgreSQL 18：Visibility Map](https://www.postgresql.org/docs/18/storage-vm.html)

---

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