如何禁用PostgreSQL的“缓存”优化？ [英] How disable postgresql "Cache" optimization?

问题描述

我正在尝试优化我的功能。

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

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

I'm trying to optimize my function.
The thing is when you run the query once you get one result.
Run the query a second time or third time the process time is much smaller.

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

第一次17 seg

Total query runtime: 17188 ms.
801 rows retrieved.

第二次11 seg

Total query runtime: 11406 ms.
801 rows retrieved.

我猜有某种缓存在后台进行优化。
如何禁用此行为，以便获得更现实的运行时值？

I'm guessing there is some kind of cache doing optimization behind scene. How can i disable this behavior so i can get a more realistic runtime value?

推荐答案

PostgreSQL没有

PostgreSQL doesn't have a "cache" optimisation, in the sense of a query result cache.

它确实缓存最近在 shared_buffers ，但是对于大多数安装而言，效果很小。主缓存是操作系统的磁盘读取缓存。有关更多信息，请参见：

It does cache table blocks that were recently read in shared_buffers, but for most installs that only has a small effect. The main cache is the operating system's disk read cache. For more information see:

查看并清除Postgres缓存/缓冲区？

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

It sounds to me like you have a system with a reasonable amount of RAM and a fast CPU but a terribly slow disk. So queries that only hit the OS's disk cache are very fast, but queries that go to disk take a couple of seconds to read the data in. So caching effects are very strong.

您应该解释（缓冲区，分析，详细）SELECT ... 您的查询。尝试几个不同的输入值，直到得到一个缓慢的值。比较计划。

You should explain (buffers, analyze, verbose) SELECT ... your queries. Try with a couple of different input values until you get a slow one. Compare plans.

如果计划相同，就可能是这样。

If the plans are the same, that's probably it.

如果计划不同，您可能会遇到查询计划者根据表统计信息的变化做出错误选择的情况。增加感兴趣的列的统计目标可能会有所帮助（请参阅手册）。如果您有不同的计划并且陷入困境/需要帮助，请随时在dba.stackexchange.com上发布新问题，并提供详细信息。

If the plans are different, you're probably hitting a situation where the query planner is making bad choices based on variations in the table statistics. Increasing the statistics targets for the columns of interest may help (see the manual). If you get different plans and are stuck / want help, feel free to post a new question on dba.stackexchange.com with details.

这篇关于如何禁用PostgreSQL的“缓存”优化？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！