相同SQL查询在千行级小数据库中执行耗时差异大问题排查

基础现象 #

当前数据库仅包含约10张表，总数据量不超过数千行，但同一条查询的执行耗时差异极大，最高接近17秒，最低不足1秒。

涉及原查询语句 #

select
  c.first_name,
  c.last_name,
  c.user_id,
  c.photo_url,
  s.dialogues
  from contributors c
    join (
      select count(*) dialogues, user_id from (
          select contributor_one_uuid user_id from dialogues
          union all 
          select contributor_two_uuid from dialogues
      ) stats group by user_id
    ) s on (s.user_id = c.user_id)
  where c.visible = true

两次执行的执行计划对比 #

慢查询（耗时~17s）执行计划

通过explain (analyze, buffers)采集结果如下：

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

快查询（耗时<1s）执行计划

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

根因分析 #

两次执行的计划结构完全一致，性能波动来自两个层面的共同作用：

• 冷热缓存差异：两次执行的Buffer统计均为shared hit，没有触发磁盘IO，说明耗时差不是磁盘读导致。慢查询触发时，数据库共享缓存、操作系统页缓存、CPU缓存均未加载dialogues、contributors表的相关数据，内存访问走延迟最高的主存路径，单次全表扫描dialogues的耗时达到64ms；首次查询完成后，相关数据全部驻留在各级缓存中，第二次执行的单次全表扫描耗时仅3ms，累计耗时直接下降一个数量级。
• 查询写法存在固有性能缺陷：优化器没有按写好的逻辑先聚合全量用户的对话数再做关联，而是把JOIN子查询改写为了相关子查询（即执行计划中的SubPlan 1），针对contributors表返回的每一行（共260行），都要全表扫描一次dialogues表做统计。这种嵌套循环的执行逻辑哪怕在小表场景下，也会把缓存状态的影响放大，产生明显的性能波动。

优化方案 #

从语句改写、索引配置两个层面调整，彻底消除不稳定的执行逻辑：

• 改写查询逻辑，避免重复扫描表：将聚合逻辑拆为独立CTE，保证dialogues表仅扫描2次（UNION ALL两部分各一次），而不是为每个贡献者单独扫描一次。改写后语句和原逻辑完全一致，同时用LEFT JOIN保证没有对话记录的贡献者不会被过滤：

WITH user_dialogue_stats AS (
    SELECT contributor_one_uuid AS user_id FROM dialogues
    UNION ALL
    SELECT contributor_two_uuid AS user_id FROM dialogues
)
SELECT
    c.first_name,
    c.last_name,
    c.user_id,
    c.photo_url,
    COUNT(s.user_id) AS dialogues
FROM contributors c
LEFT JOIN user_dialogue_stats s ON s.user_id = c.user_id
WHERE c.visible = true
GROUP BY c.user_id, c.first_name, c.last_name, c.photo_url;

• 添加对应索引，消除全表扫描：哪怕数据量较小，索引也能把单条查询的开销压到极低，完全抹平缓存状态带来的波动：
  • 为contributors.visible字段创建索引，快速过滤符合条件的贡献者记录
  • 分别为dialogues.contributor_one_uuid、dialogues.contributor_two_uuid字段创建索引，后续数据量上涨时也无需全表扫描即可完成用户对话数统计
• 定期在业务低峰期执行ANALYZE contributors; ANALYZE dialogues;更新表统计信息，保证优化器始终能选择最优执行计划，避免出现不必要的子查询改写。

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