PostgreSQL中WHERE条件用ROW行比较过滤时能否创建索引

针对PostgreSQL中使用ROW()构造器做行值比较的过滤场景，完全可以通过对应顺序的多列B树索引实现查询效率优化，不需要改写原有查询语法。

以你给出的查询为例，ROW (table1.created_on, table1.id) < ROW ('2022-02-05 09:37:06.719', 'b8e4c048-ec10-4c7e-9811')的比较逻辑，直接创建列顺序完全匹配的多列B树索引即可：

CREATE INDEX idx_table1_created_on_id ON table1 USING btree (created_on, id);

PostgreSQL的查询优化器可以自动识别行值比较的排序逻辑，和多列B树的存储排序规则完全对齐：

• 多列B树索引按照定义时从左到右的列顺序排序存储，先按第一列值排序，第一列值相等时再按第二列值排序
• ROW(col1, col2) < ROW(val1, val2)的比较逻辑同样是先比较第一列大小，第一列值相等时再比较第二列值，和索引排序逻辑完全匹配，优化器会自动选择索引范围扫描执行查询，不需要额外做语法改写。

• 列顺序必须严格对应：索引定义的列顺序必须和ROW()构造器内的列顺序完全一致，顺序错位无法命中范围扫描优化
• 仅B树索引支持该场景：哈希索引、GIN、GiST等其他索引类型不支持多列有序的行值比较逻辑，无法被该类查询使用
• 支持双向比较：不管是<、<=还是>、>=的行值比较，都可以使用同一个多列B树索引，B树支持双向扫描，不需要额外创建反向序索引

这种行值比较+多列B树索引的组合，也是Keyset分页（又称seek分页/游标分页）场景下的标准优化方案，比传统的多条件OR拼接写法性能更稳定，不会出现条件判断分支导致的索引失效问题。建完索引后可以通过EXPLAIN命令查看执行计划，出现Index Scan或Index Only Scan节点就说明索引已经正常生效。

内容的提问来源于stack exchange，提问作者user19294795