性能 – Postgresql – 如何加快更新大表(1亿行)？

Table "public.tx_input1_new" (100,000,000 rows) 

     Column     |            Type             | Modifiers
----------------|-----------------------------|----------
 blk_hash       | character varying(500)      |
 blk_time       | timestamp without time zone |
 tx_hash        | character varying(500)      |
 input_tx_hash  | character varying(100)      |
 input_tx_index | smallint                    |
 input_addr     | character varying(500)      |
 input_val      | numeric                     |

Indexes:
    "tx_input1_new_h" btree (input_tx_hash, input_tx_index)

Table "public.tx_output1_new" (100,000,000 rows)

    Column    |          Type          | Modifiers
--------------+------------------------+-----------
 tx_hash      | character varying(100) |
 output_addr  | character varying(500) |
 output_index | smallint               |
 input_val    | numeric                |

Indexes:
    "tx_output1_new_h" btree (tx_hash, output_index)

我想用另一个表更新table1：

UPDATE tx_input1 as i
SET 
  input_addr = o.output_addr,
  input_val = o.output_val
FROM tx_output1 as o
WHERE 
  i.input_tx_hash = o.tx_hash
  AND i.input_tx_index = o.output_index;

在执行此SQL命令之前,我已经为这两个表创建了索引：

CREATE INDEX tx_input1_new_h ON tx_input1_new (input_tx_hash, input_tx_index);

CREATE INDEX tx_output1_new_h ON tx_output1_new (tx_hash, output_index);

我使用EXPLAIN命令查看查询计划,但它没有使用我创建的索引.

完成此更新大约需要14-15个小时.

它内部的问题是什么？

如何缩短执行时间或调整数据库/表？

谢谢.

首先,将对其中一个表进行顺序扫描,从中构建哈希结构,然后对另一个表进行顺序扫描,并针对找到的每一行探测哈希值.怎么可能有任何索引帮助？

您可以预期此类操作需要很长时间,但有一些方法可以加快操作：

>在开始之前删除tx_input1上的所有索引和约束.您的查询是索引根本没有帮助但实际上会损害性能的示例之一,因为索引必须与表一起更新.完成UPDATE后重新创建索引和约束.根据表中索引的数量,您可以获得相当大的性能增益.
>使用SET命令尽可能高地增加此操作的work_mem参数.散列操作可以使用的内存越多,它就越快.有了一个很大的表,你可能仍然会有临时文件,但你仍然可以期待一个不错的性能提升.
>将checkpoint_segments(或从版本9.6开始的max_wal_size)增加到一个较高的值,以便在UPDATE操作期间检查点更少.
>确保两个表的表统计信息准确无误,以便PostgreSQL可以对要创建的哈希桶的数量做出很好的估计.

在UPDATE之后,如果它影响大量行,您可以考虑在tx_input1上运行VACUUM(FULL)以消除由此产生的表膨胀.这将锁定表格较长时间,因此在维护窗口期间执行此操作.它将减小表的大小,从而加快顺序扫描.