postgre提高UPDATE WHERE sql查询的性能

Question

我有一个非常简单的查询

UPDATE TableA
SET date_type = TableB.date_type
FROM TableB
WHERE TableB.int_type = TableA.int_type

我的指数是：TableA(int_type)、TableB(int_type, date_type)

EXPLAIN结果：

Update on TableA  (cost=2788789.320..34222368.900 rows=82594592 width=261)
  ->  Hash Join  (cost=2788789.320..34222368.900 rows=82594592 width=261)
          Hash Cond: (TableA.int_type = TableB.int_type)
        ->  Seq Scan on tableA  (cost=0.000..12610586.960 rows=101433296 width=247)
        ->  Hash  (cost=1272403.920..1272403.920 rows=82594592 width=18)
              ->  Seq Scan on TableB  (cost=0.000..1272403.920 rows=82594592 width=18)

查询已进行了3个多小时。

怎样才能让它跑得更快？正如我从EXPLAIN结果中看到的，没有使用索引。我是否应该选择其他索引/进行任何其他改进以使查询运行得更快？

Postgresql 9.6

Answer 1

您可以做的是避免幂等更新：

---

UPDATE TableA a
SET date_type = b.date_type
FROM TableB b
WHERE b.int_type = a.int_type
AND a.date_type IS DISTINCT FROM b.date_type  -- <<-- avoid updates with the same value
        ;

---

也许你假设a和B之间存在1对1的关系，但DBMS没有。您可以将更新限制为每个目标行最多一个源行：

---

EXPLAIN
UPDATE TableA a
SET date_type = b.date_type
FROM ( SELECT int_type, date_type
        , row_number() OVER(PARTITION BY int_type) AS rn
        FROM TableB
        ) b
WHERE b.int_type = a.int_type
AND a.date_type IS DISTINCT FROM b.date_type -- <<-- avoid idempotent updates
AND b.rn=1 -- <<-- allow only one update per target row.
        ;

Answer 2

对于此查询：

UPDATE TableA
SET date_type = TableB.date_type
FROM TableB
WHERE TableB.int_type = TableA.int_type

您可以在TableB(int_type, date_type)上尝试索引。