postgreOptimize这个简单的SQL查询?

Question

我有一个使用dblink从一个数据库回填数据并将其插入另一个数据库中的jsonb列的查询。它只需要在1000条左右的记录上运行，但是运行时间很长(在30到一分钟之间)。

为什么?我如何使它更快?

下面是查询:

CREATE EXTENSION dblink;
update "table" t1 set jsonb_example_column = jsonb_set(jsonb_example_column, '{user}', jsonb_build_object('name',jsonb_example_column->'user'->'name','phone',jsonb_example_column->'user'->'phone','address',jsonb_example_column->'user'->'address','user_id', other_db.user_id)) 
FROM dblink('dbname=$DB_NAME
port=$DB_PORT
host=$DB_HOST
user=$DB_USER
password=$DB_PASSWORD',
'SELECT user_id, external_id from other_db_table t2')
AS other_db("user_id" uuid, external_id uuid) WHERE other_db.external_id = t1.external_id;"

Answer 1

答案要感谢a_horse_with_no_name，但这里是优化后的查询，现在花费了三分之一的时间:

CREATE EXTENSION IF NOT EXISTS postgres_fdw;
CREATE SERVER user FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '0.0.0.0', port '5432', dbname 'user');
CREATE USER MAPPING FOR postgres SERVER user OPTIONS (user 'your_foreign_db_user', password 'your_foreign_db_password');
GRANT USAGE ON FOREIGN SERVER user TO postgres;
CREATE FOREIGN TABLE IF NOT EXISTS foreign_table(user_id uuid, external_id uuid) SERVER user OPTIONS (schema_name 'public', table_name 'foreign_db_table');
UPDATE "local_db_table" lt SET jsonb_example_column = jsonb_set(jsonb_example_column, '{user}', jsonb_build_object('name',jsonb_example_column->'user'->'name','phone',jsonb_example_column->'user'->'phone','address',jsonb_example_column->'user'->'address','user_id', ft.user_id))
FROM foreign_table AS ft WHERE lt.external_id = ft.external_id;