Eu tenho uma tabela com uma chave estrangeira auto-referencial e gostaria de atualizar um determinado pai e todos os descendentes do pai com a mesma atualização recursiva.
Ao usar um UPDATE com uma CTE recursiva que retorna apenas 10 linhas de 25.000 linhas, o otimizador usa uma semijunção de hash com varredura sequencial na tabela que está sendo atualizada, em vez do loop aninhado mais otimizado e varredura de índice. Tem um tempo de execução lento de cerca de 5-10ms.
Aumentar o tamanho dessa tabela para 250 mil linhas levará ao uso da verificação de índice. Ironicamente, o tempo de execução é realmente muito mais rápido (~0,5 a 1,0ms), por uma ordem de grandeza, comparado a uma tabela de 25K (~5-10ms segundos), precisamente porque está usando o índice em vez de varredura sequencial.
Meu palpite aqui é que o otimizador é incapaz de executar primeiro o CTE e depois planejar a atualização, em vez disso, ele precisa planejar com antecedência e está assumindo incorretamente que o CTE está retornando um número muito maior de linhas do que realmente é.
O Postgres não permite dicas do otimizador de índice. Além de definir enable_seqscan como desativado na produção, existe alguma solução alternativa para que o postgres use o índice?
Configurar:
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;
Aqui está a atualização DML. Isso atualiza apenas 10 linhas. Não importa se eu uso IN, EXISTS ou atualização do CTE recursivo, todos eles resultam em uma varredura sequencial
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);
Resulta em
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)
Explicar analisar dá o mesmo resultado. Estou usando explicar aqui por brevidade.
Essa semijunção de hash com varredura sequencial não é ideal, pois apenas 10 linhas de 25.000 estão sendo atualizadas. Um loop aninhado com uma varredura de índice seria ideal aqui.
Definir enable_seqscan=off reduz o tempo para ~0,1ms (de ~5-10ms)
Se eu não usar uma CTE recursiva, a seguinte atualização using generate_seriesmostra que o índice emp_id é usado corretamente para realizar a atualização por meio de um loop aninhado. Isso é o que eu esperaria da atualização recursiva do 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)
Se eu aumentar o número de linhas na tabela para 250K de 10K, o plano de explicação resultará no uso ideal do índice. No entanto, com varredura de 25.000 linhas/seq, leva ~5-10ms para executar. Com 250 mil linhas, a verificação do índice leva ~ 0,5-0,1 ms.
Meu palpite aqui é que o postgres não é capaz de executar primeiro o CTE e depois calcular um plano para a atualização. Ele precisa calcular um plano antes de executar o CTE. Portanto, o postgres não pode saber que apenas 10 linhas estão sendo retornadas do CTE e, em vez disso, precisa adivinhar o número. Portanto, o postgres está supondo que o CTE retornará algo como 1000 linhas, o que faz com que ele prefira a varredura sequencial quando a tabela contém apenas 25K. A razão pela qual minha tabela de 250K usa a varredura de índice, suponho, é que o postgres continua a adivinhar que o CTE está retornando 1000 linhas, mas de 250K uma varredura de índice faz mais sentido.
O Postgres não permite dicas do otimizador de índice. Além de definir enable_seqscan como desativado na produção, existe alguma solução alternativa para que o postgres use o índice?
A solução de @a_horse_with_no_name usando emp.id = any(array(select id from foo))é ótima. Isso resulta na seguinte explicação simples, que é um pouco diferente:
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))
Alguém pode explicar a diferença entre essas duas partes:
Original com 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)
Usando 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))
Primeiro, minha consulta original resulta em um HashAggregate do cte recursivo foo.iddepois de executar a varredura CTE. Somente depois disso ele percorre o empíndice. Não entendo porque está fazendo isso. Usando any(array(...)), ele pulará esta etapa e simplesmente fará um loop aninhado sobre a varredura cte e a varredura de índice.
Segundo, e provavelmente o mais importante, usar any(array(...))resultados neste arquivo InitPlan 2. Acredito que o que está acontecendo aqui é que de any(array(...))alguma forma força o planejador de consultas a executá-las como duas consultas diferentes. Primeiro ele executa o CTE, que retorna apenas 10 linhas. Então, o planejador sabe que com apenas 10 linhas ele pode usar uma varredura de índice em vez de seqscan. Minha solução original, por algum motivo, não pode forçar o planejador de consultas a executá-las como duas consultas diferentes, portanto, o planejador de consultas não sabe de antemão quantas linhas estão sendo retornadas.
Alguma ideia?
Isso parece sempre usar uma verificação de índice (pelo menos no Postgres 14)
Se você tem discos rápidos (SSD), você pode querer considerar abaixar
random_page_cost, para fazer com que o Postgres favoreça as verificações de índice em geral