如何快速定位客户最新未覆盖销售并优化PostgreSQL查询性能

问题概述 #

需要从大量销售与收款数据中，快速找出每个客户最新的、无法被累计付款覆盖的销售记录。现有基于PostgreSQL窗口函数的实现，在处理1000个客户/10万条数据时耗时约2秒，需优化查询性能（排除硬件升级）。

示例数据 #

现有核心SQL #

with cumsum_entry as (
    select 
        id, date, customer_id, type, amount,
        sum(amount) filter (where type='PAYMENT') over win_customer
          - sum(amount) filter (where type='SALE') over win_cumsum as cover
    from entry
    window win_customer as (partition by customer_id),
        win_cumsum as (partition by customer_id order by date rows between unbounded proceeding and current row)
)
select 
    customer_id,
    sum(amount) filter (where date >= '2024-01-01') as monthly_total,
    min(date) filter (where cover < 0) as latest_uncovered
from cumsum_entry
where date <= '2024-01-31'
group by customer_id

优化方案 #

1. 前置日期过滤，减少窗口计算数据量 #

现有SQL先对全表执行窗口函数，再过滤日期范围，导致大量无关数据参与计算。调整逻辑为先过滤日期，再执行窗口聚合：

with cumsum_entry as (
    select 
        date, customer_id, type, amount,
        sum(amount) filter (where type='PAYMENT') over win_customer
          - sum(amount) filter (where type='SALE') over win_cumsum as cover
    from entry
    where date <= '2024-01-31' -- 提前过滤日期
    window win_customer as (partition by customer_id),
        win_cumsum as (partition by customer_id order by date) -- 省略默认ROWS子句
)
select 
    customer_id,
    sum(amount) filter (where date >= '2024-01-01') as monthly_total,
    min(date) filter (where cover < 0) as latest_uncovered
from cumsum_entry
group by customer_id

2. 拆分聚合逻辑，替换全局窗口函数 #

原SQL中sum(amount) filter (where type='PAYMENT') over win_customer是对每个客户全局求和，可替换为预聚合子查询，减少窗口函数的计算复杂度：

-- 预计算每个客户的总付款
with customer_payments as (
    select 
        customer_id,
        sum(amount) as total_payment
    from entry
    where date <= '2024-01-31' and type = 'PAYMENT'
    group by customer_id
),
-- 计算累计销售
cumulative_sales as (
    select 
        date, customer_id, amount,
        sum(amount) filter (where type='SALE') over (
            partition by customer_id order by date
        ) as cumulative_sale
    from entry
    where date <= '2024-01-31'
)
select 
    cs.customer_id,
    sum(cs.amount) filter (where cs.date >= '2024-01-01') as monthly_total,
    min(cs.date) filter (where coalesce(cp.total_payment, 0) - cs.cumulative_sale < 0) as latest_uncovered
from cumulative_sales cs
left join customer_payments cp on cs.customer_id = cp.customer_id
group by cs.customer_id, cp.total_payment

3. 创建针对性复合索引 #

窗口函数依赖customer_id分区和date排序，同时需要过滤type和读取amount，创建覆盖索引可消除排序和全表扫描开销：

-- 针对客户分区、日期排序的覆盖索引
create index idx_entry_customer_date_type_amount on entry (customer_id, date) include (type, amount);

-- 如果频繁按日期范围查询，可补充以下索引
create index idx_entry_date_customer_type_amount on entry (date, customer_id) include (type, amount);

4. 移除不必要字段，缩小中间结果集 #

原CTE中选择了id字段，但后续查询未使用，可直接移除，减少内存占用和数据传输量：

with cumsum_entry as (
    select 
        date, customer_id, type, amount, -- 移除未使用的id字段
        sum(amount) filter (where type='PAYMENT') over win_customer
          - sum(amount) filter (where type='SALE') over win_cumsum as cover
    from entry
    where date <= '2024-01-31'
    window win_customer as (partition by customer_id),
        win_cumsum as (partition by customer_id order by date)
)
-- 后续查询逻辑不变

5. Django ORM层面优化 #

• 避免将大量中间数据拉取到Django内存中，尽量在数据库层面完成所有聚合计算；
• 使用raw()方法执行优化后的SQL，而非通过ORM拼接查询，减少ORM的额外开销；
• 确保只查询所需字段，避免select_related或prefetch_related引入不必要的关联数据。

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