使用合併連接查詢非常慢
我正在使用 Postgres 數據庫並嘗試優化以下查詢：
SELECT DISTINCT catalogite1_.id FROM cat_catalogitem catalogite1_ INNER JOIN cat_service service2_ ON catalogite1_.service_id=service2_.id LEFT OUTER JOIN cat_entitlement_services entitledse6_ ON service2_.id=entitledse6_.service_id LEFT OUTER JOIN cat_entitlement entitlemen7_ ON entitledse6_.entitlement_id=entitlemen7_.id AND (entitlemen7_.id IN ('505e03e5-7370-42c2-a26e-bdb2df593934' , '508da3b6-7147-4b16-971f-6e6476b8ef44' , '6c68fbd2-7cc4-4b7c-85c1-617b69578ab9' , '6c9e5ff0-a073-4923-a5ec-b47f5e4c120a' , '961bee54-e9d6-402c-a763-c3937b03402f' , '2f113c9a-9e2f-47d8-beda-df0e05faa167' , '471bca1e-a112-4842-bdfc-252b8848b862' , '482ba515-2197-4fdb-a74b-37d9a0795c4e' , '872038e4-766a-4b93-bf95-aa2735e7f942' , 'fd6345fc-8799-42a0-83d5-0234e450e397' , '7378e830-5271-482f-b73b-7ce4232b000d' , '6aeafe3b-aac9-4c67-8895-aa77da7a7d6b')) LEFT OUTER JOIN cat_subtenant catalogsub3_ ON catalogite1_.subtenant_id=catalogsub3_.id LEFT OUTER JOIN cat_entitlement_catalogitems entitledca4_ ON catalogite1_.id=entitledca4_.catalogitem_id LEFT OUTER JOIN cat_entitlement entitlemen5_ ON entitledca4_.entitlement_id=entitlemen5_.id AND (entitlemen5_.id IN ('505e03e5-7370-42c2-a26e-bdb2df593934' , '508da3b6-7147-4b16-971f-6e6476b8ef44' , '6c68fbd2-7cc4-4b7c-85c1-617b69578ab9' , '6c9e5ff0-a073-4923-a5ec-b47f5e4c120a' , '961bee54-e9d6-402c-a763-c3937b03402f' , '2f113c9a-9e2f-47d8-beda-df0e05faa167' , '471bca1e-a112-4842-bdfc-252b8848b862' , '482ba515-2197-4fdb-a74b-37d9a0795c4e' , '872038e4-766a-4b93-bf95-aa2735e7f942' , 'fd6345fc-8799-42a0-83d5-0234e450e397' , '7378e830-5271-482f-b73b-7ce4232b000d' , '6aeafe3b-aac9-4c67-8895-aa77da7a7d6b')) WHERE catalogite1_.is_requestable=true AND catalogite1_.status='PUBLISHED' AND catalogite1_.tenant_id='intel-1' AND ( service2_.id=NULL OR COALESCE(NULL) IS NULL) AND service2_.status='ACTIVE' AND service2_.tenant_id='intel-1' AND ((entitlemen7_.id IS NOT NULL) AND (catalogsub3_.id IS NULL OR entitlemen7_.subtenant_id=catalogsub3_.id) AND (entitlemen5_.id IS NULL OR entitlemen5_.subtenant_id<>entitlemen7_.subtenant_id) OR (entitlemen5_.id IS NOT NULL) AND (catalogsub3_.id IS NULL OR entitlemen5_.subtenant_id=catalogsub3_.id) AND entitledca4_.is_hidden=false)如您所見，查詢非常複雜，並且使用了多個連接和過濾條件。我的問題是這個查詢需要超過 60 秒才能完成！我的目標是將性能降低到 2-3 秒。
我已經做了一些背景研究，並研究了通過 Postgres 的
EXPLAIN ANALYZE功能執行這個查詢，這對瓶頸在哪裡提供了一些很好的見解。這是一個易於閱讀
EXPLAIN ANALYZE的輸出表示的連結：https://explain.depesz.com/s/f3hn
如您所見，主要瓶頸似乎在 Merge Join，數據庫過濾掉了超過3.17 億行！另一個瓶頸是在合併連接之前的幾個步驟中也發生的排序。我不確定為什麼會發生這種排序，因為
ORDER BY我的查詢中沒有操作。該排序似乎是外部磁碟排序，這可能是它被證明如此昂貴的原因。有人可以指出我此時優化此查詢的正確方向嗎？我想我已經設法查明了主要的性能瓶頸，只需要朝著正確的方向推進和/或如何改善這種情況的提示。
我不確定為什麼會發生這種排序，因為我的查詢中沒有 ORDER BY 操作。
它正在排序，以便它可以進行合併連接。合併連接需要排序輸入。
該排序似乎是外部磁碟排序，這可能是它被證明如此昂貴的原因。
不，實際排序根本不應該花費太多時間（儘管無論如何您可能想要增加 work_mem，但這種排序可能不需要在磁碟上。目前設置是什麼？）。但是，一旦它擁有已排序的數據，它就必須一次又一次地重新探測該數據，作為合併連接的一部分。這就是時間的流逝，其中一些時間歸因於排序步驟。此外，使用這種計劃，收集時間以報告 EXPLAIN ANALYZE 的成本可能很大，導致查詢花費的時間比不被監控時長幾倍。如果你做 EXPLAIN (ANALYZE, TIMING OFF)，你會得到什麼底線執行時間？
如果你讓它使用散列連接而不是合併連接，它可能不會改變任何東西，因為重新探測仍然必鬚髮生，只是通過不同的機制。
可能的問題是查詢作為兩個子分支執行，一個來自catalogite1_，一個來自service2_，然後在最後有效地進行笛卡爾連接。直到最後才能完成過濾，因為比較所需的一些數據來自一個分支，而一些來自另一個分支。它實際上是一個笛卡爾連接，因為 service2_ 在其中只有一個合格行，意思
catalogite1_.service_id=service2_.id不是很有選擇性我會嘗試更改查詢的這一部分：
ON service2_.id=entitledse6_.service_id對此：
ON catalogite1_.service_id=entitledse6_.service_id這可能允許過濾發生在查詢中低得多的位置。如果這可行，那麼知道為什麼計劃者沒有為您進行此切換會很有趣——它應該能夠做到。您對 join_collapse_limit 的設置是什麼？
另外，像這樣的事情：
AND ( service2_.id=NULL OR COALESCE(NULL) IS NULL)當然不要幫助規劃者做出合理的選擇！
工作記憶體不足導致從記憶體回退到磁碟進行排序。
排序（成本=1,429.34..1,443.41 行=5,630 寬度=123）（實際時間=60.587..42,128.419 行=318,428,848 循環=1）
排序鍵：catalogite1_.service_id 排序方法：外部排序磁碟：2,600kB
嘗試將 work_mem 增加到額外的 5MB 並查看是否可以修復此排序計劃節點。