PostgreSQL如何缓存语句和数据？ [英] How does PostgreSQL cache statements and data?

问题描述

在Oracle中，SQL语句将被缓存在shared_pool中，而经常选择的数据将被缓存在db_cache中。

In Oracle, SQL statements will be cached in shared_pool, and data which is selected frequently will be cached in db_cache.

PostgreSQL的作用是什么？是否将SQL语句和数据缓存在shared_buffers中？

What does PostgreSQL do? Will SQL statements and data be cached in shared_buffers?

推荐答案

通常，仅表和索引文件的内容会缓存在共享缓冲区空间中。

Generally, only the contents of table and index files will be cached in the shared buffer space.

在某些情况下会缓存查询计划。确保这一点的最佳方法是先查询一次，然后每次执行一次。

Query plans are cached in some circumstances. The best way to ensure this is to PREPARE the query once, then EXECUTE it each time.

查询结果不会自动缓存。如果您重新运行相同的查询-即使其字母相同，并且没有在数据库上执行任何更新-仍将执行整个计划。当然，它将利用共享缓冲区缓存中已经存在的任何表/索引数据；因此不必不必再次从磁盘读取所有数据。

The results of a query are not automatically cached. If you rerun the same query -- even if it's letter-for-letter identical, and no updates have been performed on the DB -- it will still execute the whole plan. It will, of course, make use of any table/index data that's already in the shared buffers cache; so it will not necessarily have to read all the data from disk again.

计划缓存通常在每个会话中完成。这意味着只有进行计划的连接才能使用缓存的版本。其他连接必须制作并使用其自己的缓存版本。这并不是真正的性能问题，因为与重新连接相比，重用计划所节省的成本几乎总是很小。 （除非您的查询真的很复杂。）

Plan caching is generally done per session. This means only the connection that makes the plan can use the cached version. Other connections have to make and use their own cached versions. This isn't really a performance issue because the saving you get from reusing a plan is almost always miniscule compared to the cost of connecting anyway. (Unless your queries are really complicated.)

如果使用PREPARE，它会缓存 ：http://www.postgresql.org/docs/current/static/sql-prepare.html

It does cache if you use PREPARE: http://www.postgresql.org/docs/current/static/sql-prepare.html

当查询位于PL / plSQL函数中时，它执行缓存： http://www.postgresql.org/docs/current/static/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING

It does cache when the query is in a PL/plSQL function: http://www.postgresql.org/docs/current/static/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING

它不会缓存

希望其他人可以详细说明查询计划缓存的任何其他情况。

Hopefully someone else can elaborate on any other cases of query plan caching.

这篇关于PostgreSQL如何缓存语句和数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！