postgresql 遞歸更新導致順序掃描

我有一個帶有自引用外鍵的表，我想使用相同的遞歸更新來更新給定的父級以及父級的所有後代。

當使用帶有遞歸 CTE 的 UPDATE 時，它只返回 25K 行中的 10 行，優化器使用散列半連接對正在更新的表進行順序掃描，而不是更優化的嵌套循環和索引掃描。它的執行時間很慢，約為 5-10 毫秒。

將此表的大小增加到 250K 行將導致使用索引掃描。具有諷刺意味的是，與 25K 表（約 5-10 毫秒秒）相比，執行時間實際上要快得多（約 0.5 到 1.0 毫秒）一個數量級，這正是因為它使用索引而不是順序掃描。

我在這裡的猜測是，優化器無法先執行 CTE 然後計劃更新，而是需要提前計劃，並且錯誤地假設 CTE 返回的行數比實際多得多。

Postgres 不允許索引優化器提示。在生產中沒有將 enable_seqscan 設置為 off，是否有任何解決方法可以讓 postgres 使用索引？

---

設置：

drop table emp;

create table emp (id int primary key, manager_id int, department text);
create index emp_manager_id on emp (manager_id);

insert into emp 
select i id, 
   case when mod(i, 10) = 0 then null else i - 1 end manager_id, 
   null department
from generate_series(0, 25000) as i;

analyze emp;
vacuum emp;

這是更新的 DML。這只會更新 10 行。不管我是使用 IN、EXISTS 還是從遞歸 CTE 更新，它們都會導致順序掃描

explain 
with recursive foo as (
   select id, manager_id, department
   from emp
   where id = 1000

   union all

   select emp.id, emp.manager_id, emp.department
   from emp join foo on emp.manager_id = foo.id
)
update emp
set department = 'IT'
where id in (select id from foo);

結果是

                                            QUERY PLAN
-----------------------------------------------------------------------------------------------------
Update on emp  (cost=766.85..939.24 rows=101 width=74)
  CTE foo
    ->  Recursive Union  (cost=0.29..763.57 rows=101 width=40)
          ->  Index Scan using emp_pkey on emp emp_1  (cost=0.29..8.30 rows=1 width=40)
                Index Cond: (id = 1000)
          ->  Nested Loop  (cost=0.29..75.32 rows=10 width=40)
                ->  WorkTable Scan on foo foo_1  (cost=0.00..0.20 rows=10 width=4)
                ->  Index Scan using emp_manager_id on emp emp_2  (cost=0.29..7.50 rows=1 width=40)
                      Index Cond: (manager_id = foo_1.id)
  ->  Hash Semi Join  (cost=3.28..175.67 rows=101 width=74)
        Hash Cond: (emp.id = foo.id)
        ->  Seq Scan on emp  (cost=0.00..145.01 rows=10001 width=14)
        ->  Hash  (cost=2.02..2.02 rows=101 width=32)
              ->  CTE Scan on foo  (cost=0.00..2.02 rows=101 width=32)

解釋分析給出相同的結果。為簡潔起見，我在這裡使用說明。

---

考慮到 25,000 行中只有 10 行正在更新，這種帶有順序掃描的散列半連接並不是最優的。帶有索引掃描的嵌套循環在這裡是理想的。

設置 enable_seqscan=off 將時間減少到 ~0.1ms（從 ~5-10ms）

如果我不使用遞歸 CTE，則使用以下更新generate_series顯示 emp_id 索引已正確用於通過嵌套循環執行更新。這是我對遞歸 CTE 更新的期望。

explain 
update emp 
set department = 'IT' 
where id in (
   select i from generate_series(1000,1009) i
);


                                       QUERY PLAN
------------------------------------------------------------------------------------------
Update on emp  (cost=0.43..83.59 rows=11 width=74)
  ->  Nested Loop  (cost=0.43..83.59 rows=11 width=74)
        ->  HashAggregate  (cost=0.14..0.25 rows=11 width=32)
              Group Key: i.i
              ->  Function Scan on generate_series i  (cost=0.00..0.11 rows=11 width=32)
        ->  Index Scan using emp_pkey on emp  (cost=0.29..7.58 rows=1 width=14)
              Index Cond: (id = i.i)

如果我將表中的行數從 10K 增加到 250K，則說明計劃確實會導致索引的最佳使用。但是，對於 25K 行/seq 掃描，執行需要大約 5-10 毫秒。對於 250K 行，索引掃描大約需要 0.5-0.1 毫秒。

我的猜測是 postgres 無法先執行 CTE，然後計算更新計劃。它需要在執行 CTE 之前計算一個計劃。因此 postgres 無法知道 CTE 只返回了 10 行，而是必須猜測數字。所以 postgres 猜測 CTE 將返回 1000 行之類的東西，這使得它在表僅包含 25K 時更喜歡順序掃描。我假設我的 250K 表使用索引掃描的原因是 postgres 繼續猜測 CTE 正在返回 1000 行，但在 250K 中，索引掃描更有意義。

---

Postgres 不允許索引優化器提示。在生產中沒有將 enable_seqscan 設置為 off，是否有任何解決方法可以讓 postgres 使用索引？

---

@a_horse_with_no_name 使用的解決方案emp.id = any(array(select id from foo))很棒。它導致以下解釋簡單，略有不同：

                                    QUERY PLAN
------------------------------------------------------------------------------------
Update on emp  (cost=44.19..48.93 rows=10 width=46)
  CTE foo
    ->  Recursive Union  (cost=0.00..42.17 rows=101 width=11)
          ->  Seq Scan on emp emp_1  (cost=0.00..3.08 rows=1 width=11)
                Filter: (id = 0)
          ->  Hash Join  (cost=0.33..3.71 rows=10 width=11)
                Hash Cond: (emp_2.manager_id = foo.id)
                ->  Seq Scan on emp emp_2  (cost=0.00..2.66 rows=166 width=11)
                ->  Hash  (cost=0.20..0.20 rows=10 width=4)
                      ->  WorkTable Scan on foo  (cost=0.00..0.20 rows=10 width=4)
  InitPlan 2 (returns $2)
    ->  CTE Scan on foo foo_1  (cost=0.00..2.02 rows=101 width=4)
  ->  Seq Scan on emp  (cost=0.00..4.73 rows=10 width=46)
        Filter: (id = ANY ($2))

誰能解釋這兩個部分之間的區別：

原始的 enable_seqscan=off：

  ->  Nested Loop  (cost=2.56..294.11 rows=101 width=74) (actual time=0.091..0.118 rows=10 loops=1)
        ->  HashAggregate  (cost=2.27..3.28 rows=101 width=32) (actual time=0.076..0.080 rows=10 loops=1)
              Group Key: foo.id
              Batches: 1  Memory Usage: 24kB
              ->  CTE Scan on foo  (cost=0.00..2.02 rows=101 width=32) (actual time=0.024..0.068 rows=10 loops=1)
        ->  Index Scan using emp_pkey on emp  (cost=0.29..2.88 rows=1 width=14) (actual time=0.003..0.003 rows=1 loops=10)
              Index Cond: (id = foo.id)

使用any(array(...))：

  InitPlan 2 (returns $2)
    ->  CTE Scan on foo foo_1  (cost=0.00..2.02 rows=101 width=4)
  ->  Seq Scan on emp  (cost=0.00..4.73 rows=10 width=46)
        Filter: (id = ANY ($2))

foo.id首先，在執行 CTE 掃描後，我的原始查詢導致遞歸 cte 的 HashAggregate 。只有在此之後，它才會遍歷emp索引。我不明白它為什麼這樣做。使用any(array(...))，它將跳過這一步，並簡單地在 cte 掃描和索引掃描上嵌套循環。

其次，可能也是最重要的，any(array(...))在 this 中使用結果InitPlan 2。我相信這裡發生的事情是以any(array(...))某種方式迫使查詢規劃器將它們作為兩個不同的查詢來執行。首先它執行 CTE，它只返回 10 行。然後，規劃器知道只有 10 行它可以使用索引掃描而不是 seqscan。由於某種原因，我的原始解決方案無法強制查詢規劃器將這些作為兩個不同的查詢執行，因此查詢規劃器事先不知道要返回多少行。

有任何想法嗎？

這似乎總是使用索引掃描（至少在 Postgres 14 上）

with recursive foo as (
   select id, manager_id, department
   from emp
   where id = 1000

   union all

   select emp.id, emp.manager_id, emp.department
   from emp 
     join foo on emp.manager_id = foo.id
)
update emp
set department = 'IT'
where emp.id = any (array(select id from foo))

如果您有快速 (SSD) 磁碟，您可能需要考慮降低random_page_cost，以使 Postgres 通常支持索引掃描

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