执行大量DELETE的PL/pgSQL循环语句完成后迟迟无法退出

我编写了一个PL/pgSQL函数，通过循环从130多张表中删除指定行。问题在于DELETE语句本身能快速完成，但循环却需要很久才能退出。

以下是该函数代码：

CREATE OR REPLACE FUNCTION myschema.delete_dependents(p_id smallint)
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE
    v_query_delete          TEXT;
    i                       record;
BEGIN

DROP TABLE IF EXISTS tmp_dependents_table;
CREATE TEMP TABLE tmp_dependents_table (
    schema_name     varchar(255),
    table_oid       oid,
    table_name      varchar(255),
    level           smallint    
);

INSERT INTO tmp_dependents_table
SELECT DISTINCT
    ot.schema_name
    ,ot.table_oid
    ,ot.table_name
    ,ot.level
FROM related_tables_recursive(current_schema()) AS ot
INNER JOIN pg_attribute AS pga
ON pga.attrelid = ot.table_oid
WHERE   pga.attname = 'col_id'
    AND ot.level > 1
ORDER BY ot.level DESC NULLS LAST;

FOR i IN 
    SELECT * FROM tmp_dependents_table
LOOP
            
    v_query_delete := '';
    
    v_query_delete := 'DELETE FROM ' || i.schema_name || '.' || i.table_name
        || ' WHERE col_id = ' || p_id::TEXT || ';';
    
    RAISE NOTICE 'EXECUTING --> %', v_query_delete;
    EXECUTE v_query_delete;
    RAISE NOTICE 'EXECUTED DELETE.';
    
END LOOP;
    
END;
$function$
;

单独执行填充临时表的SELECT语句能得到所有含col_id列的表数据；日志显示所有DELETE语句都已快速执行完毕，最后一条EXECUTED DELETE对应临时表的最后一条记录。
我已删除其中一张表（测试数据有22k条记录）上的DELETE触发器，排查后发现当前schema仅存在SELECT规则。请问还有哪些自动触发的因素会导致代码耗时远超预期？

可能的触发因素及排查方向 #

• 约束检查开销：即使没有触发器，外键约束的存在会导致DELETE时自动检查关联表的引用关系，尤其是级联操作（如果外键定义了ON DELETE CASCADE等行为），或者是延迟约束的检查（DEFERRABLE约束会在事务提交时统一检查，大量此类约束的检查累积会消耗时间）。可以通过以下查询查看相关表的外键约束：

  SELECT conname, conrelid::regclass, confrelid::regclass, confupdtype, confdeltype
  FROM pg_constraint
  WHERE conrelid IN (SELECT table_oid FROM tmp_dependents_table)
    AND contype = 'f';
  

• 自动VACUUM触发：如果某个表删除大量行后，可能触发PostgreSQL的自动VACUUM（当表的死元组比例达到阈值时），后台的VACUUM操作会占用系统资源，导致整体耗时增加。可以通过以下查询查看表的死元组及自动VACUUM情况：

  SELECT relname, n_dead_tup, last_autovacuum
  FROM pg_stat_user_tables
  WHERE relname IN (SELECT table_name FROM tmp_dependents_table);
  

• 统计信息自动更新：DELETE操作后，PostgreSQL可能触发统计信息自动更新（由autovacuum_analyze_threshold参数控制），大量表的统计信息更新会集中消耗资源，拖慢后续流程。
• 临时表与事务收尾开销：函数结束时需要清理临时表tmp_dependents_table，同时事务需要释放所有持有的锁、提交WAL日志，如果临时表数据量大或事务持有锁较多，收尾过程会耗时。可以尝试在循环结束后手动执行DROP TABLE tmp_dependents_table;，观察是否有变化。
• 隐式锁等待：虽然日志显示所有DELETE执行完毕，但函数在结束事务时可能存在锁等待（比如其他会话持有相关表的锁），导致无法快速退出。可以通过以下查询查看当前锁状态：

  SELECT locktype, relation::regclass, mode, granted, pid
  FROM pg_locks
  WHERE relation IN (SELECT table_oid FROM tmp_dependents_table);
  

• WAL日志写入瓶颈：大量DELETE操作会生成大量WAL日志，如果磁盘IO性能不足，WAL刷盘操作会成为瓶颈，尤其是函数结束时事务提交需要将所有WAL日志同步到磁盘，导致耗时增加。可以查看WAL相关统计：

  SELECT wal_written_bytes, wal_sync_time
  FROM pg_stat_wal;
  

• related_tables_recursive函数的副作用：虽然填充临时表的SELECT执行快速，但该递归函数可能存在未察觉的副作用（比如持有资源未释放），可以单独调用该函数多次，确认是否有异常耗时情况。

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