Query Execution Plan Deep Dive
A deep dive into reading execution plans helps you diagnose complex slow queries. Understanding join strategies (Nested Loop, Hash Join, Merge Join), cost units, memory usage, and the optimizer's decisions lets you tune queries with precision.
Execution Plan Node Types
- Seq Scan - reads every page of the table sequentially
- Index Scan - navigates B-tree → retrieves heap rows (random I/O)
- Index Only Scan - data in index itself, no heap access
- Bitmap Index Scan → Bitmap Heap Scan - efficient for multiple ranges
- Nested Loop Join - for each outer row, looks up inner table (great with index)
- Hash Join - build hash table from smaller table, probe with larger (no index needed)
- Merge Join - merge two pre-sorted inputs (requires sorted or indexed data)
Execution Plan Deep Dive
-- Complex query plan analysis
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT
u.name,
COUNT(o.id) AS order_count,
SUM(p.amount) AS total_paid
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
LEFT JOIN payments p ON o.id = p.order_id
GROUP BY u.id, u.name;
-- Typical plan (with indexes):
-- HashAggregate (GROUP BY)
-- Hash Left Join (users + payments)
-- Hash Left Join (users + orders)
-- Seq Scan on users (3 rows - small table)
-- Index Scan on orders using idx_orders_user_id
-- Seq Scan on payments (2 rows - small table)
-- Planning Time: 0.5 ms
-- Execution Time: 0.3 ms
-- Diagnosing a slow plan: look for:
-- 1. Seq Scan on large tables → add index
-- 2. Large estimated rows that don't match actual → run ANALYZE to update stats
-- 3. Nested Loop on large tables without index → add index on join column
-- 4. Hash Join with huge memory usage → increase work_mem
-- Force index use (PostgreSQL - for testing)
SET enable_seqscan = OFF;
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending';
-- Forces index scan even if optimizer prefers seq scan
-- Update table statistics (needed after bulk loads)
ANALYZE orders; -- updates row count/distribution stats
VACUUM ANALYZE orders; -- reclaim dead rows + update statsQuick Quiz
Tip
Tip
Practice Query Execution Plan Deep Dive in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
Technical diagram.
Common Mistake
Warning
A common mistake with Query Execution Plan Deep Dive is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of Query Execution Plan Deep Dive from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- A deep dive into reading execution plans helps you diagnose complex slow queries.
- Seq Scan - reads every page of the table sequentially
- Index Scan - navigates B-tree → retrieves heap rows (random I/O)
- Index Only Scan - data in index itself, no heap access