postgre为什么这个SQL查询会陷入无休止的循环中

Question

以下PostgreSQL查询

UPDATE table_A A 
SET is_active = false 
FROM table_A 
WHERE A.parent_id IS NULL AND A.is_active = true AND A.id = ANY 
(SELECT (B.parent_id) 
FROM table_A B 
INNER JOIN table_B ON table_A.foreign_id = table_B.id 
WHERE table_B.deleted = true);

无休止地加载。我知道相关的子查询可能需要很长时间，但使用相同参数的SELECT操作很快，并返回了所需的结果。我有一个小数据集，我让它运行一整天，只是为了确保它最终不会随着时间的推移而工作。

Table_A使用分层数据结构，并且只有特定级别的分层结构具有外键，我可以使用该外键来联接和检查第二个表。想法是：

1. 查找Table_A中与其相关联的Table_B行具有其"0"的所有行；删除"；值设置为true。

2. 从这组结果中获得parent_id列

3. 对于表A中id为parent_id列一部分的任何行，因此对于所有父行，请检查其is_active是否为true，如果为true，则将其设为false。

解释：

Update on table_A A  (cost=0.00..3906658758867.89 rows=89947680 width=192)  ->  Nested Loop  (cost=0.00..3906658758867.89 rows=89947680 width=192)
Join Filter: (SubPlan 1)
->  Seq Scan on table_A  (cost=0.00..37899.20 rows=410720 width=14)
->  Materialize  (cost=0.00..37901.39 rows=438 width=185)
->  Seq Scan on table_A A  (cost=0.00..37899.20 rows=438 width=185)
Filter: ((parent_id IS NULL) AND is_active)
SubPlan 1
->  Nested Loop  (cost=0.00..42405.74 rows=410720 width=8)
->  Seq Scan on table_B  (cost=0.00..399.34 rows=1 width=0)
Filter: (deleted AND (table_A.foreign_id = id))
->  Seq Scan on table_A B  (cost=0.00..37899.20 rows=410720 width=8) 
JIT:  Functions: 17 "  Options: Inlining true, Optimization true, Expressions true, Deforming true"

Answer 1

有时正确使用别名会有所不同。

比较以下两个查询计划
第一个是对样本数据运行的原始查询
cost=0.00..314144.00 rows=4975估计要在少于10行的表上更新4975行吗？

第二个是对第一个略有修改的版本
cost=92.26..122.20 rows=2

EXPLAIN
UPDATE table_A A SET is_active = false 
FROM table_A 
WHERE A.parent_id IS NULL 
AND A.is_active = true 
AND A.id = ANY (
SELECT (B.parent_id) 
FROM table_A B 
INNER JOIN table_B ON table_A.foreign_id = table_B.id 
WHERE table_B.deleted = true
);

|查询计划||：----------------------------------------------------------------------------------------------||更新table_a a(成本=0.00..314144.00行=4975宽=25)||->嵌套循环(成本=0.00..314144.00行=4975宽=25)||联接筛选器：(子计划1)||->按顺序扫描table_a(成本=0.00..29.90行=1990宽度=10)||->物化(成本=0.00..29.93行=5宽=18)||->按顺序扫描表_a a(成本=0.00..29.90行=5宽=18)||筛选器：((parent_id为NULL)AND IS_active)||子计划1||->嵌套循环(成本=0.15..57.97行=1990宽度=4)||->使用table_b_pkey对table_b进行索引扫描(成本=0.15..8.17行=1宽=0)||索引条件：(table_a.foreign_id=id)||筛选器：已删除||->按顺序扫描表_a b(成本=0.00..29.90行=1990宽度=4)|

EXPLAIN 
UPDATE table_A SET is_active = false 
WHERE parent_id IS NULL 
AND is_active = true 
AND id = ANY (
SELECT a2.parent_id
FROM table_A a2 
JOIN table_B b ON a2.foreign_id = b.id 
WHERE b.deleted = true
);

|查询计划||：----------------------------------------------------------------------------------------------||更新table_a(成本=92.26.122.20行=2宽=31)||->哈希联接(开销=92.26.122.20行=2宽=31)||哈希条件：(table_a.id=a2.pparent_id)||->按顺序扫描table_a(成本=0.00..29.90行=5宽=18)||筛选器：((parent_id为NULL)AND IS_active)||->哈希(成本=89.76.89.76行=200宽=16)||->HashAggregate(成本=87.76..89.76行=200宽=16)||组密钥：a2.parent_id||->哈希联接(开销=50.14..85.27行=995宽=16)||哈希条件：(a2.foreign_id=b.id)||->按顺序扫描表_a a2(成本=0.00..29.90行=1990宽度=14)||->哈希(成本=34.70.34.70行=1235宽度=10)||->按顺序扫描表_b b(成本=0.00..34.70行=1235宽=10)||筛选器：已删除|

第二个查询只使用几个别名。

update语句也可以写成子查询的联接。

UPDATE table_A AS parent
SET is_active = false 
FROM (
SELECT child.parent_id
FROM table_A AS child 
JOIN table_B AS dream
ON child.foreign_id = dream.id
WHERE child.parent_id IS NOT NULL
AND dream.deleted = true
GROUP BY child.parent_id
) dreamless 
WHERE parent.id = dreamless.parent_id
AND parent.parent_id IS NULL
AND parent.is_active = true;

1行受影响

<blockquote\

SELECT * FROM table_A

>

id|parent_id|is_active|foreign_id-：|--------:|：--------|--------：2|1|t|23|1|t|54|null|t|35|3|t|41|null|f|1

db<gt；小提琴这里

Answer 2

我认为这个小提琴很好地代表了你的情况。

两件不同的事情可能会让这次跑步变得非常慢。

首先，看起来您的更新查询对一个90兆行的表进行了完整的表扫描。这是大量的数据。

在表A上创建索引可能有助于加快在表A中查找符合条件的行。

CREATE INDEX "active_parentnull_id" 
ON table_A USING BTREE
("is_active", ("parent_id" IS NULL), "id");

类似地，在TABLE_B上创建索引可能会有所帮助。

CREATE INDEX "deleted_id"
ON table_B USING BTREE
("deleted", "id"); 

其次，您可能正在更新大量的行。由于事务语义的原因，对大量行的UPDATE操作可能会花费非常长的时间：RDBMS尽其所能让它在数据的其他用户看来，就像你的更新是即时发生的一样。要在许多行中实现这一点，需要大量的IO和CPU。

因此，您应该尝试批量运行更新。像这样重构查询并使用LIMIT子句。

UPDATE table_A
SET is_active = false   
WHERE id IN (
SELECT DISTINCT id
FROM table_A A
WHERE A.parent_id IS NULL
AND A.is_active = true
AND A.id = ANY (
SELECT (B.parent_id) 
FROM table_A B 
INNER JOIN table_B ON table_A.foreign_id = table_B.id 
WHERE table_B.deleted = true)
LIMIT 1000);

然后重复运行查询，直到它不更新任何行。这可能需要一段时间，但肯定会比一次完成所有事情花费更少的时间。