PostgreSQL查询优化：列车统计数据查询性能优化咨询

问题背景 #

现有两张表：

• Trains表：固定209条数据
• Passages表：当前11620条数据，每日新增约300条

需求：获取每列列车的统计数据，包括最后一次通行日期和总通行次数。

表结构 #

train表结构 #

+----+--------+----------+---------------+------------------+
| id | number | depot_id | train_type_id | wheelset_type_id |
+----+--------+----------+---------------+------------------+

passages表结构 #

+----+-------+--------------+-----------+
| id | speed | train_number | date_time |
+----+-------+--------------+-----------+

原查询及性能问题 #

当前使用的查询语句：

SELECT
  DISTINCT ON (train.id)
  train.id,
  train.number,
  train.train_type_id,
  train.wheelset_type_id,
  passages.id as passage_id,
  passages.date_time,
  (
    SELECT count(id) FROM passages WHERE train_number = train.number
  ) AS passage_count
FROM train
INNER JOIN passages ON passages.train_number=train.number
ORDER BY train.id, passages.date_time DESC;

通过EXPLAIN分析，该查询耗时约6.5秒，性能瓶颈集中在子查询：

(select count(id) from passages where train_number = train.number) as passage_count

从执行计划可见，这个关联子查询被执行了11283次，每次都对passages表做全表扫描，导致大量重复IO操作，是性能低下的核心原因。

优化方案 #

1. 改写查询，避免重复扫描 #

通过预聚合Passages表的统计数据，只扫描Passages表1-2次即可完成计算，大幅降低IO开销。

方案一：使用CTE一次性聚合统计数据

WITH passage_stats AS (
    SELECT
        train_number,
        COUNT(*) AS passage_count,
        MAX(date_time) AS last_passage_time,
        FIRST_VALUE(id) OVER (PARTITION BY train_number ORDER BY date_time DESC) AS passage_id
    FROM passages
    GROUP BY train_number
)
SELECT
    t.id,
    t.number,
    t.train_type_id,
    t.wheelset_type_id,
    ps.passage_id,
    ps.last_passage_time AS date_time,
    ps.passage_count
FROM train t
JOIN passage_stats ps ON t.number = ps.train_number;

方案二：拆分最新记录与统计数的CTE

WITH passage_latest AS (
    SELECT DISTINCT ON (train_number)
        train_number,
        id AS passage_id,
        date_time
    FROM passages
    ORDER BY train_number, date_time DESC
),
passage_counts AS (
    SELECT
        train_number,
        COUNT(*) AS passage_count
    FROM passages
    GROUP BY train_number
)
SELECT
    t.id,
    t.number,
    t.train_type_id,
    t.wheelset_type_id,
    pl.passage_id,
    pl.date_time,
    pc.passage_count
FROM train t
JOIN passage_latest pl ON t.number = pl.train_number
JOIN passage_counts pc ON t.number = pc.train_number;

2. 添加索引加速查询 #

给passages表创建复合索引，覆盖train_number、date_time（降序）和id，这样无论是获取最新记录还是统计数量，都能直接通过索引完成，无需全表扫描：

CREATE INDEX idx_passages_train_number_date ON passages (train_number, date_time DESC, id);

添加索引后，上述优化后的查询性能会进一步提升，同时原查询的子查询也会利用索引减少扫描时间。

原查询执行计划 #

"QUERY PLAN"
"Unique  (cost=2547558.08..2547616.12 rows=211 width=36) (actual time=6845.152..6846.093 rows=109 loops=1)"
"  Output: train.id, train.number, train.train_type_id, train.wheelset_type_id, passages.id, passages.date_time, ((SubPlan 1))"
"  Buffers: shared hit=835018"
"  ->  Sort  (cost=2547558.08..2547587.10 rows=11608 width=36) (actual time=6845.151..6845.547 rows=11283 loops=1)"
"        Output: train.id, train.number, train.train_type_id, train.wheelset_type_id, passages.id, passages.date_time, ((SubPlan 1))"
"        Sort Key: train.id, passages.date_time DESC"
"        Sort Method: quicksort  Memory: 1266kB"
"        Buffers: shared hit=835018"
"        ->  Hash Join  (cost=6.75..2546774.38 rows=11608 width=36) (actual time=0.840..6839.787 rows=11283 loops=1)"
"              Output: train.id, train.number, train.train_type_id, train.wheelset_type_id, passages.id, passages.date_time, (SubPlan 1)"
"              Hash Cond: (passages.train_number = train.number)"
"              Buffers: shared hit=835018"
"              ->  Seq Scan on public.passages  (cost=0.00..190.08 rows=11608 width=16) (actual time=0.010..0.827 rows=11608 loops=1)"
"                    Output: passages.id, passages.speed, passages.train_number, passages.system_id, passages.orientation, passages.date_time"
"                    Buffers: shared hit=74"
"              ->  Hash  (cost=4.11..4.11 rows=211 width=16) (actual time=0.049..0.050 rows=211 loops=1)"
"                    Output: train.id, train.number, train.train_type_id, train.wheelset_type_id"
"                    Buckets: 1024  Batches: 1  Memory Usage: 18kB"
"                    Buffers: shared hit=2"
"                    ->  Seq Scan on public.train  (cost=0.00..4.11 rows=211 width=16) (actual time=0.009..0.027 rows=211 loops=1)"
"                          Output: train.id, train.number, train.train_type_id, train.wheelset_type_id"
"                          Buffers: shared hit=2"
"              SubPlan 1"
"                ->  Aggregate  (cost=219.36..219.37 rows=1 width=8) (actual time=0.605..0.605 rows=1 loops=11283)"
"                      Output: count(passages_1.id)"
"                      Buffers: shared hit=834942"
"                      ->  Seq Scan on public.passages passages_1  (cost=0.00..219.10 rows=103 width=4) (actual time=0.051..0.594 rows=162 loops=11283)"
"                            Output: passages_1.id, passages_1.speed, passages_1.train_number, passages_1.system_id, passages_1.orientation, passages_1.date_time"
"                            Filter: (passages_1.train_number = train.number)"
"                            Rows Removed by Filter: 11446"
"                            Buffers: shared hit=834942"
"Planning Time: 0.166 ms"
"Execution Time: 6846.292 ms"

train表单独查询执行计划 #

"QUERY PLAN"
"Seq Scan on public.train  (cost=0.00..4.11 rows=211 width=20) (actual time=0.014..0.024 rows=211 loops=1)"
"  Output: id, number, depot_id, train_type_id, wheelset_type_id"
"  Buffers: shared hit=2"
"Planning Time: 0.081 ms"
"Execution Time: 0.040 ms"

passages表单独查询执行计划 #

"QUERY PLAN"
"Seq Scan on public.passages  (cost=0.00..190.08 rows=11608 width=32) (actual time=0.009..0.598 rows=11608 loops=1)"
"  Output: id, speed, train_number, system_id, orientation, date_time"
"  Buffers: shared hit=74"
"Planning Time: 0.050 ms"
"Execution Time: 0.891 ms"

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