PostgreSQL使用OR条件时走Filter而非Index Cond的原因及优化方案

优化方案 #

1. 直接改写查询为行值比较语法 #

你可以直接用PostgreSQL支持的多字段行值比较逻辑替换原来的OR条件，写法如下，逻辑完全等价且不需要拆分UNION：

SELECT * FROM transactions 
WHERE (block_height, index) > (10000, 5)
ORDER BY block_height ASC, index ASC
LIMIT 10;

2. 确保存在匹配的联合索引 #

要让上述查询命中索引范围扫描，你需要提前创建(block_height, index)的升序联合索引：

-- 建议使用CONCURRENTLY避免锁表，适合在线生产环境执行
CREATE INDEX CONCURRENTLY idx_transactions_block_height_index ON transactions (block_height ASC, index ASC);

性能说明 #

原来的OR条件写法无法被PostgreSQL优化器识别为连续的索引范围，因此只能先扫描所有block_height >= 10000的行，再通过Filter过滤不符合要求的记录，会扫描数百万无效行导致性能极差。
而行值比较的写法会被优化器直接翻译为索引范围条件，执行计划中条件会出现在Index Cond中，仅扫描符合要求的少量记录，性能和拆分后UNION的写法基本一致，部分场景下因为仅需要一次索引扫描，性能比UNION方案更好。

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