使用CREATE TABLE方式的PostgreSQL更新查询耗时过长问题

问题描述 #

有一个通过CREATE TABLE方式更新表的PostgreSQL查询，预期15分钟完成，但实际运行已超过1小时。调整temp_buffers从1GB至16GB后无明显改善。监控显示执行期间CPU使用率持续95-100%，内存使用率约70-80%。

服务器规格 #

PostgreSQL版本: 15
核心数: 4
内存: 32 GB
磁盘空间: 250 GB（剩余50%）
操作系统: Linux Ubuntu 22.04

PostgreSQL配置 #

shared_buffers: 16GB
work_mem: 64MB
maintenance_work_mem: 2GB
effective_cache_size: 16GB
max_wal_size: 1GB

（包含完整pg_settings详细配置）

涉及表信息 #

执行的SQL代码 #

BEGIN;
ALTER TABLE source_switchdata_tmp_details RENAME TO source_switchdata_tmp_details_og;
CREATE TABLE source_switchdata_tmp_details AS
SELECT DISTINCT ON (A.uniqueid) A.transactiondate,
        A.cycles,
        A.payout,
        A.transactionamount,
        A.bcid,
        A.bcname,
        A.username,
        A.terminalid,
        A.uidauthcode,
        A.itc,
        A.transactiondetails,
        A.deststan,
        A.sourcestan,
        A.hostresponsecode,
        A.institutionid,
        A.acquirer,
        A.bcrefid,
        A.cardno,
        A.rrn,
        A.transactiontype,
        A.filename,
        A.cardnotrim,
        A.uniqueid,
        A.transactiondatetime,
        A.transactionstatus,
        A.commission,
        A.overall_probable_status,
        A.details_id,
        A.recon_status_3,
        A.probable_recon_status_1_to_2,
        A.probable_recon_status_1_to_3,
        A.probable_recon_status_3,
        A.recon_created_date,
        A.is_adjustment,
        A.adjustment_type,
        A.priority_no,
        A.is_payload_created,
        A.recon_key_priority_1_1_to_2,
        A.recon_key_priority_1_1_to_3,
        A.recon_key_priority_2_1_to_2,
        A.recon_key_priority_2_1_to_3,
        A.process_status,
        A.reconciliation_date_time,
        CURRENT_TIMESTAMP AS recon_updated_date,
        CASE
                WHEN C.recon_key_priority_1_2_to_1 IS NOT NULL THEN 'Reconciled'
                ELSE 'Not Reconciled'
        END AS recon_status_1_to_2,
        CASE
                WHEN D.recon_key_priority_1_3_to_1 IS NOT NULL THEN 'Reconciled'
                WHEN D.recon_key_priority_2_3_to_1 IS NOT NULL THEN 'Reconciled'
                ELSE 'Not Reconciled'
        END AS recon_status_1_to_3,
        CASE
                WHEN (C.recon_key_priority_1_2_to_1 IS NOT NULL AND D.recon_key_priority_1_3_to_1 IS NOT NULL) THEN 'Reconciled'
                WHEN (D.recon_key_priority_2_3_to_1 IS NOT NULL) THEN 'Reconciled'
                ELSE 'Not Reconciled'
        END AS overall_recon_status
FROM source_switchdata_tmp_details_og A
        LEFT JOIN source_aepscbsdata_tmp_details C ON (A.recon_key_priority_1_1_to_2 = C.recon_key_priority_1_2_to_1)
        LEFT JOIN source_npcidata_tmp_details D 
        ON (A.recon_key_priority_1_1_to_3 = D.recon_key_priority_1_3_to_1) 
        OR (A.recon_key_priority_2_1_to_3 = D.recon_key_priority_2_3_to_1);
DROP TABLE source_switchdata_tmp_details_og;
ALTER TABLE source_switchdata_tmp_details
ADD CONSTRAINT source_switchdata_tmp_details_pkey PRIMARY KEY (uniqueid);
COMMIT;

关键观察 #

• 关联列recon_key_priority上无索引
• 移除其中一个关联后，查询可在10分钟内完成

执行计划 #

Unique  (cost=6563725.85..4473887467620.09 rows=12066620 width=2259)
  ->  Nested Loop Left Join  (cost=6563725.85..4473887437453.54 rows=12066620 width=2259)
        Join Filter: (((a.recon_key_priority_1_1_to_3)::text = (d.recon_key_priority_1_3_to_1)::text) OR ((a.recon_key_priority_2_1_to_3)::text = (d.recon_key_priority_2_3_to_1)::text))
        ->  Gather Merge  (cost=6563725.85..7969082.59 rows=12066620 width=2188)
              Workers Planned: 2
              ->  Sort  (cost=6562725.82..6575295.22 rows=5027758 width=2188)
                    Sort Key: a.uniqueid
                    ->  Parallel Hash Left Join  (cost=302203.79..1242931.06 rows=5027758 width=2188)
                          Hash Cond: ((a.recon_key_priority_1_1_to_2)::text = (c.recon_key_priority_1_2_to_1)::text)
                          ->  Parallel Seq Scan on source_switchdata_tmp_details a  (cost=0.00..915793.58 rows=5027758 width=2155)
                          ->  Parallel Hash  (cost=283965.02..283965.02 rows=1459102 width=33)
                                ->  Parallel Seq Scan on source_aepscbsdata_tmp_details c  (cost=0.00..283965.02 rows=1459102 width=33)
        ->  Materialize  (cost=0.00..1184989.72 rows=12689248 width=66)
              ->  Seq Scan on source_npcidata_tmp_details d  (cost=0.00..972840.48 rows=12689248 width=66)
JIT:
  Functions: 18
  Options: Inlining true, Optimization true, Expressions true, Deforming true

额外排查信息 #

调整PostgreSQL配置无效，发现Block I/O命中率极低（部分时段不足1%），出现进程等待队列发送的异常现象。

预先在recon_key_priority列上创建索引后，该CREATE TABLE更新查询仅耗时4分钟。

内容的提问来源于stack exchange，提问作者Purushottam Nawale