Intermediate LevelPostgreSQL
How do you read EXPLAIN (ANALYZE, BUFFERS) output to identify disk I/O bottlenecks?
❓ Interview Prompt: Explain EXPLAIN (ANALYZE, BUFFERS), actual execution timing vs planner estimates, shared hit/read blocks, and disk I/O diagnosis.
Calling 'EXPLAIN (ANALYZE, BUFFERS)' actually executes the query and returns detailed runtime execution metrics:
Key Metrics:
•
'actual time=start..end': Execution latency in milliseconds.
•
'Buffers: shared hit=N': 8KB data blocks read directly from RAM ('shared_buffers').
•
'Buffers: shared read=N': 8KB data blocks fetched from physical disk storage! (High 'read' count indicates disk I/O bottlenecks!).
Code Implementation Example
sql
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM orders
WHERE customer_id = 1052 AND status = 'COMPLETED';💡Executing EXPLAIN (ANALYZE, BUFFERS) query diagnosis.
🎯 Interview Key Takeaway
Always include BUFFERS in EXPLAIN ANALYZE queries to verify if data is served from RAM (hit) or physical disk (read).
#EXPLAIN#BUFFERS#ANALYZE#Query Performance#Intermediate
Back to all PostgreSQL Questions→