PostgreSQL含CTE的INSERT语句无限运行问题求助

架构老林

2026-7-2

PostgreSQL含CTE的INSERT语句无限运行及锁表问题排查

问题背景

此前正常运行的PostgreSQL INSERT语句（含CTE）自上周起开始无限运行且无报错，运行期间INCLUDED_ENTITIES表出现锁表情况。该语句核心逻辑为：从KNOWN_PRODUCTS表中，针对指定caseid筛选出AGREEMENT_PRODUCT_EXCLUSIONS表中未排除的product_type，关联AGREEMENTS表后将结果插入INCLUDED_ENTITIES表。

示例数据

-- KNOWN_PRODUCTS表
product_type    date
PT1             2024-01-07
PT2             2024-01-07
PT3             2024-01-07

-- AGREEMENT_PRODUCT_EXCLUSIONS表
product_type   caseid        date
PT1            10001         2024-01-07

-- AGREEMENTS表
entity   branch cpty_entity  cpty_branch caseid product_category date
1        1      101          1           10001  Der              2024-01-07
1        1      101          2           10002  Der              2024-01-07
1        1      101          3           10003  Der              2024-01-07

原SQL语句

with BUSINESS_DATE as (
    select date(max(DATA_DATE)) as DATA_DATE from KNOWN_PRODUCTS
),
CASEIDS as (
    select CASEID 
    from AGREEMENT_PRODUCT_EXCLUSIONS CAPE
    where date(DATA_DATE) = (select DATA_DATE from BUSINESS_DATE)
    group by CASEID 
),
CASEID_PRODUCT as (
    select C.CASEID
          ,KP.PRODUCT_TYPE
          ,rank() over (order by C.CASEID, KP.PRODUCT_TYPE) as ID
          ,case
            when KP.PRODUCT_TYPE = 'R1' then 'Rep'
            when KP.PRODUCT_TYPE = 'S1' then 'Sec'
            else 'Der'
           end as PRODUCT_CATEGORY
    from CASEIDS C cross JOIN KNOWN_PRODUCTS KP
    where date(KP.DATA_DATE) = (select DATA_DATE from BUSINESS_DATE)
      and KP.PRODUCT_TYPE != '()'
),
INCLUDED_PRODUCT_TYPE as (
    select AC.CASEID, AC.PRODUCT_TYPE, AC.PRODUCT_CATEGORY 
    from CASEID_PRODUCT AC
    left join AGREEMENT_PRODUCT_EXCLUSIONS E on AC.PRODUCT_TYPE = E.PRODUCT_TYPE
                                                 and AC.CASEID = E.CASEID
                                                 and date(E.DATA_DATE) = (select DATA_DATE from BUSINESS_DATE)
    where E.CASEID is null and E.PRODUCT_TYPE is null
),
FINAL_DATA as (
    select CA.ENTITY
         , CA.BRANCH
         , CA.CPTY_ENTITY
         , CA.CPTY_BRANCH
         , IC.PRODUCT_TYPE
         , IC.PRODUCT_CATEGORY
    from INCLUDED_PRODUCT_TYPE IC
    join AGREEMENTS CA on CA.CASEID = IC.CASEID
                      and CA.PRODUCT_CATEGORY = IC.PRODUCT_CATEGORY
    where date(CA.DATA_DATE) = (select DATA_DATE from BUSINESS_DATE)
    group by CA.ENTITY
          , CA.BRANCH
          , CA.CPTY_ENTITY
          , CA.CPTY_BRANCH
          , IC.PRODUCT_TYPE
          , IC.PRODUCT_CATEGORY
)
insert into INCLUDED_ENTITIES(ENTITY, BRANCH, CPTY_ENTITY, CPTY_BRANCH, PRODUCT_TYPE, PRODUCT_CATEGORY)
select ENTITY
     , BRANCH
     , CPTY_ENTITY
     , CPTY_BRANCH
     , PRODUCT_TYPE
     , PRODUCT_CATEGORY
from FINAL_DATA;

执行计划（Explain结果）

1.   Insert on included_entities as included_entities       
2.   Aggregate      
3.   Seq Scan on known_products as known_products       
4.   Subquery Scan      
5.   Group      
6.   CTE Scan       
7.   CTE Scan       
8.   Sort       
9.   Nested Loop Anti Join
Join Filter: ((ac.product_type = (e.product_type)::text) AND (ac.caseid = e.caseid))
10.  Hash Inner Join
Hash Cond: ((ca.caseid = ac.caseid) AND (ca.product_category = ac.product_category))
11.  Seq Scan on agreements as ca
Filter: ((data_date)::date = $2)
12.  Hash       
13.  Subquery Scan      
14.  Nested Loop Inner Join     
15.  CTE Scan       
16.  Seq Scan on known_products as kp
Filter: ((product_type <> '()'::text) AND ((data_date)::date = $3))
17.  Group      
18.  CTE Scan       
19.  Sort       
20.  Seq Scan on agreement_product_exclusions as cape
Filter: ((data_date)::date = $4)
21.  Seq Scan on agreement_product_exclusions as e
Filter: ((data_date)::date = $1)
Total rows: 1 of 1
Query complete 00:00:00.144
Ln 38, Col 30

核心问题排查

笛卡尔积导致数据量爆炸：CASEID_PRODUCT CTE中使用CASEIDS C cross JOIN KNOWN_PRODUCTS KP，若近期CASEIDS的caseid数量或KNOWN_PRODUCTS的product_type数量大幅增长，会生成海量中间数据，后续JOIN、过滤操作会因数据量过大持续运行。
嵌套循环反连接低效：执行计划第9步为Nested Loop Anti Join，且对AGREEMENT_PRODUCT_EXCLUSIONS表做全表扫描（第21步）。该表数据量变大后，嵌套循环逐行匹配会导致性能急剧下降；同时date(E.DATA_DATE)的函数转换导致无法利用DATA_DATE字段的索引，只能全表扫描。
重复子查询与索引失效：多个CTE重复调用(select DATA_DATE from BUSINESS_DATE)，且所有日期过滤都用date()函数包裹字段，导致相关字段的索引无法生效，全表扫描开销剧增。
不必要的GROUP BY操作：FINAL_DATA CTE中的GROUP BY完全多余——关联后的结果不会出现分组字段重复的记录，额外的分组操作会增加排序和聚合开销。
锁表根源：INSERT语句运行期间会持有INCLUDED_ENTITIES表的排他锁，若语句持续运行会阻塞其他操作；同时中间步骤对大表的全表扫描可能持有共享锁，进一步加剧锁竞争。

优化建议

替换笛卡尔积或分批处理：若CASEIDS与KNOWN_PRODUCTS存在隐含关联逻辑，避免直接CROSS JOIN；若确实需要全量组合，考虑分批处理以减少单批次中间数据量。
添加复合索引并优化日期过滤：
- 给AGREEMENT_PRODUCT_EXCLUSIONS表创建复合索引：CREATE INDEX idx_ape_date_caseid_pt ON AGREEMENT_PRODUCT_EXCLUSIONS(DATA_DATE, CASEID, PRODUCT_TYPE);
- 给KNOWN_PRODUCTS表创建索引：CREATE INDEX idx_kp_date_pt ON KNOWN_PRODUCTS(DATA_DATE, PRODUCT_TYPE);
- 给AGREEMENTS表创建索引：CREATE INDEX idx_ag_date_caseid_pc ON AGREEMENTS(DATA_DATE, CASEID, PRODUCT_CATEGORY);
- 将date(DATA_DATE) = 'xxx'改为DATA_DATE >= 'xxx'::date AND DATA_DATE < 'xxx'::date + interval '1 day'，避免函数转换字段，让索引生效。
简化CTE结构并移除冗余操作：预加载排除的产品类型，避免多次扫描同一张表；删除FINAL_DATA中的GROUP BY语句；将BUSINESS_DATE的结果复用，减少重复子查询。优化后的SQL示例：

WITH BUSINESS_DATE AS (
    SELECT max(DATA_DATE)::date AS DATA_DATE FROM KNOWN_PRODUCTS
),
EXCLUDED_PRODUCTS AS (
    SELECT CASEID, PRODUCT_TYPE
    FROM AGREEMENT_PRODUCT_EXCLUSIONS
    WHERE DATA_DATE >= (SELECT DATA_DATE FROM BUSINESS_DATE)
      AND DATA_DATE < (SELECT DATA_DATE FROM BUSINESS_DATE) + interval '1 day'
),
CASEID_PRODUCT AS (
    SELECT C.CASEID,
           KP.PRODUCT_TYPE,
           CASE WHEN KP.PRODUCT_TYPE = 'R1' THEN 'Rep'
                WHEN KP.PRODUCT_TYPE = 'S1' THEN 'Sec'
                ELSE 'Der' END AS PRODUCT_CATEGORY
    FROM (SELECT CASEID FROM EXCLUDED_PRODUCTS GROUP BY CASEID) C
    CROSS JOIN KNOWN_PRODUCTS KP
    WHERE KP.DATA_DATE >= (SELECT DATA_DATE FROM BUSINESS_DATE)
      AND KP.DATA_DATE < (SELECT DATA_DATE FROM BUSINESS_DATE) + interval '1 day'
      AND KP.PRODUCT_TYPE != '()'
),
INCLUDED_PRODUCT_TYPE AS (
    SELECT CP.CASEID, CP.PRODUCT_TYPE, CP.PRODUCT_CATEGORY
    FROM CASEID_PRODUCT CP
    LEFT JOIN EXCLUDED_PRODUCTS EP 
        ON CP.CASEID = EP.CASEID AND CP.PRODUCT_TYPE = EP.PRODUCT_TYPE
    WHERE EP.CASEID IS NULL
)
INSERT INTO INCLUDED_ENTITIES(ENTITY, BRANCH, CPTY_ENTITY, CPTY_BRANCH, PRODUCT_TYPE, PRODUCT_CATEGORY)
SELECT CA.ENTITY, CA.BRANCH, CA.CPTY_ENTITY, CA.CPTY_BRANCH, IP.PRODUCT_TYPE, IP.PRODUCT_CATEGORY
FROM INCLUDED_PRODUCT_TYPE IP
JOIN AGREEMENTS CA 
    ON CA.CASEID = IP.CASEID AND CA.PRODUCT_CATEGORY = IP.PRODUCT_CATEGORY
WHERE CA.DATA_DATE >= (SELECT DATA_DATE FROM BUSINESS_DATE)
  AND CA.DATA_DATE < (SELECT DATA_DATE FROM BUSINESS_DATE) + interval '1 day';

检查数据量变化：确认近期CASEIDS、KNOWN_PRODUCTS、AGREEMENT_PRODUCT_EXCLUSIONS表的数据量是否有大幅增长，这是语句突然变慢的常见诱因。

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

PostgreSQL含CTE的INSERT语句无限运行问题求助

问题背景 #

示例数据 #

原SQL语句 #

执行计划（Explain结果） #

核心问题排查 #