PostgreSQL 12中多排序条件查询性能骤降原因及多排序条件索引创建咨询

兄弟，咱们把问题拆解开唠唠：

为啥俩查询性能差这么多？ #

第一个查询只按blockNumber DESC排序，PostgreSQL能利用你建的包含from_/to_和blockNumber的索引——比如(from_, to_, blockNumber)这类，快速筛选出符合(from_='abcd' or to_='abcd') and blockNumber<=12345的数据，而且索引本身就是按blockNumber有序的，直接取前1000条就行，完全不用额外排序，所以速度飞起。

但第二个查询多了transactionIndex ASC的排序条件，事儿就复杂了：你的现有索引里，没有一个能同时满足**筛选条件（from_或to_匹配+blockNumber<=12345）和排序条件（blockNumber DESC, transactionIndex ASC）**的组合。PostgreSQL没法直接从索引里拿到按这俩字段排好序的结果，只能先把所有符合筛选条件的数据捞出来（这可能是几百万甚至上千万条），然后在内存或磁盘里做全量排序（也就是执行计划里的Sort节点），最后再取前1000条——这就是为啥耗时直接飙到2分钟。

该怎么优化？ #

核心问题是你用了OR条件（from_='abcd' or to_='abcd'），这种条件很难用单一复合索引高效覆盖，咱们可以拆分查询+针对性建索引：

第一步：创建两个精准匹配的复合索引

分别针对from_和to_的筛选场景，把排序字段和要返回的hash也包含进去：

CREATE INDEX idx_transfers_from_block_tx ON _transfers(from_, blockNumber DESC, transactionIndex ASC) INCLUDE (hash);
CREATE INDEX idx_transfers_to_block_tx ON _transfers(to_, blockNumber DESC, transactionIndex ASC) INCLUDE (hash);

这俩索引的逻辑是：先按from_/to_定位到目标钱包的记录，这些记录已经按blockNumber DESC, transactionIndex ASC排好序了，还直接包含了要返回的hash，完全不用回表查数据。

第二步：修改查询语句，用UNION ALL拆分OR条件

把原来的OR查询拆成两个独立子查询，分别匹配from_和to_，再合并结果后排序取前1000：

SELECT hash FROM (
    SELECT hash, blockNumber, transactionIndex 
    FROM _transfers 
    WHERE from_='abcd' AND blockNumber<=12345
    UNION ALL
    SELECT hash, blockNumber, transactionIndex 
    FROM _transfers 
    WHERE to_='abcd' AND blockNumber<=12345
) AS combined_results
ORDER BY blockNumber DESC, transactionIndex ASC
LIMIT 1000;

这样每个子查询都会用到咱们刚建的专属索引，快速拿到有序的小批量数据，合并后的总数据量远小于原来直接筛选的量，排序起来自然就快了。

为啥你之前的索引没用？ #

比如你建的(from_, to_, blockNumber, transactionIndex)，这个索引是先按from_排序，再按to_排序，所以当你要查to_='abcd'但from_不是abcd的记录时，索引根本没法快速定位；而(blockNumber, transactionIndex)索引又没办法帮你快速筛选出from_或to_是abcd的记录，只能先扫所有blockNumber<=12345的记录，再过滤from_/to_，效率极低。

备注：内容来源于stack exchange，提问作者sirjay