PostgreSQL 16 對查詢計劃程序進行了大量改進，并使許多 SQL 查詢的運行速度比以前版本的 PostgreSQL 更快。

如果查看 PG16 的Release Note，你將看到其中有一些優化器的增強。但是，由于每個 PostgreSQL 版本中的更新都很大，因此無法提供有關每項更新的足夠詳細信息。因此，在了解它是否與你相關之前，你可能需要更詳細的信息來了解更改的內容。

在這篇博文中，假設你已經掌握了 EXPLAIN 的基礎知識，您將深入了解 PostgreSQL 16 查詢計劃程序中所做的 10 項改進。對于 PG16 規劃器（規劃器在其他關系數據庫中通常稱為優化器）的每項改進，您還將獲得 PG15 和 PG16 規劃器輸出之間的比較，以及更改內容的示例，以獨立測試的形式，您可以自己嘗試。

讓我們深入了解一下 PG16 中 PostgreSQL 規劃器的這 10 項改進：

1. 在更多場景允許增量排序，包括 DISTINCT

增量排序最初是在 PostgreSQL 13 中添加的。這些增量排序減少了獲取排序結果所需的工作量。如何？通過利用某些給定結果集已按 1 個或多個前導列排序的知識，并且僅對其余列執行排序。

例如，如果 column a 上有一個 btree 索引，并且我們需要按 a ，b 排序的行，那么我們可以使用 btree 索引（它在 column a 上提供預排序結果），并且僅在 的值發生變化時對到目前為止看到的a行進行排序。使用 PostgreSQL 使用的快速排序算法，對多個較小的組進行排序比對一個大組進行排序更有效。

PostgreSQL 16 查詢計劃程序現在考慮對SELECT DISTINCT查詢執行增量排序。在 PG16 之前，當為SELECT DISTINCT查詢選擇排序方法時，計劃者只考慮執行完整排序（這比增量排序更昂貴）。

-- Setup
CREATE TABLE distinct_test (a INT, b INT);
INSERT INTO distinct_test
SELECT x,1 FROM generate_series(1,1000000)x;
CREATE INDEX on distinct_test(a);
VACUUM ANALYZE distinct_test;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT DISTINCT a,b FROM distinct_test;

PG15 EXPLAIN 輸出

QUERY PLAN
---------------------------------------------------------------
 HashAggregate (actual rows=1000000 loops=1)
   Group Key: a, b
   Batches: 81  Memory Usage: 11153kB  Disk Usage: 31288kB
   ->  Seq Scan on distinct_test (actual rows=1000000 loops=1)
 Planning Time: 0.065 ms
 Execution Time: 414.226 ms
(6 rows)

PG16 EXPLAIN 輸出

QUERY PLAN
------------------------------------------------------------------
 Unique (actual rows=1000000 loops=1)
   ->  Incremental Sort (actual rows=1000000 loops=1)
         Sort Key: a, b
         Presorted Key: a
         Full-sort Groups: 31250  Sort Method: quicksort  Average Memory: 26kB  Peak Memory: 26kB
         ->  Index Scan using distinct_test_a_idx on distinct_test (actual rows=1000000 loops=1)
 Planning Time: 0.108 ms
 Execution Time: 263.167 ms
(8 rows)

在上面的 PostgreSQL 16 EXPLAIN 輸出中，您可以看到 planner 選擇在a列上使用distinct_test_a_idx索引，然后執行 an Incremental Sort 以對所有相等值a進行排序 by b 。這Presorted Key: a表示了這一點。因為上面的INSERT語句只為每個值 a of 添加了一個值 b ，所以每批按增量排序的 Tuples 只包含一行。
上面 PostgreSQL 16 EXPLAIN 的輸出顯示，Peak Memory?Incremental Sort它只有 26 KB，而 PostgreSQL 15 使用的哈希方法需要大量內存，以至于它需要將大約 30 MB 的數據溢出到磁盤。 查詢在 PostgreSQL 16 上的執行速度提高了 63%。

2. 為具有 ORDER BY 或 DISTINCT 的聚合添加使用預排序數據的能力

在 PostgreSQL 15 及更早版本中，包含 or DISTINCT 子句的ORDER BY聚合函數將導致執行程序始終在Aggregate計劃的節點內執行排序。因為總是執行排序，所以計劃者永遠不會嘗試形成一個計劃來提供預排序的 Importing 來按順序聚合行。
PostgreSQL 16 查詢計劃程序現在嘗試形成一個計劃，該計劃以正確的順序將行饋送到計劃的Aggregate節點。執行程序現在足夠聰明，可以識別這一點，并在行已經按正確的順序預先排序時放棄自己執行排序。

-- Setup
CREATE TABLE aggtest (a INT, b text);
INSERT INTO aggtest SELECT a,md5((b%100)::text) FROM generate_series(1,10) a, generate_series(1,100000)b;
CREATE INDEX ON aggtest(a,b);
VACUUM FREEZE ANALYZE aggtest;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, BUFFERS)
SELECT a,COUNT(DISTINCT b) FROM aggtest GROUP BY a;

PG15 EXPLAIN 輸出

QUERY PLAN
---------------------------------------------------------------
 GroupAggregate (actual rows=10 loops=1)
   Group Key: a
   Buffers: shared hit=892, temp read=4540 written=4560
   ->  Index Only Scan using aggtest_a_b_idx on aggtest (actual rows=1000000 loops=1)
         Heap Fetches: 0
         Buffers: shared hit=892
 Planning Time: 0.122 ms
 Execution Time: 302.693 ms
(8 rows)

PG16 EXPLAIN 輸出

QUERY PLAN
---------------------------------------------------------------
 GroupAggregate (actual rows=10 loops=1)
   Group Key: a
   Buffers: shared hit=892
   ->  Index Only Scan using aggtest_a_b_idx on aggtest (actual rows=1000000 loops=1)
         Heap Fetches: 0
         Buffers: shared hit=892
 Planning Time: 0.061 ms
 Execution Time: 115.534 ms
(8 rows)

除了 PostgreSQL 16 執行查詢的速度是 PG15 的兩倍多之外，上述EXPLAIN ANALYZE輸出中這種變化的唯一跡象是 PostgreSQL 16 輸出中不存在的temp read=4540 written=4560。在 PG15 中，這是由溢出到磁盤的隱式排序引起的。

3. 允許在 UNION ALL 上記憶

Memoize計劃節點最初是在 PostgreSQL 14 中引入的。Memoize plan 節點充當 parameterized Nested Loop 和 Nested Loop 內側之間的緩存層。當需要多次查找相同的值時，Memoize 可以很好地提高性能，因為當所需的行已經被查詢并緩存時，它可以跳過執行其子節點。
PostgreSQL 16 查詢計劃程序現在將考慮在Memoize?UNION ALL查詢出現在 parameterized Nested Loop 的內側時使用。

-- Setup
CREATE TABLE t1 (a INT PRIMARY KEY);
CREATE TABLE t2 (a INT PRIMARY KEY);
CREATE TABLE lookup (a INT);

INSERT INTO t1 SELECT x FROM generate_Series(1,10000) x;
INSERT INTO t2 SELECT x FROM generate_Series(1,10000) x;
INSERT INTO lookup SELECT x%10+1 FROM generate_Series(1,1000000)x;

ANALYZE t1,t2,lookup;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT * FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2) t
INNER JOIN lookup l ON l.a = t.a;

PG15 EXPLAIN 輸出

QUERY PLAN
-------------------------------------------------------------------------------
 Nested Loop (actual rows=2000000 loops=1)
   ->  Seq Scan on lookup l (actual rows=1000000 loops=1)
   ->  Append (actual rows=2 loops=1000000)
         ->  Index Only Scan using t1_pkey on t1 (actual rows=1 loops=1000000)
               Index Cond: (a = l.a)
               Heap Fetches: 1000000
         ->  Index Only Scan using t2_pkey on t2 (actual rows=1 loops=1000000)
               Index Cond: (a = l.a)
               Heap Fetches: 1000000
 Planning Time: 0.223 ms
 Execution Time: 1926.151 ms
(11 rows)

PG16 EXPLAIN 輸出

QUERY
---------------------------------------------------------------------------------
 Nested Loop (actual rows=2000000 loops=1)
   ->  Seq Scan on lookup l (actual rows=1000000 loops=1)
   ->  Memoize (actual rows=2 loops=1000000)
         Cache Key: l.a
         Cache Mode: logical
         Hits: 999990  Misses: 10  Evictions: 0  Overflows: 0  Memory Usage: 2kB
         ->  Append (actual rows=2 loops=10)
               ->  Index Only Scan using t1_pkey on t1 (actual rows=1 loops=10)
                     Index Cond: (a = l.a)
                     Heap Fetches: 10
               ->  Index Only Scan using t2_pkey on t2 (actual rows=1 loops=10)
                     Index Cond: (a = l.a)
                     Heap Fetches: 10
 Planning Time: 0.229 ms
 Execution Time: 282.120 ms
(15 rows)

在上面的 PostgreSQL 16 EXPLAIN 輸出中，您可以看到Memoize節點位于Append節點的頂部，這導致 的數量loops?Append從 PG15 中的 100 萬減少到 PG16 中的 10 個。每次Memoize節點有緩存命中時，都無需執行 Append to fetch records。這會導致查詢在 PostgreSQL 16 上的運行速度提高約 6 倍。

4. 允許以不可為空的輸入作為內部關系來執行反連接

執行 for Hash Join an INNER JOIN 時，PostgreSQL 更喜歡在兩個表中較小的一個上構建哈希表。較小的哈希表更好，因為構建它們的工作量更少。較小的表也更好，因為它們對 CPU 的緩存更友好，并且 CPU 在等待數據從主內存到達時不太可能停止。
在 PostgreSQL 16 之前的版本中，如果您在查詢中使用NOT EXISTS，可能會看到 an Anti Join 始終將 NOT EXISTS part 中提到的表放在聯接的內側。這意味著無法靈活地對兩個表中較小的表進行哈希處理，從而導致可能必須在較大的表上構建哈希表。
PostgreSQL 16 查詢計劃程序現在可以選擇對兩個表中較小的一個進行哈希處理。現在可以這樣做，因為 PostgreSQL 16 支持 Right Anti Join .

-- Setup
CREATE TABLE small(a int);
CREATE TABLE large(a int);
INSERT INTO small
SELECT a FROM generate_series(1,100) a;
INSERT INTO large
SELECT a FROM generate_series(1,1000000) a;
VACUUM ANALYZE small,large;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT * FROM small s
WHERE NOT EXISTS(SELECT 1 FROM large l WHERE s.a = l.a);

PG15 EXPLAIN 輸出

QUERY PLAN
---------------------------------------------------------------
 Hash Anti Join (actual rows=0 loops=1)
   Hash Cond: (s.a = l.a)
   ->  Seq Scan on small s (actual rows=100 loops=1)
   ->  Hash (actual rows=1000000 loops=1)
         Buckets: 262144  Batches: 8  Memory Usage: 6446kB
         ->  Seq Scan on large l (actual rows=1000000 loops=1)
 Planning Time: 0.103 ms
 Execution Time: 139.023 ms
(8 rows)

PG16 EXPLAIN 輸出

QUERY PLAN
-----------------------------------------------------------
 Hash Right Anti Join (actual rows=0 loops=1)
   Hash Cond: (l.a = s.a)
   ->  Seq Scan on large l (actual rows=1000000 loops=1)
   ->  Hash (actual rows=100 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 12kB
         ->  Seq Scan on small s (actual rows=100 loops=1)
 Planning Time: 0.094 ms
 Execution Time: 77.076 ms
(8 rows)

從上面的EXPLAIN ANALYZE輸出中可以看到，由于 PG16 的規劃器選擇使用 ，Hash Right Anti JoinPostgreSQL 16 Memory Usage 中的帶寬比 PostgreSQL 15 中的少得多，Execution Time幾乎減少了一半。

5. 允許并行化 FULL 和內部右 OUTER 哈希連接

PostgreSQL 11 引入了 Parallel Hash Join .這允許并行查詢中的多個并行工作程序協助構建單個哈希表。在 11 之前的版本中，每個 worker 都會構建自己的相同哈希表，從而導致額外的內存開銷。
在 PostgreSQL 16 中，Parallel Hash Join已得到改進，現在支持 FULL JOIN RIGHT 類型。這允許并行執行具有 的FULL OUTER JOIN查詢，還允許Right Joins并行執行計劃。

-- Setup
CREATE TABLE odd (a INT);
CREATE TABLE even (a INT);
INSERT INTO odd
SELECT a FROM generate_series(1,1000000,2) a;
INSERT INTO even
SELECT a FROM generate_series(2,1000000,2) a;
VACUUM ANALYZE odd, even;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT COUNT(o.a),COUNT(e.a) FROM odd o FULL JOIN even e ON o.a = e.a;

PG15 EXPLAIN 輸出

QUERY PLAN
-------------------------------------------------------------------
 Aggregate (actual rows=1 loops=1)
   ->  Hash Full Join (actual rows=1000000 loops=1)
         Hash Cond: (o.a = e.a)
         ->  Seq Scan on odd o (actual rows=500000 loops=1)
         ->  Hash (actual rows=500000 loops=1)
               Buckets: 262144  Batches: 4  Memory Usage: 6439kB
               ->  Seq Scan on even e (actual rows=500000 loops=1)
 Planning Time: 0.079 ms
 Execution Time: 220.677 ms
(9 rows)

PG16 EXPLAIN 輸出

QUERY PLAN
--------------------------------------------------------------------------------
 Finalize Aggregate (actual rows=1 loops=1)
   ->  Gather (actual rows=2 loops=1)
         Workers Planned: 1
         Workers Launched: 1
         ->  Partial Aggregate (actual rows=1 loops=2)
               ->  Parallel Hash Full Join (actual rows=500000 loops=2)
                     Hash Cond: (o.a = e.a)
                     ->  Parallel Seq Scan on odd o (actual rows=250000 loops=2)
                     ->  Parallel Hash (actual rows=250000 loops=2)
                           Buckets: 262144  Batches: 4  Memory Usage: 6976kB
                           ->  Parallel Seq Scan on even e (actual rows=250000 loops=2)
 Planning Time: 0.161 ms
 Execution Time: 129.769 ms
(13 rows)

輸出EXPLAIN顯示 PostgreSQL 16 能夠并行執行連接，這導致查詢的 Execution Time .

6. 當 RANGE 模式處于活動狀態但不必要時，允許窗口函數使用更快的 ROWS 模式

當查詢包含窗口函數（如 row_number() 、 、 rank()?dense_rank()?percent_rank() 、 cume_dist() 和 ntile() ）時，如果 window 子句未指定ROWS選項，則 PostgreSQL 將始終使用 default RANGE 選項。該RANGE選項使 executor 向前看，直到找到第一個 “non-peer” 行。對等行是窗口框架中的一行，根據 window 子句的ORDER BY子句進行同等比較。如果沒有ORDER BY子句，則窗口框架中的所有行都是對等的。當處理具有許多行的記錄時，這些行根據 window 子句的ORDER BY子句進行相等排序，用于識別這些對等行的額外處理可能成本很高。
無論 ROWS 在 query 的 window 子句中指定 或 ，RANGE上面提到的 window 函數的行為都沒有任何不同。但是，16 之前的 PostgreSQL 版本中的執行程序并不知道這一點，并且由于某些窗口函數確實關心 ROWS /RANGE 選項，因此執行程序在所有情況下都必須對對等行執行檢查。
PostgreSQL 16 查詢計劃程序知道哪些窗口函數關心 /RANGE 選項，ROWS并將此信息傳遞給執行程序，以便它可以跳過不必要的額外處理。
當用于限制查詢中的結果數量時row_number()，此優化效果特別好，如以下示例所示。

-- Setup
CREATE TABLE scores (id INT PRIMARY KEY, score INT);
INSERT INTO scores SELECT s,random()*10 FROM generate_series(1,1000000)s;
CREATE INDEX ON scores(score);
VACUUM ANALYZE scores;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT * FROM (
    SELECT id,ROW_NUMBER() OVER (ORDER BY score) rn,score
    FROM scores
) m WHERE rn <= 10;

PG15 EXPLAIN 輸出

QUERY PLAN
-------------------------------------------------------------------------------
 WindowAgg (actual rows=10 loops=1)
   Run Condition: (row_number() OVER (?) <= 10)
   ->  Index Scan using scores_score_idx on scores (actual rows=50410 loops=1)
 Planning Time: 0.096 ms
 Execution Time: 29.775 ms
(5 rows)

PG16 EXPLAIN 輸出

QUERY PLAN
----------------------------------------------------------------------------
 WindowAgg (actual rows=10 loops=1)
   Run Condition: (row_number() OVER (?) <= 10)
   ->  Index Scan using scores_score_idx on scores (actual rows=11 loops=1)
 Planning Time: 0.191 ms
 Execution Time: 0.058 ms
(5 rows)

上面 PG15 EXPLAIN 輸出中的Index Scan節點顯示，在執行停止之前，必須從scores_score_idx索引中讀取 50410 行。而在 PostgreSQL 16 中，由于執行程序意識到一旦row_number達到 11 行，就沒有更多符合<= 10條件的行了。這和使用 ROWS window 子句選項的執行程序都導致此查詢在 PostgreSQL 16 上的運行速度提高了 500 倍以上。

7. 優化不斷增加的窗口函數 ntile（） 、 cume_dist（） 和 percent_rank（）

此更改擴展了 PostgreSQL 15 中完成的工作。在 PG15 中，查詢計劃程序被修改為允許執行程序提前停止處理WindowAgg執行程序節點。當WHERE子句中的項以某種方式篩選窗口函數時，可以執行此操作，一旦條件變為 false，它就永遠不會再次為 true。

row_number()是一個函數示例，它可以提供這樣的保證，因為它是一個單調遞增的函數，即同一分區中的后續行永遠不會有低于前一行的row_number。

PostgreSQL 16 查詢計劃程序擴展了此優化的覆蓋范圍，還涵蓋了 ntile() 和 cume_dist()?percent_rank() 。在 PostgreSQL 15 中，這僅適用于 row_number() 、 、 rank()?dense_rank()?count() 和 count(*) 。

-- Setup
CREATE TABLE marathon (id INT PRIMARY KEY, time INTERVAL NOT NULL);
INSERT INTO marathon
SELECT id,'03:00:00'::interval + (CAST(RANDOM() * 3600 AS INT) || 'secs')::INTERVAL - (CAST(RANDOM() * 3600 AS INT) || ' secs')::INTERVAL
FROM generate_series(1,50000) id;
CREATE INDEX ON marathon (time);
VACUUM ANALYZE marathon;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT * FROM (SELECT *,percent_rank() OVER (ORDER BY time) pr
FROM marathon) m WHERE pr <= 0.01;

PG15 EXPLAIN 輸出

QUERY PLAN
-----------------------------------------------------------------------
 Subquery Scan on m (actual rows=500 loops=1)
   Filter: (m.pr <= '0.01'::double precision)
   Rows Removed by Filter: 49500
   ->  WindowAgg (actual rows=50000 loops=1)
         ->  Index Scan using marathon_time_idx on marathon (actual rows=50000 loops=1)
 Planning Time: 0.108 ms
 Execution Time: 84.358 ms
(7 rows)

PG16 EXPLAIN 輸出

QUERY PLAN
-----------------------------------------------------------------------
 WindowAgg (actual rows=500 loops=1)
   Run Condition: (percent_rank() OVER (?) <= '0.01'::double precision)
   ->  Index Scan using marathon_time_idx on marathon (actual rows=50000 loops=1)
 Planning Time: 0.180 ms
 Execution Time: 19.454 ms
(5 rows)

從上面的 PostgreSQL 16 EXPLAIN 輸出中，您可以看到 planner 能夠將pr <= 0.01條件用作 ，Run Condition而在 PostgreSQL 15 中，此子句在子查詢上顯示為 aFilter。在 PG16 中，run 條件用于提前中止WindowAgg節點的執行。這導致 Execution Time PG16 比 PG15 快 4 倍多。

8. 允許在分區表上刪除左連接和唯一連接

很長一段時間以來，PostgreSQL 一直能夠刪除查詢中不需要左側聯接表中的 LEFT JOIN where 列，并且聯接不可能復制任何行。
但是，在 PostgreSQL 16 之前的版本中，不支持對分區表進行左聯接刪除。為什么？因為 Planner 用于確定任何 inner-side row 是否有可能復制任何 outer-side 行的證明對于分區表不存在。
PostgreSQL 16 查詢計劃程序現在允許對分區表進行LEFT JOIN刪除優化。
這種聯接消除優化更有可能對視圖有所幫助，因為通常并非始終查詢視圖中存在的所有列。

-- Setup
CREATE TABLE part_tab (id BIGINT PRIMARY KEY, payload TEXT) PARTITION BY HASH(id);
CREATE TABLE part_tab_p0 PARTITION OF part_tab FOR VALUES WITH (modulus 2, remainder 0);
CREATE TABLE part_tab_p1 PARTITION OF part_tab FOR VALUES WITH (modulus 2, remainder 1);
CREATE TABLE normal_table (id INT, part_tab_id BIGINT);

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT nt.* FROM normal_table nt LEFT JOIN part_tab pt ON nt.part_tab_id = pt.id;

PG15 EXPLAIN 輸出

QUERY PLAN
-------------------------------------------------------------------
 Merge Right Join (actual rows=0 loops=1)
   Merge Cond: (pt.id = nt.part_tab_id)
   ->  Merge Append (actual rows=0 loops=1)
         Sort Key: pt.id
         ->  Index Only Scan using part_tab_p0_pkey on part_tab_p0 pt_1 (actual rows=0 loops=1)
               Heap Fetches: 0
         ->  Index Only Scan using part_tab_p1_pkey on part_tab_p1 pt_2 (actual rows=0 loops=1)
               Heap Fetches: 0
   ->  Sort (actual rows=0 loops=1)
         Sort Key: nt.part_tab_id
         Sort Method: quicksort  Memory: 25kB
         ->  Seq Scan on normal_table nt (actual rows=0 loops=1)
 Planning Time: 0.325 ms
 Execution Time: 0.037 ms
(14 rows)

PG16 EXPLAIN 輸出

QUERY PLAN
-----------------------------------------------------
 Seq Scan on normal_table nt (actual rows=0 loops=1)
 Planning Time: 0.244 ms
 Execution Time: 0.015 ms
(3 rows)

這里需要注意的重要一點是，PostgreSQL 16 計劃不包括 join to part_tab 意味著所要做的就是 scan normal_table .

9. 盡可能使用 Limit 而不是 Unique 來實現 DISTINCT

PostgreSQL 查詢計劃程序在檢測到所有行都包含相同的值時，能夠避免包含計劃節點以刪除重復的結果。檢測這一點是微不足道的，當可以應用優化時，它可以帶來巨大的性能提升。

-- Setup
CREATE TABLE abc (a int, b int, c int);
INSERT INTO abc SELECT a%10,a%10,a%10 FROM generate_series(1,1000000)a;
VACUUM ANALYZE abc;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT DISTINCT a,b,c FROM abc WHERE a = 5 AND b = 5 AND c = 5;

PG15 EXPLAIN 輸出

QUERY PLAN
------------------------------------------------------------------------
 Unique (actual rows=1 loops=1)
   ->  Gather (actual rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Unique (actual rows=1 loops=3)
               ->  Parallel Seq Scan on abc (actual rows=33333 loops=3)
                     Filter: ((a = 5) AND (b = 5) AND (c = 5))
                     Rows Removed by Filter: 300000
 Planning Time: 0.114 ms
 Execution Time: 30.381 ms
(10 rows)

PG16 EXPLAIN 輸出

QUERY PLAN
---------------------------------------------------
 Limit (actual rows=1 loops=1)
   ->  Seq Scan on abc (actual rows=1 loops=1)
         Filter: ((a = 5) AND (b = 5) AND (c = 5))
         Rows Removed by Filter: 4
 Planning Time: 0.109 ms
 Execution Time: 0.025 ms
(6 rows)

如果您仔細查看 SQL 查詢，您會注意到DISTINCT子句中的每一列在WHERE子句中也有一個相等條件。這意味著查詢中的所有輸出行在每列中都將具有相同的值。PostgreSQL 16 查詢計劃器能夠利用這些知識，并將查詢結果簡單地LIMIT轉換為 1 行。PostgreSQL 15 通過讀取整個結果并使用Unique運算符將所有行減少到一行來生成相同的查詢結果。Execution Time PostgreSQL 16 的速度比 PostgreSQL 15 快 1200 倍以上。

10. 放寬 select_ outer_ pathkeys_ for_ merge（） 中過于嚴格的規則

在 PostgreSQL 16 之前，當查詢計劃程序考慮執行 a Merge Join 時，它會檢查合并的排序順序是否適合任何上層計劃操作（例如 DISTINCT ， GROUP BY 或 ORDER BY ），并且僅在它與上層的要求完全匹配時使用該順序。這個選擇有點過時了，因為Incremental Sorts可以用于這些上層操作，并且增量排序可以利用僅由結果需要作為排序依據的一些前導列預排序的結果。
PostgreSQL 16 查詢計劃程序將考慮Merge Join順序時使用的規則從“行的順序必須完全匹配”調整為“必須至少有 1 個前導列正確排序”。這允許 Planner 使用 Incremental Sorts 將行按正確的順序進行上級操作。我們從本博客的前面部分了解到，在可能的情況下，增量排序比完全排序需要更少的工作，因為增量排序能夠利用部分排序的輸入并以較小的批次執行排序，從而減少內存消耗和整體排序比較。

-- Setup

CREATE TABLE a (a INT, b INT);
CREATE TABLE b (x INT, y INT);
INSERT INTO a SELECT a,a FROM generate_series(1,1000000) a;
INSERT INTO b SELECT a,a FROM generate_series(1,1000000) a;
VACUUM ANALYZE a, b;

SET enable_hashjoin=0;
SET max_parallel_workers_per_gather=0;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT a,b,count(*) FROM a INNER JOIN b ON a.a = b.x GROUP BY a,b ORDER BY a DESC, b;

PG15 EXPLAIN 輸出

QUERY PLAN
---------------------------------------------------------------------------
 GroupAggregate (actual rows=1000000 loops=1)
   Group Key: a.a, a.b
   ->  Sort (actual rows=1000000 loops=1)
         Sort Key: a.a DESC, a.b
         Sort Method: external merge  Disk: 17664kB
         ->  Merge Join (actual rows=1000000 loops=1)
               Merge Cond: (a.a = b.x)
               ->  Sort (actual rows=1000000 loops=1)
                     Sort Key: a.a
                     Sort Method: external merge  Disk: 17664kB
                     ->  Seq Scan on a (actual rows=1000000 loops=1)
               ->  Materialize (actual rows=1000000 loops=1)
                     ->  Sort (actual rows=1000000 loops=1)
                           Sort Key: b.x
                           Sort Method: external merge  Disk: 11768kB
                           ->  Seq Scan on b (actual rows=1000000 loops=1)
 Planning Time: 0.175 ms
 Execution Time: 1010.738 ms
(18 rows)

PG16 EXPLAIN 輸出

QUERY PLAN
---------------------------------------------------------------------------
 GroupAggregate (actual rows=1000000 loops=1)
   Group Key: a.a, a.b
   ->  Incremental Sort (actual rows=1000000 loops=1)
         Sort Key: a.a DESC, a.b
         Presorted Key: a.a
         Full-sort Groups: 31250  Sort Method: quicksort  Average Memory: 26kB  Peak Memory: 26kB
         ->  Merge Join (actual rows=1000000 loops=1)
               Merge Cond: (a.a = b.x)
               ->  Sort (actual rows=1000000 loops=1)
                     Sort Key: a.a DESC
                     Sort Method: external merge  Disk: 17672kB
                     ->  Seq Scan on a (actual rows=1000000 loops=1)
               ->  Materialize (actual rows=1000000 loops=1)
                     ->  Sort (actual rows=1000000 loops=1)
                           Sort Key: b.x DESC
                           Sort Method: external merge  Disk: 11768kB
                           ->  Seq Scan on b (actual rows=1000000 loops=1)
 Planning Time: 0.140 ms
 Execution Time: 915.589 ms
(19 rows)

在上面的 PG16 EXPLAIN 輸出中，您可以看到使用了 an Incremental Sort （與 PG15 相比，PG15 改用 a Sort ），這導致 PG16 Execution Time 中的查詢略有減少，而用于執行排序的內存大幅減少。

Conclusion結論

來自世界各地的許多工程師在 PostgreSQL 16 中完成了大量工程工作來改進查詢計劃器。我要感謝所有通過審查我參與的作品提供幫助的人，以及所有對更改提供反饋的人。
上述 PostgreSQL 16 計劃程序的 10 項改進中的每一項都是默認啟用的，并且要么應用于可以優化的所有情況，要么由查詢計劃程序在認為優化會有所幫助時選擇性地應用。
 如果您運行的是舊版本的 PostgreSQL，我建議您在 PostgreSQL 16 上嘗試工作負載，看看哪些查詢更快。與往常一樣，歡迎在 pgsql-general@postgresql.org 郵件列表中提供有關 PostgreSQL 實際使用情況的反饋 — 您不必只提交問題，也可以隨時分享積極的體驗。因此，請告訴我們您使用 PostgreSQL 16 規劃器的體驗。

歸屬：David Rowley 撰寫的這篇關于 Postgres 16 查詢計劃程序改進的博客文章最初發布在 Citus Open Source Blog 上。