同一PostgreSQL连接多次执行查询后性能下降问题排查

在同一数据库连接下多次执行同一查询，数次迭代后查询性能大幅下降。该问题可在Arch Linux、Ubuntu及官方Docker镜像等多种操作系统上稳定复现，且Postgres 15和16版本均受影响。

新建连接可临时恢复性能，但重复执行数次查询后性能下降现象会再次出现。多次执行期间数据集无变化，调整shared_buffers和work_mem参数后问题仍未改善。

查询语句 #

select a.id
from answer a
inner join question q on q.id = a.question_id
inner join response r on r.id = a.response_id
inner join campaign c on c.id = r.campaign_id
where q.project_indicator_id = any($1)
and (c.start_at between $2 and $3 or c.end_at between $2 and $3)
and (c.tag_id = any($4) or cardinality($4) = 0)

查询计划差异 #

安装auto_explain后观察到，即使查询本身未变，不同执行次数的查询计划存在明显差异：

快速执行时的查询计划 #

Query Parameters: $1 = '{a577b5d0-844d-4207-91fe-87855900f63e}', $2 = '2018-01-01', $3 = '2025-06-01', $4 = '{}'
Nested Loop  (cost=6.16..412.52 rows=39 width=37) (actual time=0.131..0.198 rows=2 loops=1)
  Output: a.id
  Inner Unique: true
  Buffers: shared hit=22
  ->  Nested Loop  (cost=5.88..400.46 rows=39 width=74) (actual time=0.110..0.164 rows=2 loops=1)
        Output: a.id, r.campaign_id
        Inner Unique: true
        Buffers: shared hit=16
        ->  Nested Loop  (cost=5.47..383.34 rows=39 width=74) (actual time=0.087..0.097 rows=2 loops=1)
              Output: a.id, a.response_id
              Buffers: shared hit=8
              ->  Index Scan using idx_b6f7494e6ea56129 on public.question q  (cost=0.29..8.30 rows=1 width=37) (actual time=0.035..0.038 rows=1 loops=1)
                    Output: q.id, q.survey_id, q.project_indicator_id, q.required, q.created_at, q."position", q.disabled_at, q.condition_question_id
                    Index Cond: (q.project_indicator_id = ANY ('{a577b5d0-844d-4207-91fe-87855900f63e}'::text[]))
                    Buffers: shared hit=3
              ->  Bitmap Heap Scan on public.answer a  (cost=5.18..374.06 rows=98 width=111) (actual time=0.040..0.046 rows=2 loops=1)
                    Output: a.id, a.response_id, a.question_id, a.type, a.scale, a.number, a.percent, a.text, a.choice_id
                    Recheck Cond: (a.question_id = q.id)
                    Heap Blocks: exact=2
                    Buffers: shared hit=5
                    ->  Bitmap Index Scan on idx_dadd4a251e27f6bf  (cost=0.00..5.16 rows=98 width=0) (actual time=0.027..0.027 rows=2 loops=1)
                          Index Cond: (a.question_id = q.id)
                          Buffers: shared hit=3
        ->  Index Scan using response_pkey on public.response r  (cost=0.41..0.44 rows=1 width=74) (actual time=0.030..0.030 rows=1 loops=2)
              Output: r.id, r.campaign_id, r.created_at, r.code
              Index Cond: (r.id = a.response_id)
              Buffers: shared hit=8
  ->  Index Scan using data_collect_pkey on public.campaign c  (cost=0.28..0.31 rows=1 width=37) (actual time=0.013..0.013 rows=1 loops=2)
        Output: c.id, c.survey_id, c.start_at, c.title, c.created_at, c.end_at, c.population_size, c.deactivated_at, c.tag_id
        Index Cond: (c.id = r.campaign_id)
        Filter: (((c.start_at >= '2018-01-01'::date) AND (c.start_at <= '2025-06-01'::date)) OR ((c.end_at >= '2018-01-01'::date) AND (c.end_at <= '2025-06-01'::date)))
        Buffers: shared hit=6
Settings: work_mem = '512MB', temp_buffers = '512MB', max_parallel_workers = '16', max_parallel_workers_per_gather = '4', parallel_leader_participation = 'off', geqo_effort = '7'

缓慢执行时的查询计划 #

Query Parameters: $1 = '{a577b5d0-844d-4207-91fe-87855900f63e}', $2 = '2018-01-01', $3 = '2025-06-01', $4 = '{}'
Hash Join  (cost=88.33..384.05 rows=1 width=37) (actual time=965.921..2169.968 rows=2 loops=1)
  Output: a.id
  Inner Unique: true
  Hash Cond: (a.question_id = q.id)
  Buffers: shared hit=229624 read=15859
  ->  Nested Loop  (cost=0.71..295.47 rows=366 width=74) (actual time=16.525..2074.728 rows=616974 loops=1)
        Output: a.id, a.question_id
        Buffers: shared hit=229621 read=15859
        ->  Nested Loop  (cost=0.29..231.75 rows=27 width=37) (actual time=11.336..108.081 rows=46372 loops=1)
              Output: r.id
              Buffers: shared hit=9162 read=744
              ->  Seq Scan on public.campaign c  (cost=0.00..97.49 rows=1 width=37) (actual time=3.685..6.152 rows=1687 loops=1)
                    Output: c.id, c.survey_id, c.start_at, c.title, c.created_at, c.end_at, c.population_size, c.deactivated_at, c.tag_id
                    Filter: ((((c.start_at >= $2) AND (c.start_at <= $3)) OR ((c.end_at >= $2) AND (c.end_at <= $3))) AND ((c.tag_id = ANY ($4)) OR (cardinality($4) = 0)))
                    Rows Removed by Filter: 6
                    Buffers: shared hit=1 read=33
              ->  Index Scan using idx_3e7b0bfbf639f774 on public.response r  (cost=0.29..133.79 rows=47 width=74) (actual time=0.016..0.056 rows=27 loops=1687)
                    Output: r.id, r.campaign_id, r.created_at, r.code
                    Index Cond: (r.campaign_id = c.id)
                    Buffers: shared hit=9161 read=711
        ->  Index Scan using idx_dadd4a25fbf32840 on public.answer a  (cost=0.42..2.13 rows=23 width=111) (actual time=0.022..0.040 rows=13 loops=46372)
              Output: a.id, a.response_id, a.question_id, a.type, a.scale, a.number, a.percent, a.text, a.choice_id
              Index Cond: (a.response_id = r.id)
              Buffers: shared hit=220459 read=15115
  ->  Hash  (cost=87.46..87.46 rows=13 width=37) (actual time=0.037..0.039 rows=1 loops=1)
        Output: q.id
        Buckets: 1024  Batches: 1  Memory Usage: 9kB
        Buffers: shared hit=3
        ->  Bitmap Heap Scan on public.question q  (cost=42.93..87.46 rows=13 width=37) (actual time=0.032..0.033 rows=1 loops=1)
              Output: q.id
              Recheck Cond: (q.project_indicator_id = ANY ($1))
              Heap Blocks: exact=1
              Buffers: shared hit=3
              ->  Bitmap Index Scan on idx_b6f7494e6ea56129  (cost=0.00..42.93 rows=13 width=0) (actual time=0.025..0.025 rows=1 loops=1)
                    Index Cond: (q.project_indicator_id = ANY ($1))
                    Buffers: shared hit=2
Settings: work_mem = '512MB', temp_buffers = '512MB', max_parallel_workers = '16', max_parallel_workers_per_gather = '4', parallel_leader_participation = 'off', geqo_effort = '7'

内容的提问来源于stack exchange，提问作者Paul Le Corre