{"Entry":{"collection":"guc","key":"effective_cache_size","name":"effective_cache_size","aliases":[],"metadata":{"baseline":true,"boot_human":"Not specified","boot_val":null,"category":"Query Tuning / Planner Cost Constants","category_zh":"","changed_in":["9.3","9.4"],"changes":[{"documentation_changed":true,"fields":{},"from":"7.4","status":"changed","to":"8.0"},{"documentation_changed":true,"fields":{},"from":"8.1","status":"changed","to":"8.2"},{"documentation_changed":true,"fields":{},"from":"9.0","status":"changed","to":"9.1"},{"documentation_changed":true,"fields":{"extra_desc":{"from":"That is, the portion of the kernel's disk cache that will be used for PostgreSQL data files. This is measured in disk pages, which are normally 8 kB each.","to":"That is, the size of the cache used for PostgreSQL data files. This is measured in disk pages, which are normally 8 kB each."},"short_desc":{"from":"Sets the planner's assumption about the size of the disk cache.","to":"Sets the planner's assumption about the size of the data cache."}},"from":"9.2","status":"changed","to":"9.3"},{"documentation_changed":true,"fields":{"boot_val":{"from":"16384","to":"524288"},"extra_desc":{"from":"That is, the size of the cache used for PostgreSQL data files. This is measured in disk pages, which are normally 8 kB each.","to":"That is, the total size of the caches (kernel cache and shared buffers) used for PostgreSQL data files. This is measured in disk pages, which are normally 8 kB each."},"short_desc":{"from":"Sets the planner's assumption about the size of the data cache.","to":"Sets the planner's assumption about the total size of the data caches."}},"from":"9.3","status":"changed","to":"9.4"},{"documentation_changed":true,"fields":{},"from":"11","status":"changed","to":"12"}],"content_hash":"c344615845a67404b8140d758271efb87d526505a1b94430072857223348343e","context":"","default_changed_in":["9.4"],"default_history":[{"from":"9.0","to":"9.3","value":"128 MiB (16384 × 8kB)"},{"from":"9.4","to":"19","value":"4 GiB (524288 × 8kB)"}],"derived_content_hash":"fb50dc467f2d947d23d489d74d1dcfaedc5d50823c564dadb46624eb56d4460b","editorial":{"advice":{"olap":"Large scans can evict or compete with cached data, so do not equate installed RAM with cache available to a single analytical query. Calibrate against representative mixed and cold-cache runs.","oltp":"Estimate the cache actually usable by PostgreSQL after OS and co-located service needs, then validate index-heavy plans with EXPLAIN. Avoid copying a fixed percentage from a host with different concurrency or working-set behavior.","small":"Leave room for the OS and other services; a value near total RAM is usually an overstatement on a shared small host. Treat it as a planner estimate, not a memory target."},"mechanism":["effective_cache_size is a cost-model input. Higher values make index scans look more attractive; lower values make sequential scans more attractive.","The estimate should reflect both shared_buffers and the portion of the operating-system page cache likely to hold PostgreSQL data, while accounting for overlap and for concurrent queries sharing the same cache capacity.","Changing this parameter does not resize PostgreSQL shared memory, reserve kernel cache, or guarantee that pages remain cached between queries. Its effect is indirect, through plan selection."],"pitfalls":["Expecting the setting to allocate or reserve memory.","Setting it equal to installed RAM without subtracting memory unavailable to PostgreSQL caching.","Ignoring that concurrent queries on different data sets share the effective cache.","Overstating it and then attributing index-heavy plan choices to unrelated cost parameters."],"references":[{"title":"PostgreSQL 19 Beta 4: effective_cache_size","url":"https://www.postgresql.org/docs/19/runtime-config-query.html#GUC-EFFECTIVE-CACHE-SIZE"},{"title":"PostgreSQL 18: Using EXPLAIN","url":"https://www.postgresql.org/docs/18/using-explain.html"},{"title":"PostgreSQL 19 release notes","url":"https://www.postgresql.org/docs/19/release-19.html"}],"related":["shared_buffers","random_page_cost","seq_page_cost","effective_io_concurrency","max_connections"],"summary":"A planner estimate of how much PostgreSQL data cache is effectively available to one query; it changes plan costing but allocates no memory."},"enumvals":[],"first_version":"7.4","group":"Query Tuning","group_slug":"query","imported_at":"2026-09-27T17:57:31.15017+08:00","intro_commit":{},"key":"effective_cache_size","last_version":"20","max_val":"","min_val":"","name":"effective_cache_size","position":109,"present_in":["7.4","8.0","8.1","8.2","8.3","8.4","9.0","9.1","9.2","9.3","9.4","9.5","9.6","10","11","12","13","14","15","16","17","18","19","20"],"repair_provenance":{"original_source_revision":"english-manuals:49f893bc6eac015ac3a88986d77203d4bf27321e164c3d4bceacb6be8ad9d147","receipt_sha256":"94f9e7e6df121e5101c429c36e7de353f502c3e6f0d6ef2e19fa97d94e8d6e28","repair":"wiki-repair-20261023"},"short_desc":"Sets the planner's assumption about the effective size of the disk cache that is available to a single query.","short_desc_zh":"","source_rev":"wiki-repair-20261023:7b70c737d6694bbc4b9e2171acbb123e969ef6975dedc0eb59b13baa11165c25","unit":"","vartype":"integer"}},"Definition":{"Collection":"guc","Key":"effective_cache_size","SourceDatabase":"center","Version":"18","SourceTable":"guc","SourceKey":"effective_cache_size","SourceRevision":"wiki-repair-20261023:7b70c737d6694bbc4b9e2171acbb123e969ef6975dedc0eb59b13baa11165c25","Facts":{"boot_val":"524288","category":"Query Tuning / Planner Cost Constants","context":"user","description":"Sets the planner's assumption about the effective size of the disk cache that is available to a single query. This is factored into estimates of the cost of using an index; a higher value makes it more likely index scans will be used, a lower value makes it more likely sequential scans will be used. When setting this parameter you should consider both PostgreSQL's shared buffers and the portion of the kernel's disk cache that will be used for PostgreSQL data files, though some data might exist in both places. Also, take into account the expected number of concurrent queries on different tables, since they will have to share the available space. This parameter has no effect on the size of shared memory allocated by PostgreSQL, nor does it reserve kernel disk cache; it is used only for estimation purposes. The system also does not assume data remains in the disk cache between queries. If this value is specified without units, it is taken as blocks, that is BLCKSZ bytes, typically 8kB. The default is 4 gigabytes (4GB). (If BLCKSZ is not 8kB, the default value scales proportionally to it.)","description_zh":"设置规划器对一个单一查询可用的有效磁盘缓存尺寸的假设。这个参数会被考虑在使用一个索引的代价估计中，更高的数值会使得索引扫描更可能被使用，更低的数值会使得顺序扫描更可能被使用。在设置这个参数时，你还应该考虑 PostgreSQL 的共享缓冲区以及将被用于 PostgreSQL 数据文件的内核磁盘缓存，尽管有些数据可能在两个地方都存在。另外，还要考虑预计在不同表上的并发查询数目，因为它们必须共享可用的空间。这个参数对 PostgreSQL 分配的共享内存尺寸没有影响，它也不会预留内核磁盘缓存，它只用于估计的目的。系统也不会假设在查询之间数据会保留在磁盘缓存中。如果指定值时没有单位，则以块为单位，即 BLCKSZ 字节，通常为 8kB。默认值是 4 吉字节（4GB）。（如果 BLCKSZ 不是 8kB，默认值会随之按比例缩放。）","description_zh_html":"\u003cp\u003e\n        设置规划器对一个单一查询可用的有效磁盘缓存尺寸的假设。这个参数会被考虑在使用一个索引的代价估计中，更高的数值会使得索引扫描更可能被使用，更低的数值会使得顺序扫描更可能被使用。在设置这个参数时，你还应该考虑 \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e 的共享缓冲区以及将被用于 \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e 数据文件的内核磁盘缓存，尽管有些数据可能在两个地方都存在。另外，还要考虑预计在不同表上的并发查询数目，因为它们必须共享可用的空间。这个参数对 \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e 分配的共享内存尺寸没有影响，它也不会预留内核磁盘缓存，它只用于估计的目的。系统也不会假设在查询之间数据会保留在磁盘缓存中。如果指定值时没有单位，则以块为单位，即 \u003ccode class=\"symbol\"\u003eBLCKSZ\u003c/code\u003e 字节，通常为 8kB。默认值是 4 吉字节（\u003ccode class=\"literal\"\u003e4GB\u003c/code\u003e）。（如果 \u003ccode class=\"symbol\"\u003eBLCKSZ\u003c/code\u003e 不是 8kB，默认值会随之按比例缩放。）\n       \u003c/p\u003e","doc":{"anchor":"GUC-EFFECTIVE-CACHE-SIZE","file":"runtime-config-query.html","lang":"en","sha256":"33456ca7ef52bf28e93e34b932a411db7a1b95800179add2cf867dc6ec9144bc","slug":"18"},"documented":true,"enumvals":null,"extra_desc":"That is, the total size of the caches (kernel cache and shared buffers) used for PostgreSQL data files. This is measured in disk pages, which are normally 8 kB each.","lang":"en","max_val":"2147483647","metadata_version":"18","min_val":"1","name":"effective_cache_size","short_desc":"Sets the planner's assumption about the total size of the data caches.","source":"pg-settings-source-snapshot","translation_provenance":{"inputs":[{"kind":"current-pgdoc-translation","path":"/Users/vonng/pgsty/pgdoc/zh/18/html/runtime-config-query.html","sha256":"38b0ec7d6c52647c7cdf6401bda43777b62b324f1821fef3ee0f0f0ddb388513"}],"kind":"same-version-pgdoc","locale":"zh-Hans","source_revision":"b1b500221dbe5437cfca004d6b04d3f6e422765d","version":"18"},"unit":"8kB","vartype":"integer","zh_from":"same-version-pgdoc"},"ManualEvidence":{"doc":{"anchor":"GUC-EFFECTIVE-CACHE-SIZE","file":"runtime-config-query.html","lang":"en","sha256":"33456ca7ef52bf28e93e34b932a411db7a1b95800179add2cf867dc6ec9144bc","slug":"18"}},"MeasuredEvidence":{"metadata_version":"18"}},"Text":{"Collection":"guc","Key":"effective_cache_size","SourceDatabase":"center","Version":"18","Locale":"en","Title":"effective_cache_size","Summary":"Sets the planner's assumption about the effective size of the disk cache that is available to a single query. This is factored into estimates of the cost of using an index; a higher value makes it more likely index scans will be used, a lower value makes it more likely sequential scans will be used. When setting this parameter you should consider both PostgreSQL's shared buffers and the portion of the kernel's disk cache that will be used for PostgreSQL data files, though some data might exist in both places. Also, take into account the expected number of concurrent queries on different tables, since they will have to share the available space. This parameter has no effect on the size of shared memory allocated by PostgreSQL, nor does it reserve kernel disk cache; it is used only for estimation purposes. The system also does not assume data remains in the disk cache between queries. If this value is specified without units, it is taken as blocks, that is BLCKSZ bytes, typically 8kB. The default is 4 gigabytes (4GB). (If BLCKSZ is not 8kB, the default value scales proportionally to it.)","BodyHTML":"\u003cp\u003eSets the planner\u0026#39;s assumption about the effective size of the disk cache that is available to a single query. This is factored into estimates of the cost of using an index; a higher value makes it more likely index scans will be used, a lower value makes it more likely sequential scans will be used. When setting this parameter you should consider both PostgreSQL\u0026#39;s shared buffers and the portion of the kernel\u0026#39;s disk cache that will be used for PostgreSQL data files, though some data might exist in both places. Also, take into account the expected number of concurrent queries on different tables, since they will have to share the available space. This parameter has no effect on the size of shared memory allocated by PostgreSQL, nor does it reserve kernel disk cache; it is used only for estimation purposes. The system also does not assume data remains in the disk cache between queries. If this value is specified without units, it is taken as blocks, that is BLCKSZ bytes, typically 8kB. The default is 4 gigabytes (4GB). (If BLCKSZ is not 8kB, the default value scales proportionally to it.)\u003c/p\u003e","SourceRevision":"wiki-repair-20261023:7b70c737d6694bbc4b9e2171acbb123e969ef6975dedc0eb59b13baa11165c25","ContentHash":"9059a31faf9a23efe669593c1cbbdd81a8b9383dd765ee558879f9b2f2c944bc","Payload":{"description":"Sets the planner's assumption about the effective size of the disk cache that is available to a single query. This is factored into estimates of the cost of using an index; a higher value makes it more likely index scans will be used, a lower value makes it more likely sequential scans will be used. When setting this parameter you should consider both PostgreSQL's shared buffers and the portion of the kernel's disk cache that will be used for PostgreSQL data files, though some data might exist in both places. Also, take into account the expected number of concurrent queries on different tables, since they will have to share the available space. This parameter has no effect on the size of shared memory allocated by PostgreSQL, nor does it reserve kernel disk cache; it is used only for estimation purposes. The system also does not assume data remains in the disk cache between queries. If this value is specified without units, it is taken as blocks, that is BLCKSZ bytes, typically 8kB. The default is 4 gigabytes (4GB). (If BLCKSZ is not 8kB, the default value scales proportionally to it.)"}},"RequestedLocale":"en","Fallback":false,"Versions":["10","11","12","13","14","15","16","17","18","19","20","7.4","8.0","8.1","8.2","8.3","8.4","9.0","9.1","9.2","9.3","9.4","9.5","9.6"],"Locales":["en","zh-Hans"],"Signatures":null,"Spellings":null,"SQLState":null,"Evidence":null}