虚拟化系统会影响解释计划吗？
我在 Postgresql 上的解释计划上得到了奇怪且不同的结果。 Postgresql 服务器安装在 VMWare 计算机上，当对给定 SQL 查询执行多个解释计划时，会返回不同的结果。在我看来，硬件虚拟化可能会向 Postgresql 服务器提供“错误”信息，以便它返回“异常且有些随机”的成本测量结果。对于这些令人惊讶和奇怪的结果，我是对的还是有其他解释？
无论如何，如果您知道的话，我将不胜感激任何有用的文档。
I'm having strange and different results for explain plans on Postgresql. Postgresql server is installed on a VMWare machine and when executing several explain plans for a given SQL query, different results are returned. It seems to me that the hardware virtualization may provide "erroneous" information to Postgresql server so that it returns "anormal and somewhat random" costs measurements. Am I right or is there any other explanation for those surprising and strange results?
In any case, if you know any, I'd appreciate any helpful docs.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
VACUUM 应该是数据库操作的常规部分。但这可能不是您问题的根源。
由于连续执行的成本不同，一个在 VMWare 下，一个不在 VMWare 下，并且没有数据库更改，所以我认为虚拟化正在产生一些效果。我非常确定虚拟机的 RAM 似乎比直接硬件少，但我现在没有办法测试它，或者测试它对查询优化器的影响。
VACUUM should be a regular part of database operations. It's probably not the source of your problem, though.
Since you got different costs on consecutive executions, one under VMWare and one without, with no database changes, I'd say virtualization is having some effect. I'm pretty sure that a virtual machine would appear to have less RAM than straight hardware, but I don't have a way to test that, or to test its effect on the query optimizer right now.
计划不会直接受到底层硬件的影响。它们是成本参数（例如，
random_page_cost）、内存设置（例如，work_mem）和表统计信息的乘积。您应该能够轻松验证两个实例之间的参数设置是否相同。 （理想情况下，可以根据硬件特性对这些参数进行调整，然后在不同的系统上会得到不同的计划。）表统计数据取决于表中的实际数据（您没有提及是否有相同的数据）两个实例）和收集统计数据时的一些随机元素。确保 ANALYZE 已运行。如果你还是不明白，就把计划贴出来吧。Plans are not directly affected by the underlying hardware. They are a product of cost parameters (e.g.,
random_page_cost), memory settings (e.g.,work_mem), and table statistics. You ought to be able to verify easily whether the parameter settings are the same between your two instances. (Ideally one would make adjustments to these parameters based on hardware characteristics, and then you would get different plans on different systems.) The table statistics are dependent on the actual data in the tables (you did not mention whether you had the same data in both instances) and some random element when the statistics are collected. Make sureANALYZEhas been run. If you're still clueless, post the plans.