索引

索引的价值在于改变真实查询需要完成的工作，索引了多少列并不是有效的成功指标。一个能直接找到某位客户最新三笔订单的窄 B-tree，可能胜过无法提供所需顺序的宽索引；读取几乎全部行的查询，则可能正确地忽略两者。评估时要把访问方式、排序和返回数据量放在一起，而不是看到索引名称就宣布优化完成。 从需要的结果出发 假设应用要查询某位客户最新三笔订单。customer 的等值条件缩小候选范围，placed_at 降序选择该客户最新记录，再用 id 作为最后决胜键，处理时间戳相同的情况。(customer, placed_at DESC, id DESC) 就表达了这条查询需要的键顺序。每个键都应对应可解释的条件或排序要求，而不是根据列名凭直觉添加。 收益不只是避免全表扫描。有序索引路径可以取得所需的有限结果后就停止，不必先收集该客户全部订单再排序。去掉 LIMIT，或改成返回大部分行后，成本平衡可能变化。因此同一个索引必须结合完整 SQL 和常用参数评价，不能只盯 WHERE 的一个片段，更不能把少量结果的计划当成大规模导出的保证。 WHERE 条件的文本顺序不会定义索引顺序。PostgreSQL 规划逻辑表达式，把 customer 等值写在 AND 前面或后面，不会重排 B-tree。真正相关的是索引方向、空值顺序、前导键限制和最终 ORDER BY。修改查询时应明确核对这些结构属性，而不是套用“列写在前面就能用索引”的笼统规则。PostgreSQL 18 还能在缺失前导键的不同值较少时，对某些后续键条件采用 B-tree skip scan；没有前导等值条件，并非绝对禁止使用索引。 如实读取执行计划 EXPLAIN 展示计划工作，EXPLAIN ANALYZE 会真正执行并报告实际行数和循环次数。查询返回表中大部分数据，尤其需要索引中没有的宽列时，顺序扫描可能是正确选择。这不证明列顺序错误，也不证明索引创建失败。应把它看成优化器在当前条件下选择的访问成本，而不是必须消除的红色标记。 反过来，出现索引名也不证明查询便宜。索引扫描仍可能访问大量条目和堆元组，bitmap 路径可能还要排序，index-only 计划也可能检查堆可见性。结合 actual rows、适用时的 Heap Fetches、缓冲区活动和 Sort 阅读。顶层有 LIMIT 只有在下层也避免了大量多余工作时才真正省事，不能忽略 LIMIT 下面的完整树。 ...

表达式索引把计算结果保存为索引键。查询比较 lower(email) 时，可以搜索预先计算的键，而不用对整张表逐行执行 lower。代价是索引创建和维护阶段需要完成计算与存储。它不会修改原始 email，也不保证任何查询都会选择这个索引，更不意味着表达式文本有一点不同就必然失去匹配。 索引计算后的含义 lower(email) 索引保存的是与行关联的规范化结果，使用相同计算含义的等值条件可以成为索引条件。直接比较原始 email 的语义不同，不会自动获得这条搜索路径。同样，先 trim 再 lower 增加了一项操作，现有索引不能假定去除空白永远不会改变数据。因此是否匹配要看计算本身，而不是只看它们都涉及 email 列。 PostgreSQL 匹配解析后的表达式，并进行某些简化。多加几层括号本身不会破坏匹配，因此查询不需要与索引定义逐字完全相同。反过来，数学上等价的写法也不一定都能被识别为相同。应检查计划中的实际条件和索引表达式定义，不能只做表面字符串比较，也不能承诺规划器能够证明任意表达式等价。 唯一表达式索引还能执行大小写规范化后的唯一性规则。这是在改变允许存储的数据，不只是提速。已有行规范化后发生冲突，会阻止索引创建；未来插入也会面对新约束。单纯 lower 不是邮箱等价性的完整定义，其他文本还可能受 locale 和 collation 影响。应先明确业务规范化规则，再决定把哪一种计算结果作为约束依据。 让被索引的值保持稳定 索引表达式中的函数必须满足 PostgreSQL 的不可变性要求：结果应依赖输入参数，而不能随着当前时间或外部状态自行变化。now() 不能被当成永远稳定的计算值建索引。为了绕过检查而把函数随意标为 IMMUTABLE，并不会让函数真的不可变，反而可能使已存索引键与后续计算结果不一致，产生比慢查询更严重的正确性问题。 表达式在插入及需要维护索引的更新时计算。即使规划器从不选它，存储和维护成本仍然存在。修改自定义函数行为或规范化策略时，也必须考虑已经保存在索引里的旧结果；仅替换函数代码不会重新计算所有键。重建索引和验证已有数据，应当属于这次语义变更的一部分，而不是上线后发现异常才补做。 坏掉的样子 ORM 重构可能把条件改成 lower(trim(email))，索引却仍保存 lower(email)。查询结果仍正确，但执行器可能扫描更多行。另一种问题是排序规则或自定义函数改变后，代码仍依赖旧键。排查时要对照最终生成的 SQL、索引定义、相关函数行为与目标环境，不能仅根据源代码中的一个方法名推断数据库收到什么。 把唯一表达式索引当成无害的性能调整也很危险。已有规范化重复值会让创建失败，未来写入则受到新的唯一约束。应审计并按业务语义处理冲突，不能为了建索引悄悄删除记录。生产创建还需要选择合适的方式并验证完整结束；目录里存在一个索引名称，不足以证明它已经成功构建并可用，失败的并发构建可能留下需要处理的状态。 快速验证 保存为 expression.sql，执行 psql -X -v ON_ERROR_STOP=1 -f expression.sql，连接可丢弃的 PostgreSQL 18 数据库。实验的一万个地址使用保留的 example.test 域名，表和索引都是临时对象，不会接触真实账户。异常块用于检查错误边界，而不只是展示一条成功查询。 ...

部分索引可以把高频访问的少量行放在更小的结构中。假如 open 任务很少，而请求多数只找 open 任务，在 id 上建立 WHERE status = 'open' 的索引就值得评估。但它是否可用，取决于规划器能为当前查询证明什么，不只是应用在某次执行时碰巧传入了 open。准备语句的计划方式会直接影响这份证据。 证明不需要被排除的行 索引里没有 closed 任务，因此不能独立回答可能需要 closed 行的查询。PostgreSQL 必须在规划时识别查询条件蕴含索引谓词。显式写出的 status = 'open' 提供了这个事实；通用计划中的 status = $1 则没有，因为同一份计划还必须在参数为 closed 时保持正确。规划器不能依靠应用的口头约定丢弃可能需要的数据。 蕴含关系不总是逐字相同。PostgreSQL 能识别一些简单逻辑关系，但不是任意表达式的定理证明器。统一真实需要的谓词，有助于避免重构把原本可用的索引条件藏起来。仅过滤索引键 id 也不能证明状态为 open，即使当前数据或业务习惯让它经常如此。性能优化必须保持查询语义，不能把偶然数据状态当成永久约束。 准备计划改变可用信息 准备语句可以使用根据具体参数规划的 custom plan，也可以使用跨参数复用的 generic plan。状态参数为 open 时，定制计划可能知道所需谓词并考虑部分索引；通用计划面对未知状态，就不能把该索引当成完整答案路径。SQL 文本没有变化，改变的是规划阶段掌握的信息，这也是同一语句不同执行阶段出现不同计划的原因之一。 ...

PostgreSQL 默认的 B-tree 索引支持多种常见需求，包括等值、范围、有序访问和唯一性约束；哈希索引提供的则是更窄的等值访问路径。两者的选择应先从需要支持的运算符与数据约束开始，再进入性能测量。说哈希“只要是等值就一定更快”，或者规划器“几乎从来不会选它”，都是用未经负载验证的概括代替具体问题。先确认能力，再比较成本，顺序不能颠倒。 先检查能力，再比较耗时 B-tree 按运算符类和排序规则保留键的顺序，因此既能定位单个等值键，也可能支持有界范围与 ORDER BY，省掉额外排序。它还可以为 IS NULL 和 IS NOT NULL 提供索引访问。不过，存在索引不等于强制使用索引；查询需要读取很大一部分表时，规划器仍可能选择其他路径。这并不改变索引在语义上支持哪些操作。 文本模式另有条件。像 LIKE 'abc%' 这样由常量构成、锚定开头的前缀，可以利用合适的 B-tree 路径，但排序规则和运算符类会影响适用性。非 C 区域设置下，可能需要专门支持模式匹配的运算符类。开头带通配符的匹配，也不会因为创建了普通 B-tree 就自动变成高选择性的有序范围。不能把“支持部分前缀匹配”写成“所有 LIKE 都能一趟索引解决”。 PostgreSQL 哈希索引保存四字节哈希码，而不是完整列值。它支持等值，不保留键顺序，只支持单个索引列，也不能检查唯一性。对于较大值，较小的索引条目可能有利于某些等值负载，但读取堆记录进行复核、访问桶和溢出页仍有成本。数据结构的名字本身不能证明某次真实查询谁会胜出。 坏掉的样子 把 B-tree 换成哈希索引，可能失去原有的范围或有序访问路径，也会失去 IS NULL 的索引访问能力：PostgreSQL 哈希索引不支持这个条件。更严重的是，把承担唯一性要求的索引替换成哈希索引，因为后者根本无法提供对应约束。即使点查偶尔更快，也不能用性能收益替代数据完整性契约。 空值语义需要单独核对。默认唯一 B-tree 允许多个空值，因为唯一性检查把它们视为彼此不同。如果应用要求最多只能出现一个空值，应在唯一约束或索引上明确使用 NULLS NOT DISTINCT，而不能从“唯一”两个字直接推导。索引支持怎样访问，与它强制哪些数据规则，是关联但不同的两项决定。验证时必须分别看查询结果和约束行为。 ...

一个任务队列可能只有一百条待处理记录，却保留了成千上万条已完成记录。如果主要请求都是领取待处理任务，只为这个子集建立索引就可能更合适。PostgreSQL 把它称为部分索引，索引谓词决定哪些表行拥有条目。收益仍取决于真实查询和状态变化，不能把“所有索引越小越好”当成脱离业务的优化目标。 坏掉的样子 条件之间的蕴含方向不能写反：查询条件必须蕴含索引谓词，也就是查询可能需要的每一行都在索引覆盖范围内。只收录待处理任务的索引，不能完整回答没有状态限制的全表查询。两个条件看上去不矛盾，也不代表规划器能证明它们之间的关系。PostgreSQL 不会识别所有数学上等价的写法，所以让谓词接近应用实际过滤表达式，也有助于人工审阅和计划识别。 哪些参数已经知道，同样由计划阶段决定。预备语句的定制计划，可以使用这次传入的状态值；通用计划却必须对所有可能的参数都成立，不能假定每次调用永远查询待处理状态。因此，手工测试常量查询时看到索引被使用，并不能直接证明应用长期执行的预备语句也会选择同一条访问路径。 快速验证 在 PostgreSQL 18 的可丢弃数据库中，使用一个全新的 psql -X -v ON_ERROR_STOP=1 会话按顺序执行。实验没有建立覆盖全表的其他索引，便于观察资格差异。先创建一万条任务，其中编号为一百整数倍的记录处于待处理状态： CREATE SCHEMA partial_demo; CREATE TABLE partial_demo.jobs (id integer,state text); INSERT INTO partial_demo.jobs SELECT g,CASE WHEN g%100=0 THEN 'ready' ELSE 'done' END FROM generate_series(1,10000) AS g; CREATE INDEX ready_jobs ON partial_demo.jobs(id) WHERE state='ready'; VACUUM (ANALYZE) partial_demo.jobs; SELECT state,count(*) FROM partial_demo.jobs GROUP BY state ORDER BY state; 分组结果应是已完成九千九百条、待处理一百条。这里索引键是任务编号，谓词检查的是状态，两者不要求是同一个字段。先检查带常量条件的计划： ...