What is EXPLAIN in PostgreSQL?
Why Interviewers Ask This
This is a classic screening question for PostgreSQL roles. Hiring managers ask it early in interviews to gauge your baseline understanding and determine if you can communicate technical concepts clearly.
Answer
EXPLAIN displays the query execution plan that the PostgreSQL query planner will use for a query. EXPLAIN SELECT * FROM users WHERE email = 'a@b.com'; shows the plan without executing. EXPLAIN ANALYZE executes the query and shows actual timings alongside estimated ones — essential for diagnosing performance. Key plan nodes: Seq Scan (full table scan), Index Scan (uses index), Index Only Scan (data from index only — fastest), Bitmap Heap Scan (batch index+heap reads), Hash Join/Merge Join/Nested Loop (join algorithms). Look at cost, rows estimates vs actuals, and where estimates are far off (stale statistics). Run ANALYZE tablename to update statistics.
Common Mistake
Candidates often give textbook answers here. Interviewers are more impressed when you relate the concept to a specific problem you solved in a real PostgreSQL project.