Parameter · Query Tuning / Planner Cost Constants
effective_cache_size
Sets the planner's assumption about the total size of the data caches. 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.
- Default:
4GB - Type: integer (8kB)
- Range: 8kB … max (2147483647)
- Applies: Session (any user)
In plain words
This does not allocate any memory. It tells the planner how much of the database is probably in RAM (PostgreSQL's cache plus the operating system's file cache). A larger value makes repeated index lookups look cheaper, so index plans are chosen more often.
For DBAs and operators
The planner uses it in the index scan cost model to estimate how many page fetches of a large index scan will hit cache when the same pages are revisited. It is a planning hint only: no memory is reserved, and too high a value cannot cause out-of-memory errors.
A common rule is 50–75% of the machine's RAM on a dedicated database server, i.e. roughly shared_buffers plus the expected OS page cache. The default 4GB is low for large servers, and a too-small value makes the planner overestimate repeated index fetches and drift toward sequential scans and hash joins.
It should reflect what is shared by concurrent queries: if many big queries run at once, each cannot rely on the whole cache. Containers and VMs with memory limits should use the limit, not the host's RAM. Together with random_page_cost it is one of the two cost settings worth tuning on almost every install.
How to change it
Any user can change it for their session with SET.
See it in context
Planner cost model Memory architecture Shared buffers Life of a SELECT