postgresql - PostgreSQL 更新慢

Question

我的更新查询需要几天时间才能运行，并且在生产中似乎呈指数增长；我希望它能跑得更快。首先，我的查询如下所示：

UPDATE table_1 a
SET boolean_column = TRUE
WHERE a.boolean_column = FALSE
AND EXISTS(SELECT b.joining_key
         FROM table_2 b
         WHERE a.primary_key = b.joining_key
           AND b.category = 'abc');

执行计划：

Update on table_1 a (cost=0.00..1137712.90 rows=1 width=136)
-> Nested Loop Semi Join (cost=0.00..1137712.90 rows=1 width=136)
Join Filter: (a.primary_key = (b.joining_key)::text)
-> Seq Scan on table_1 a (cost=0.00..36152.13 rows=1 width=129)
Filter: (NOT boolean_column)
-> Seq Scan on table_2 b (cost=0.00..1091459.31 rows=808117 width=33)
Filter: ((category)::text = 'abc'::text)

table_1有约table_245 万行，约 450 万行，其中约 80 万行是category = 'abc'

奇怪的是，当我对不同的类别运行相同的查询时，只需要大约 15 分钟：

UPDATE table_1 a
SET boolean_column = TRUE
WHERE a.boolean_column = FALSE
AND EXISTS(SELECT b.joining_key
         FROM table_2 b
         WHERE a.primary_key = b.joining_key
           AND b.category = 'bcd');

执行计划：

Update on table_1 a (cost=0.00..1154676.35 rows=1 width=136)
-> Nested Loop Semi Join (cost=0.00..1154676.35 rows=1 width=136)
Join Filter: (a.primary_key = (b.joining_key)::text)
-> Seq Scan on table_1 a (cost=0.00..36152.13 rows=1 width=129)
Filter: (NOT boolean_column)
-> Seq Scan on table_2 b (cost=0.00..1091533.00 rows=2159298 width=33)
Filter: ((category)::text = 'bcd'::text)

在这种情况下，table_2有大约 200 万行category = 'bcd'

我不知道它是否相关，但我查看了一些统计数据table_2并看到大量死元组：

SELECT schemaname, relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_all_tables
WHERE relname = 'table_2'
ORDER BY n_dead_tup
             / (n_live_tup
                    * current_setting('autovacuum_vacuum_scale_factor')::float8
        + current_setting('autovacuum_vacuum_threshold')::float8)
        DESC;

/* returns
schemaname  relname  n_live_tup     n_dead_tup  last_autovacuum
public      table_2  4363942        549768      2021-11-09 03:15:53.936604+00:00
*/

在这种情况下跑步会有vacuum帮助吗？尽管我对此表示怀疑，因为其他类别似乎运行良好。我可以提供更多信息 - 谢谢。

编辑 1：table_1只有一个约束，table_1.primary_key而table_2只有一个约束，即table_2.primary_key. 目前，table_2.joining_key没有与table_1.primary_key. 添加该约束是否有帮助？

标签： postgresqloptimizationsql-updatesql-optimization