EXPLAIN ANALYZE command example in PostgreSQL

Imagine you have a huge orders table (millions of rows) but forgot to create an index on the customer_id column.

---

The Slow Query

You run this query because it's running too slow:

SELECT * FROM orders WHERE customer_id = 54321;

The Diagnosis: EXPLAIN ANALYZE

You run the analysis command:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 54321;

The Output (The Bad Plan)

The results will look something like this:

                                  QUERY PLAN
--------------------------------------------------------------------------------------
 Seq Scan on orders  (cost=0.00..120000.00 rows=100 loops=1) 
   (actual time=500.000..7500.000 rows=100 loops=1) 
   Filter: (customer_id = 54321)
   Rows Removed by Filter: 9999900
 Planning Time: 0.100 ms
 Execution Time: 7500.000 ms (7.5 seconds)

Term	What it Means	The Problem ⚠️
Seq Scan on orders	The plan is to perform a Sequential Scan, meaning PostgreSQL read every single row in the entire orders table.	This is the bottleneck. Scanning millions of rows is very slow.
cost=...	PostgreSQL's internal estimate of the query cost.	The planner knew this would be expensive, but it didn't have a better option.
actual time=...7500.000	The actual time spent on this step was 7,500 milliseconds (7.5 seconds).	This confirms the query is slow!
Rows Removed by Filter: 9999900	The database read 10,000,000 rows but discarded 9,999,900 of them because they didn't match the customer_id.	It did 99.999% unnecessary work.

---

The Fix: Create the Index

To fix this, you create an index on the column you are filtering by:

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

The Re-Diagnosis: The Good Plan

You run EXPLAIN ANALYZE again:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 54321;

The Output (The Good Plan)

The plan changes dramatically:

                                  QUERY PLAN
--------------------------------------------------------------------------------------
 Index Scan using idx_orders_customer_id on orders  (cost=0.42..8.44 rows=100 loops=1) 
   (actual time=0.150..0.450 rows=100 loops=1) 
 Planning Time: 0.120 ms
 Execution Time: 0.500 ms (0.0005 seconds)

Term	What it Means	The Improvement ✅
Index Scan using...	The plan is now to use the index you created.	The database skips the massive table and jumps straight to the data using the index.
actual time=...0.500	The total execution time is now 0.5 milliseconds.	The query is now over 15,000 times faster!

This example shows how EXPLAIN ANALYZE helps you confirm why a query is slow and prove that your optimization (the new index) actually fixed the problem.