EXPLAIN ANALYZEAdvanced
1Question 1 of 1
sqlHow do you analyze PostgreSQL execution plans with EXPLAIN (ANALYZE, BUFFERS)?
Explanation & Concept
`EXPLAIN ANALYZE` executes the query and returns actual wall-clock timing alongside planner estimates. Adding the `BUFFERS` option displays how many 8KB pages were read from the PostgreSQL shared buffer cache (`shared hit`) versus fetched from OS disk storage (`shared read`).
High `shared read` indicates cache misses and high disk I/O, signifying missing indexes or inadequate shared memory allocation.
Code Example
sql
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT * FROM users
WHERE email = 'alex@example.com';
-- Sample Output:
-- Index Scan using idx_users_email on users (cost=0.29..8.30 rows=1 width=128)
-- (actual rows=1 loops=1)
-- Index Cond: (email = 'alex@example.com')
-- Buffers: shared hit=3How It Works & Key Takeaways
`shared hit=3` means only 3 memory pages were inspected directly from RAM, with zero disk reads. This confirms optimal index performance.
Mock Test · 1 Questions · Optional
Ready for Extensions (PostGIS etc.)?
Take a quick test to evaluate yourself, or move straight to the next module.
Advanced1 questions