SQL random_page_cost vs seq_page_cost 的 HDD vs SSD vs NVMe 磁盘类型调整

random_page_cost与seq_page_cost的比值直接影响postgresql优化器对索引扫描与顺序扫描的成本权衡，需根据实际存储介质的随机/顺序i/o性能差距（如hdd约4–6、ssd约1.1–1.5、nvme约1.0–1.2）合理设置，否则会导致执行计划误判。

为什么 random_page_cost 和 seq_page_cost 的比值直接影响查询计划？

PostgreSQL 用这两个参数估算磁盘 I/O 成本，优化器靠它决定走索引扫描还是顺序扫描。比值不是“越小越好”或“越大越好”，而是要反映你实际磁盘的随机访问 vs 顺序访问性能差距。HDD 上随机读慢得离谱，NVMe 上这个差距可能只剩 1.2 倍——如果还按默认值 random_page_cost = 4.0、seq_page_cost = 1.0（即 4:1），优化器会严重高估索引扫描代价，该走索引时强行全表扫。

HDD、SSD、NVMe 对应的推荐比值和设置方式

别背数值，看原理：比值 ≈ 实际随机读延迟 ÷ 实际顺序读吞吐等效延迟。实测工具可用 pg_test_fsync 或 hdparm -tT（linux）辅助判断，但更稳的方式是观察慢查询计划是否频繁误判。

• HDD：默认 random_page_cost = 4.0 基本可用，若系统老旧或负载重，可试 4.5～6.0
• 消费级 SSD：通常 random_page_cost = 1.1～1.5 更准（顺序和随机性能差距大幅收窄）
• 企业级 NVMe（如 Intel Optane 或 PCIe 4.0 SSD）：设 random_page_cost = 1.0～1.2，部分场景甚至可设为 1.0（即认为随机/顺序无差别）
• seq_page_cost 一般不动（保持 1.0），调的是比值，不是绝对值

改完参数后必须检查的三件事

改了不生效？或者生效后查询变慢？大概率卡在这几个环节：

• 没执行 SET LOCAL 或改完没 select pg_reload_conf() —— 参数在 postgresql.conf 中修改后需重载，仅 ALTER SYSTEM 不自动触发
• 连接已建立，会沿用旧会话的规划器参数；新参数只影响后续新连接或显式 SET 的会话
• 最常被忽略：某些查询因统计信息过期，导致即使成本算对了，也选错计划。务必配合 ANALYZE 表，尤其在大批量写入后
• 不要全局一刀切：OLAP 大表扫描多，可略降 random_page_cost；OLTP 小事务多索引访问频繁，可略提

用 EXPLAIN (ANALYZE, BUFFERS) 验证是否真起作用

光看 EXPLAIN 输出的“cost”数字没意义，重点对比实际执行路径和 buffer 命中情况：

• 如果之前走 Seq Scan，调低 random_page_cost 后变成 Index Scan 且 actual time 下降 → 调对了
• 如果变成 Index Scan 但 Buffers: shared read=xxx 暴涨（比如从 100 跳到 5000），说明索引选择率差，物理随机读反而更重 → 这时该调的是统计信息或加条件，不是硬压 random_page_cost
• 注意 WorkMem 并发影响：同一台机器上多个复杂排序/聚合，可能把内存吃光，导致外排，这时磁盘随机 IO 模式又变了，random_page_cost 的参考基准就偏了

调参这事没有银弹。磁盘型号、RAID 配置、文件系统（XFS vs ext4）、甚至 PostgreSQL 版本（12+ 对 NVMe 的 cost model 有微调）都会扰动结果。最稳妥的做法是：小步调、勤验证、盯真实慢查。