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。由于某种原因，我的原始解决方案无法强制查询规划器将这些作为两个不同的查询执行，因此查询规划器事先不知道要返回多少行。

有任何想法吗？