effective_cache_size Set Too Low: Planner Avoids Index Scans

Scenario

A 32 GB server has shared_buffers = 8GB and effective_cache_size = 128MB (the compile-time default). The query planner treats index scans as expensive because it assumes that random pages fetched through an index are unlikely to be cached, forcing it to pessimistically estimate high I/O cost. Result: seq scans are chosen on large tables even when a selective index exists. Correcting effective_cache_size to reflect actual available cache (shared_buffers + OS page cache ≈ 75% of RAM = 24GB) restores index scan preference.

How to Identify

Conditions:

• SHOW effective_cache_size returns a value far below available RAM
• EXPLAIN shows Seq Scan on large tables despite selective WHERE clauses
• Indexes exist and are valid (pg_indexes, pg_index.indisvalid = true)
• Lowering random_page_cost or raising effective_cache_size in a session causes the planner to switch to index scans
• No change after ANALYZE — statistics are fresh but planner still avoids indexes

Analysis Steps

-- Check current effective_cache_size vs actual server RAM:
SHOW effective_cache_size;
SHOW shared_buffers;

-- View all planner cost parameters together:
SELECT name, setting, unit
FROM pg_settings
WHERE name IN (
    'effective_cache_size',
    'shared_buffers',
    'random_page_cost',
    'seq_page_cost',
    'cpu_tuple_cost',
    'cpu_index_tuple_cost'
)
ORDER BY name;

-- Demonstrate the planner's behavior before and after correction:
-- Before fix (effective_cache_size=128MB):
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
-- Expect: Seq Scan (planner thinks cache won't help random I/O)

-- Test what the planner would do with a corrected estimate (session only):
SET effective_cache_size = '24GB';
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
-- Expect: Index Scan using orders_customer_id_idx

RESET effective_cache_size;

Pitfalls

• effective_cache_size is not an actual memory allocation — it is purely a planner cost hint. Setting it does not allocate or reserve any memory. No restart is needed; it is reload-safe.
• Setting it too low (e.g., 128MB on a 32GB server) causes the planner to treat index random I/O as uncached and expensive, preferring seq scans.
• Setting it too high is less harmful — the planner may be slightly over-optimistic about caching but does not allocate memory and rarely causes worse plans.
• The correct value is approximately shared_buffers + OS page cache. A conservative baseline: 75% of total RAM.
• effective_cache_size interacts with random_page_cost — both affect the relative cost of index vs sequential scans. Fix both together when migrating to SSD.
• Column correlation (from pg_stats.correlation) also affects index scan cost estimates; low-correlation columns can still see seq scans even with correct effective_cache_size.

Resolution Approach

Set effective_cache_size to 75% of total server RAM as a baseline (e.g., 24GB on a 32GB server). This reflects both shared_buffers and the OS page cache that PostgreSQL benefits from. Apply with ALTER SYSTEM and reload. No restart required.