âš¡IntermediateViews & Indexes
EXPLAIN and EXPLAIN ANALYZE
Read query execution plans to understand and optimize how PostgreSQL runs your queries.
Overview
EXPLAIN shows the query execution plan how PostgreSQL will retrieve data (sequential scan, index scan, hash join, etc.) and the estimated cost. EXPLAIN ANALYZE actually executes the query and shows actual vs estimated row counts and timings. Understanding plans is essential for optimization.
Key metrics: Seq Scan (full table scan), Index Scan (uses index), cost (startup..total), actual time, rows, loops. A high difference between estimated and actual rows indicates stale statistics run ANALYZE to update them.
Key Points
- •EXPLAIN SELECT ... shows plan without executing
- •EXPLAIN ANALYZE SELECT ... executes and shows actual timings
- •EXPLAIN (ANALYZE, BUFFERS) also shows cache hit statistics
- •Seq Scan on large tables is a performance red flag
- •High estimated vs actual row count difference → run ANALYZE
- •EXPLAIN (FORMAT JSON) machine-readable output
Examples
Reading query plans
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42;
-- Human readable: cost=startup..total
-- Seq Scan: full table scan
-- Index Scan: uses index
-- Hash Join / Nested Loop: join strategiesExercises
Write SQL to solve each exercise. Your progress is saved automatically.
Loading editor…
Completed: 0 / 5 exercises
Knowledge Check
Question 1 of 4