大数据量Insert查询执行超时异常及优化方案咨询

先看你的原代码，首先能发现几个可以快速优化的点，再针对大数据量场景给出系统性的优化建议：

1. 简化版本号获取逻辑，避免重复聚合查询 #

原代码里两次查询max(versionnumber)，这在表数据量大的时候，每次聚合都会扫描全表，完全是不必要的开销。可以用COALESCE一次搞定：

DO $$ 
DECLARE 
    maxversionnumber integer; 
BEGIN 
    -- 一次查询获取版本号，空则设为1，否则+1
    SELECT COALESCE(max(versionnumber), 0) + 1 INTO maxversionnumber 
    FROM reissue.holdingsclose;

    -- 后续的INSERT和DELETE逻辑
    INSERT INTO reissue.holdingsclose (feedid, filename, asofdate, returntypeid, currencyid, securityid, indexid, indexname, securitycode, sizemarkercode, icbclassificationkey, rgsclassificationkey, securitycountry, securitycurrency, pricecurrency, sizemarkerkey, startmarketcap, closemarketcap, marketcapbeforeinvestability, marketcapafterinvestability, adjustmentfactor, marketcapafteradjustmentfactor, grossmarketcap, netmarketcap, xddividendmarketcap, xddividendnetmarketcap, openprice, closeprice, shares, sharechg, investabilityweight, dividendyield, pctindexweight, growthfactor, valuefactor, marketcapgrowthfactor, marketcapvaluefactor, dailypriceperformance, dailytotalperformance, dailynetperformance, insertiontime, classificationnareitkey, classificationnomurakey, growthshares, valueshares, smid, meena, eurozone150flag, euro40, paneurope60, top100flag, top200flag, r2500, developedemerging, europe, europeexuk, frontier, g123, gccflag, gicsindustry, methodologycode, createdate, createuserid, modifieduser, modifieddate, reissueflag, productcode,versionnumber)
    SELECT feedid, filename, asofdate, returntypeid, currencyid, securityid, indexid, indexname, securitycode, sizemarkercode, icbclassificationkey, rgsclassificationkey, securitycountry, securitycurrency, pricecurrency, sizemarkerkey, startmarketcap, closemarketcap, marketcapbeforeinvestability, marketcapafterinvestability, adjustmentfactor, marketcapafteradjustmentfactor, grossmarketcap, netmarketcap, xddividendmarketcap, xddividendnetmarketcap, openprice, closeprice, shares, sharechg, investabilityweight, dividendyield, pctindexweight, growthfactor, valuefactor, marketcapgrowthfactor, marketcapvaluefactor, dailypriceperformance, dailytotalperformance, dailynetperformance, insertiontime, classificationnareitkey, classificationnomurakey, growthshares, valueshares, smid, meena, eurozone150flag, euro40, paneurope60, top100flag, top200flag, r2500, developedemerging, europe, europeexuk, frontier, g123, gccflag, gicsindustry, methodologycode, createdate, createuserid, modifieduser, modifieddate, reissueflag, productcode ,maxversionnumber as versionnumber 
    FROM fct.holdingsclose WHERE feedid = '"+ feedId +"';

    DELETE FROM fct.holdingsclose WHERE feedid = '"+ feedId +"';
END $$;

这一步能减少一次全表扫描的开销，尤其是当reissue.holdingsclose数据量很大时，效果明显。

2. 替换版本号的聚合查询为序列（性能飞跃优化） #

如果versionnumber是按批次递增的（每个插入批次共用一个版本号），完全可以用PostgreSQL的序列来替代max()查询。序列是原子操作，不需要扫描全表，性能提升非常显著：

步骤1：创建序列 #

CREATE SEQUENCE reissue.holdingsclose_version_seq;

步骤2：对齐序列初始值（可选） #

如果需要序列和现有表的版本号对齐，执行：

SELECT setval('reissue.holdingsclose_version_seq', (SELECT COALESCE(max(versionnumber), 0) FROM reissue.holdingsclose));

步骤3：修改版本号获取逻辑 #

DO $$ 
DECLARE 
    maxversionnumber integer; 
BEGIN 
    -- 直接获取序列的下一个值，比查max快N倍
    SELECT nextval('reissue.holdingsclose_version_seq') INTO maxversionnumber;

    INSERT INTO reissue.holdingsclose (feedid, filename, asofdate, returntypeid, currencyid, securityid, indexid, indexname, securitycode, sizemarkercode, icbclassificationkey, rgsclassificationkey, securitycountry, securitycurrency, pricecurrency, sizemarkerkey, startmarketcap, closemarketcap, marketcapbeforeinvestability, marketcapafterinvestability, adjustmentfactor, marketcapafteradjustmentfactor, grossmarketcap, netmarketcap, xddividendmarketcap, xddividendnetmarketcap, openprice, closeprice, shares, sharechg, investabilityweight, dividendyield, pctindexweight, growthfactor, valuefactor, marketcapgrowthfactor, marketcapvaluefactor, dailypriceperformance, dailytotalperformance, dailynetperformance, insertiontime, classificationnareitkey, classificationnomurakey, growthshares, valueshares, smid, meena, eurozone150flag, euro40, paneurope60, top100flag, top200flag, r2500, developedemerging, europe, europeexuk, frontier, g123, gccflag, gicsindustry, methodologycode, createdate, createuserid, modifieduser, modifieddate, reissueflag, productcode,versionnumber)
    SELECT feedid, filename, asofdate, returntypeid, currencyid, securityid, indexid, indexname, securitycode, sizemarkercode, icbclassificationkey, rgsclassificationkey, securitycountry, securitycurrency, pricecurrency, sizemarkerkey, startmarketcap, closemarketcap, marketcapbeforeinvestability, marketcapafterinvestability, adjustmentfactor, marketcapafteradjustmentfactor, grossmarketcap, netmarketcap, xddividendmarketcap, xddividendnetmarketcap, openprice, closeprice, shares, sharechg, investabilityweight, dividendyield, pctindexweight, growthfactor, valuefactor, marketcapgrowthfactor, marketcapvaluefactor, dailypriceperformance, dailytotalperformance, dailynetperformance, insertiontime, classificationnareitkey, classificationnomurakey, growthshares, valueshares, smid, meena, eurozone150flag, euro40, paneurope60, top100flag, top200flag, r2500, developedemerging, europe, europeexuk, frontier, g123, gccflag, gicsindustry, methodologycode, createdate, createuserid, modifieduser, modifieddate, reissueflag, productcode ,maxversionnumber as versionnumber 
    FROM fct.holdingsclose WHERE feedid = '"+ feedId +"';

    DELETE FROM fct.holdingsclose WHERE feedid = '"+ feedId +"';
END $$;

3. 优化大数据量INSERT的核心建议 #

3.1 临时禁用非必要索引 #

插入大量数据时，每个索引都需要实时更新，会严重拖慢速度。如果业务允许（比如插入期间没有查询需求），可以先禁用reissue.holdingsclose上的非主键/唯一索引，插入完成后再重建：

-- 禁用索引（替换为你的实际索引名）
ALTER INDEX idx_holdingsclose_asofdate DISABLE;
ALTER INDEX idx_holdingsclose_securityid DISABLE;

-- 执行INSERT操作...

-- 重建索引（比插入时实时更新快数倍）
ALTER INDEX idx_holdingsclose_asofdate REBUILD;
ALTER INDEX idx_holdingsclose_securityid REBUILD;

3.2 调整PostgreSQL会话级配置 #

针对大数据量操作，临时调整以下参数（无需重启数据库，仅对当前会话生效）：

-- 增加排序/哈希操作的内存，避免磁盘临时表
SET work_mem = '64MB'; -- 服务器内存32G以上可设为256MB
-- 增加维护操作的内存（比如重建索引）
SET maintenance_work_mem = '256MB';
-- 关闭自动提交，减少事务开销（你的PL/pgSQL块本身是一个事务，此步可选）
SET autocommit = off;

3.3 拆分大事务（极端大数据量场景） #

如果单次插入的数据量超过几百万行，单个大事务会导致WAL日志暴涨、锁持有时间过长。可以把数据按批次插入，同时保证每个批次共用同一个版本号：

DO $$ 
DECLARE 
    maxversionnumber integer;
    batch_size integer := 100000; -- 每批次插入10万行，可根据服务器性能调整
    total_rows integer;
    processed_rows integer := 0;
BEGIN 
    SELECT nextval('reissue.holdingsclose_version_seq') INTO maxversionnumber;

    -- 获取当前feedid对应的总行数
    SELECT COUNT(*) INTO total_rows FROM fct.holdingsclose WHERE feedid = '"+ feedId +"';

    WHILE processed_rows < total_rows LOOP
        INSERT INTO reissue.holdingsclose (feedid, filename, asofdate, returntypeid, currencyid, securityid, indexid, indexname, securitycode, sizemarkercode, icbclassificationkey, rgsclassificationkey, securitycountry, securitycurrency, pricecurrency, sizemarkerkey, startmarketcap, closemarketcap, marketcapbeforeinvestability, marketcapafterinvestability, adjustmentfactor, marketcapafteradjustmentfactor, grossmarketcap, netmarketcap, xddividendmarketcap, xddividendnetmarketcap, openprice, closeprice, shares, sharechg, investabilityweight, dividendyield, pctindexweight, growthfactor, valuefactor, marketcapgrowthfactor, marketcapvaluefactor, dailypriceperformance, dailytotalperformance, dailynetperformance, insertiontime, classificationnareitkey, classificationnomurakey, growthshares, valueshares, smid, meena, eurozone150flag, euro40, paneurope60, top100flag, top200flag, r2500, developedemerging, europe, europeexuk, frontier, g123, gccflag, gicsindustry, methodologycode, createdate, createuserid, modifieduser, modifieddate, reissueflag, productcode,versionnumber)
        SELECT feedid, filename, asofdate, returntypeid, currencyid, securityid, indexid, indexname, securitycode, sizemarkercode, icbclassificationkey, rgsclassificationkey, securitycountry, securitycurrency, pricecurrency, sizemarkerkey, startmarketcap, closemarketcap, marketcapbeforeinvestability, marketcapafterinvestability, adjustmentfactor, marketcapafteradjustmentfactor, grossmarketcap, netmarketcap, xddividendmarketcap, xddividendnetmarketcap, openprice, closeprice, shares, sharechg, investabilityweight, dividendyield, pctindexweight, growthfactor, valuefactor, marketcapgrowthfactor, marketcapvaluefactor, dailypriceperformance, dailytotalperformance, dailynetperformance, insertiontime, classificationnareitkey, classificationnomurakey, growthshares, valueshares, smid, meena, eurozone150flag, euro40, paneurope60, top100flag, top200flag, r2500, developedemerging, europe, europeexuk, frontier, g123, gccflag, gicsindustry, methodologycode, createdate, createuserid, modifieduser, modifieddate, reissueflag, productcode ,maxversionnumber as versionnumber 
        FROM fct.holdingsclose 
        WHERE feedid = '"+ feedId +"'
        ORDER BY securityid -- 按固定字段排序，保证分批无重复数据
        LIMIT batch_size OFFSET processed_rows;

        processed_rows := processed_rows + batch_size;
        COMMIT; -- 每批次提交一次，减少事务内存占用
    END LOOP;

    -- 最后删除fct表的对应数据
    DELETE FROM fct.holdingsclose WHERE feedid = '"+ feedId +"';
END $$;

3.4 优化DELETE操作 #

如果fct.holdingsclose中feedid对应的数据量极大，DELETE操作会很慢。可以考虑：

• 把fct.holdingsclose设计成分区表，按feedid或asofdate分区，这样删除整个分区只需要DROP PARTITION，速度极快。
• 如果该feedid的数据占表的绝大多数，可先将其他feedid的数据迁移到临时表，TRUNCATE fct.holdingsclose后再迁回保留数据（此方案需评估业务影响）。

4. 其他小细节优化 #

• 确保fct.holdingsclose的feedid字段有索引，这样WHERE feedid = ...的过滤查询会更快。
• 如果reissue.holdingsclose是非核心业务表（允许crash后丢失数据），可以将其设为UNLOGGED表，插入速度会比普通表快很多（因为不写入WAL日志）。

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