PostgreSQL中UNION合并结果与主查询不一致问题排查

问题背景 #

原主查询因性能问题被拆分为多个子查询通过UNION合并，但合并后的结果与原查询完全不匹配，核心需求是修正拆分逻辑，确保结果一致同时提升查询性能。

核心差异与问题原因 #

• 逻辑本质完全不同：原查询是对ledger_entry关联company、tax_office、currency后做分组聚合，返回按公司+维度分组后的统计值；而拆分后的UNION直接返回单条原始记录或无关表的空值记录，无任何聚合逻辑，这是结果不匹配的根本原因。
• 缺失关键维度字段：原查询返回register_no、name、taxoffice等公司/税务/货币维度字段，拆分后的UNION子查询几乎未返回这些字段，结果结构完全不一致。
• 子查询逻辑错误：
  • 原查询的performance是全局统计值（基于符合条件的所有ledger_entry和matched_invoice），拆分后的Parça2将该全局值与单条记录绑定，逻辑错误。
  • 原查询的P0/P1是按公司+货币维度分组后的区间统计，拆分后的Parça1仅做单条记录的区间判断，未做聚合，结果完全偏离。
• 冗余子查询引入无效数据：Parça3/4/5直接从currency、tax_office、Company全表返回空值记录，完全多余，会引入大量无关数据。
• UNION去重干扰：UNION默认自动去重，而原查询的聚合结果无去重逻辑，进一步导致结果偏差。

修正方案（结果一致+性能优化） #

放弃错误的UNION拆分思路，改为对原查询的嵌套子查询、关联逻辑进行重构优化，以下是修正后的查询：

WITH performance_stats AS (
    -- 预计算全局performance统计，避免嵌套子查询重复计算
    SELECT 
        ROUND(CAST(COALESCE(SUM(TOPLAM)/ SUM(credit), 0) AS INTEGER), 2) AS performance
    FROM (
        SELECT 
            DATE_PART('day', CAST(B.create_date AS TIMESTAMP) - CAST(A.document_date AS TIMESTAMP)) * B.credit AS TOPLAM,
            B.credit 
        FROM ledger_entry A 
        INNER JOIN matched_invoice B ON B.ledger_entry_id = A.id 
        WHERE 
            A.due_date <= '2024-03-25' 
            AND COALESCE(A.credit, 0) > 0
            AND A.firm_id = 123078 
            AND A.document_date >= '2024-03-25'
    ) foo
),
customer_rep_lookup AS (
    -- 预计算每个公司的最新联系人，避免分组时重复子查询
    SELECT 
        cr.company_id,
        u.first_name || ' ' || u.last_name AS cus_rep
    FROM customer_representative cr
    LEFT JOIN "user" u ON u.id = COALESCE(cr.customer_representative_id, cr.sales_representative_id)
    WHERE cr.id IN (
        SELECT MAX(id) FROM customer_representative GROUP BY company_id
    )
),
ledger_entry_filtered AS (
    -- 预过滤ledger_entry，排除不需要的记录
    SELECT 
        le.*,
        CASE WHEN le.due_date > '2024-03-25' THEN le.balance_debit END AS unexpired_line
    FROM ledger_entry le
    WHERE 
        le.debit <> 0 
        AND le.firm_id = 123078
        AND (
            le.entity_id IS NULL 
            OR NOT EXISTS (
                SELECT 1 FROM invoice i 
                WHERE i.id = le.entity_id AND i.is_exchange_difference = true
            )
        )
),
balance_periods AS (
    -- 预计算每个公司+货币的P0/P1区间余额
    SELECT 
        l.company_id,
        l.currency_id,
        COALESCE(SUM(CASE WHEN l.due_date BETWEEN '2024-03-26' AND '2024-04-01' THEN l.balance_debit END), 0) AS P0,
        COALESCE(SUM(CASE WHEN l.due_date BETWEEN '2024-04-02' AND '2024-04-08' THEN l.balance_debit END), 0) AS P1
    FROM ledger_entry l
    WHERE 
        l.firm_id = 123078
        AND (
            l.entity_id IS NULL 
            OR NOT EXISTS (
                SELECT 1 FROM invoice i 
                WHERE i.id = l.entity_id AND i.is_exchange_difference = true
            )
        )
    GROUP BY l.company_id, l.currency_id
)
-- 主查询：关联所有预计算CTE，执行分组聚合
SELECT 
    c.register_no, 
    c.name, 
    t.name AS taxoffice, 
    c.tax_no, 
    COALESCE(crl.cus_rep, NULL) AS cus_rep, 
    cr.code AS currency, 
    SUM(lef.debit) AS total, 
    SUM(lef.debit) - SUM(lef.balance_debit) AS paid, 
    SUM(lef.balance_debit) AS balance, 
    ps.performance, 
    SUM(lef.unexpired_line) AS unexpired, 
    bp.P0, 
    bp.P1, 
    1 AS s
FROM ledger_entry_filtered lef
JOIN company c ON lef.company_id = c.id
LEFT JOIN tax_office t ON c.tax_office_id = t.id
JOIN currency cr ON lef.currency_id = cr.id
LEFT JOIN customer_rep_lookup crl ON c.id = crl.company_id
JOIN performance_stats ps ON TRUE
JOIN balance_periods bp ON c.id = bp.company_id AND cr.id = bp.currency_id
GROUP BY 
    c.register_no, 
    c.name, 
    t.name, 
    c.tax_no, 
    cr.code, 
    cr.id, 
    c.id, 
    ps.performance,
    bp.P0,
    bp.P1
HAVING 
    SUM(lef.balance_debit) <> 0 
    AND SUM(lef.balance_debit) >= -1.0E9 
    AND SUM(lef.balance_debit) <= 1.0E9
ORDER BY s;

优化说明 #

• CTE预计算：将嵌套子查询转为CTE预计算，避免分组时重复执行子查询，提升性能。
• 替换NOT IN为NOT EXISTS：避免NULL值导致的过滤逻辑错误，同时提升查询效率。
• 拆分聚合逻辑：将P0/P1的区间聚合单独提取，减少主查询的计算复杂度。
• 显式JOIN替代逗号关联：逻辑更清晰，利于数据库优化器生成高效执行计划。

额外性能建议 #

• 为ledger_entry(firm_id, company_id, currency_id, due_date)创建联合索引。
• 为customer_representative(company_id, id)创建联合索引，加速最新联系人查询。
• 为invoice(id, is_exchange_difference)创建索引，加速NOT EXISTS过滤。

内容的提问来源于stack exchange，提问作者Yusuf İncesu