如何禁用postgresql“缓存”优化？

Question

我在试着优化我的函数。

问题是，一旦得到一个结果，就运行查询。

第二次或第三次运行查询时，处理时间要短得多。

SELECT  map.get_near_link(a.X, a.Y, a.azimuth)
FROM traffic.avl;

第一次17分

Total query runtime: 17188 ms.
801 rows retrieved.

第二次11seg

Total query runtime: 11406 ms.
801 rows retrieved.

我猜在幕后有某种缓存在做优化。如何禁用此行为才能获得更真实的运行时值？

Answer 1

从查询结果缓存的意义上讲，PostgreSQL没有“缓存”优化。

它确实缓存了最近在shared_buffers中读取的表块，但对于大多数安装来说，这只会产生很小的影响。主缓存是操作系统的磁盘读缓存。有关详细信息，请参阅：

See and clear Postgres caches/buffers?

在我看来，您的系统具有相当数量的RAM和快速的CPU，但磁盘速度非常慢。因此，只命中操作系统的磁盘缓存的查询速度非常快，但进入磁盘的查询需要几秒钟才能读取数据。所以缓存效果非常强。

你应该explain (buffers, analyze, verbose) SELECT ...你的查询。尝试使用几个不同的输入值，直到得到一个较慢的输入值。比较计划。

如果计划是一样的，那很可能就是这样。

如果计划不同，您可能会遇到这样一种情况:查询规划器正在根据表统计数据的变化做出错误的选择。增加感兴趣的列的统计目标可能会有所帮助(请参阅手册)。如果你得到了不同的计划，并且遇到困难/需要帮助，请随时在dba.stackexchange.com上发布新的问题并提供详细信息。

Answer 2

sync; sudo service postgresql stop; echo 1 > /proc/sys/vm/drop_caches; sudo service postgresql start

Answer 3

既然你说你想优化你的函数，而不是你的磁盘布局，那么第二个计时可能是你想要关注的，因为它们是你函数本身运行时间的最纯粹的度量，而不是收集数据馈送到你的函数所需的时间。

由于第二次执行时间仍然是第一次执行时间的2/3，所以即使你想要优化总执行时间，那也是你应该关注的地方。

如果您确实需要这样做，您可以清除缓存，如另一个答案中所述，但这通常太繁琐，无法用于日常工作。更好的做法是创建一个程序/脚本，该程序/脚本选择a.X、a.Y、a.azimuth的随机(但通常是真实的)值，并使用它们运行查询。通过每次更改一组真实示例中的值，您将获得最能代表真实世界的结果。制作这样的驱动程序需要更多的前期工作，但通常是有回报的。