sql - Postgres 不同的查询计划产品/质量保证

Question

我有两台数据库服务器都处于“测试”模式，其中一台预计将升级为生产服务器。因此，规范和一些配置有所不同，但我们发现动力不足的服务器会产生更好的查询计划，从而加快查询速度。

统计:

两个系统的数据大致相同，数量如下:

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

更大的数据库服务器具有以下规范:

内存:500GB

CPU:16 核 2.0 GHz 英特尔

使用固态硬盘

Postgres 10.0

Memlock 设置为专门为 Postgres 预留 485 GB

Postgres 设置:

shared_buffers:125 GB

work_mem:36 MB

effective_cache_size:300 GB

random_page_cost:1

default_statistics_target:1000

查询计划:https://explain.depesz.com/s/9Ww6

较小的服务器具有以下统计数据:

内存:281GB

CPU:4 核 2.0 GHz Intel

使用固态硬盘

Postgres 10.0

Memlock 设置为专门为 Postgres 预留 240 GB

Postgres 设置:

shared_buffers:50 GB

work_mem:25.6 MB

effective_cache_size:150 GB

random_page_cost:4

default_statistics_target:100

查询计划:https://explain.depesz.com/s/4WUH

我们尝试切换 random_page_cost、默认统计信息(之后是分析)和工作内存以相互匹配。在对查询中的所有表运行 vacuum full 之后，收获最大。

工作负载:这台机器是一个只读副本，用于将数据的文件提取为 XML 文件等。因此它接收复制的数据并且具有相当大的读取负载。

问题:我应该寻找什么来使这个查询在运行速度较慢的大型服务器上表现更好？理想情况下，此查询的运行速度要比在较小的服务器上快得多。看起来我们已经扩展了我们未能正确设置设置以利用我们的硬件。一定有什么我忽略了。

编辑:我把未混淆的计划放上去了。我也尝试过将统计数据从 1000 增加到 3000，但这对计划没有帮助。更改 random_page_cost 以在服务器之间匹配也是如此。

Answer 1

两台机器上的PostgreSQL配置差别很大，所以查询计划不同也就不足为奇了。特别是 random_page_cost 影响很大。

您绝对应该使用不同的 shared_buffers 设置对您的工作负载进行基准测试:您的设置可能太高了(通常 8GB 是合适的)。

但我认为两个您的查询计划都很糟糕，而且您的问题出在不同的地方。

优化器错误估计了从 showtimes.mappable_program 上的索引扫描返回的行数，这种错误估计会导致更严重的错误估计和错误的计划选择。

尝试增加列上统计数据的密度:

ALTER TABLE showtimes.mappable_program ALTER mapping_scheme_id
   SET STATISTICS 1000;

然后ANALYZE 表。

如果这不起作用，请通过替换来修改查询

WHERE COALESCE(mp2.ignore::integer, 0) = 0

与

WHERE mp2.ignore = '0' OR mp2.ignore IS NULL

这可能有助于优化器更好地估计条件。