Recentemente, comecei a converter um projeto pessoal do Microsoft SQL Server para o PostgreSQL e fiquei surpreso com o desempenho abismal que encontrei fazendo um UPDATE JOINentre duas tabelas.
Suponha que eles se pareçam com:
CREATE TABLE foo (
id INTEGER NOT NULL PRIMARY KEY,
bar INTEGER NULL
);
CREATE TABLE foo2 (
id INTEGER NOT NULL PRIMARY KEY,
bar INTEGER NULL
);
No T-SQL eu faria uma atualização usando um join usando algo assim:
UPDATE foo
SET bar = t2.bar
FROM foo t1
JOIN foo2 t2
ON t1.id = t2.id;
Mas rodando no Postgres, a consulta é glacialmente lenta.
Se eu mudar para:
UPDATE foo
SET bar = t2.bar
FROM foo2 t2
WHERE foo.id = t2.id;
Isso não é um problema.
Eu entendo que a sintaxe é diferente, mas eu esperava que o otimizador de consulta resolvesse algo no mesmo estádio. Em vez disso, as coisas enlouquecem. Além das diferenças sintáticas, há uma diferença sutil entre as duas consultas que não consigo ver?
Explique os planos
Update on foo (cost=85852.43..6211995294.24 rows=338326628280 width=1027)
-> Nested Loop (cost=85852.43..6211995294.24 rows=338326628280 width=1027)
-> Seq Scan on foo (cost=0.00..145721.10 rows=582410 width=1010)
-> Materialize (cost=85852.43..247935.91 rows=580908 width=17)
-> Hash Join (cost=85852.43..241627.37 rows=580908 width=17)
Hash Cond: (t1.id = t2.id)
-> Seq Scan on foo t1 (cost=0.00..145721.10 rows=582410 width=10)
-> Hash (cost=75754.08..75754.08 rows=580908 width=15)
-> Seq Scan on foo2 t2 (cost=0.00..75754.08 rows=580908 width=15)
Update on foo (cost=87575.47..535974.25 rows=581621 width=1022)
-> Hash Join (cost=87575.47..535974.25 rows=581621 width=1022)
Hash Cond: (foo.id = t2.id)
-> Seq Scan on foo (cost=0.00..151301.17 rows=1140417 width=1011)
-> Hash (cost=75761.21..75761.21 rows=581621 width=36)
-> Seq Scan on foo2 t2 (cost=0.00..75761.21 rows=581621 width=36)