一项PostgreSQL配置更改如何将慢速查询性能提高50倍

您好，Khabrovites！ 我引起您的注意，是Pavan Patibandla撰写的文章“单个PostgreSQL配置如何更改将慢查询性能提高了50倍”的译文。 它对改善PostgreSQL的性能有很大帮助。

在Amplitude，我们的目标是提供易于使用的交互式产品分析，以便每个人都能找到有关产品问题的答案。 为了确保可用性，Amplitude必须快速提供这些答案。 因此，当我们的一位客户抱怨在Amplitude用户界面中加载事件属性下拉列表需要花费多长时间时，我们开始对该问题进行详细研究。

通过跟踪不同级别的延迟，我们意识到完成一个特定的PostgreSQL查询需要20秒。 这让我们感到惊讶，因为两个表的联接列中都有索引。

要求缓慢



该查询的PostgreSQL执行计划对我们来说是意外的。 尽管两个表都具有索引，但PostgreSQL还是决定对大表进行顺序扫描来执行哈希联接。 顺序扫描大型表占用了大部分查询时间。

慢查询执行计划



我最初怀疑这可能是由于碎片造成的。 但是在检查数据之后，我意识到数据仅添加到该表中，实际上并没有从该表中删除。 由于在这里用VACUUM清理位置并没有多大帮助，因此我开始进一步研究。 然后，我在另一个具有良好响应时间的客户端上尝试了相同的请求。 令我惊讶的是，查询执行计划看起来完全不同！

在另一个客户端上执行相同请求的执行计划



有趣的是，应用程序A访问的数据仅比应用程序B多10倍，但响应时间却长3,000倍。

要查看其他PostgreSQL查询计划，我关闭了哈希连接并重新启动了查询。

慢查询的替代执行计划



好吧！ 使用嵌套循环而不是哈希联接时，同一请求完成的速度快50倍。 那么PostgreSQL为什么为应用程序A选择最差的计划？

通过仔细查看两个计划的估计成本和实际提前期，估计成本与实际提前期的比率非常不同。 造成这种差异的主要原因是顺序扫描的成本估算。 PostgreSQL估计顺序扫描将比4000多个索引扫描更好，但实际上，索引扫描要快50倍。

这使我想到了random_page_cost和seq_page_cost配置选项 。 为HDD配置的random_page_cost和seq_page_cost的默认PostgreSQL值分别为4和1 ，在这种情况下，对磁盘的随机访问比顺序访问要昂贵。 但是，对于使用gp2 EBS卷（固态驱动器）进行的部署，这些成本是不准确的。 对于我们的部署，随机访问和顺序访问几乎相同。

我将random_page_cost的值更改为1 并重试了该请求。 这次，PostgreSQL使用了嵌套循环，查询运行速度提高了50倍。 更改之后，我们还注意到PostgreSQL的最大响应时间显着减少。

缓慢请求的整体性能已得到显着改善。



如果您使用SSD并以默认配置使用PostgreSQL，建议您尝试设置random_page_cost和seq_page_cost 。 您可能会对性能的显着提高感到惊讶。

我要自己补充一点，我设置了最小参数seq_page_cost = random_page_cost = 0.1，以使内存（缓存）中的数据优先于处理器操作，因为我为PostgreSQL分配了大量RAM（RAM大小超过了磁盘上数据库的大小）。 尚不清楚为什么postgres社区仍然使用与具有少量RAM和HDD的服务器相关的默认设置，而与现代服务器无关。 希望这个问题会尽快解决。