PostgreSQL不足300行结果集查询耗时20秒优化问题

问题现象 #

查询运行在PostgreSQL数据库环境，返回结果不足300行，首次执行耗时超过20秒，服务器CPU使用率仅为10%，初步判断性能瓶颈出现在JOIN关联环节，需要调整查询结构将执行耗时降至合理范围。

原始查询语句 #

with contrib as (
    select
      first_name,
      last_name,
      user_id,
      photo_url
    from contributors
    where visible = true
    group by 1,2,3,4
  ),
  dwm as (
    select * from dialogues_with_metadata
  ),
  joined as (
    select
      c.*,
      dwm.dialogue_id
    from contrib c
    left join dwm on c.user_id = dwm.contributor_one_user_id or c.user_id = dwm.contributor_two_user_id
  )
  select
    first_name,
    last_name,
    user_id,
    photo_url,
    count(distinct dialogue_id) as dialogues
  from joined
  group by 1,2,3,4
  order by 3 desc

涉及表结构 #

contributors表 #

• user_id：主键，uuid类型
• username
• first_name
• last_name
• hash
• description
• blurb
• photo_url
• blurb_updated_at
• visible

dialogues表 #

• dialogue_id：uuid类型
• contributor_one_uuid
• contributor_two_uuid
• title
• image_url
• visible
• categories
• image_source
• current_popularity
• created_at
• override_url
• visible

各阶段执行计划 #

视图dialogues_with_metadata的执行计划 #

执行explain select * from dialogues_with_metadata;返回结果：

Hash Left Join  (cost=167.80..172.34 rows=137 width=880)
  Hash Cond: (a.dialogue_id = b.writing_dialogue_id)
  CTE main
    ->  Hash Right Join  (cost=64.30..111.60 rows=137 width=504)
          Hash Cond: (c2.user_id = d.contributor_two_uuid)
          ->  Seq Scan on contributors c2  (cost=0.00..43.60 rows=260 width=125)
          ->  Hash  (cost=62.59..62.59 rows=137 width=325)
                ->  Hash Left Join  (cost=46.85..62.59 rows=137 width=325)
                      Hash Cond: (d.contributor_one_uuid = c1.user_id)
                      ->  Seq Scan on dialogues d  (cost=0.00..15.37 rows=137 width=216)
                      ->  Hash  (cost=43.60..43.60 rows=260 width=125)
                            ->  Seq Scan on contributors c1  (cost=0.00..43.60 rows=260 width=125)
  CTE dialogues_with_installment_counts
    ->  HashAggregate  (cost=50.39..52.00 rows=129 width=28)
          Group Key: writings.dialogue_id
          ->  Seq Scan on writings  (cost=0.00..46.82 rows=476 width=28)
                Filter: finalized
  ->  CTE Scan on main a  (cost=0.00..2.74 rows=137 width=868)
  ->  Hash  (cost=2.58..2.58 rows=129 width=28)
        ->  CTE Scan on dialogues_with_installment_counts b  (cost=0.00..2.58 rows=129 width=28)

第一次调整后的执行计划（无analyze参数） #

Seq Scan on contributors c  (cost=0.00..4012.89 rows=247 width=109)
  Filter: visible
  SubPlan 1
    ->  Aggregate  (cost=16.06..16.07 rows=1 width=8)
          ->  Seq Scan on dialogues  (cost=0.00..16.05 rows=2 width=0)
                Filter: ((c.user_id = contributor_one_uuid) OR (c.user_id = contributor_two_uuid))

第一次调整后带实际执行指标的执行计划 #

执行explain (analyze, buffers) select返回结果：

Seq Scan on contributors c  (cost=0.00..4205.86 rows=259 width=109) (actual time=0.073..16819.258 rows=260 loops=1)
  Filter: visible
  Rows Removed by Filter: 13
  Buffers: shared hit=3681
  SubPlan 1
    ->  Aggregate  (cost=16.06..16.07 rows=1 width=8) (actual time=64.548..64.549 rows=1 loops=260)
          Buffers: shared hit=3640
          ->  Seq Scan on dialogues  (cost=0.00..16.05 rows=2 width=0) (actual time=49.155..64.547 rows=1 loops=260)
                Filter: ((c.user_id = contributor_one_uuid) OR (c.user_id = contributor_two_uuid))
                Rows Removed by Filter: 136
                Buffers: shared hit=3640
Planning Time: 0.136 ms
Execution Time: 16819.365 ms

缓存命中后的同查询执行计划 #

第二次执行相同查询（缓存命中后）耗时缩短16秒，执行计划如下：

Seq Scan on contributors c  (cost=0.00..4205.86 rows=259 width=109) (actual time=0.063..801.278 rows=260 loops=1)
  Filter: visible
  Rows Removed by Filter: 13
  Buffers: shared hit=3681
  SubPlan 1
    ->  Aggregate  (cost=16.06..16.07 rows=1 width=8) (actual time=3.080..3.080 rows=1 loops=260)
          Buffers: shared hit=3640
          ->  Seq Scan on dialogues  (cost=0.00..16.05 rows=2 width=0) (actual time=0.009..3.079 rows=1 loops=260)
                Filter: ((c.user_id = contributor_one_uuid) OR (c.user_id = contributor_two_uuid))
                Rows Removed by Filter: 136
                Buffers: shared hit=3640
Planning Time: 0.127 ms
Execution Time: 801.379 ms

问题根因 #

1. 原始查询使用OR条件做左连接，PostgreSQL无法高效使用哈希连接或索引嵌套循环连接，导致关联阶段效率极低
2. 第一次调整改用相关子查询后，对260个符合条件的贡献者逐行全表扫描对话表，冷缓存场景下反复读盘导致耗时飙升到16秒以上，即使热缓存也需要800毫秒，仍有优化空间
3. 视图dialogues_with_metadata本身包含两层CTE和多次表扫描，直接关联视图会引入不必要的计算开销

优化方案 #

查询结构调整 #

拆分OR关联条件，用UNION ALL替代OR连接，避免全表反复扫描，同时去掉冗余的分组逻辑（user_id是contributors表主键，不需要按多字段分组）：

WITH visible_contributors AS (
  SELECT user_id, first_name, last_name, photo_url
  FROM contributors
  WHERE visible = true
),
dialogue_contributor_map AS (
  SELECT contributor_one_uuid AS user_id, dialogue_id FROM dialogues WHERE visible = true
  UNION ALL
  SELECT contributor_two_uuid AS user_id, dialogue_id FROM dialogues WHERE visible = true
)
SELECT 
  vc.first_name,
  vc.last_name,
  vc.user_id,
  vc.photo_url,
  COUNT(DISTINCT dcm.dialogue_id) AS dialogues
FROM visible_contributors vc
LEFT JOIN dialogue_contributor_map dcm ON vc.user_id = dcm.user_id
GROUP BY vc.user_id, vc.first_name, vc.last_name, vc.photo_url
ORDER BY vc.user_id DESC

索引优化 #

在dialogues表的两个关联字段上创建部分覆盖索引，直接从索引取关联数据，不需要回表：

CREATE INDEX idx_dialogues_contributor_one ON dialogues(contributor_one_uuid) INCLUDE (dialogue_id) WHERE visible = true;
CREATE INDEX idx_dialogues_contributor_two ON dialogues(contributor_two_uuid) INCLUDE (dialogue_id) WHERE visible = true;

优化后预期效果 #

• 冷缓存场景下耗时可降至100毫秒以内，不需要反复扫描dialogues全表
• 热缓存场景下耗时可降至10毫秒级别
• 不会出现IO等待导致的CPU低占用慢查询问题

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