Skip to content
RED SAGSkills
mediumDatabase Administrator interview question

Walk me through how you read an EXPLAIN ANALYZE plan to find why a query is slow.

What the interviewer wants to hear

  • Use EXPLAIN (ANALYZE, BUFFERS) on realistic data and parameters
  • Read the tree from the inside out and find the nodes with most actual time
  • Compare estimated rows with actual rows to spot bad statistics
  • Look for sequential scans on large tables, sorts or hashes spilling to disk, and nested loops with many loops
  • Fix the root cause: index, statistics, query rewrite or memory, then measure again

Model answer

I run EXPLAIN (ANALYZE, BUFFERS) with the real parameters, ideally on production-like data, because plans change with data size. The plan is a tree, so I read it from the innermost nodes up and look for where the actual time jumps. The most useful check is estimated rows versus actual rows: if the planner expected 10 rows and got 1 million, it probably chose the wrong join type, so I look at statistics, stale ANALYZE, correlated columns or a function hiding a column. Then I look for sequential scans on big tables that should use an index, sorts showing external merge on disk, hash joins in batches, and nested loops with huge loop counts. BUFFERS shows whether time goes to reading from disk. I change one thing, such as an index or rewrite, and compare the plan again.

Can you answer it under time pressure?

Take a timed medium round and get scored on every answer.

Start mock interview

More medium Database Administrator questions