You want to optimize a complex query with multiple joins and sorts. The server has 128GB RAM. How should you set work_mem and effective_cache_size for best performance?
hard📝 optimization Q8 of 15
PostgreSQL - Performance Tuning
You want to optimize a complex query with multiple joins and sorts. The server has 128GB RAM. How should you set work_mem and effective_cache_size for best performance?
ASet work_mem equal to effective_cache_size
BSet work_mem very low (e.g., 1MB) and effective_cache_size to 10% of RAM
CSet work_mem to total RAM and effective_cache_size to zero
DSet work_mem moderately high per connection (e.g., 64MB) and effective_cache_size to about 75% of RAM (e.g., 96GB)
Step-by-Step Solution
Solution:
Step 1: Set effective_cache_size to reflect OS cache
About 75% of RAM is typical to inform planner of available cache.
Step 2: Set work_mem per connection moderately high
64MB allows efficient sorts and joins without exhausting memory.
Final Answer:
Set work_mem moderately high per connection (e.g., 64MB) and effective_cache_size to about 75% of RAM (e.g., 96GB) -> Option D
Quick Check:
Balance work_mem and effective_cache_size for performance [OK]
Quick Trick:Balance per-operation memory and cache estimate for tuning [OK]
Common Mistakes:
Setting work_mem too high causing memory exhaustion
Setting effective_cache_size too low causing bad plans
Matching work_mem to effective_cache_size incorrectly
Master "Performance Tuning" in PostgreSQL
9 interactive learning modes - each teaches the same concept differently
