sql - 如何编写高效的UPDATE-SELECT sql

Question

我为一个大约有 50.000.000 个用户的表编写了一个 sql。查询花费的时间比我预期的要多，大约 23 小时没有完成。

UPDATE users
    SET building_id = B.id
    FROM (
      SELECT *
      FROM buildings B
    ) AS B
    WHERE B.city          = address_city
      AND B.town          = address_town
      AND B.neighbourhood = address_neighbourhood
      AND B.street        = address_street
      AND B.no            = address_building_no

此 sql 的想法是从用户中删除建筑物/地址信息，而不是将其引用到建筑物表中。

解释

Update on users  (cost=22226900.43..22548054.14 rows=15212 width=166) 
->  Merge Join  (cost=22226900.43..22548054.14 rows=15212 width=166)
         Merge Cond: (((users.address_city)::text = (b.city)::text) AND ((users.address_town)::text = (b.town)::text) AND ((users.address_neighbourhood)::text = (b.neighbourhood)::text) AND ((users.address_street)::text = (b.street)::text) AND ((users.address_building_no)::text = (b.no)::text))
         ->  Sort  (cost=21352886.76..21401078.96 rows=96384398 width=156)
               Sort Key: users.address_city, users.address_town, users.address_neighbourhood, users.address_street, users.address_building_no
               ->  Seq Scan on users  (cost=0.00..2559921.19 rows=96384398 width=156)
         ->  Materialize  (cost=874013.68..883606.86 rows=9593179 width=63)
               ->  Sort  (cost=874013.68..878810.27 rows=9593179 width=63)
                     Sort Key: b.city, b.town, b.neighbourhood, b.street, b.no
                     ->  Seq Scan on buildings b  (cost=0.00..136253.54 rows=9593179 width=63) (10 rows)

我不知道这个sql是为每个用户使用内部SELECT sql还是为事务缓存。另外，如果它缓存，它是否使用缓存临时表的索引？

我不能这样写sql:

FROM (
  SELECT * 
  FROM buildings B
  WHERE B.city          = users.address_city
    AND B.town          = users.address_town
    AND B.neighbourhood = users.address_neighbourhood
    AND B.street        = users.address_street
    AND B.no            = users.address_building_no
  )

它表示无法从内部选择访问 users。您对如何在内部 sql 语句中访问建筑物有什么建议吗。

Answer 1

我想

create table t as select column_list from a join b on column=column;
alter table t rename to users;

会更快，并且只会产生微秒级锁定......当然，如果一个表目前不可编辑，并且 temp_tablespace 中有足够的空间