sql - 在postgresql中将数据从一个表移动到另一个表

Question

我的数据库中有 2 个表。他们都有超过 16m 的记录，他们有相同的关系 uuid(我有两个 uuid 字段的索引)。其中一个大约 166GB，另一个大约 50GB。我会更改我的问题的表名，但我希望你能回答这个问题。

假设我的第一个表称为用户，第二个表是配置文件。现在我的用户表上有一个字段，我想将它复制到我的配置文件表中。

我昨晚做了一些事情，但它仍在处理中，已经超过 10 个小时了。

我现在有 3 个问题。第一个问题；我的查询可以吗？

ALTER TABLE profiles ADD COLUMN start_stamp TIMESTAMP DEFAULT NOW();
SET start_stamp = (SELECT start_stamp::DATE FROM users WHERE uuid = profiles.uuid);
CREATE INDEX start_stamp ON profiles;

第二个问题；这两个查询之间有什么区别吗？如果是，有什么区别，哪个更好？

UPDATE profiles 
SET start_stamp = (SELECT start_stamp::DATE FROM users WHERE uuid = profiles.uuid);

QUERY PLAN
--------------------------------------------------------------------------
Update on profiles  (cost=0.00..159956638.61 rows=18491638 width=116)
->  Seq Scan on profiles  (cost=0.00..159956638.61 rows=18491638 width=116)
     SubPlan 1
       ->  Index Scan using unique_user_uuid on users  (cost=0.56..8.58 rows=1 width=20)
             Index Cond: ((uuid)::text = (profiles.uuid)::text)




UPDATE profile
SET start_stamp = users.start_stamp
FROM users
WHERE profiles.start_stamp = users.start_stamp;

QUERY PLAN
--------------------------------------------------------------------------
Update on profiles  (cost=2766854.25..5282948.42 rows=11913522 width=142)
->  Hash Join  (cost=2766854.25..5282948.42 rows=11913522 width=142)
     Hash Cond: ((profiles.uuid)::text = (users.uuid)::text)
     ->  Seq Scan on profiles  (cost=0.00..1205927.56 rows=18491656 width=116)
     ->  Hash  (cost=2489957.22..2489957.22 rows=11913522 width=63)
           ->  Seq Scan on users  (cost=0.00..2489957.22 rows=11913522 width=63)

我的最后一个问题是；有没有更好的方法将值从表复制到另一个记录超过 16m 和 200gb 的值？

谢谢。

Answer 1

更新/复制大量数据的最快方法是 CTAS(创建表作为选择)。只有在您有权这样做并且您可以更改名称或删除原始表时才有可能。

在你的情况下它会是这样的:

create table tmp_profiles as
select p.* , us.strat_stamp:date
 from profiles p
 left join users u on p.uuid = us.uuid;

drop table profiles;

alter table tmp_profiles, rename to profiles;

之后，您必须重新创建键、索引和其他约束。

如果您更新表中超过 5% 的记录，那么 CTAS 至少会比常规更新快几倍。低于该阈值更新可能比 CTAS 更快。