What is the difference between EXPLAIN and EXPLAIN ANALYZE, and how do you read a query plan?

Before an IRCTC train leaves, the chart shows the planned route and expected arrival times. That is EXPLAIN: the plan and the guesses. After the journey, the running status tells you the real times at every station and where it got delayed. That is EXPLAIN ANALYZE: the plan plus what really happened.

When a query is slow, you first look at the plan to see what PostgreSQL decided to do, then you look at the actual numbers to find the step that ate the time.

EXPLAIN
SELECT * FROM orders WHERE customer_id = 42;

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

Output of the second command (numbers will differ on your machine):

Index Scan using idx_orders_customer on orders  (cost=0.43..8.45 rows=1 width=64) (actual time=0.031..0.035 rows=3 loops=1)
  Index Cond: (customer_id = 42)
Planning Time: 0.120 ms
Execution Time: 0.060 ms

How to read one line:

A plan is a tree. Each node feeds its rows to the node above it. Read it from the most indented line upwards: the innermost nodes run first (scans), then joins, sorts and aggregates on top.

Node names you will see often:

• Seq Scan: reads the whole table.
• Index Scan / Index Only Scan: uses an index; "only" means it did not need the table.
• Bitmap Heap Scan: collects matching locations from an index, then reads table pages in order.
• Nested Loop, Hash Join, Merge Join: the three join methods.

The most useful check is estimated rows versus actual rows. If the planner expected 10 rows and got 200,000, it probably chose a bad join method or skipped an index. That usually points to stale or missing statistics, so try ANALYZE table_name.

Also look for "Rows Removed by Filter" with a big number, which means a scan read many rows only to throw them away. An index on that filter column often helps.

• Checking whether a new index is actually being used before shipping it.
• Finding why a dashboard query on a large Swiggy-style orders table suddenly became slow after data grew.
• Adding BUFFERS (EXPLAIN (ANALYZE, BUFFERS)) to see how many pages came from cache and how many from disk.

• Running EXPLAIN ANALYZE on an UPDATE or DELETE in production. It really executes the statement. Wrap it in a transaction and roll back:

BEGIN;
EXPLAIN ANALYZE DELETE FROM sessions WHERE expires_at < now();
ROLLBACK;

• Treating cost as milliseconds. It is an internal unit used to compare plans.
• Forgetting to multiply actual time by loops for nodes inside a nested loop.
• Testing on a tiny local table. The planner picks different plans for 100 rows and for 10 million rows.

• When would the planner choose a Hash Join over a Nested Loop?
• What is the difference between an Index Scan and a Bitmap Heap Scan?
• How would you find the slowest queries in a live system? (Hint: the pg_stat_statements extension.)