Postgres python psycopg2中的慢速更新

发布于 2025-02-09 10:25:51 字数 763 浏览 1 评论 0原文

在表 ABC 中，我有60m的记录和9-10列。我在 msg_id（pk）的列上创建了索引。

我有另一个表格为 temp_abc ，其中包含1M记录，还在 msg_id（pk）的列上创建了索引。

现在，我必须更新 abc 表的2列，其中abc.msg_id = temp_abc.msg_id然后设置 abc.col_1 ='static_value_1''和 abc.col_2 ='' static_value_2'

目前需要11分钟才能更新，我想在3-5分钟内进行。是否可以？

查询：

f""" update 
       {tlt_table_name} 
 set 
     "MATCH_STATUS"='MATCH',"FINAL_STATUS"='PENDING FOR PROCESSING' 

 from 
    {temp_match_report_table} as tm 
 where 
    {tlt_table_name}."HEAD_MSG_ID" = tm."HEAD_MSG_ID"  """

解释结果：请参阅此处的解释

原文

In the table ABC, I have 60M records and 9-10 columns. I created index on a column which is msg_id(PK).

I have another table which is temp_ABC which contains 1M records, also created index on a column which is msg_id(PK).

now I have to update 2 columns of ABC table where ABC.msg_id = temp_ABC.msg_id then set ABC.col_1='STATIC_VALUE_1' and ABC.col_2='STATIC_VALUE_2'

Currently it is taking 11 minutes to update and I wanted to do it in 3-5 minutes. is it possible ?

query :

f""" update 
       {tlt_table_name} 
 set 
     "MATCH_STATUS"='MATCH',"FINAL_STATUS"='PENDING FOR PROCESSING' 

 from 
    {temp_match_report_table} as tm 
 where 
    {tlt_table_name}."HEAD_MSG_ID" = tm."HEAD_MSG_ID"  """

Explain result : see explain result from here

分享到QQ

分享到微博