Part 137: Reading Execution Plans: EXPLAIN & EXPLAIN ANALYZE

EXPLAIN reveals the internal algorithm chosen by the database Cost-Based Optimizer.

1. Syntax

-- Show estimated execution plan:
EXPLAIN SELECT * FROM users WHERE email = 'test@devify.com';

-- Actually RUN query and show true execution time & buffer hits:
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM users WHERE email = 'test@devify.com';

Key Plan Terms:

• Seq Scan / Table Scan: Scanned entire table (usually bad on large tables).
• Index Scan: Navigated B-Tree to find rows.
• Index Only Scan: Resolved entirely inside index pages.
• Nested Loop: Efficient for small row sets.
• Hash Join: Efficient for joining large tables.

Key Takeaways

• Always profile slow queries with EXPLAIN (ANALYZE, BUFFERS).

In Part 138, we explore Common Query Antipatterns & SARGability!