PostgreSQL存储过程优化：基于JSONB更新boxes表双字段

核心思路 #

直接通过jsonb_to_recordset将传入的JSONB数组转换为可关联的记录集，结合boxes_data表建立boxes与JSON参数的关联，用单条UPDATE语句完成标记删除和dt字段更新操作，最大化利用PostgreSQL的批量处理能力，避免循环遍历带来的性能损耗。

优化后函数代码 #

CREATE OR REPLACE FUNCTION krv.delete_krvs_sridsv2(_krvs jsonb)
RETURNS void AS $$
BEGIN
    -- 批量更新boxes表：标记is_deleted为true，并同步对应rid的dt值
    UPDATE krv.boxes b
    SET 
        is_deleted = true,
        dt = j.dt::timestamp -- 若JSON中dt是timestamp类型，可省略显式转换
    FROM jsonb_to_recordset(_krvs) AS j(rid text, dt text) -- 按JSON实际字段类型定义，比如dt为timestamp则写dt timestamp
    JOIN krv.boxes_data bd 
        ON bd.rid = j.rid
    WHERE b.id = bd.trbx; -- 替换为boxes与boxes_data实际关联字段，比如boxes主键对应boxes_data.trbx
END;
$$ LANGUAGE plpgsql VOLATILE;

关键优化点 #

• 批量处理替代循环：避免逐行遍历JSON数组，用数据库原生批量更新逻辑，减少PL/pgSQL上下文切换开销
• 关联逻辑简化：通过一次JOIN完成所有表与参数的关联，让查询优化器生成最优执行计划
• 减少数据库交互：单条语句完成所有更新操作，降低网络往返和事务开销

注意事项 #

1. 确认boxes与boxes_data的关联字段正确（示例中假设boxes.id = boxes_data.trbx，需根据实际表结构调整）
2. 保证JSON参数中dt的类型与boxes.dt字段类型匹配，若为字符串需显式转换为对应日期/时间类型
3. 若需要统计影响行数，可将函数返回类型改为integer，并在UPDATE后添加RETURN GET DIAGNOSTICS row_count = ROW_COUNT;

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