A query that takes 200ms in development can take 20 seconds in production once the table has a million rows. The gap usually isn’t the query itself — it’s that the database is doing more work than it needs to: scanning every row when an index would narrow it down, or picking the wrong join order. Learning to read EXPLAIN output and understand what the planner is doing turns query tuning from guesswork into a straightforward diagnosis. All examples use PostgreSQL.

The EXPLAIN Command

EXPLAIN shows the query plan — what steps the database will take to execute your query, in what order, and the planner’s estimated cost.

EXPLAIN SELECT * FROM orders WHERE user_id = 42;

Seq Scan on orders  (cost=0.00..18450.00 rows=150 width=64)
  Filter: (user_id = 42)

Seq Scan means a sequential scan — the database reads every row in the table and filters. For a table with a million rows, that’s expensive.

EXPLAIN ANALYZE — actual numbers

EXPLAIN shows estimates. EXPLAIN ANALYZE actually runs the query and shows real timing:

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;

Seq Scan on orders  (cost=0.00..18450.00 rows=150 width=64)
                    (actual time=0.042..312.881 rows=148 loops=1)
  Filter: (user_id = 42)
  Rows Removed by Filter: 999852
Planning Time: 0.4 ms
Execution Time: 313.2 ms

Key fields to look at:

  • cost: startup..total in arbitrary planner units. Lower is better.
  • rows: estimated vs actual. Large discrepancies indicate stale statistics.
  • actual time: milliseconds. The second number is wall time to complete the node.
  • loops: how many times this node ran (relevant in nested loops).

Reading the Plan Tree

Complex queries produce tree-shaped plans. Read them bottom-up — the innermost node runs first.

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

HashAggregate  (cost=4200..4400 rows=200 width=40)
               (actual time=45.2..47.1 rows=198 loops=1)
  ->  Hash Join  (cost=1200..3800 rows=200 width=32)
                 (actual time=12.3..40.1 rows=5948 loops=1)
        Hash Cond: (o.user_id = u.id)
        ->  Seq Scan on orders  (actual time=0.02..18.4 rows=50000 loops=1)
        ->  Hash  (actual time=11.8..11.8 rows=198 loops=1)
              ->  Index Scan on users  (actual time=0.1..11.5 rows=198 loops=1)
                    Index Cond: (created_at > '2024-01-01')

The planner used an index scan for users (good) but a sequential scan for orders. If orders is large and you only need a subset, an index on orders.user_id would help.

Adding Indexes

B-tree index (default)

Good for equality checks, range queries, and ORDER BY:

CREATE INDEX idx_orders_user_id ON orders (user_id);

After adding the index, re-run EXPLAIN ANALYZE:

Index Scan using idx_orders_user_id on orders  (actual time=0.04..0.9 rows=148 loops=1)
  Index Cond: (user_id = 42)
Execution Time: 1.3 ms

313ms dropped to 1.3ms.

Partial index

Index only the rows you actually query — smaller, faster:

CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

This index is only used for queries that include WHERE status = 'pending', but it’s tiny compared to indexing the full table.

Composite index

When you filter on multiple columns, column order matters. Put the most selective column first, or the column used in equality conditions before range conditions:

CREATE INDEX idx_orders_user_status ON orders (user_id, status);

This supports:

  • WHERE user_id = 42
  • WHERE user_id = 42 AND status = 'pending'

But not WHERE status = 'pending' alone — the leftmost column must be present.

Covering index

An index that includes all columns needed by the query, so the database never has to touch the table:

CREATE INDEX idx_orders_covering ON orders (user_id) INCLUDE (total, created_at);

EXPLAIN ANALYZE SELECT total, created_at FROM orders WHERE user_id = 42;

Index Only Scan using idx_orders_covering on orders
  (actual time=0.02..0.3 rows=148 loops=1)
  Heap Fetches: 0

Heap Fetches: 0 means the query was answered entirely from the index.

Join Strategies

PostgreSQL picks from three join algorithms depending on table size and available indexes.

Nested Loop Join

For small inner tables or when the inner side has an index. The outer loop iterates rows; for each, it probes the inner table.

Nested Loop
  ->  Index Scan on users (rows=10)
  ->  Index Scan on orders (Index Cond: user_id = users.id)

Very fast when the inner table hit count is low. Scales poorly when both sides are large.

Hash Join

Builds a hash table from the smaller relation, then probes it for each row in the larger one. Good for large equi-joins without indexes.

Hash Join
  Hash Cond: (o.user_id = u.id)
  ->  Seq Scan on orders
  ->  Hash
        ->  Seq Scan on users

Merge Join

Both sides must be sorted on the join key. Efficient for already-sorted data or when an index provides sorted order.

Hinting the planner (use sparingly)

PostgreSQL doesn’t support query hints natively, but you can disable strategies temporarily to test:

SET enable_hashjoin = off;
EXPLAIN ANALYZE SELECT ...;
SET enable_hashjoin = on;

Common Optimization Patterns

Update statistics

Stale statistics cause bad estimates. Run ANALYZE after bulk loads:

ANALYZE orders;
-- Or for the whole database:
ANALYZE;

PostgreSQL autovacuum handles this automatically for most workloads, but after large batch inserts it’s worth running manually.

Avoid functions on indexed columns

This kills index usage:

-- Bad: function prevents index on created_at from being used
SELECT * FROM orders WHERE DATE(created_at) = '2024-06-01';

-- Good: range condition uses the index
SELECT * FROM orders
WHERE created_at >= '2024-06-01' AND created_at < '2024-06-02';

Use LIMIT with ORDER BY

SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;

With an index on created_at, the planner can use an index scan and stop after 10 rows instead of sorting the whole table.

Check for sequential scans on large tables

-- Find tables being sequentially scanned most often
SELECT schemaname, relname, seq_scan, idx_scan
FROM pg_stat_user_tables
ORDER BY seq_scan DESC
LIMIT 20;

High seq_scan on a large table is a signal to check whether a useful index is missing.

Use pg_stat_statements for slow query discovery

-- Requires pg_stat_statements extension
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

This is your starting point for finding what to optimize in production.

Conclusion

Query optimization follows a consistent workflow: run EXPLAIN ANALYZE, identify the expensive node, add or adjust an index, and re-measure. Most performance problems trace back to sequential scans on large tables, missing composite indexes, or functions that disable index usage. Once you get comfortable reading plan output, you’ll spot the problem in seconds — and the fix is usually a single CREATE INDEX statement.