Postgresql

處理與嵌套循環相關的次優 postgres 查詢計劃

  • March 30, 2022

我正在開發一個動態建構 SQL 查詢並針對 postgres 13 執行它們的應用程序。

一些查詢非常慢，因為它們使用嵌套循環，但查詢規劃器低估了循環的數量。手動執行相同的查詢將set enable_nestloop to off;查詢速度從 28 秒提高到 500 毫秒。（我將在最後發布此範例查詢的計劃）。我認為原因是使用了很多過濾器（使用者可以根據需要添加更多過濾器）。

我嘗試過的事情：

  • 全域禁用嵌套循環：對某些查詢有很大幫助，但會使許多其他查詢慢一些，因此不是一個好的解決方案
  • 將統計目標增加到最大值：我已將所有相關表的統計目標設置為最大值（10000）並分析了所有表。沒有效果。
  • 擴展統計：我設置了多個擴展統計。我嘗試了很多不同的組合，雖然它有助於一些查詢，但對於大多數查詢（包括範例查詢）它沒有效果。我希望它會產生影響，因為過濾器通常可以相互依賴。
  • 操作 random_page_cost：我將隨機訪問的成本設置得非常高（例如 seq 訪問成本的 15 倍）。這鼓勵了規劃器不要對某些查詢使用嵌套循環，但它會使許多其他查詢的性能變差，並且感覺比一般禁用嵌套循環更糟糕。

到目前為止，我沒有運氣，我已經沒有選擇了。非常歡迎提出想法！

這是一個查詢和計劃的範例（有和沒有嵌套循環）：

explain analyze select
   count(distinct EMPLOYEE.employeeId) as "measure_0",
   avg(DATA.score) as "measure_1",
   EMPLOYEE.managerId as "dimension_0",
   DATA.versionId as "dimension_1"
from
   data
join EMPLOYEE on
   (DATA.EMPLOYEEID = EMPLOYEE.EMPLOYEEID
       and DATA.versionid = EMPLOYEE.versionid
       and EMPLOYEE.namespace = 'company1')
where
   (DATA.namespace = 'company1'
       and EMPLOYEE.managerId in ('id1', 'id2', ...[100 more])
           and EMPLOYEE.versionId in ('version1', 'version2', ...[10 more])
               and DATA.versionId in ('version1', 'version2', ...[10 more, same as above]))
group by
   "dimension_0",
   "dimension_1";

啟用嵌套循環的計劃：

GroupAggregate  (cost=62420.73..62420.75 rows=1 width=34) (actual time=28117.300..28142.646 rows=172 loops=1)
 Group Key: employee.managerid, data.versionid
 ->  Sort  (cost=62420.73..62420.73 rows=1 width=35) (actual time=28116.925..28123.021 rows=23132 loops=1)
       Sort Key: employee.managerid, data.versionid
       Sort Method: quicksort  Memory: 4021kB
       ->  Gather  (cost=1082.22..62420.72 rows=1 width=35) (actual time=85.357..28059.440 rows=23132 loops=1)
             Workers Planned: 2
             Workers Launched: 2
             ->  Nested Loop  (cost=82.22..61420.62 rows=1 width=35) (actual time=89.586..27974.865 rows=7711 loops=3)
                   ->  Parallel Bitmap Heap Scan on employee  (cost=81.79..7385.09 rows=16 width=27) (actual time=9.869..118.660 rows=541 loops=3)
                         Recheck Cond: (((namespace)::text = 'company1'::text) AND ((versionid)::text = ANY ('{version1,version2, ...[10 more]}'::text[])))
                         Filter: ((managerid)::text = ANY ('{id1, id2, ...[100 more]}'::text[]))
                         Rows Removed by Filter: 5739
                         Heap Blocks: exact=2848
                         ->  Bitmap Index Scan on index_employee_namespace_versionid  (cost=0.00..81.78 rows=6338 width=0) (actual time=5.864..5.865 rows=20535 loops=1)
                               Index Cond: (((namespace)::text = 'company1'::text) AND ((versionid)::text = ANY ('{version1,version2, ...[10 more]}'::text[])))
                   ->  Index Scan using index_data_namespace_versionid_employeeid on data  (cost=0.43..3377.21 rows=1 width=30) (actual time=29.467..51.361 rows=14 loops=1624)
                         Index Cond: (((namespace)::text = 'company1'::text) AND ((versionid)::text = (employee.versionid)::text) AND ((versionid)::text = ANY ('{version1,version2, ...[10 more]}'::text[])))
                         Filter: ((employee.employeeid)::text = (employeeid)::text)
                         Rows Removed by Filter: 19192
Planning Time: 19.748 ms
Execution Time: 28142.911 ms

禁用嵌套循環：

GroupAggregate  (cost=150534.11..150534.14 rows=1 width=34) (actual time=486.321..511.282 rows=172 loops=1)
 Group Key: employee.managerid, data.versionid
 ->  Sort  (cost=150534.11..150534.12 rows=1 width=35) (actual time=486.009..494.591 rows=23132 loops=1)
       Sort Key: employee.managerid, data.versionid
       Sort Method: quicksort  Memory: 4021kB
       ->  Gather  (cost=143162.61..150534.10 rows=1 width=35) (actual time=315.171..427.200 rows=23132 loops=1)
             Workers Planned: 2
             Workers Launched: 2
             ->  Parallel Hash Join  (cost=142162.61..149534.00 rows=1 width=35) (actual time=295.646..398.855 rows=7711 loops=3)
                   Hash Cond: (((employee.employeeid)::text = (data.employeeid)::text) AND ((employee.versionid)::text = (data.versionid)::text))
                   ->  Parallel Bitmap Heap Scan on employee  (cost=81.79..7385.09 rows=16 width=27) (actual time=18.563..107.860 rows=541 loops=3)
                         Recheck Cond: (((namespace)::text = 'company1'::text) AND ((versionid)::text = ANY ('{version1,version2, ...[10 more]}'::text[])))
                         Filter: ((managerid)::text = ANY ('{id1, id2, ...[100 more]}'::text[]))
                         Rows Removed by Filter: 5739
                         Heap Blocks: exact=4523
                         ->  Bitmap Index Scan on index_employee_namespace_versionid  (cost=0.00..81.78 rows=6338 width=0) (actual time=5.950..5.952 rows=20535 loops=1)
                               Index Cond: (((namespace)::text = 'company1'::text) AND ((versionid)::text = ANY ('{version1,version2, ...[10 more]}'::text[])))
                   ->  Parallel Hash  (cost=140631.49..140631.49 rows=96622 width=30) (actual time=275.676..275.679 rows=76621 loops=3)
                         Buckets: 262144  Batches: 1  Memory Usage: 18304kB
                         ->  Parallel Bitmap Heap Scan on data  (cost=2894.61..140631.49 rows=96622 width=30) (actual time=14.263..159.147 rows=76621 loops=3)
                               Recheck Cond: (((namespace)::text = 'company1'::text) AND ((versionid)::text = ANY ('{version1,version2, ...[10 more]}'::text[])))
                               Heap Blocks: exact=4099
                               ->  Bitmap Index Scan on index_data_namespace_versionid_employeeid  (cost=0.00..2836.64 rows=231892 width=0) (actual time=29.397..29.397 rows=229862 loops=1)
                                     Index Cond: (((namespace)::text = 'company1'::text) AND ((versionid)::text = ANY ('{version1,version2, ...[10 more]}'::text[])))
Planning Time: 19.753 ms
Execution Time: 511.575 ms

更新的查詢計劃

在此處使用set max_parallel_workers_per_gather TO 0;和使用緩衝區。

enable_nestloop=on

GroupAggregate  (cost=149659.22..149659.24 rows=1 width=34) (actual time=29435.636..29452.065 rows=172 loops=1)
 Group Key: employee.managerid, data.versionid
 Buffers: shared hit=29795930 read=12823
 I/O Timings: read=4732.666
 ->  Sort  (cost=149659.22..149659.22 rows=1 width=34) (actual time=29435.143..29437.483 rows=23132 loops=1)
       Sort Key: employee.managerid, data.versionid
       Sort Method: quicksort  Memory: 4021kB
       Buffers: shared hit=29795930 read=12823
       I/O Timings: read=4732.666
       ->  Nested Loop  (cost=0.86..149659.21 rows=1 width=34) (actual time=440.432..29371.818 rows=23132 loops=1)
             Buffers: shared hit=29795927 read=12823
             I/O Timings: read=4732.666
             ->  Index Scan using index_employee_namespace_versionid on employee  (cost=0.43..7451.89 rows=43 width=26) (actual time=2.811..2057.831 rows=1624 loops=1)
                   Index Cond: (((namespace)::text = 'company1'::text) AND ((versionid)::text = ANY ('{version1,version2, ...[10 more]}'::text[])))
                   Filter: ((managerid)::text = ANY ('{{id1, id2, ...[100 more]}'::text[]))
                   Rows Removed by Filter: 17216
                   Buffers: shared hit=18938 read=727
                   I/O Timings: read=484.313
             ->  Index Scan using index_data_namespace_versionid_employeeid on data  (cost=0.43..3307.14 rows=1 width=30) (actual time=10.658..16.779 rows=14 loops=1624)
                   Index Cond: (((namespace)::text = 'company1'::text) AND ((versionid)::text = (employee.versionid)::text) AND ((versionid)::text = ANY ('{version1,version2, ...[10 more]}'::text[])))
                   Filter: ((employee.employeeid)::text = (employeeid)::text)
                   Rows Removed by Filter: 19192
                   Buffers: shared hit=29776989 read=12096
                   I/O Timings: read=4248.353
Planning:
 Buffers: shared hit=698
Planning Time: 40.529 ms
Execution Time: 29452.638 ms

enable_nestloop=off

GroupAggregate  (cost=158256.87..158256.90 rows=1 width=34) (actual time=444.300..462.896 rows=172 loops=1)
 Group Key: employee.managerid, data.versionid
 Buffers: shared hit=32581
 ->  Sort  (cost=158256.87..158256.88 rows=1 width=34) (actual time=444.046..447.080 rows=23132 loops=1)
       Sort Key: employee.managerid, data.versionid
       Sort Method: quicksort  Memory: 4021kB
       Buffers: shared hit=32581
       ->  Hash Join  (cost=150369.91..158256.86 rows=1 width=34) (actual time=305.940..410.826 rows=23132 loops=1)
             Hash Cond: (((employee.employeeid)::text = (data.employeeid)::text) AND ((employee.versionid)::text = (data.versionid)::text))
             Buffers: shared hit=32581
             ->  Index Scan using index_employee_namespace_versionid on employee  (cost=0.43..7451.89 rows=43 width=26) (actual time=0.128..89.940 rows=1624 loops=1)
                   Index Cond: (((namespace)::text = 'company1'::text) AND ((versionid)::text = ANY ('{version1,version2, ...[10 more]}'::text[])))
                   Filter: ((managerid)::text = ANY ('{{id1, id2, ...[100 more]}'::text[]))
                   Rows Removed by Filter: 17216
                   Buffers: shared hit=19662
             ->  Hash  (cost=146839.27..146839.27 rows=235347 width=30) (actual time=304.247..304.250 rows=229862 loops=1)
                   Buckets: 262144  Batches: 1  Memory Usage: 17088kB
                   Buffers: shared hit=12919
                   ->  Bitmap Heap Scan on data  (cost=2980.64..146839.27 rows=235347 width=30) (actual time=19.781..165.733 rows=229862 loops=1)
                         Recheck Cond: (((namespace)::text = 'company1'::text) AND ((versionid)::text = ANY ('{version1,version2, ...[10 more]}'::text[])))
                         Heap Blocks: exact=12285
                         Buffers: shared hit=12919
                         ->  Bitmap Index Scan on index_data_namespace_versionid_employeeid  (cost=0.00..2921.80 rows=235347 width=0) (actual time=17.701..17.701 rows=229862 loops=1)
                               Index Cond: (((namespace)::text = 'company1'::text) AND ((versionid)::text = ANY ('{version1,version2, ...[10 more]}'::text[])))
                               Buffers: shared hit=634
Planning Time: 21.766 ms
Execution Time: 463.536 ms

如果IN列表中有那麼多元素，請將它們寫入臨時表，ANALYZE該表併計算與該表的連接。

您目前正在針對同一個 IN 列表測試 EMPLOYEE.versionId 和 DATA.versionId。規劃人員在進行估算時會將這兩個選擇性相乘。但這當然是錯誤的，因為兩個 versionId 列已經被限制為彼此相等，要麼兩者都將通過 IN 列表，要麼兩者都將失敗。它們是完全依賴的，而不是像規劃者想像的那樣獨立。因此，您可以嘗試的最簡單的事情是刪除這兩個列表中的測試之一。當然，這不是唯一的估計問題，但它是最容易解決的。

很難預測這會發生什麼，原因有兩個。因此，您只需要嘗試一下即可。一個原因是我們不知道 IN-list 的選擇性有多大。所以我們不知道只乘以它一次而不是兩次會產生多大的差異。另一個是估計的行數被限制為不少於一，並且也報告為整數。那麼雜湊連接的 1 估計值真的是 1.49，還是真的 0.0000001？將其中的第一個乘以（例如）100 將對最終結果產生很大影響，而將後者乘以 100 則不會。

在實驗上，我確實看到擁有 varchar(255) 類型的列確實會導致計劃顯示大量轉換為文本。將這些類型更改為“文本”不太可能產生性能差異，但它會使計劃更易於閱讀。跨類型比較可能會禁用有效的索引使用，並且還會導致計劃中出現顯式轉換，這就是我詢問它的原因。但是由於 varchar 列總是顯示為強制轉換但不會禁用索引，所以這實際上可能不是問題。

但這確實讓我想知道為什麼索引“index_data_namespace_versionid_employeeid”的使用如此糟糕。名字暗示索引結束(namespace, versionid, employeeid). 但是如果是這樣，為什麼在過濾器中而不是在索引條件中存在employeeid？我自己無法重現這一點，我將索引條件中的所有 3 列顯示為簡單的相等檢查，而 versionID IN-list 檢查是降級到過濾器的內容。如果您可以使用索引的所有 3 列，我認為“壞”計劃會變得更好——可能比散列計劃更好，或者至少好到不必再擔心了。刪除無關的 IN 列表檢查應該能夠實現這一點（或者它可能會提示更改為使用散列連接，從而完全擺脫嵌套循環——其中任何一個都是勝利）。但如果這不起作用，您可以嘗試反轉索引中的最後兩列，所以它會是(namespace, employeeid, versionid). 由於我無法重現該問題，因此我無法測試這些東西是否可以解決它。

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

相關問答

Postgresql

FROM 子句中的相關函式是否針對每一行執行？

  • July 10, 2022
檢視問答
Postgresql

具有大 IN 的 Postgres 查詢，並且在臨時表上加入似乎不起作用

  • July 7, 2022
檢視問答
Postgresql

為什麼優化器不在我的表上使用聚群索引？

  • May 12, 2022
檢視問答
Postgresql

使用許多小型查找優化 PostgreSQL 查詢

  • May 6, 2022
檢視問答
Postgresql

PostgreSQL 順序掃描而不是索引掃描為什麼？

  • December 4, 2021
檢視問答
Postgresql

PostgreSQL：在查詢計劃中實現為 Merge Join 的內部節點

  • November 5, 2021
檢視問答