PostgreSQL添加delete_flag条件后查询性能骤降问题及优化需求

表结构 #

create table billing (
   org_id character varying(5) not null
   , billing_id character varying(20) not null
   , contract_key character varying(30) not null
   , started_date character varying(8) not null
   , primary key (org_id, billing_id, contract_key, started_date)
);

create table contract (
   org_id character varying(5) not null
   , contract_key character varying(30) not null
   , apply_started_date character varying(8) not null
   , delete_flag character varying(1)
   , primary key (org_id, contract_key, apply_started_date)
);

billing表有200万条记录，contract表有350万条记录，需求是关联两张表，且仅关联contract表中apply_started_date最新的记录。

问题现象 #

• 最初的查询语句执行耗时仅127毫秒：

select * from billing
left join contract
on billing.org_id = contract.org_id
and billing.contract_key = contract.contract_key
inner join (
   select org_id, contract_key, MAX(apply_started_date) as max_apply_started_date
   from contract
   where apply_started_date::integer < 20230829
   group by org_id, contract_key
) contract_aggregate
on contract.org_id = contract_aggregate.org_id
and contract.contract_key = contract_aggregate.contract_key
and contract.apply_started_date = contract_aggregate.max_apply_started_date
where billing.org_id = '001'
and billing.billing_id in ('B000000000000001','B000000000000002','B000000000000003','B000000000000004','B000000000000005')
and contract.apply_started_date::integer < 20230829

• 添加contract.delete_flag='0'条件后，查询耗时骤升至3586毫秒，即使给delete_flag单独加索引也无效果（注：95%以上数据的delete_flag值为0）：

select * from billing
left join contract
on billing.org_id = contract.org_id
and billing.contract_key = contract.contract_key
inner join (
   select org_id, contract_key, MAX(apply_started_date) as max_apply_started_date
   from contract
   where apply_started_date::integer < 20230829
   and delete_flag = '0'
   group by org_id, contract_key
) contract_aggregate
on contract.org_id = contract_aggregate.org_id
and contract.contract_key = contract_aggregate.contract_key
and contract.apply_started_date = contract_aggregate.max_apply_started_date
where billing.org_id = '001'
and billing.billing_id in ('B000000000000001','B000000000000002','B000000000000003','B000000000000004','B000000000000005')
and contract.apply_started_date::integer < 20230829
and delete_flag = '0'

执行计划对比 #

无delete_flag条件时的执行计划 #

QUERY PLAN
Nested Loop (cost=23.41..239297.08 rows=1 width=174) (actual time=0.075..0.096 rows=5 loops=1)
   Join Filter: ((billing.contract_key)::text = (contract.contract_key)::text)
   -> Merge Join (cost=22.85..239294.65 rows=3 width=128) (actual time=0.066..0.073 rows=5 loops=1)
         Merge Cond: ((contract_1.contract_key)::text = (billing.contract_key)::text)
         -> GroupAggregate (cost=0.56..224689.00 rows=1166664 width=67) (actual time=0.030..0.035 rows=6 loops=1)
               Group Key: contract_1.org_id, contract_1.contract_key
               -> Index Only Scan using contract_pkey on contract contract_1 (cost=0.56..204272.38 rows=1166664 width=44) (actual time=0.025..0.028 rows=7 loops=1)
                     Index Cond: (org_id = '001'::text)
                     Filter: ((apply_started_date)::integer < 20230829)
                     Heap Fetches: 0
         -> Sort (cost=22.30..22.31 rows=5 width=61) (actual time=0.034..0.034 rows=5 loops=1)
               Sort Key: billing.contract_key
               Sort Method: quicksort Memory: 25kB
               -> Index Only Scan using billing_pkey on billing (cost=0.43..22.24 rows=5 width=61) (actual time=0.015..0.024 rows=5 loops=1)
                     Index Cond: ((org_id = '001'::text) AND (billing_id = ANY ('{B000000000000001,B000000000000002,B000000000000003,B000000000000004,B000000000000005}'::text[]) ))
                     Heap Fetches: 0
   -> Index Scan using contract_pkey on contract (cost=0.56..0.80 rows=1 width=46) (actual time=0.004..0.004 rows=1 loops=5)
         Index Cond: (((org_id)::text = '001'::text) AND ((contract_key)::text = (contract_1.contract_key)::text) AND ((apply_started_date)::text = (max(( contract_1.apply_started_date)::text))))
         Filter: ((apply_started_date)::integer < 20230829)
Planning Time: 0.610ms
Execution Time: 0.127ms

添加delete_flag条件时的执行计划 #

QUERY PLAN
Nested Loop  (cost=229048.18..266966.69 rows=1 width=174) (actual time=3228.310..3228.334 rows=5 loops=1)
  Join Filter: ((billing.contract_key)::text = (contract.contract_key)::text)
  ->  Merge Join  (cost=229047.62..266964.26 rows=3 width=128) (actual time=3228.285..3228.292 rows=5 loops=1)
        Merge Cond: ((contract_1.contract_key)::text = (billing.contract_key)::text)
        ->  GroupAggregate  (cost=229025.33..252358.61 rows=1166664 width=67) (actual time=3228.213..3228.218 rows=6 loops=1)
              Group Key: contract_1.org_id, contract_1.contract_key
              ->  Sort  (cost=229025.33..231941.99 rows=1166664 width=44) (actual time=3228.200..3228.201 rows=7 loops=1)
                    Sort Key: contract_1.contract_key
                    Sort Method: external merge  Disk: 184760kB
                    ->  Seq Scan on contract contract_1  (cost=0.00..111460.84 rows=1166664 width=44) (actual time=0.030..2114.875 rows=3500000 loops=1)
                          Filter: (((delete_flag)::text = '0'::text) AND ((org_id)::text = '001'::text) AND ((apply_started_date)::integer < 20230829))
        ->  Sort  (cost=22.30..22.31 rows=5 width=61) (actual time=0.066..0.067 rows=5 loops=1)
              Sort Key: billing.contract_key
              Sort Method: quicksort  Memory: 25kB
              ->  Index Only Scan using billing_pkey on billing  (cost=0.43..22.24 rows=5 width=61) (actual time=0.039..0.050 rows=5 loops=1)
                    Index Cond: ((org_id = '001'::text) AND (billing_id = ANY ('{B000000000000001,B000000000000002,B000000000000003,B000000000000004,B000000000000005}'::text[])))
                    Heap Fetches: 0
  ->  Index Scan using contract_pkey on contract  (cost=0.56..0.80 rows=1 width=46) (actual time=0.007..0.007 rows=1 loops=5)
        Index Cond: (((org_id)::text = '001'::text) AND ((contract_key)::text = (contract_1.contract_key)::text) AND ((apply_started_date)::text = (max((contract_1.apply_started_date)::text))))
        Filter: (((delete_flag)::text = '0'::text) AND ((apply_started_date)::integer < 20230829))
Planning Time: 0.653 ms
Execution Time: 3586.872 ms

原因分析 #

从执行计划可以明显看到核心差异：

1. 无delete_flag条件时，contract表使用Index Only Scan（主键索引），因为主键索引以org_id开头，能快速过滤org_id='001'的记录，再过滤apply_started_date，全程不需要回表，效率极高。
2. 添加delete_flag条件后，数据库选择了全表扫描（Seq Scan），原因是：
   • 单独给delete_flag加索引没用，95%数据的delete_flag都是0，索引选择性极差，数据库判定全表扫描比走索引更高效。
   • 原主键索引不包含delete_flag字段，无法通过主键索引过滤该条件，导致数据库只能全表扫描后再过滤，还要进行磁盘排序（external merge），这是性能骤降的核心原因。

优化方案 #

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

创建包含org_id、delete_flag、contract_key、apply_started_date的复合索引，直接通过索引过滤所有条件，同时满足分组和求最大值的需求，无需回表和额外排序：

CREATE INDEX idx_contract_org_delete_key_date ON contract (org_id, delete_flag, contract_key, apply_started_date);

2. 改用窗口函数改写查询 #

用ROW_NUMBER()窗口函数获取每个org_id+contract_key组内的最新记录，避免子查询分组，让数据库更高效地利用索引：

SELECT b.*, c.*
FROM billing b
LEFT JOIN (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY org_id, contract_key ORDER BY apply_started_date DESC) AS rn
    FROM contract
    WHERE delete_flag = '0'
      AND apply_started_date::integer < 20230829
) c ON b.org_id = c.org_id 
   AND b.contract_key = c.contract_key 
   AND c.rn = 1
WHERE b.org_id = '001'
  AND b.billing_id IN ('B000000000000001','B000000000000002','B000000000000003','B000000000000004','B000000000000005')

3. 优化日期字段数据类型 #

当前apply_started_date是字符串类型，查询时需要转换为整数，会导致索引失效。建议将其改为integer或date类型：

-- 修改contract表的apply_started_date字段类型
ALTER TABLE contract ALTER COLUMN apply_started_date TYPE integer USING apply_started_date::integer;
-- 可选：修改billing表的started_date字段类型
ALTER TABLE billing ALTER COLUMN started_date TYPE integer USING started_date::integer;

修改后查询条件可直接写apply_started_date < 20230829，无需类型转换，进一步提升索引效率。

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