PostgreSQL含ORDER BY子句的查询优化问题求助

有一个PostgreSQL查询执行耗时1分钟以上，排查发现是ORDER BY aggregatet0_.id ASC子句导致的。移除该子句后，查询1秒内就能完成。疑惑点：结果集仅3410条记录，排序3000条数据不该这么慢，是不是查询在遍历全表？该表有70多万条记录。

查询语句 #

SELECT aggregatet0_.id                     AS id,
       aggregatet0_.pan                    AS col_1_0,
       aggregatet0_.account_number         AS col_2_0,
       aggregatet0_.local_date             AS col_3_0,
       aggregatet0_.local_time             AS col_4_0,
       aggregatet0_.stan                   AS col_5_0,
       aggregatet0_.rrn                    AS col_6_0,
       aggregatet0_.other_ref              AS col_7_0,
       aggregatet0_.sequence_number        AS col_8_0,
       aggregatet0_.amount                 AS col_9_0,
       aggregatet0_.transaction_type       AS col_10_0,
       aggregatet0_.cur_code               AS col_11_0,
       aggregatet0_.data                   AS col_12_0,
       aggregatet0_.posting_date           AS col_13_0,
       aggregatet0_.transmission_date_time AS col_14_0,
       aggregatet0_.response_code          AS col_15_0,
       aggregatet0_.issuer                 AS col_16_0,
       aggregatet0_.recon_status           AS col_17_0,
       aggregatet0_.matched_in             AS col_18_0,
       aggregatet0_.in_review              AS col_19_0,
       aggregatet0_.notes                  AS col_20_0,
       aggregatet0_.recon_account          AS col_21_0
FROM   recon_core.aggregate_transaction aggregatet0_
       LEFT OUTER JOIN recon_core.recon_account_type reconaccou1_
                    ON ( reconaccou1_.id = aggregatet0_.recon_account_type )
WHERE  ( Coalesce('MATCHED', NULL, '') = ''
          OR aggregatet0_.recon_status IN ( 'MATCHED' ) )
       AND ( Coalesce('RAAST', 'Digital', 'POS', 'Branch', 'ATM', '1LINK', NULL,
             '') =
             ''
              OR reconaccou1_.account_type IN (
                 'RAAST', 'Digital', 'POS', 'Branch',
                 'ATM', '1LINK' ) )
       AND ( Coalesce(NULL, NULL, '') = ''
              OR aggregatet0_.destination IN ( NULL ) )
       AND ( ( NULL IS NULL )
             AND ( NULL IS NULL )
              OR Extract(hour FROM aggregatet0_.capture_date_time) BETWEEN NULL
                 AND
                 NULL )
       AND ( Coalesce(NULL, NULL, '') = ''
              OR aggregatet0_.recon_account IN ( NULL ) )
       AND ( 17001 IN ( 17001 )
              OR aggregatet0_.recon_account_id IN ( 17001 ) )
       AND aggregatet0_.posting_date = '2023-06-05'
       AND ( Coalesce('DEPOSIT', 'FUNDS-TRANSFER-OWN', 'CASH-WITHDRAWAL',
                   'CASH-WITHDRAWAL-REVERSAL', 'PURCHASE',
             'FUNDS-TRANSFER-SWIFT',
                   'BALANCE-INQUIRY', 'REFUND', 'CASH-WITHDRAWAL-RETRACT',
             'BILL-PAYMENT',
                   'FUNDS-TRANSFER-LOCAL', 'FUNDS-TRANSFER-INTERBANK', NULL, '')
             = ''
              OR aggregatet0_.transaction_type IN (
                     'DEPOSIT', 'FUNDS-TRANSFER-OWN',
                     'CASH-WITHDRAWAL', 'CASH-WITHDRAWAL-REVERSAL',
                                                    'PURCHASE',
                     'FUNDS-TRANSFER-SWIFT',
                     'BALANCE-INQUIRY', 'REFUND',
                                                    'CASH-WITHDRAWAL-RETRACT',
                     'BILL-PAYMENT'
                     ,
                     'FUNDS-TRANSFER-LOCAL', 'FUNDS-TRANSFER-INTERBANK' ) )
ORDER  BY aggregatet0_.id ASC
LIMIT  15;  

移除ORDER BY aggregatet0_.id ASC后，查询执行正常。

现有索引（均为B树索引） #

• id（主键）
• transaction_type
• posting_date

更新：查询计划 #

Limit  (cost=0.42..2151.51 rows=15 width=2218) (actual time=56156.995..56157.052 rows=15 loops=1)
  ->  Nested Loop  (cost=0.42..200051.50 rows=1395 width=2218) (actual time=56156.993..56157.048 rows=15 loops=1)
        Join Filter: (aggregatet0_.recon_account_type = reconaccou1_.id)
        Rows Removed by Join Filter: 45
        ->  Index Scan using pk__aggregat__3213e83f059b2b7d on aggregate_transaction aggregatet0_  (cost=0.42..199876.96 rows=2092 width=2222) (actual time=56156.569..56156.612 rows=15 loops=1)
              Filter: (((recon_status)::text = 'MATCHED'::text) AND (posting_date = '2023-06-05'::date) AND ((transaction_type)::text = ANY ('{DEPOSIT,FUNDS-TRANSFER-OWN,CASH-WITHDRAWAL,CASH-WITHDRAWAL-REVERSAL,PURCHASE,FUNDS-TRANSFER-SWIFT,BALANCE-INQUIRY,REFUND,CASH-WITHDRAWAL-RETRACT,BILL-PAYMENT,FUNDS-TRANSFER-LOCAL,FUNDS-TRANSFER-INTERBANK}'::text[])))
              Rows Removed by Filter: 752365
        ->  Materialize  (cost=0.00..1.19 rows=6 width=4) (actual time=0.027..0.028 rows=4 loops=15)
              ->  Seq Scan on recon_account_type reconaccou1_  (cost=0.00..1.16 rows=6 width=4) (actual time=0.401..0.404 rows=4 loops=1)
                    Filter: ((account_type)::text = ANY ('{RAAST,Digital,POS,Branch,ATM,1LINK}'::text[]))
                    Rows Removed by Filter: 5
Planning Time: 0.330 ms
Execution Time: 56157.120 ms

问题分析 #

从查询计划可以明确：慢的根源不是排序3000条数据，而是PostgreSQL选择了从主键索引从头开始扫描，逐一过滤符合条件的记录，总共扫描了752365条（全表数据）才找到15条符合要求的记录。

因为加了ORDER BY id ASC + LIMIT 15，优化器认为从主键索引按顺序扫描，找到15条符合条件的记录就停止，比先筛选所有符合条件的记录再排序取前15更高效，但实际情况是符合条件的记录在全表中占比极低，导致全表扫描耗时极长。

优化方案 #

1. 创建针对性复合索引 #

创建覆盖WHERE过滤条件的复合索引，同时包含排序字段，让优化器可以快速筛选出符合条件的记录，再排序取前15：

CREATE INDEX idx_agg_posting_date_recon_status_type ON recon_core.aggregate_transaction 
(posting_date, recon_status, transaction_type) 
INCLUDE (id);

• posting_date是等值查询，放在索引最前列，快速缩小扫描范围
• 后续的recon_status和transaction_type都是过滤条件，进一步筛选数据
• INCLUDE (id)避免排序时回表查询主键，提升排序效率

2. 简化冗余WHERE条件 #

原查询中的很多Coalesce判断都是冗余的（比如Coalesce('MATCHED', NULL, '') = ''结果为false，实际生效的是OR后的分支），简化后让优化器更容易判断执行路径：

WHERE aggregatet0_.recon_status = 'MATCHED'
  AND reconaccou1_.account_type IN ('RAAST', 'Digital', 'POS', 'Branch', 'ATM', '1LINK')
  AND aggregatet0_.recon_account_id IN (17001)
  AND aggregatet0_.posting_date = '2023-06-05'
  AND aggregatet0_.transaction_type IN ('DEPOSIT', 'FUNDS-TRANSFER-OWN', 'CASH-WITHDRAWAL', 'CASH-WITHDRAWAL-REVERSAL', 'PURCHASE', 'FUNDS-TRANSFER-SWIFT', 'BALANCE-INQUIRY', 'REFUND', 'CASH-WITHDRAWAL-RETRACT', 'BILL-PAYMENT', 'FUNDS-TRANSFER-LOCAL', 'FUNDS-TRANSFER-INTERBANK')

注意：原查询的LEFT JOIN因为WHERE中用到了reconaccou1_.account_type，实际会被优化为INNER JOIN，可以明确写出INNER JOIN避免歧义。

3. 验证优化效果 #

执行EXPLAIN ANALYZE查看新的查询计划，应该会看到优化器使用新创建的复合索引进行扫描，而不是全表扫描主键索引，筛选出符合条件的记录后排序取前15的耗时会大幅降低。

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