PostgreSQL超大规模数据表分组查询性能优化咨询

初始场景 #

数据表recorder.records核心字段：

• id：uuid类型，主键
• channel_number：smallint类型，非空（原描述中number）
• rec_end：timestamptz类型，非空（原描述中end）

数据表总大小44GB（含其他列），共约3300万条数据，现有索引：

• id列主键索引
• channel_number单列索引
• rec_end单列索引

执行查询语句：

SELECT
    channel_number AS key,
    MAX(rec_end) AS value
FROM
    recorder.records
GROUP BY
    channel_number

初始查询耗时2分30秒，通过pg_activity发现耗时集中在DataFileRead阶段。曾尝试创建channel_number与rec_end的联合索引，无明显效果。

初始EXPLAIN分析结果 #

Finalize GroupAggregate  (cost=1579928.34..1579935.14 rows=272 width=10) (actual time=122799.356..122800.074 rows=484 loops=1)
  Output: channel_number, max(rec_end)
  Group Key: records.channel_number
  Buffers: shared hit=47698 read=405990
  ->  Sort  (cost=1579928.34..1579929.70 rows=544 width=10) (actual time=122799.341..122799.516 rows=1402 loops=1)
        Output: channel_number, (PARTIAL max(rec_end))
        Sort Key: records.channel_number
        Sort Method: quicksort  Memory: 114kB
        Buffers: shared hit=47698 read=405990
        ->  Gather  (cost=1579846.50..1579903.62 rows=544 width=10) (actual time=122797.253..122798.243 rows=1402 loops=1)
              Output: channel_number, (PARTIAL max(rec_end))
              Workers Planned: 2
              Workers Launched: 2
              Buffers: shared hit=47695 read=405990
              ->  Partial HashAggregate  (cost=1578846.50..1578849.22 rows=272 width=10) (actual time=122790.051..122790.227 rows=467 loops=3)
                    Output: channel_number, PARTIAL max(rec_end)
                    Group Key: records.channel_number
                    Buffers: shared hit=142755 read=1224460
                    Worker 0: actual time=122786.935..122787.107 rows=463 loops=1
                      Buffers: shared hit=47780 read=410936
                    Worker 1: actual time=122786.704..122786.866 rows=466 loops=1
                      Buffers: shared hit=47280 read=407534
                    ->  Parallel Seq Scan on recorder.records  (cost=0.00..1508302.67 rows=14108767 width=10) (actual time=0.109..117323.421 rows=11287013 loops=3)
                          Output: channel_number, rec_end
                          Buffers: shared hit=142755 read=1224460
                          Worker 0: actual time=0.124..117335.992 rows=11353602 loops=1
                            Buffers: shared hit=47780 read=410936
                          Worker 1: actual time=0.133..117377.755 rows=11262479 loops=1
                            Buffers: shared hit=47280 read=407534
Planning time: 1.347 ms
Execution time: 122855.164 ms

优化操作 #

执行全表清理与统计更新：

VACUUM (VERBOSE, ANALYZE) recorder.records;

VACUUM执行输出 #

vacuuming "recorder.records"
relation "records" page 1367214 is uninitialized --- fixing
scanned index "idxRecordsStart" to remove 176561 row versions
scanned index "idxRecordEnd" to remove 176561 row versions
scanned index "idxRecordChanName" to remove 176561 row versions
scanned index "idxRecordChanNumber" to remove 176561 row versions
scanned index "idxRecordFromId" to remove 176561 row versions
scanned index "idxRecordStart" to remove 176561 row versions
scanned index "idxRecordToId" to remove 176561 row versions
scanned index "records_pkey" to remove 176561 row versions
scanned index "idx_selectlastactivities" to remove 176561 row versions
"records": removed 176561 row versions in 100289 pages
index "idxRecordsStart" now contains 33861036 row versions in 93797 pages
index "idxRecordEnd" now contains 33861036 row versions in 93789 pages
index "idxRecordChanName" now contains 33861036 row versions in 132796 pages
index "idxRecordChanNumber" now contains 33861038 row versions in 93941 pages
index "idxRecordFromId" now contains 33861038 row versions in 124531 pages
index "idxRecordStart" now contains 33861036 row versions in 93655 pages
index "idxRecordToId" now contains 33861036 row versions in 99374 pages
index "records_pkey" now contains 33861041 row versions in 129580 pages
index "idx_selectlastactivities" now contains 33861038 row versions in 130382 pages
"records": found 25116 removable, 19977336 nonremovable row versions in 860585 out of 1367215 pages
vacuuming "pg_toast.pg_toast_16417"
relation "pg_toast.pg_toast_16417" page 3618915 is uninitialized --- fixing
...
snip
...
relation "pg_toast.pg_toast_16417" page 3619248 is uninitialized --- fixing
scanned index "pg_toast_16417_index" to remove 380 row versions
"pg_toast.pg_toast_16417": removed 380 row versions in 98 pages
index "pg_toast_16417_index" now contains 14549648 row versions in 39922 pages
"pg_toast.pg_toast_16417": found 380 removable, 11057108 nonremovable row versions in 2753990 out of 3619249 pages
analyzing "recorder.records"
"records": scanned 30000 of 1367215 pages, containing 742819 live rows and 0 dead rows; 30000 rows in sample, 32553901 estimated total rows

优化效果 #

执行VACUUM后，首次查询耗时10秒，第二次查询（数据缓存后）耗时3秒，性能提升显著。

优化后EXPLAIN分析结果 #

Finalize GroupAggregate  (cost=1000.59..888879.49 rows=282 width=10) (actual time=130.517..8604.517 rows=484 loops=1)
  Output: channel_number, max(rec_end)
  Group Key: records.channel_number
  Buffers: shared hit=2504441 read=9462
  ->  Gather Merge  (cost=1000.59..888873.85 rows=564 width=10) (actual time=130.301..8603.054 rows=1066 loops=1)
        Output: channel_number, (PARTIAL max(rec_end))
        Workers Planned: 2
        Workers Launched: 2
        Buffers: shared hit=2504441 read=9462
        ->  Partial GroupAggregate  (cost=0.56..887808.73 rows=282 width=10) (actual time=31.150..7326.359 rows=355 loops=3)
              Output: channel_number, PARTIAL max(rec_end)
              Group Key: records.channel_number
              Buffers: shared hit=12782952 read=52399
              Worker 0: actual time=46.750..8518.649 rows=410 loops=1
                Buffers: shared hit=5149485 read=21530
              Worker 1: actual time=46.631..8518.263 rows=357 loops=1
                Buffers: shared hit=5129026 read=21407
              ->  Parallel Index Only Scan using idx_selectlastactivities on recorder.records  (cost=0.56..819985.28 rows=13564125 width=10) (actual time=0.078..5663.425 rows=11287013 loops=3)
                    Output: channel_number, rec_end
                    Heap Fetches: 0
                    Buffers: shared hit=12782952 read=52399
                    Worker 0: actual time=0.091..6558.824 rows=13998677 loops=1
                      Buffers: shared hit=5149485 read=21530
                    Worker 1: actual time=0.101..6538.998 rows=13952277 loops=1
                      Buffers: shared hit=5129026 read=21407
Planning time: 0.304 ms
Execution time: 8680.485 ms

关键优化点说明 #

1. 清理无效数据：VACUUM移除了17万+死元组，减少磁盘读取的无效数据量
2. 更新统计信息：ANALYZE让查询优化器获取到准确的数据分布，选择了更优的执行计划，从全表扫描切换为索引仅扫描
3. 高效索引利用：优化后查询直接从idx_selectlastactivities索引获取所需字段，无需回表，大幅降低IO开销

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