Master query plan vocabulary: EXPLAIN ANALYZE output, sequential vs index scans, hash joins, cost estimates, and identifying query bottlenecks.
0 / 5 completed
1 / 5
What does `EXPLAIN ANALYZE` do in PostgreSQL?
EXPLAIN ANALYZE actually executes the query and reports: the plan the query planner chose, the planner's row/cost estimates alongside actual row counts and timing per node. Discrepancies between estimates and actuals reveal planner statistics problems.
2 / 5
A query plan shows 'Seq Scan on orders (cost=0.00..48320.00)'. What does this mean?
A sequential scan reads every row in the table. For small tables or queries that retrieve most rows, this is fine. For selective queries on large tables (e.g. WHERE user_id = 12345), a sequential scan usually indicates a missing or unused index.
3 / 5
What does 'the query planner chose a hash join' mean?
A hash join builds a hash table from the smaller join input in memory, then scans the larger input to find matches. It's efficient for large equi-joins but requires memory. If the hash table spills to disk, performance degrades significantly.
4 / 5
What does 'cost estimate vs. actual time' discrepancy in EXPLAIN ANALYZE indicate?
Large discrepancies between estimated and actual rows typically mean the table statistics are stale (ANALYZE hasn't been run) or the data distribution is skewed in ways the planner doesn't model well. The fix is usually running ANALYZE or creating custom statistics.
5 / 5
An engineer says 'the bottleneck is the nested loop join'. What does this mean?
Nested loop joins execute an inner scan for every outer row. When the inner side lacks an index, each outer row triggers a sequential scan — making the join O(n×m). For large tables, this is devastating for performance and usually signals a missing index.