A query with multiple hash joins and sorts runs slowly despite high work_mem. The server has limited RAM and high load. What combined tuning approach can improve performance?

hard📝 optimization Q9 of 15

PostgreSQL - Performance Tuning

A query with multiple hash joins and sorts runs slowly despite high work_mem. The server has limited RAM and high load. What combined tuning approach can improve performance?

ALower work_mem to prevent memory exhaustion and increase effective_cache_size to help planner choose better plans

BIncrease work_mem further and decrease effective_cache_size

CSet work_mem and effective_cache_size to the same low value

DDisable effective_cache_size and rely only on work_mem

Step-by-Step Solution

Solution:

Step 1: Understand memory pressure effects
High work_mem on limited RAM causes swapping, slowing queries.
Step 2: Adjust settings
Lowering work_mem prevents memory exhaustion; increasing effective_cache_size helps planner choose better plans.
Final Answer:
Lower work_mem to prevent memory exhaustion and increase effective_cache_size to help planner choose better plans -> Option A
Quick Check:
Tune work_mem and effective_cache_size based on RAM and load [OK]

Quick Trick: Balance memory settings with server capacity and query needs [OK]

Common Mistakes:

Ignoring server RAM limits
Disabling effective_cache_size
Setting both values too low

Master "Performance Tuning" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

Want More Practice?

15+ quiz questions · All difficulty levels · Free

Free Signup - Practice All Questions

A query with multiple hash joins and sorts runs slowly despite high work_mem. The server has limited RAM and high load. What combined tuning approach can improve performance?

Step 1: Understand memory pressure effects

Step 2: Adjust settings

Final Answer:

Quick Check:

Want More Practice?