Postgres Join查询性能分析：定位耗时点与优化改写方案

耗时点定位 #

从执行计划可以明确：

• 子查询unique_comm_lock执行效率很高，仅耗时约14ms，返回728行数据
• 核心耗时来自Nested Loop中对commission表的循环Index Scan：PostgreSQL选择嵌套循环连接，用unique_comm_lock的728行数据逐个扫描commission表，单次扫描耗时87.4ms，总耗时约63.6秒；且每次扫描都未返回数据，但嵌套循环机制仍会执行完所有循环，导致整体耗时居高不下

原查询语句 #

SELECT "commission"."period_start_date","commission"."period_end_date","commission"."payee_email_id","commission"."commission_plan_id","commission"."criteria_id","commission"."line_item_id","commission"."tier_id","commission"."original_tier_id",SUM("commission"."amount") "amount",CONCAT('Commission') "record_type",MAX("commission"."secondary_kd") "secondary_kd",MAX("commission"."knowledge_begin_date") "knowledge_begin_date",MAX("unique_comm_lock"."max_locked_knowledge_date") "locked_kd" FROM "commission" JOIN (SELECT "client_id","payee_email_id","period_start_date","period_end_date",MAX("locked_knowledge_date") "max_locked_knowledge_date" FROM "commission_lock" WHERE "client_id"=31 AND NOT "is_deleted" AND "knowledge_end_date" IS NULL AND "is_locked"=true GROUP BY "client_id","payee_email_id","period_start_date","period_end_date") "unique_comm_lock" ON "commission"."client_id"="unique_comm_lock"."client_id" AND NOT "commission"."is_deleted" AND "commission"."payee_email_id"="unique_comm_lock"."payee_email_id" AND "commission"."knowledge_begin_date"<="unique_comm_lock"."max_locked_knowledge_date" AND ("commission"."knowledge_end_date">"unique_comm_lock"."max_locked_knowledge_date" OR "commission"."knowledge_end_date" IS NULL) AND "commission"."period_start_date"="unique_comm_lock"."period_start_date" AND "commission"."period_end_date"="unique_comm_lock"."period_end_date" WHERE "commission"."client_id"=31 AND "commission"."knowledge_begin_date">='2023-09-03T03:16:56.633166+00:00' GROUP BY "commission"."period_start_date","commission"."period_end_date","commission"."payee_email_id","commission"."commission_plan_id","commission"."criteria_id","commission"."line_item_id","commission"."tier_id","commission"."original_tier_id"

原执行计划 #

GroupAggregate  (cost=7269.33..7269.38 rows=1 width=307) (actual time=63641.414..63641.416 rows=0 loops=1)
      Group Key: commission.period_start_date, commission.period_end_date, commission.payee_email_id, commission.commission_plan_id, commission.criteria_id, commission.line_item_id, commission.tier_id, commission.original_tier_id
      ->  Sort  (cost=7269.33..7269.33 rows=1 width=246) (actual time=63641.413..63641.414 rows=0 loops=1)
            Sort Key: commission.period_start_date, commission.period_end_date, commission.payee_email_id, commission.commission_plan_id, commission.criteria_id, commission.line_item_id, commission.tier_id, commission.original_tier_id
            Sort Method: quicksort  Memory: 25kB
            ->  Nested Loop  (cost=1651.85..7269.32 rows=1 width=246) (actual time=63641.383..63641.384 rows=0 loops=1)
                  ->  HashAggregate  (cost=1651.15..1657.50 rows=635 width=54) (actual time=12.537..14.365 rows=728 loops=1)
                        Group Key: commission_lock.client_id, commission_lock.payee_email_id, commission_lock.period_start_date, commission_lock.period_end_date
                        ->  Bitmap Heap Scan on commission_lock  (cost=42.51..1642.88 rows=662 width=54) (actual time=11.570..11.938 rows=728 loops=1)
                              Recheck Cond: ((client_id = 31) AND (knowledge_end_date IS NULL))
                              Filter: ((NOT is_deleted) AND is_locked)
                              Heap Blocks: exact=61
                              ->  Bitmap Index Scan on cl_ked_il_li_idx  (cost=0.00..42.35 rows=662 width=0) (actual time=11.545..11.545 rows=865 loops=1)
                                    Index Cond: ((client_id = 31) AND (is_deleted = false) AND (knowledge_end_date IS NULL) AND (is_locked = true))
                  ->  Index Scan using comm_pei_ped_psd_kbd_ked_idx on commission  (cost=0.69..8.82 rows=1 width=250) (actual time=87.396..87.396 rows=0 loops=728)
                        Index Cond: ((client_id = 31) AND (is_deleted = false) AND ((payee_email_id)::text = (commission_lock.payee_email_id)::text) AND (period_end_date = commission_lock.period_end_date) AND (period_start_date = commission_lock.period_start_date) AND (knowledge_begin_date <= (max(commission_lock.locked_knowledge_date))) AND (knowledge_begin_date >= '2023-09-03 03:16:56.633166+00'::timestamp with time zone))
                        Filter: ((knowledge_end_date > (max(commission_lock.locked_knowledge_date))) OR (knowledge_end_date IS NULL))

优化改写方案 #

1. 强制使用哈希连接替代嵌套循环 #

嵌套循环在驱动表返回大量数据且被驱动表无匹配数据时效率极低，通过禁用嵌套循环强制数据库使用哈希连接，避免循环扫描：

SELECT 
    c.period_start_date,
    c.period_end_date,
    c.payee_email_id,
    c.commission_plan_id,
    c.criteria_id,
    c.line_item_id,
    c.tier_id,
    c.original_tier_id,
    SUM(c.amount) AS amount,
    'Commission' AS record_type,
    MAX(c.secondary_kd) AS secondary_kd,
    MAX(c.knowledge_begin_date) AS knowledge_begin_date,
    MAX(ucl.max_locked_knowledge_date) AS locked_kd
FROM commission c
JOIN (
    SELECT 
        client_id,
        payee_email_id,
        period_start_date,
        period_end_date,
        MAX(locked_knowledge_date) AS max_locked_knowledge_date
    FROM commission_lock
    WHERE client_id=31 
      AND NOT is_deleted 
      AND knowledge_end_date IS NULL 
      AND is_locked=true
    GROUP BY client_id,payee_email_id,period_start_date,period_end_date
) ucl ON 
    c.client_id=ucl.client_id 
    AND NOT c.is_deleted 
    AND c.payee_email_id=ucl.payee_email_id 
    AND c.knowledge_begin_date<=ucl.max_locked_knowledge_date 
    AND (c.knowledge_end_date>ucl.max_locked_knowledge_date OR c.knowledge_end_date IS NULL) 
    AND c.period_start_date=ucl.period_start_date 
    AND c.period_end_date=ucl.period_end_date
WHERE c.client_id=31 
  AND c.knowledge_begin_date>='2023-09-03T03:16:56.633166+00:00'
GROUP BY c.period_start_date,c.period_end_date,c.payee_email_id,c.commission_plan_id,c.criteria_id,c.line_item_id,c.tier_id,c.original_tier_id;

-- 会话级别禁用嵌套循环，仅影响当前查询
SET LOCAL enable_nestloop = off;

2. 创建覆盖索引，提升commission表扫描效率 #

当前索引未完全覆盖连接、过滤及查询所需字段，创建包含所有必要字段的复合覆盖索引，避免回表扫描，即使无匹配数据也能快速完成扫描：

CREATE INDEX idx_comm_client_deleted_payee_period_knowledge 
ON commission (client_id, is_deleted, payee_email_id, period_start_date, period_end_date, knowledge_begin_date)
INCLUDE (knowledge_end_date, amount, secondary_kd, commission_plan_id, criteria_id, line_item_id, tier_id, original_tier_id);

3. 提前过滤commission表数据，缩小连接范围 #

将commission表的过滤条件提前到子查询中，减少参与连接的数据量，降低连接成本：

SELECT 
    c.period_start_date,
    c.period_end_date,
    c.payee_email_id,
    c.commission_plan_id,
    c.criteria_id,
    c.line_item_id,
    c.tier_id,
    c.original_tier_id,
    SUM(c.amount) AS amount,
    'Commission' AS record_type,
    MAX(c.secondary_kd) AS secondary_kd,
    MAX(c.knowledge_begin_date) AS knowledge_begin_date,
    MAX(ucl.max_locked_knowledge_date) AS locked_kd
FROM (
    SELECT * 
    FROM commission
    WHERE client_id=31 
      AND NOT is_deleted 
      AND knowledge_begin_date>='2023-09-03T03:16:56.633166+00:00'
) c
JOIN (
    SELECT 
        client_id,
        payee_email_id,
        period_start_date,
        period_end_date,
        MAX(locked_knowledge_date) AS max_locked_knowledge_date
    FROM commission_lock
    WHERE client_id=31 
      AND NOT is_deleted 
      AND knowledge_end_date IS NULL 
      AND is_locked=true
    GROUP BY client_id,payee_email_id,period_start_date,period_end_date
) ucl ON 
    c.payee_email_id=ucl.payee_email_id 
    AND c.knowledge_begin_date<=ucl.max_locked_knowledge_date 
    AND (c.knowledge_end_date>ucl.max_locked_knowledge_date OR c.knowledge_end_date IS NULL) 
    AND c.period_start_date=ucl.period_start_date 
    AND c.period_end_date=ucl.period_end_date
GROUP BY c.period_start_date,c.period_end_date,c.payee_email_id,c.commission_plan_id,c.criteria_id,c.line_item_id,c.tier_id,c.original_tier_id;

4. 改用左连接+过滤空值（业务允许时） #

通过左连接后过滤匹配数据的方式，引导数据库选择更高效的执行计划，避免嵌套循环的低效扫描：

SELECT 
    c.period_start_date,
    c.period_end_date,
    c.payee_email_id,
    c.commission_plan_id,
    c.criteria_id,
    c.line_item_id,
    c.tier_id,
    c.original_tier_id,
    SUM(c.amount) AS amount,
    'Commission' AS record_type,
    MAX(c.secondary_kd) AS secondary_kd,
    MAX(c.knowledge_begin_date) AS knowledge_begin_date,
    MAX(ucl.max_locked_knowledge_date) AS locked_kd
FROM commission c
LEFT JOIN (
    SELECT 
        client_id,
        payee_email_id,
        period_start_date,
        period_end_date,
        MAX(locked_knowledge_date) AS max_locked_knowledge_date
    FROM commission_lock
    WHERE client_id=31 
      AND NOT is_deleted 
      AND knowledge_end_date IS NULL 
      AND is_locked=true
    GROUP BY client_id,payee_email_id,period_start_date,period_end_date
) ucl ON 
    c.client_id=ucl.client_id 
    AND NOT c.is_deleted 
    AND c.payee_email_id=ucl.payee_email_id 
    AND c.knowledge_begin_date<=ucl.max_locked_knowledge_date 
    AND (c.knowledge_end_date>ucl.max_locked_knowledge_date OR c.knowledge_end_date IS NULL) 
    AND c.period_start_date=ucl.period_start_date 
    AND c.period_end_date=ucl.period_end_date
WHERE c.client_id=31 
  AND c.knowledge_begin_date>='2023-09-03T03:16:56.633166+00:00'
  AND ucl.client_id IS NOT NULL -- 等价于内连接的过滤条件
GROUP BY c.period_start_date,c.period_end_date,c.payee_email_id,c.commission_plan_id,c.criteria_id,c.line_item_id,c.tier_id,c.original_tier_id;

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