为何PostgreSQL要对看似已排序的结果集再次排序？

原始查询 #

SELECT
    DATE(b.book_date),
    SUM(b.total_amount) revenue,
    COUNT(DISTINCT(t.passenger_id)) count_passengers
FROM bookings b
JOIN tickets t ON t.book_ref = b.book_ref
GROUP BY
    DATE(b.book_date)
ORDER BY
    COUNT(DISTINCT(t.passenger_id)) DESC,
    SUM(b.total_amount) DESC;

无索引时的执行计划 #

|QUERY PLAN                                                                                                                                                          |
|--------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|Sort  (cost=20001452121.83..20001453243.21 rows=448552 width=44) (actual time=10862.729..10862.752 rows=392 loops=1)                                                |
|  Sort Key: (count(DISTINCT t.passenger_id)) DESC, (sum(b.total_amount)) DESC                                                                                       |
|  Sort Method: quicksort  Memory: 55kB                                                                                                                              |
|  ->  GroupAggregate  (cost=20001359986.85..20001396213.70 rows=448552 width=44) (actual time=8131.457..10862.442 rows=392 loops=1)                                 |
|        Group Key: (date(b.book_date))                                                                                                                              |
|        ->  Sort  (cost=20001359986.85..20001367361.50 rows=2949857 width=22) (actual time=8131.394..8329.844 rows=2949857 loops=1)                                 |
|              Sort Key: (date(b.book_date))                                                                                                                         |
|              Sort Method: external merge  Disk: 95136kB                                                                                                            |
|              ->  Merge Join  (cost=20000859819.02..20000921997.07 rows=2949857 width=22) (actual time=6064.572..7486.794 rows=2949857 loops=1)                     |
|                    Merge Cond: (b.book_ref = t.book_ref)                                                                                                           |
|                    ->  Sort  (cost=10000342915.67..10000348193.45 rows=2111110 width=21) (actual time=854.300..1138.458 rows=2111110 loops=1)                      |
|                          Sort Key: b.book_ref                                                                                                                      |
|                          Sort Method: external merge  Disk: 66024kB                                                                                                |
|                          ->  Seq Scan on bookings b  (cost=10000000000.00..10000034558.10 rows=2111110 width=21) (actual time=90.339..215.696 rows=2111110 loops=1)|
|                    ->  Sort  (cost=10000516903.35..10000524278.00 rows=2949857 width=19) (actual time=5210.197..5407.427 rows=2949857 loops=1)                     |
|                          Sort Key: t.book_ref                                                                                                                      |
|                          Sort Method: external sort  Disk: 95320kB                                                                                                 |
|                          ->  Seq Scan on tickets t  (cost=10000000000.00..10000078913.57 rows=2949857 width=19) (actual time=0.134..241.174 rows=2949857 loops=1)  |
|Planning Time: 0.121 ms                                                                                                                                             |
|JIT:                                                                                                                                                                |
|  Functions: 16                                                                                                                                                     |
|  Options: Inlining true, Optimization true, Expressions true, Deforming true                                                                                       |
|  Timing: Generation 0.834 ms, Inlining 7.090 ms, Optimization 51.113 ms, Emission 32.119 ms, Total 91.156 ms                                                       |
|Execution Time: 10890.239 ms                                                                                                                                        |

创建的优化索引 #

CREATE INDEX idx_bookings_bd_ta_bref ON bookings USING btree(book_date, total_amount, book_ref);
CREATE INDEX idx_tickets_bref ON tickets USING hash(book_ref);

优化后的执行计划 #

|QUERY PLAN                                                                                                                                                                        |
|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|Sort  (cost=966377.54..967498.92 rows=448552 width=44) (actual time=6961.514..6961.535 rows=392 loops=1)                                                                          |
|  Sort Key: (count(DISTINCT t.passenger_id)) DESC, (sum(b.total_amount)) DESC                                                                                                     |
|  Sort Method: quicksort  Memory: 55kB                                                                                                                                            |
|  ->  GroupAggregate  (cost=874242.56..910469.41 rows=448552 width=44) (actual time=4283.854..6961.216 rows=392 loops=1)                                                          |
|        Group Key: (date(b.book_date))                                                                                                                                            |
|        ->  Sort  (cost=874242.56..881617.20 rows=2949857 width=22) (actual time=4283.792..4475.416 rows=2949857 loops=1)                                                         |
|              Sort Key: (date(b.book_date))                                                                                                                                       |
|              Sort Method: external merge  Disk: 95136kB                                                                                                                          |
|              ->  Nested Loop  (cost=0.43..436252.77 rows=2949857 width=22) (actual time=64.342..3727.674 rows=2949857 loops=1)                                                   |
|                    ->  Index Only Scan using idx_bookings_bd_ta_bref on bookings b  (cost=0.43..73467.08 rows=2111110 width=21) (actual time=0.049..172.735 rows=2111110 loops=1)|
|                          Heap Fetches: 0                                                                                                                                         |
|                    ->  Index Scan using idx_tickets_bref on tickets t  (cost=0.00..0.15 rows=2 width=19) (actual time=0.001..0.001 rows=1 loops=2111110)                         |
|                          Index Cond: (book_ref = b.book_ref)                                                                                                                     |
|                          Rows Removed by Index Recheck: 0                                                                                                                        |
|Planning Time: 0.139 ms                                                                                                                                                           |
|JIT:                                                                                                                                                                              |
|  Functions: 10                                                                                                                                                                   |
|  Options: Inlining true, Optimization true, Expressions true, Deforming true                                                                                                     |
|  Timing: Generation 0.448 ms, Inlining 6.415 ms, Optimization 34.103 ms, Emission 23.769 ms, Total 64.734 ms                                                                     |
|Execution Time: 6971.385 ms                                                                                                                                                       |

问题分析与解决 #

核心原因 #

你创建的索引是按book_date（timestampz类型）排序，但GROUP BY的键是DATE(b.book_date)——这是一个日期转换表达式。PostgreSQL的查询优化器无法自动推断出原始timestampz的索引顺序，和转换后的DATE值分组顺序完全一致，因此不会认定连接后的结果集已经满足分组所需的有序性，依然会执行外部排序来确保GroupAggregate可以按分组键正确聚合。

哪怕实际数据中同一个DATE对应的timestampz值在索引里是连续的，优化器也不会做这个假设，必须有明确的索引来匹配分组键的表达式。

解决方案 #

创建表达式索引，直接按分组键DATE(book_date)排序，让优化器可以直接利用索引顺序来跳过分组前的排序：

CREATE INDEX idx_bookings_date_bd_ta_bref ON bookings USING btree(DATE(book_date), total_amount, book_ref);

这个索引会直接按转换后的DATE值排序，Index Only Scan返回的结果天然符合DATE(b.book_date)的有序要求，连接后的结果集也会保持这个顺序。此时GroupAggregate可以使用流式聚合，无需再执行外部排序，大幅提升性能。

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