Query Plans (EXPLAIN ANALYZE)
Before PostgreSQL runs a query, its query planner decides how to run it: which indexes to use (if any), which join algorithm to pick, and in what order to access tables. EXPLAIN lets you see that decision. It is the single most important tool for understanding — and fixing — slow queries.
EXPLAIN vs EXPLAIN ANALYZE
These two commands look similar but behave very differently, and mixing them up is a common source of confusion.
Command | What it does |
|---|---|
| Shows the planned execution strategy only. The query is not executed, and no rows are returned. Row counts and costs are estimates from planner statistics. |
| Actually executes the query, then shows the plan alongside real timing and real row counts for each step. |
Inspect a write query's plan without committing it
BEGIN; EXPLAIN ANALYZE UPDATE orders SET status = 'shipped' WHERE customer_id = 42; ROLLBACK;
Reading the output
A plan is a tree of nodes, read from the innermost (deepest indented) node outward. Each node reports the operation, its estimated cost, and — with ANALYZE — the actual time and row count. The main things to look for:
Seq Scan on a large table — a full sequential scan of every row. On a small table this is fine and often faster than an index; on a large table it usually signals a missing (or unused) index.
Index Scan — the planner used an index to jump straight to matching rows, then fetched the full row from the table. Generally a good sign for selective queries.
Index Only Scan — the planner satisfied the query entirely from the index itself, without touching the table at all. The fastest access path when it applies.
Estimated vs. actual rows — compare
rows=in the plan (estimate) with the actual row count reported byANALYZE. A large gap between them means the planner's statistics are stale or misleading, which can cause it to pick a bad plan.ANALYZE <table>refreshes those statistics.Nested Loop / Hash Join / Merge Join — the three join strategies. Nested loops are efficient for small row counts on one side; hash and merge joins scale better for large joins.
Worked example: adding an index changes the plan
Suppose orders has a few hundred thousand rows and no index on customer_id. Filtering by it forces a full table scan:
Before: no index on customer_id
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42; -- QUERY PLAN -- ------------------------------------------------------------------------------------------------- -- Seq Scan on orders (cost=0.00..8567.00 rows=48 width=96) (actual time=0.021..42.318 rows=51 loops=1) -- Filter: (customer_id = 42) -- Rows Removed by Filter: 249949 -- Planning Time: 0.112 ms -- Execution Time: 42.351 ms
Every row in the table is checked and discarded except the 51 that match. Adding an index on the filtered column gives the planner a much cheaper path:
After: index added on customer_id
CREATE INDEX idx_orders_customer_id ON orders (customer_id); EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42; -- QUERY PLAN -- --------------------------------------------------------------------------------------------------------------- -- Index Scan using idx_orders_customer_id on orders (cost=0.29..8.51 rows=48 width=96) (actual time=0.018..0.041 rows=51 loops=1) -- Index Cond: (customer_id = 42) -- Planning Time: 0.098 ms -- Execution Time: 0.062 ms
The Seq Scan becomes an Index Scan, the estimated cost drops from thousands to single digits, and execution time falls from tens of milliseconds to a fraction of a millisecond. On a table with millions of rows the difference is even more dramatic.
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42; -- Index Scan using idx_orders_customer_id on orders (...) (actual time=0.018..0.041 rows=51 loops=1) -- Index Cond: (customer_id = 42) -- Buffers: shared hit=4 -- Planning Time: 0.098 ms -- Execution Time: 0.062 ms