PostgreSQL索引磁盘读取过慢，如何提升至900MB/s？

DevOps 小满

2026-6-21

问题描述

数据库规模过大，索引无法全部放入内存，查询时索引从磁盘读取速度仅约2MB/s，远低于Azure P80磁盘理论支持的900MB/s。
测试验证：执行2个不同表的并行查询时，磁盘读取速度可达4MB/s；但执行2个同表并行查询时，速度仍维持2MB/s，确认问题源于索引本身而非磁盘性能上限。
通过执行计划确认：索引未命中缓存时查询极慢，索引全部加载至RAM时查询瞬间完成。
环境配置：Azure VM E16s_v3，PostgreSQL 12，P80磁盘。

涉及的分区表DDL如下：

-- public."F_TDLJ_HIST_1" definition

-- Drop table

-- DROP TABLE public."F_TDLJ_HIST_1";

CREATE TABLE public."F_TDLJ_HIST_1" (
    "ID_TRAIN" int4 NOT NULL,
    "ID_JOUR" int4 NOT NULL,
    "ID_LEG" int4 NOT NULL,
    "JX" int4 NOT NULL,
    "RES" int4 NULL,
    "REV" float8 NULL,
    "CAPA" int4 NULL,
    "OFFRE" int4 NULL,
    CONSTRAINT "F_TDLJ_HIST_1_OLDP_pkey" PRIMARY KEY ("ID_TRAIN", "ID_JOUR", "ID_LEG", "JX")
)
PARTITION BY RANGE ("ID_JOUR");
CREATE INDEX "F_TDLJ_HIST_1_OLDP_ID_JOUR_JX_idx" ON ONLY public."F_TDLJ_HIST_1" USING btree ("ID_JOUR", "JX");
CREATE INDEX "F_TDLJ_HIST_1_OLDP_ID_JOUR_idx" ON ONLY public."F_TDLJ_HIST_1" USING btree ("ID_JOUR");
CREATE INDEX "F_TDLJ_HIST_1_OLDP_ID_LEG_idx" ON ONLY public."F_TDLJ_HIST_1" USING btree ("ID_LEG");
CREATE INDEX "F_TDLJ_HIST_1_OLDP_ID_TRAIN_idx" ON ONLY public."F_TDLJ_HIST_1" USING btree ("ID_TRAIN");
CREATE INDEX "F_TDLJ_HIST_1_OLDP_JX_idx" ON ONLY public."F_TDLJ_HIST_1" USING btree ("JX");


-- public."F_TDLJ_HIST_1" foreign keys

ALTER TABLE public."F_TDLJ_HIST_1" ADD CONSTRAINT "F_TDLJ_HIST_1_OLDP_ID_JOUR_fkey" FOREIGN KEY ("ID_JOUR") REFERENCES public."D_JOUR"("ID_JOUR");
ALTER TABLE public."F_TDLJ_HIST_1" ADD CONSTRAINT "F_TDLJ_HIST_1_OLDP_ID_LEG_fkey" FOREIGN KEY ("ID_LEG") REFERENCES public."D_OD"("ID_OD");
ALTER TABLE public."F_TDLJ_HIST_1" ADD CONSTRAINT "F_TDLJ_HIST_1_OLDP_ID_TRAIN_fkey" FOREIGN KEY ("ID_TRAIN") REFERENCES public."D_TRAIN"("ID_TRAIN");
ALTER TABLE public."F_TDLJ_HIST_1" ADD CONSTRAINT "F_TDLJ_HIST_1_OLDP_JX_fkey" FOREIGN KEY ("JX") REFERENCES public."D_JX"("JX");

调整random_page_cost后的查询语句：

set track_io_timing=TRUE; set random_page_cost = 10000000;
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)(
    select "ID_TRAIN", "ID_JOUR", "JX", "ID_LEG", "RES", "REV" from "F_TDLJ_HIST_1" fth
    inner join "D_TRAIN" using ("ID_TRAIN")
    inner join "D_ENTNAT" using ("ID_ENTNAT")
    where "ENTITY" = 'LOIREPARIS' and "ID_JOUR" between 4770 and 4820 and "JX" between -92 and 1
);

对应的执行计划：

Nested Loop  (cost=2.63..30525.21 rows=38926 width=28) (actual time=80.858..884011.228 rows=304556 loops=1)
  Buffers: shared hit=35897 read=255071
  I/O Timings: read=879812.508
  ->  Nested Loop  (cost=2.07..60.13 rows=35 width=4) (actual time=0.136..1.552 rows=272 loops=1)
        Buffers: shared hit=71
        ->  Index Scan using "UX_ENTNAT" on "D_ENTNAT"  (cost=0.27..2.49 rows=1 width=4) (actual time=0.058..0.060 rows=1 loops=1)
              Index Cond: (("ENTITY")::text = 'LOIREPARIS'::text)
              Buffers: shared hit=3
        ->  Bitmap Heap Scan on "D_TRAIN"  (cost=1.80..57.11 rows=53 width=8) (actual time=0.073..1.249 rows=272 loops=1)
              Recheck Cond: ("ID_ENTNAT" = "D_ENTNAT"."ID_ENTNAT")
              Heap Blocks: exact=65
              Buffers: shared hit=68
              ->  Bitmap Index Scan on "fki_D_TRAIN_ID_ENTNAT_fkey"  (cost=0.00..1.78 rows=53 width=0) (actual time=0.040..0.041 rows=272 loops=1)
                    Index Cond: ("ID_ENTNAT" = "D_ENTNAT"."ID_ENTNAT")
                    Buffers: shared hit=3
  ->  Index Scan using "F_TDLJ_HIST_1_OLDP_p4770_pkey" on "F_TDLJ_HIST_1_OLDP_p4770" fth  (cost=0.56..778.42 rows=9201 width=28) (actual time=4.305..3248.805 rows=1120 loops=272)
        Index Cond: (("ID_TRAIN" = "D_TRAIN"."ID_TRAIN") AND ("ID_JOUR" >= 4770) AND ("ID_JOUR" <= 4820) AND ("JX" >= '-92'::integer) AND ("JX" <= 1))
        Buffers: shared hit=35826 read=255071
        I/O Timings: read=879812.508
Settings: effective_cache_size = '96GB', effective_io_concurrency = '200', max_parallel_workers_per_gather = '4', random_page_cost = '1e+07', search_path = 'public', work_mem = '64MB'
Planning Time: 185.491 ms
Execution Time: 884168.159 ms

优化建议

1. 重构索引，将随机IO转为顺序IO

当前查询使用主键索引（ID_TRAIN, ID_JOUR, ID_LEG, JX），但查询条件未包含ID_LEG，导致索引扫描时需要频繁跳转到不同的ID_LEG分支，产生大量随机IO，这是磁盘读取速度极低的核心原因。

创建匹配查询条件的覆盖索引：

-- 为每个分区创建（或通过主表自动继承）
CREATE INDEX idx_f_tdlj_hist_train_jour_jx ON public."F_TDLJ_HIST_1" USING btree ("ID_TRAIN", "ID_JOUR", "JX")
INCLUDE ("ID_LEG", "RES", "REV");

索引顺序完全匹配查询的过滤条件（ID_TRAIN, ID_JOUR, JX），避免随机跳转。
通过INCLUDE子句包含查询需要返回的字段，实现索引覆盖扫描，无需回表读取数据，进一步减少IO操作。

2. 调整PostgreSQL IO参数

提升并发IO能力：对于Azure P80高IOPS磁盘，调大effective_io_concurrency允许同时发起更多IO请求：
```
ALTER SYSTEM SET effective_io_concurrency = 500;
SELECT pg_reload_conf();
```
优化缓存配置：E16s_v3拥有64GB内存，将shared_buffers设为物理内存的1/4（16GB），提升缓存命中率：
```
ALTER SYSTEM SET shared_buffers = '16GB';
-- 需重启PostgreSQL生效
```
加速索引维护：如果需要重建索引，调大maintenance_work_mem：
```
ALTER SYSTEM SET maintenance_work_mem = '8GB';
SELECT pg_reload_conf();
```

3. 利用Azure磁盘特性优化

确保P80磁盘启用高级缓存（Read/Write模式），利用Azure的缓存层加速磁盘读取。
考虑将索引单独部署在独立的P80磁盘上，避免与数据盘的IO资源竞争。

4. 修正执行计划选择

更新统计信息，让PostgreSQL生成更合理的执行计划：
```
ANALYZE public."F_TDLJ_HIST_1";
```

临时测试顺序扫描性能，对比索引扫描：

SET enable_indexscan = OFF;
-- 执行原查询并查看执行时间
EXPLAIN ANALYZE (
    select "ID_TRAIN", "ID_JOUR", "JX", "ID_LEG", "RES", "REV" from "F_TDLJ_HIST_1" fth
    inner join "D_TRAIN" using ("ID_TRAIN")
    inner join "D_ENTNAT" using ("ID_ENTNAT")
    where "ENTITY" = 'LOIREPARIS' and "ID_JOUR" between 4770 and 4820 and "JX" between -92 and 1
);

如果顺序扫描速度显著更快，说明当前索引设计不匹配查询模式，需优先采用覆盖索引优化。

内容的提问来源于stack exchange，提问作者Doe Jowns

PostgreSQL索引磁盘读取过慢，如何提升至900MB/s？

1. 重构索引，将随机IO转为顺序IO #

2. 调整PostgreSQL IO参数 #

3. 利用Azure磁盘特性优化 #

4. 修正执行计划选择 #

1. 重构索引，将随机IO转为顺序IO

2. 调整PostgreSQL IO参数

3. 利用Azure磁盘特性优化

4. 修正执行计划选择