effective_cache_size and Index Cost Estimates #

A large reporting query uses a sequential scan on a 90 GB table on the new server and a fast index scan on the old one. The hardware is better, the data is identical, random_page_cost matches — and effective_cache_size on the new server is still at its default of 4 GB, while the old one was set to 96 GB years ago and forgotten.

effective_cache_size is the setting most often misunderstood in the cost estimation model. It allocates nothing, caches nothing and limits nothing. It is purely an input to the planner’s arithmetic, and it only affects one family of decisions: how expensive an index scan is expected to be.

The Cost-Model Condition #

When the planner costs an index scan, it has to estimate how many of the pages it will touch are already in memory — either PostgreSQL’s shared_buffers or the operating system’s page cache. A page in memory costs little to read; a page that must come from disk costs random_page_cost. The planner cannot see the actual cache contents, so it uses effective_cache_size as its assumption about how much memory is available to hold the table and index pages a single query needs.

The effect is concentrated in two places:

What it does not affect: sequential scan cost, hash and sort memory decisions (work_mem governs those), or anything at execution time. A value that is too large does not cause memory pressure; a value that is too small does not save memory. It only moves the crossover point between access paths described in sequential vs index scans.

A reasonable value is the memory the server can realistically devote to caching data: shared_buffers plus the OS page cache that remains after connections, work memory and other processes. On a dedicated database server that is commonly 50–75% of RAM.

A planner assumption, not an allocation Four stages left to right. effective_cache_size sets the assumed cacheable memory. The planner estimates how much of the index and table fit. That discounts random page fetches in the index scan cost. The cheaper index scan cost moves the crossover with the sequential scan. effective_cache_size assumed, not allocated fraction cached index + heap vs setting fetch discount cheaper index cost path decision index vs seq scan nothing in this chain touches memory at execution time

Annotated EXPLAIN Evidence #

The symptom is a sequential scan with a cost close to an index scan alternative that is much faster in practice. Compare the two plans under both settings:

SET effective_cache_size = '4GB';     -- default
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events WHERE account_id BETWEEN 2000 AND 2400;
Seq Scan on events  (cost=0.00..11820400.00 rows=1840000 width=180)
                    (actual time=0.05..61204.7 rows=1812300 loops=1)
  Filter: ((account_id >= 2000) AND (account_id <= 2400))
  Rows Removed by Filter: 408187700
  Buffers: shared hit=402110 read=11418290
Execution Time: 61840.2 ms
-- total cost 11.8M: the index scan was estimated slightly higher, so it lost
SET effective_cache_size = '96GB';    -- realistic for a 128 GB server
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events WHERE account_id BETWEEN 2000 AND 2400;
Index Scan using events_account_id_idx on events
    (cost=0.57..6104220.10 rows=1840000 width=180)
    (actual time=0.04..3810.6 rows=1812300 loops=1)
  Index Cond: ((account_id >= 2000) AND (account_id <= 2400))
  Buffers: shared hit=1204880 read=140220
Execution Time: 3902.4 ms
-- same estimate of rows; cost almost halved by the cache assumption

What to read:

Evidence that only the cache assumption changed Three plan fields are annotated. Identical row estimates rule out statistics. The index scan cost dropping by half shows page-fetch pricing moved. A high shared hit count in the actual run shows the pages were in memory all along. rows=1840000 (both plans) estimates unchanged: not statistics cost 11.8M → 6.1M for the index path page-fetch pricing moved Buffers: shared hit=1204880 the pages really were cached when estimates match and only costs move, look at cost parameters

Step-by-Step Resolution #

  1. Check the current value and its source.

    SELECT name, setting, unit, source, sourcefile
FROM pg_settings WHERE name IN ('effective_cache_size', 'shared_buffers', 'random_page_cost');

    effective_cache_size is reported in 8 kB pages; multiply setting by 8 to get kilobytes.

  2. Estimate realistic cacheable memory. Take total RAM, subtract what connections and work memory need at peak — see pool size and the total work_mem budget — and other processes on the host. What remains, plus shared_buffers, is a defensible value.

  3. Test the effect in a transaction on the queries that misbehave:

    BEGIN;
SET LOCAL effective_cache_size = '96GB';
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM events WHERE account_id BETWEEN 2000 AND 2400;
ROLLBACK;

  4. Apply it server-wide once the value is justified; it can be changed with a reload, no restart:

    ALTER SYSTEM SET effective_cache_size = '96GB';
SELECT pg_reload_conf();

  5. Re-plan cached statements. Prepared statements holding generic plans do not see the new value until they are replanned, as explained in plan invalidation after DDL and ANALYZE.

  6. Watch for regressions in the other direction. A value far above real memory makes index scans on cold data look cheaper than they are; if Buffers: shared read dominates the new index plans, the setting is optimistic for that workload.

Where the crossover moves Bars show estimated total cost for the same query. The sequential scan costs 11.8 million at every setting. The index scan costs 12.1 million at 4 gigabytes, 8.4 million at 32 gigabytes, and 6.1 million at 96 gigabytes, so it wins from 32 gigabytes upward. Seq Scan (any setting) 11.8M cost units Index Scan @ 4GB 12.1M — loses Index Scan @ 32GB 8.4M — wins Index Scan @ 96GB 6.1M — wins clearly cost units are planner estimates, not milliseconds

Before and After #

-- BEFORE: effective_cache_size = 4GB (default)
Seq Scan on events  (cost=0.00..11820400.00)   Buffers: shared read=11418290   Execution Time: 61840.2 ms

-- AFTER: effective_cache_size = 96GB
Index Scan using events_account_id_idx  (cost=0.57..6104220.10)   Buffers: shared read=140220   Execution Time: 3902.4 ms

Why the default is so often wrong #

The 4 GB default was chosen to be safe on small machines, where overestimating the cache would push the planner toward index scans that really do hit disk. On a modern database server it is almost always too low, and because it neither fails nor warns, it can stay at the default for the life of a cluster. Migrations are the typical moment it bites: a new host provisioned from a template configuration carries the default, while the old host had been tuned. Plans that relied on the old value for large index range scans quietly switch to sequential scans, and the regression appears as “the new server is slower” rather than as a configuration difference. Capturing plans with the SETTINGS option makes this visible immediately, because a tuned value shows up in the Settings: line and the default does not.

The setting interacts with random_page_cost. Both push in the same direction for index scans, and tuning one to compensate for the other produces a configuration that is right for today’s cache hit ratio and wrong after the next change in data volume. Set random_page_cost to reflect storage latency — see tuning random_page_cost for SSD storage — and effective_cache_size to reflect memory, and let each describe its own resource.

Common Pitfalls #

Treating it as a memory allocation. Raising it does not grow any cache and lowering it does not free memory. Diagnostic signal: a change proposed “to reduce memory usage”. Fix: tune shared_buffers and work_mem for memory; use this only for planning.

Setting it higher than physical memory. The planner then assumes cache hits that cannot happen. Diagnostic signal: index plans with Buffers: shared read far above hit on repeated runs. Fix: cap it at realistic cacheable memory.

Diagnosing it from a warm test run. A repeated test shows cached pages regardless of the setting. Diagnostic signal: an index plan that is fast in testing and slow after restarts or failovers. Fix: test on a cold cache as well, and reason from memory size rather than one run.

Per-session overrides left in place. Like any planner setting it can be set by role or session. Diagnostic signal: different plans for the same query from different application roles. Fix: audit pg_db_role_setting as described in session parameter drift.

Frequently Asked Questions #

Does effective_cache_size allocate memory? #

No. It is only an assumption the planner uses when costing index scans. Actual caching is done by shared_buffers and the operating system’s page cache, whose sizes are unaffected by this setting.

What should effective_cache_size be set to? #

The memory realistically available for caching table and index pages: shared_buffers plus the operating system page cache left after connections and other processes. On a dedicated server that is typically 50–75% of RAM.

Does changing effective_cache_size require a restart? #

No. It can be changed with ALTER SYSTEM and a configuration reload, or per session with SET. Cached generic plans for prepared statements keep their old costs until they are replanned.

Up: Cost Estimation Models