高配Postgres服务器查询计划劣于低配，如何提升查询性能？

阿华AIGC实验室

2026-5-28

优化高配PostgreSQL服务器查询性能的方案

问题背景

我们有两台处于测试模式的PostgreSQL 10.0数据库服务器，计划将其中高配的一台升级为生产服务器。两台服务器数据量大致相同，但高配硬件规格更高的情况下，低配服务器生成的查询计划反而更优、查询速度更快。目前已尝试部分参数调整，但效果不佳，需要找到遗漏的配置点来发挥高配硬件的优势。

两台服务器的数据量基本一致，具体如下：

Size | Part
--------------------
1.47 TB | Entire DB
871 GB | Tables
635 GB | Indexes

将高配服务器的random_page_cost、default_statistics_target（调整后执行ANALYZE）及work_mem配置与低配服务器对齐；
对查询涉及的所有表执行VACUUM FULL，该操作带来的性能提升最为显著。

目标服务器为只读副本，主要用于提取XML等格式的数据文件，接收主库复制的数据，读负载较重。

结合你的配置和已做的操作，以下几个方向可以重点排查：

虽然你调整了全局的default_statistics_target，但复杂查询往往依赖特定列的精细统计数据：

针对查询中涉及过滤、连接的关键列，单独设置更高的统计目标，比如：

ALTER TABLE your_target_table ALTER COLUMN critical_filter_column SET STATISTICS 5000;
ANALYZE your_target_table;

对比两台服务器pg_stats视图中关键列的n_distinct、most_common_vals、histogram_bounds等数据，确保高配服务器的统计信息没有偏差。如果统计数据不一致，在高配服务器上执行全库ANALYZE VERBOSE;彻底刷新统计。

高配服务器的内存资源更充足，但要确保配置能真正利用这些资源：

检查effective_cache_size是否符合实际系统缓存情况：用free -h查看操作系统的页缓存大小，确保该参数设置接近实际可用的系统缓存（通常设为RAM的50%-75%），这会直接影响PostgreSQL的成本估算。
监控work_mem的实际使用：查看pg_stat_activity中的temp_files和temp_bytes字段，如果查询出现大量临时文件，说明work_mem不足以支撑排序/哈希操作，可以针对特定查询临时调高（比如SET work_mem = '64MB';），同时注意全局work_mem过高可能导致内存耗尽，建议结合查询计划中的排序节点大小来调整。

高配服务器的CPU核心更多，需要让PostgreSQL的成本模型适配多核心环境：

用EXPLAIN ANALYZE在两台服务器上运行相同查询，对比实际执行时间和计划预估的成本差异，定位高配服务器成本估算不准的环节。比如random_page_cost=1适合SSD，但如果低配用4时选择了更优的顺序扫描，可能需要微调seq_page_cost让成本模型更贴合实际IO性能。
开启并优化并行查询：PostgreSQL 10支持并行扫描，检查max_parallel_workers_per_gather（默认4）、max_parallel_workers参数，建议将max_parallel_workers_per_gather调高到8（适配16核CPU），让查询能利用多核心优势。同时注意parallel_setup_cost和parallel_tuple_cost的设置，避免PostgreSQL低估并行查询的收益。

确保高配服务器的存储和索引状态最优：

对比两台服务器的索引结构：确认索引类型、列顺序、是否存在部分索引/表达式索引完全一致。如果有索引差异，及时重建或创建缺失的索引；对于可能损坏的索引，执行REINDEX INDEX your_index_name;修复。
检查表的fillfactor设置：对于只读副本，将表的fillfactor设为100（默认是90），然后重新执行VACUUM FULL，减少页面碎片化，提升读性能。
测试SSD实际性能：用fio工具测试高配服务器的随机读写速度，确认是否和低配服务器的SSD性能一致，排除硬件层面的IO瓶颈。