Q18 of 26 · SQL
What does EXPLAIN (or EXPLAIN ANALYZE) do, and why might a QA engineer use it?
SQLMidsqlexplainquery-performanceindexesperformance-testing
Short answer
Short answer: EXPLAIN shows the query execution plan — how the database will fetch and join rows. EXPLAIN ANALYZE actually runs the query and shows real timing. QA engineers use it to diagnose slow test setup queries or to verify that an index exists and is being used.
Detail
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending';
The output shows whether the DB performs a Seq Scan (scans every row — slow on large tables) or an Index Scan (uses an index — fast). If your test-data setup query takes 10 seconds because of a missing index, EXPLAIN reveals it immediately.
QA-relevant reads from the output:
- Seq Scan on large table → missing index, test will be slow in production-sized environments
- Hash Join vs Nested Loop → join strategy; nested loops on large tables without indexes are O(n²)
- actual rows vs estimated rows → if the DB estimates 1 row but scans 10,000, table statistics are stale (run ANALYZE to update them)
Typical QA use cases:
- Your test seed query takes 30 seconds — EXPLAIN shows a Seq Scan; add an index and it drops to 0.3s.
- Verifying that a new database index the team added actually gets used for the expected query pattern.
- During performance testing: confirming that queries under load have the execution plan you expect.
Note: EXPLAIN ANALYZE executes the query, so don't use it with destructive statements unless wrapped in a transaction you'll ROLLBACK.
// WHAT INTERVIEWERS LOOK FOR
Knowing what EXPLAIN shows (execution plan) vs EXPLAIN ANALYZE (actual execution). A concrete QA scenario — slow test setup or index verification.