PostgreSQL 如何缓存语句和数据？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7142335/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How does PostgreSQL cache statements and data?
提问by francs
In Oracle, SQL statements will be cached in shared_pool, and data which is selected frequently will be cached in db_cache.
在 Oracle 中，SQL 语句会缓存在 shared_pool 中，经常被选中的数据会缓存在 db_cache 中。
What does PostgreSQL do? Will SQL statements and data be cached in shared_buffers?
PostgreSQL 有什么作用？SQL 语句和数据会缓存在 shared_buffers 中吗？
回答by Edmund
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.
查询的结果不会自动缓存。如果您重新运行相同的查询——即使它是完全相同的，并且没有对数据库执行更新——它仍然会执行整个计划。当然，它会使用共享缓冲区缓存中已经存在的任何表/索引数据；所以它不一定要再次从磁盘读取所有数据。
Update on plan caching
更新计划缓存
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 reallycomplicated.)
计划缓存通常按会话完成。这意味着只有制定计划的连接才能使用缓存版本。其他连接必须创建并使用它们自己的缓存版本。这并不是真正的性能问题，因为与无论如何连接的成本相比，您从重用计划中获得的节省几乎总是微不足道的。（除非您的查询非常复杂。）
It doescache if you use PREPARE: http://www.postgresql.org/docs/current/static/sql-prepare.html
如果您使用 PREPARE，它会缓存：http: //www.postgresql.org/docs/current/static/sql-prepare.html
It doescache when the query is in a PL/plSQL function: http://www.postgresql.org/docs/current/static/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING
当查询在 PL/plSQL 函数中时，它会缓存：http: //www.postgresql.org/docs/current/static/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING
It does notcache ad-hoc queries entered in psql.
它不缓存在 psql 中输入的临时查询。
Hopefully someone else can elaborate on any other cases of query plan caching.
希望其他人可以详细说明查询计划缓存的任何其他情况。
