同表FROM子句关联UPDATE语句执行过慢调优求助

更新语句 #

UPDATE schema_name.tbl_bllng upd
SET
rsv_clm = Mc.c_fullform
, counter = tbll.counter + 1
 
FROM
    schema_name.tbl_bllng tbll
    INNER JOIN schema_name.tbl_ihdr ihdr
    ON(
    ihdr.b_id = tbll.b_id
    AND ihdr.Icnt = 0
    AND ihdr.id = 1
    AND ihdr.active_flg = False)
    LEFT OUTER JOIN public.schema_name2.master_table Mc
    ON(
    ihdr.rsv_clm IS NOT NULL
    AND Mc.ic_id = ihdr.rsv_clm
    AND Mc.id = 1
    AND Mc.cd = 1
    AND Mc.active_flg = False)
    WHERE
    tbll.tseq_id = upd.tseq_id
    AND tbll.id = 1
    AND tbll.active_flg = False;

查询计划 #

QUERY PLAN
Update on tbl_bllng upd  (cost=1.14..932050009.01 rows=53247247974 width=8053)
  ->  Merge Join  (cost=1.14..932050009.01 rows=53247247974 width=8053)
        Merge Cond: ((tbll.b_id)::text = (ihdr.b_id)::text)
        ->  Nested Loop  (cost=0.71..108174.20 rows=171730 width=7628)
              ->  Index Scan using idx_bllng_id on tbl_bllng tbll  (cost=0.29..13970.46 rows=171730 width=23)
                    Index Cond: ((id = 1) AND (active_flg = false))
              ->  Index Scan using bllng_pkc on tbl_bllng upd  (cost=0.42..0.55 rows=1 width=7613)
                    Index Cond: (tseq_id = tbll.tseq_id)
        ->  Materialize  (cost=0.43..117666.47 rows=1240212 width=95)
              ->  Nested Loop Left Join  (cost=0.43..114565.94 rows=1240212 width=95)
                    Join Filter: ((ihdr.rsv_clm IS NOT NULL) AND ((Mc.ic_id)::text = (ihdr.rsv_clm)::text))
                    ->  Index Scan using idx_tbl_bll on tbl_ihdr ihdr  (cost=0.43..95961.70 rows=1240212 width=429)
                          Index Cond: ((Icnt = 0) AND (id = 1) AND (active_flg = false))
                    ->  Materialize  (cost=0.00..1.06 rows=1 width=162)
                          ->  Seq Scan on "schema_name2.master_table" Mc  (cost=0.00..1.06 rows=1 width=162)
                                Filter: ((NOT active_flg) AND (id = 1) AND (cd = 1))

表信息 #

记录数 #

• schema_name2.master_table：4条
• schema_name.tbl_bllng：171010条
• schema_name.tbl_ihdr：1240212条

数据类型 #

tbl_ihdr

• b_id: string
• Icnt: integer
• rsv_clm: string
• id: int
• active_flg: Boolean

tbl_bllng

• b_id: string
• tseq_id: Int
• id: int
• active_flg: boolean

调优方案 #

1. 简化更新语句（移除不必要的自连接） #

原语句通过tbll自连接upd，但tbll和upd本质是同表的同一子集，可直接让upd关联ihdr，减少连接开销：

UPDATE schema_name.tbl_bllng upd
SET
  rsv_clm = Mc.c_fullform,
  counter = upd.counter + 1
FROM schema_name.tbl_ihdr ihdr
LEFT JOIN public.schema_name2.master_table Mc
  ON ihdr.rsv_clm IS NOT NULL
  AND Mc.ic_id = ihdr.rsv_clm
  AND Mc.id = 1
  AND Mc.cd = 1
  AND Mc.active_flg = False
WHERE
  upd.b_id = ihdr.b_id
  AND ihdr.Icnt = 0
  AND ihdr.id = 1
  AND ihdr.active_flg = False
  AND upd.id = 1
  AND upd.active_flg = False;

2. 优化索引，避免回表与排序开销 #

• 针对tbl_ihdr：现有索引idx_tbl_bll仅覆盖过滤条件，需扩展为覆盖索引，包含关联与需要的字段：

  CREATE INDEX idx_tbl_ihdr_filter_join ON schema_name.tbl_ihdr (Icnt, id, active_flg) INCLUDE (b_id, rsv_clm);
  

  该索引可直接满足过滤条件，同时获取b_id（关联用）和rsv_clm（关联Mc用），无需回表查询。

• 针对tbl_bllng：现有索引idx_bllng_id仅覆盖过滤条件，扩展为覆盖索引：

  CREATE INDEX idx_tbl_bllng_filter_join ON schema_name.tbl_bllng (id, active_flg) INCLUDE (b_id, tseq_id, counter);
  

  该索引可直接满足upd的过滤条件，同时获取b_id（关联用）和counter（更新用），无需回表。

3. 更新统计信息，修正预估行数偏差 #

查询计划中预估行数（532亿）与实际行数（17万）严重不符，导致优化器选择低效连接方式，执行以下命令更新统计：

ANALYZE schema_name.tbl_bllng;
ANALYZE schema_name.tbl_ihdr;
ANALYZE public.schema_name2.master_table;

4. 调整连接方式（可选） #

当前使用Merge Join，但b_id重复率可能较高，Hash Join效率更优，可临时关闭Merge Join测试：

SET enable_mergejoin = off;
-- 执行更新语句后再恢复
SET enable_mergejoin = on;

5. 预过滤master_table数据（可选） #

由于master_table仅4条记录，可预先筛选符合条件的记录，减少连接时的过滤开销：

WITH filtered_mc AS (
  SELECT ic_id, c_fullform
  FROM public.schema_name2.master_table
  WHERE id = 1 AND cd = 1 AND active_flg = False
)
UPDATE schema_name.tbl_bllng upd
SET
  rsv_clm = mc.c_fullform,
  counter = upd.counter + 1
FROM schema_name.tbl_ihdr ihdr
LEFT JOIN filtered_mc mc
  ON ihdr.rsv_clm IS NOT NULL AND mc.ic_id = ihdr.rsv_clm
WHERE
  upd.b_id = ihdr.b_id
  AND ihdr.Icnt = 0
  AND ihdr.id = 1
  AND ihdr.active_flg = False
  AND upd.id = 1
  AND upd.active_flg = False;

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