PostgreSQL interview question · Most asked
How do you read EXPLAIN ANALYZE output in PostgreSQL?
Summary
Read the plan tree from the innermost nodes outward. For each node compare estimated rows with actual rows, look at actual time (per loop, multiplied by loops), and add BUFFERS to see how many pages came from cache versus disk. Big estimate errors and nodes with most of the time are where to look.
Longer Answer
- Node types:
Seq Scan,Index Scan,Index Only Scan,Bitmap Heap Scan; joinsNested Loop,Hash Join,Merge Join;Sort(watch forexternal merge Disk),HashAggregate. rows=1000estimated versusactual rows=950000usually means bad statistics or correlated columns, leading to a wrong join strategy.Rows Removed by Filtershows wasted work an index could avoid.EXPLAIN (ANALYZE, BUFFERS)showsshared hit(cache) versusread(disk).
EXPLAIN ANALYZE executes the statement; wrap writes in BEGIN ... ROLLBACK. Tools such as explain.depesz.com or explain.dalibo.com visualise plans.
Key Points
- Inner nodes first; time x loops.
- Estimated vs actual rows.
- BUFFERS: cache vs disk.
- Wrap DML in a rolled-back transaction.
Explanation
What interviewers look for
Comparing estimates with actuals and connecting a bad estimate to a bad plan choice.
Follow-up questions
- What would you do if estimates are badly off?
More most-asked PostgreSQL questions
- JSON or JSONB in PostgreSQL, and how do you index JSONB?
- Why do PostgreSQL deployments need connection pooling, and how do PgBouncer's modes differ?
- How does MVCC work in PostgreSQL?
- What isolation levels does PostgreSQL provide, and how do they behave?
- What index types does PostgreSQL support, and when do you use each?
- Which schema changes take heavy locks in PostgreSQL, and how do you avoid outages during migrations?
- What does VACUUM do, and how does autovacuum work?
- How do you build a job queue on PostgreSQL with SELECT ... FOR UPDATE SKIP LOCKED?