effective_cache_size allocates nothing

effective_cache_size tells the planner roughly how much memory the buffer pool and the operating system’s cache between them are likely to have available for this database’s pages. It reserves nothing, allocates nothing and is not visible to the executor. Its only job is to feed one estimate: how many of the pages an index access will need are likely to be found in memory already, which makes repeated index access look cheaper as the value rises.

A hint with a narrow reach

It is routinely described as though it controlled caching, and it appears in tuning guides beside genuine memory settings, which is how a server ends up with a plausible-looking value that nobody can explain. Changing it changes costs. Nothing else about the server is different afterwards, and because it takes effect immediately with no restart, it is one of the few settings safe to experiment with on a live server.

The reach is narrower than the reputation too. The estimate it feeds concerns index pages fetched repeatedly, so it matters most where an index is probed many times, which in practice means the inner side of a nested loop. On a single scan over a table there is often no visible effect at all. Raising it does not make the planner prefer indexes in general; it slightly reduces the cost of one family of index access, and the plan usually stays where it was.

The same plan at two different values

A nested loop probing an index three hundred times, with everything else held still.

CREATE TABLE reading AS SELECT ((g::bigint * 7919) % 500000)::int AS scattered_key
FROM generate_series(1, 500000) g;
CREATE INDEX ON reading (scattered_key);
CREATE TABLE probe AS SELECT ((g::bigint * 137) % 500000)::int + 1 AS k FROM generate_series(1, 300) g;
ANALYZE reading, probe;
SET enable_hashjoin = off;
SET enable_mergejoin = off;
SET effective_cache_size = '4MB';
EXPLAIN (SUMMARY OFF) SELECT count(*) FROM probe p JOIN reading r ON r.scattered_key = p.k;
SET effective_cache_size = '32GB';
EXPLAIN (SUMMARY OFF) SELECT count(*) FROM probe p JOIN reading r ON r.scattered_key = p.k;

SELECT 500000
CREATE INDEX
SELECT 300
ANALYZE
SET
SET
SET
                                                 QUERY PLAN                                                 
------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=2360.75..2360.76 rows=1 width=8)
   ->  Nested Loop  (cost=0.42..2360.00 rows=300 width=0)
         ->  Seq Scan on probe p  (cost=0.00..5.00 rows=300 width=4)
         ->  Index Only Scan using reading_scattered_key_idx on reading r  (cost=0.42..7.84 rows=1 width=4)
               Index Cond: (scattered_key = p.k)
(5 rows)

SET
                                                 QUERY PLAN                                                 
------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=2352.75..2352.76 rows=1 width=8)
   ->  Nested Loop  (cost=0.42..2352.00 rows=300 width=0)
         ->  Seq Scan on probe p  (cost=0.00..5.00 rows=300 width=4)
         ->  Index Only Scan using reading_scattered_key_idx on reading r  (cost=0.42..7.81 rows=1 width=4)
               Index Cond: (scattered_key = p.k)
(5 rows)

Four megabytes to thirty-two gigabytes is four orders of magnitude of difference in the hint, and the estimated cost of one index probe moved from 7.84 to 7.81. The plan is identical, the row estimates are identical, and no memory changed hands in either direction. Whether a gap that size ever changes which plan wins depends on how close the alternatives already were, which is a fair reason not to spend an outage window here.

Setting it and moving on

Give it a value that reflects the machine, somewhere around the memory the server can expect to have for caching, and then treat it as done. When an index is being passed over and you want to know why, the input to check is almost never this one: the usual culprits are physical correlation, covered in sequential scan versus index scan, and stale or misleading statistics, covered in query plan regression.