使用 Merge Join 時，帶有分頁的 PostgreSQL 查詢很慢

我正在嘗試優化一個查詢，該查詢用於通過連接表迭代行頁面。在將查詢從 using 更改OFFSET為WHERE id > last_id_from_prev_page和ORDER BY如此處所述之後，查詢在開始時表現良好，但隨著它越來越接近中間頁面，它表現不佳。

這是一些最小的 DDL 來表示我正在使用的數據：

-- DDL:
create table sub ( -- 4.3M records
   subscription_id     varchar(255) -- UUIDs
       not null constraint pk_sub primary key,
   subscription_status varchar(255) -- (ACTIVE, PENDING, or CANCELLED)
);

create table sub_item ( -- 4.4M records, mostly 1:1 with subscriptions
   subscription_item_id varchar(255) -- UUIDs
       not null constraint pk_sub_item primary key,
   subscription_id      varchar(255) -- FK to sub
       constraint fk_sub_item_sub_id references sub,
   item_ref             varchar(255) -- 25 character strings, mostly the same
);

create index idx_sub_item_sub_id_btree on sub_item (subscription_id);

我正在使用的查詢：

-- Query: Grab a page from the middle
EXPLAIN ANALYZE
SELECT s.subscription_id, *
FROM sub s
   JOIN sub_item si ON s.subscription_id = si.subscription_id AND si.item_ref = '1001107599999910222001000'
WHERE s.subscription_status = 'ACTIVE'
 AND s.SUBSCRIPTION_ID > '7ca1cf6b-2452-4d1b-bd03-1ba68b63b528'
ORDER BY s.subscription_id
LIMIT 50;

預設情況下，此查詢執行一個性能很差的合併連接，通常是 10 秒以上：

Limit  (cost=2.41..325.76 rows=50 width=179) (actual time=10336.095..10340.389 rows=50 loops=1)
 ->  Merge Join  (cost=2.41..765832.96 rows=118421 width=179) (actual time=10336.086..10339.900 rows=50 loops=1)
       Merge Cond: ((s.subscription_id)::text = (si.subscription_id)::text)
       ->  Index Scan using pk_sub on sub s  (cost=0.56..270648.21 rows=632913 width=45) (actual time=0.025..1.643 rows=90 loops=1)
             Index Cond: ((subscription_id)::text > '7ca1cf6b-2452-4d1b-bd03-1ba68b63b528'::text)
             Filter: ((subscription_status)::text = 'ACTIVE'::text)
             Rows Removed by Filter: 211
       ->  Index Scan using idx_sub_item_sub_id_btree on sub_item si  (cost=0.56..490385.99 rows=813151 width=98) (actual time=0.016..8260.586 rows=393058 loops=1)
             Filter: ((item_ref)::text = '1001107599999910222001000'::text)
             Rows Removed by Filter: 1742475
Planning Time: 0.309 ms
Execution Time: 10340.691 ms

我對閱讀這些 Postgres 查詢計劃並不是很有經驗，但似乎sub_item大部分時間都花在了對錶的索引掃描上。我找到了幾種方法來規避這一點。一是為了簡單地SET enable_mergejoin = OFF;。另一種是HASH在sub_item.subscription_id. 這兩者都會導致規劃器使用嵌套循環連接，它的執行速度要快得多：

Limit  (cost=1.11..446.97 rows=50 width=179) (actual time=0.150..4.351 rows=50 loops=1)
 ->  Nested Loop  (cost=1.11..1055964.59 rows=118421 width=179) (actual time=0.140..3.850 rows=50 loops=1)
       ->  Index Scan using pk_sub on sub s  (cost=0.56..270648.21 rows=632913 width=45) (actual time=0.064..0.898 rows=90 loops=1)
             Index Cond: ((subscription_id)::text > '7ca1cf6b-2452-4d1b-bd03-1ba68b63b528'::text)
             Filter: ((subscription_status)::text = 'ACTIVE'::text)
             Rows Removed by Filter: 211
       ->  Index Scan using idx_sub_item_sub_id_btree on sub_item si  (cost=0.56..1.23 rows=1 width=98) (actual time=0.013..0.016 rows=1 loops=90)
             Index Cond: ((subscription_id)::text = (s.subscription_id)::text)
             Filter: ((item_ref)::text = '1001107599999910222001000'::text)
             Rows Removed by Filter: 0
Planning Time: 0.196 ms
Execution Time: 4.623 ms

但我不清楚為什麼 Merge Join 這麼慢。根據我的理解，它在表被排序或具有排序訪問權限時使用（這些通過subPK 和sub_item.subscription_idbtree 索引）。沒有連接的查詢幾乎是即時的，直接查找sub_item.subscription_id也幾乎是即時的。

所以我的兩個問題是：

1. 為什麼 Merge Join 在這種類型的查詢/分頁中表現如此糟糕？
2. 在這種情況下，為什麼 Postgres 查詢規劃器偏愛慢得多的 Merge Join？

計劃者不了解 s.subscription_id 的限制如何與合併連接互動。“s”上的索引掃描通過跳轉到索引的中間快速跳過大約一半的元組，而“si”上的索引掃描將讀取與表中快速跳過的元組相對應的所有元組“ s" 在它開始查找任何匹配返回的元組之前（因此計入 LIMIT）。

您可以通過複製 subscription_id 條件對“si”進行索引掃描，以快速跳過這些元組：

SELECT s.subscription_id, *
FROM sub s
   JOIN sub_item si ON s.subscription_id = si.subscription_id AND si.item_ref = '1001107599999910222001000' AND
                        si.subscription_id > '7ca1cf6b-2452-4d1b-bd03-1ba68b63b528'
WHERE s.subscription_status = 'ACTIVE'
 AND s.SUBSCRIPTION_ID > '7ca1cf6b-2452-4d1b-bd03-1ba68b63b528'
ORDER BY s.subscription_id
LIMIT 50;

我將額外的測試放在 ON 而不是 WHERE 中，因為如果您要將其轉換為 LEFT JOIN，它需要在 ON 中。既然是inner JOIN，那麼放在哪裡都無所謂，不如把它放在比較安全的地方，以防以後發生變化。

通常，計劃者足夠聰明，可以意識到如果 ax=bx 則任何針對 ax 的測試也可以自動應用於 bx。我不明白為什麼它在這裡不這樣做的細節。

引用自：https://dba.stackexchange.com/questions/283117