PostgreSQL12 108GB分区大表查询耗时3分钟，求优化方案

算力阿峰

2026-10-5

PostgreSQL12分区大表查询性能优化方案

我们使用PostgreSQL12数据库，存在一张含索引总大小为108GB的大表，因查询速度慢尝试对表进行分区，但未解决性能问题。该表已经是按月聚合的汇总表，由于支持多维度过滤无法进一步聚合，表中identity相关列构成唯一键。
当前慢查询语句如下：

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
SELECT
    val_a AS x,
    val_b AS y,
    SUM(value) AS value
FROM
    test_table_tp
WHERE
    client = '767jjDHIPLkshj'
    AND identity_1 = '12edfdijijasd'
    AND identity_2 = '98jjaskhuUUHss'
    AND identity_3 = 1
    AND date_col BETWEEN '2021-04-01'::date AND ('2021-07-01'::date + Interval '1 day')::date
GROUP BY
    val_a,
    val_b;

该查询执行耗时约3分钟，执行计划如下：

->  HashAggregate  (cost=260438.41..260718.30 rows=27989 width=16) (actual time=214427.239..214427.614 rows=1298 loops=1)
               Output: test_table_tp.val_a, test_table_tp.val_b, sum(test_table_tp.value)
               Group Key: test_table_tp.val_a, test_table_tp.val_b
               Buffers: shared hit=216750 read=331936
               I/O Timings: read=211977.843
               ->  Append  (cost=0.56..258339.24 rows=279890 width=12) (actual time=3.057..213976.334 rows=722233 loops=1)
                     Buffers: shared hit=216750 read=331936
                     I/O Timings: read=211977.843
                     ->  Index Scan using idx_202104_108521 on public.test_table_tp_p2021_04_108521 test_table_tp  (cost=0.56..71154.06 rows=83535 width=12) (actual time=3.056..69033.315 rows=216908 loops=1)
                           Output: test_table_tp.val_a, test_table_tp.val_b, test_table_tp.value
                           Index Cond: (((test_table_tp.client)::text = '767jjDHIPLkshj'::text) AND ((test_table_tp.identity_1)::text = '12edfdijijasd'::text) AND ((test_table_tp.identity_2)::text = '98jjaskhuUUHss'::text) AND (test_table_tp.identity_3 = 1) AND (test_table_tp.date_col >= '2021-04-01'::date) AND (test_table_tp.date_col <= '2021-07-02'::date))
                           Buffers: shared hit=68990 read=96437
                           I/O Timings: read=68466.167
                     ->  Index Scan using idx_202105_108553 on public.test_table_tp_p2021_05_108553 test_table_tp_1  (cost=0.56..51361.84 rows=55441 width=12) (actual time=8.641..55685.999 rows=160618 loops=1)
                           Output: test_table_tp_1.val_a, test_table_tp_1.val_b, test_table_tp_1.value
                           Index Cond: (((test_table_tp_1.client)::text = '767jjDHIPLkshj'::text) AND ((test_table_tp_1.identity_1)::text = '12edfdijijasd'::text) AND ((test_table_tp_1.identity_2)::text = '98jjaskhuUUHss'::text) AND (test_table_tp_1.identity_3 = 1) AND (test_table_tp_1.date_col >= '2021-04-01'::date) AND (test_table_tp_1.date_col <= '2021-07-02'::date))
                           Buffers: shared hit=48314 read=69911
                           I/O Timings: read=55277.406
                     ->  Index Scan using idx_202106_108585 on public.test_table_tp_p2021_06_108585 test_table_tp_2  (cost=0.56..63581.82 rows=66779 width=12) (actual time=2.870..48249.339 rows=188842 loops=1)
                           Output: test_table_tp_2.val_a, test_table_tp_2.val_b, test_table_tp_2.value
                           Index Cond: (((test_table_tp_2.client)::text = '767jjDHIPLkshj'::text) AND ((test_table_tp_2.identity_1)::text = '12edfdijijasd'::text) AND ((test_table_tp_2.identity_2)::text = '98jjaskhuUUHss'::text) AND (test_table_tp_2.identity_3 = 1) AND (test_table_tp_2.date_col >= '2021-04-01'::date) AND (test_table_tp_2.date_col <= '2021-07-02'::date))
                           Buffers: shared hit=54983 read=90249
                           I/O Timings: read=47732.316
                     ->  Index Scan using idx_202107_108617 on public.test_table_tp_p2021_07_108617 test_table_tp_3  (cost=0.56..70842.08 rows=74135 width=12) (actual time=2.849..40902.561 rows=155865 loops=1)
                           Output: test_table_tp_3.val_a, test_table_tp_3.val_b, test_table_tp_3.value
                           Index Cond: (((test_table_tp_3.client)::text = '767jjDHIPLkshj'::text) AND ((test_table_tp_3.identity_1)::text = '12edfdijijasd'::text) AND ((test_table_tp_3.identity_2)::text = '98jjaskhuUUHss'::text) AND (test_table_tp_3.identity_3 = 1) AND (test_table_tp_3.date_col >= '2021-04-01'::date) AND (test_table_tp_3.date_col <= '2021-07-02'::date))
                           Buffers: shared hit=44463 read=75339
                           I/O Timings: read=40501.954
             Planning Time: 18.081 ms
             Execution Time: 214427.963 ms
 Planning Time: 0.083 ms
 Execution Time: 214461.427 ms
(41 rows)

Time: 214462.391 ms (03:34.462)

优化方案

从执行计划可以看出，99%的耗时来自磁盘I/O，总计读取331936个数据块，I/O耗时达211977ms，可通过以下方案优化：

创建覆盖索引消除回表
当前索引仅包含过滤条件列，每次索引扫描后都需要回表读取val_a、val_b、value三个列，产生大量随机I/O。可在每个分区上创建覆盖索引，直接通过索引获取所有查询需要的字段：
```
-- 每个分区按如下格式创建索引，也可以直接在父表创建后继承到分区
CREATE INDEX idx_covering_test ON test_table_tp (client, identity_1, identity_2, identity_3, date_col) INCLUDE (val_a, val_b, value);
```
创建后索引扫描直接返回所有需要的字段，无需回表，I/O量可降低60%以上。
调整数据库内存配置
当前缓存命中率仅39%（216750/(216750+331936)），大部分数据需要从磁盘读取：
- 调整shared_buffers为系统内存的25%，增大数据库缓存空间，提升热点数据命中率
- 调整effective_cache_size为系统内存的75%，帮助优化器更准确判断索引可用性
- 适当调大work_mem，避免哈希聚合出现磁盘临时文件，提升聚合计算速度
对表按过滤维度聚簇
因为查询过滤条件固定为client、identity_1、identity_2、identity_3等值，可以使用CLUSTER命令按上述覆盖索引对表进行物理排序：
```
CLUSTER test_table_tp USING idx_covering_test;
```
聚簇后相同过滤条件的行在磁盘上连续存储，将随机I/O转换为顺序I/O，扫描速度可提升数倍。注意聚簇操作会锁表，需要在业务低峰期执行。
更新统计信息
执行计划中估算扫描行数为279890，实际返回行数为722233，误差超过150%，会导致优化器生成次优执行计划。执行如下命令更新表统计信息：
```
ANALYZE VERBOSE test_table_tp;
```

高频查询场景新增物化视图
如果该类查询属于高频访问场景，可以创建预聚合的物化视图，按查询维度提前聚合结果，定时刷新：

CREATE MATERIALIZED VIEW mv_test_agg AS
SELECT client, identity_1, identity_2, identity_3, date_col, val_a, val_b, SUM(value) AS value
FROM test_table_tp
GROUP BY client, identity_1, identity_2, identity_3, date_col, val_a, val_b;

-- 创建索引加速物化视图查询
CREATE INDEX idx_mv_filter ON mv_test_agg (client, identity_1, identity_2, identity_3, date_col);

查询时直接访问物化视图，可将查询耗时降低到毫秒级。

内容的提问来源于stack exchange，提问作者success malla

PostgreSQL12 108GB分区大表查询耗时3分钟，求优化方案

优化方案 #

优化方案