在Postgresql中，从另一个表更新一个大表需要很长时间(超过6个小时)

Question

我想"update"<<strong> 31列/strong>表A(~ 2200万行)表B (~ 3100万行)(.我创建了一个临时表，其中只包含我感兴趣的列(即31列)，也是为了消除第二个条件(行数也减少到~ 2200万)。(. 接下来，我为两个列创建了一个索引:"column1"临时表和表A(用于连接)之间的关系。然后我创建了一个"多列索引"在临时表的所有列上，列"column1"除外。不幸的是，更新仍然很长(我在加载6小时后停止了请求)我对指数了解不多。我所做的有什么不好的做法吗?是否存在能够大大缩短更新持续时间的解决方案?
提前感谢。
sql脚本的一部分和更新的解释计划如下:

Create temp table if not exists table_TMP as (
select
"column1",
"column2",
"column3",
all other columns...
from table_B
where one_column_of_table_B=TRUE
);
CREATE INDEX if not exists idx_table_TMP
ON table_TMP USING btree
("column1")
TABLESPACE pg_default;
//multiindex on 31 columns
CREATE INDEX if not exists idx2_table_TMP ON table_TMP (
"column2",
"column3",
all other columns...);
update table_A set
"table_A_column2"=tmp."column2",
"table_A_column3"=tmp."column3",
all other columns...
FROM table_TMP tmp
Where  table_A.column1 = tmp.column1;
//explain of the update :
"Update on table_A  (cost=5798151.63..24891890.93 rows=21716146 width=4104)"
"  ->  Hash Join  (cost=5798151.63..24891890.93 rows=21716146 width=4104)"
"        Hash Cond: ((tmp.column1)::text = (table_A.column1)::text)"
"        ->  Seq Scan on table_TMP tmp  (cost=0.00..770061.46 rows=21716146 width=2342)"
"        ->  Hash  (cost=622658.39..622658.39 rows=22008739 width=1798)"
"              ->  Seq Scan on table_A  (cost=0.00..622658.39 rows=22008739 width=1798)"

Answer 1

您应该通过编辑您的问题而不是在评论中发布新的计划，这样它们可以被正确格式化。

Buckets: 16384  Batches: 4096  Memory Usage: 842kB

您的work_mem似乎是1MB。这在任何现代系统中都很低，特别是对于这种类型的查询。由于内存较低，每个表被分成4096个批次，每次处理一个批次。在只读语句中，这应该不会太糟糕，因为所有的IO都是顺序完成的(我不知道为什么花了3个小时，虽然这看起来很长，我只能猜测你的硬件真的很糟糕)。但是对于UPDATE，这是完全毁灭性的，因为它不能更新批处理表文件，它必须更新原始表。它实际上是在要更新的表上进行4096次传递。

增加work_mem应该使选择和更新都更快，但更新可能仍然非常慢。从select中创建一个新表，然后用新表替换旧表可能是最好的选择。

如果在当前会话中使用set enable_hashjoin=off;，它将迫使计划器使用其他连接方法，这可能会很有趣，但没有太多理由认为它会更快。