Most query performance problems are visible if you know how to look. The database tells you exactly how it’s executing a query - what indexes it’s using, how many rows it’s scanning, where the time goes. The tool for this in PostgreSQL is EXPLAIN ANALYZE.

Learning to read its output is one of the highest-value things a backend developer can do.

The Basic Command

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id, u.name;

EXPLAIN shows the query plan without executing the query. EXPLAIN ANALYZE actually executes the query and shows both the plan and the actual execution statistics. Use EXPLAIN ANALYZE to see real numbers. Use EXPLAIN (without ANALYZE) for queries you can’t afford to run (like deletes that would mutate data).

A typical output:

HashAggregate  (cost=1250.43..1275.43 rows=2500 width=40)
               (actual time=45.832..47.210 rows=2500 loops=1)
  Group Key: u.id, u.name
  ->  Hash Left Join  (cost=450.00..1125.43 rows=25000 width=32)
                      (actual time=8.421..38.654 rows=25000 loops=1)
        Hash Cond: (o.user_id = u.id)
        ->  Seq Scan on orders o  (cost=0.00..550.00 rows=25000 width=8)
                                  (actual time=0.015..12.342 rows=25000 loops=1)
        ->  Hash  (cost=325.00..325.00 rows=5000 width=36)
                  (actual time=7.832..7.833 rows=5000 loops=1)
              ->  Seq Scan on users u  (cost=0.00..325.00 rows=5000 width=36)
                                       (actual time=0.012..5.234 rows=2847 loops=1)
                    Filter: (created_at > '2024-01-01')
                    Rows Removed by Filter: 2153
Planning Time: 0.412 ms
Execution Time: 47.893 ms

Reading this bottom-up (innermost nodes execute first):

1. Sequential scan on users, filtering by created_at - scans all 5000 rows, returns 2847
2. Sequential scan on orders - scans all 25000 rows
3. Hash join combining both
4. Aggregation

The Nodes That Matter

Seq Scan (Sequential Scan): reads the entire table from beginning to end. Every row is examined. Fine for small tables or when you need most of the rows. A warning sign when the table is large and the query filters should narrow results significantly.

Index Scan: follows an index to find specific rows, then fetches each row from the table. Good for selective queries that return a small fraction of the table. The Filter line shows any filtering done after the index lookup.

Index Only Scan: like an index scan, but all needed columns are in the index itself - no table fetch required. The fastest type of scan. Appears when your query only needs columns covered by the index.

Bitmap Heap Scan: collects row locations from an index (or multiple indexes), then fetches all needed rows from the table in physical order. More efficient than many random Index Scans when returning a moderate number of rows.

Nested Loop: for each row in the outer relation, scan the inner relation. Efficient when the inner scan uses an index and the outer result set is small. Catastrophically slow if the outer set is large and the inner scan is a sequential scan.

Hash Join: build a hash table from one side of the join, then probe it with the other side. Good for larger joins when both sides need to be read fully.

Merge Join: requires both sides to be sorted on the join key. Efficient when both sides have appropriate indexes.

Reading Cost Numbers

The cost numbers: (cost=start..total rows=N width=W)

• start: estimated cost before the first row is returned
• total: estimated cost to return all rows
• rows: estimated number of rows returned
• width: estimated average width of a row in bytes

Cost is measured in arbitrary units where 1.0 = one sequential page read. These are estimates based on table statistics. The actual time values are what actually happened.

When estimates are wildly off from actuals (estimated 100 rows, actually 100,000), the planner made a bad plan based on stale statistics. Run ANALYZE tablename to update statistics.

Finding the Bottleneck

The total execution time is at the bottom. To find where time is spent:

1. Find the node with the highest actual time on its right side (the cumulative time including children)
2. Check its children’s times to see if one child dominates
3. Keep drilling until you find the leaf node that’s slow

Common patterns:

Sequential scan on a large table with a filter: the table needs an index on the filter column. If you’re filtering on users.email, create an index: CREATE INDEX ON users(email).

Index scan that returns too many rows: the index is less selective than expected. Check if the filter column has low cardinality (many rows with the same value).

Nested loop with a large outer set: missing index on the inner table’s join column. orders.user_id should have an index if you’re joining users to orders.

N+1 pattern: you see the same query executed 1000 times with different parameters. The application is calling the query in a loop instead of batching. Fix at the application level with a single query using IN (...) or a JOIN.

A Practical Example: Fixing a Slow Query

Starting with a slow query:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE status = 'pending' AND created_at > NOW() - INTERVAL '7 days';

Output shows a Seq Scan on orders scanning 500,000 rows to return 847.

Fix 1: single-column index on created_at (usually the more selective filter):

CREATE INDEX CONCURRENTLY idx_orders_created_at ON orders(created_at);

Re-run EXPLAIN ANALYZE. If it now uses an Index Scan but still scans many rows because many orders were created in the last 7 days, consider a composite index:

CREATE INDEX CONCURRENTLY idx_orders_status_created ON orders(status, created_at);

The order matters: put the equality condition (status = 'pending') first, the range condition (created_at > ...) second.

CREATE INDEX CONCURRENTLY creates the index without locking the table for writes - use this in production.

When Not to Add an Index

Indexes speed up reads and slow down writes. Every INSERT, UPDATE, and DELETE must maintain all indexes on the table. Tables with very high write rates and many indexes can have significant write overhead.

Indexes also take storage space and memory. An index that’s never used is pure cost.

Before adding an index, check if it would actually be used:

-- After creating the index, check if it gets used
SELECT indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname = 'orders';

An index with idx_scan = 0 after a few weeks in production is probably not needed.

The fastest query is one that avoids unnecessary work. EXPLAIN ANALYZE shows you exactly what work is being done. The fix is almost always either an index, a statistics update, or a query rewrite that avoids scanning rows you don’t need.