PostgreSQL 15千万级传感器数据聚合查询性能优化求助
2026-8-6
PostgreSQL 15聚合查询性能优化排查
问题背景
现有存储传感器数据的表ts_number，包含约1078万条记录（仅1073条val为NULL），表结构及索引如下：
CREATE TABLE public.ts_number ( id int4 NOT NULL, ts int8 NOT NULL, val float4 NULL, CONSTRAINT ts_number_pkey PRIMARY KEY (id, ts) ); CREATE INDEX ts_number_id_idx ON public.ts_number USING btree (id, ts, val);
需求是将所有数据按1分钟间隔聚合，原查询语句：
select date_trunc('minute', to_timestamp(tn.ts / 1000)) as "time", tn.id, round(avg(tn.val::numeric), 1) as value from ts_number tn where tn.val is not null group by tn.id, date_trunc('minute', to_timestamp(tn.ts / 1000)) order by date_trunc('minute', to_timestamp(tn.ts / 1000));
原查询性能瓶颈分析
关闭JIT后的执行计划显示总耗时约46.6秒，核心瓶颈如下：
QUERY PLAN | ----------------------------------------------------------------------------------------------------------------------------------------+ Sort (cost=1438043.51..1448107.59 rows=4025633 width=44) (actual time=45605.997..46283.850 rows=3507690 loops=1) | Sort Key: (date_trunc('minute'::text, to_timestamp(((ts / 1000))::double precision))) | Sort Method: external merge Disk: 92136kB | Buffers: shared hit=32 read=79640, temp read=56259 written=100732 | I/O Timings: shared/local read=12067.837, temp read=1178.562 write=3915.268 | -> HashAggregate (cost=707851.61..923882.68 rows=4025633 width=44) (actual time=29691.221..42565.892 rows=3507690 loops=1) | Group Key: date_trunc('minute'::text, to_timestamp(((ts / 1000))::double precision)), id | Planned Partitions: 32 Batches: 33 Memory Usage: 33041kB Disk Usage: 368648kB | Buffers: shared hit=32 read=79640, temp read=44742 written=89197 | I/O Timings: shared/local read=12067.837, temp read=1147.856 write=3727.306 | -> Seq Scan on ts_number tn (cost=0.00..295394.36 rows=10785399 width=16) (actual time=6.016..18958.130 rows=10785155 loops=1)| Filter: (val IS NOT NULL) | Rows Removed by Filter: 1073 | Buffers: shared hit=32 read=79640 | I/O Timings: shared/local read=12067.837 | Planning Time: 0.289 ms | Execution Time: 46617.899 ms |
- 全表扫描IO开销：
val is not null仅排除1073行，优化器选择全表扫描，读取79640个共享缓冲区耗时约12秒，是查询的基础IO成本。 - HashAggregate磁盘溢出：生成350多万个分组时，
work_mem（10MB）不足以容纳所有分组，导致HashAggregate拆分33个批次，磁盘写入约360MB，这部分IO耗时约22秒。 - 排序磁盘溢出：最终对350多万行结果排序时，内存不足触发外部合并排序，额外增加IO耗时。
计算列优化后的性能分析
添加计算列ts_time并创建覆盖索引后，总耗时降至约35秒，但仍存在明显问题：
QUERY PLAN | -----------------------------------------------------------------------------------------------------------------------------------------------------------+ Finalize GroupAggregate (cost=260210.24..262870.90 rows=10200 width=20) (actual time=28025.740..34159.907 rows=3507690 loops=1) | Group Key: ts_time, id | Buffers: shared hit=86581 read=93231, temp read=31437 written=32171 | I/O Timings: shared/local read=46057.413, temp read=121.378 write=1017.921 | -> Gather Merge (cost=260210.24..262590.40 rows=20400 width=44) (actual time=28025.718..31512.629 rows=3738515 loops=1) | Workers Planned: 2 | Workers Launched: 2 | Buffers: shared hit=86581 read=93231, temp read=31437 written=32171 | I/O Timings: shared/local read=46057.413, temp read=121.378 write=1017.921 | -> Sort (cost=259210.21..259235.71 rows=10200 width=44) (actual time=23712.198..24001.505 rows=1246172 loops=3) | Sort Key: ts_time, id | Sort Method: external merge Disk: 90712kB | Buffers: shared hit=86581 read=93231, temp read=31437 written=32171 | I/O Timings: shared/local read=46057.413, temp read=121.378 write=1017.921 | Worker 0: Sort Method: external merge Disk: 78008kB | Worker 1: Sort Method: external merge Disk: 76384kB | -> Partial HashAggregate (cost=258429.08..258531.08 rows=10200 width=44) (actual time=20173.138..21185.291 rows=1246172 loops=3) | Group Key: ts_time, id | Batches: 5 Memory Usage: 262193kB Disk Usage: 7536kB | Buffers: shared hit=86553 read=93231, temp read=799 written=1530 | I/O Timings: shared/local read=46057.413, temp read=3.658 write=34.272 | Worker 0: Batches: 1 Memory Usage: 237585kB | Worker 1: Batches: 1 Memory Usage: 237585kB | -> Parallel Seq Scan on ts_number tn (cost=0.00..224726.62 rows=4493662 width=16) (actual time=2.821..16943.198 rows=3595052 loops=3)| Filter: (val IS NOT NULL) | Rows Removed by Filter: 358 | Buffers: shared hit=86553 read=93231 | I/O Timings: shared/local read=46057.413 | Planning: | Buffers: shared hit=25 | Planning Time: 22.619 ms | Execution Time: 34999.933 ms |
- 并行扫描导致磁盘IO竞争，共享缓冲区读取耗时从12秒升至46秒，成为新的主要瓶颈；
- 排序和HashAggregate仍存在磁盘溢出，内存不足问题未解决。
进一步优化方案
1. 调高work_mem参数
当前work_mem为10MB，远不足以处理350多万个分组的聚合和排序操作。临时调整可执行：
SET work_mem = '64MB';
全局修改postgresql.conf：
work_mem = 64MB
按25个连接计算，总内存占用为25*64MB=1.6GB，加上shared_buffers的1GB，仍在分配的4GB内存范围内。调高后可避免磁盘溢出，大幅减少IO耗时。
2. 强制使用有序聚合（GroupAggregate）
创建覆盖索引并强制优化器选择索引扫描，让数据按分组键有序读取，避免HashAggregate的开销：
CREATE INDEX idx_ts_number_ts_time_id_val ON public.ts_number USING btree (ts_time, id, val) WHERE val IS NOT NULL; -- 临时禁用全表扫描测试 SET enable_seqscan = off;
此时数据已按ts_time, id排序，可直接使用GroupAggregate，无需Hash和额外排序。
3. 优化计算列生成逻辑
原计算列使用make_interval效率较低，替换为更直接的时间转换：
ALTER TABLE public.ts_number ADD COLUMN ts_time timestamp generated always as ( date_trunc('minute', timestamp '1970-01-01' + ts * interval '1 millisecond') ) stored;
该方式避免了make_interval的函数调用开销，计算更高效。
4. 调整并行扫描参数
当前max_parallel_workers_per_gather为2，可调整为3（服务器为4核CPU，max_parallel_workers为4），提升并行扫描效率：
max_parallel_workers_per_gather = 3
5. 预热缓存
提前将全表数据加载到缓存，后续查询可直接从内存读取：
SELECT * FROM ts_number WHERE val IS NOT NULL;
内容的提问来源于stack exchange，提问作者Andi Depressivum
相关产品推荐
相关产品推荐
