PostgreSQL中逗号分隔列关联查询性能优化问题求助

原查询的核心问题 #

你的原查询通过多次split_part+UNION的子查询实现关联，存在两个致命性能缺陷：

1. 依赖外部字段的关联子查询会逐行重复执行3次全表扫描（每个split_part对应一次），数据量越大耗时指数级增长；
2. 固定拆分3个值的写法不灵活，无法适配receipt_id中值数量变化的场景，还会产生不必要的重复计算。

最优优化方案：用string_to_array+LATERAL JOIN重构查询 #

PostgreSQL提供的string_to_array可以将逗号分隔字符串转为数组，再配合unnest拆分为多行，最后用LATERAL JOIN关联voucher表，只需扫描一次ap_invoice_creation表，性能提升显著：

SELECT 
    ap.document_no AS invoice_number,
    ap.curr_date AS invoice_date,
    ap.receipt_id,
    -- 如果需要展示拆分后的单个值，可保留此列，否则可删除
    receipt_part.rid AS split_receipt_id,
    v.* -- 根据业务需求选择voucher表的字段
FROM ap_invoice_creation ap
-- 用LATERAL JOIN拆分receipt_id为多行
JOIN LATERAL unnest(string_to_array(ap.receipt_id::text, ',')) AS receipt_part(rid) ON true
-- 直接关联voucher表，避免IN子查询的性能损耗
JOIN voucher v ON v.voucher_id::text = receipt_part.rid
WHERE ap.status = 'Posted'
-- 如果需要去重（对应原查询的UNION逻辑），可添加DISTINCT
-- DISTINCT

进一步性能优化建议 #

1. 消除不必要的类型转换：
   如果voucher.voucher_id是数值类型（比如int），将拆分后的rid转为对应类型，避免隐式类型转换导致索引失效：

   JOIN voucher v ON v.voucher_id = receipt_part.rid::int
   

2. 添加针对性索引：

   • 给ap_invoice_creation.status加索引，快速过滤出Posted状态的数据：

     CREATE INDEX idx_ap_invoice_status ON ap_invoice_creation(status);
     

   • 确保voucher.voucher_id有主键索引（通常主键默认自带，若没有则手动添加）：

     CREATE INDEX idx_voucher_id ON voucher(voucher_id);
     

3. 避免冗余数据返回：
   不要查询不需要的字段（比如原查询中拆分的前两个值如果业务不需要就删掉），减少数据传输量。

内容的提问来源于stack exchange，提问作者Dharani.C