Advanced

SQL Query Optimization: Indexes, EXPLAIN & Speed

A query that works on 100 rows can crawl on 10 million. Learn to read EXPLAIN plans, index the right columns, and avoid the classic slow-query traps.

Optimization starts with measurement, not guessing. A query that returns instantly on your laptop with 100 rows can take 30 seconds in production against 10 million. The difference is rarely the SQL syntax — it is how the database chooses to execute it. Before you change anything, ask the database exactly what it is doing, then fix the single step that actually costs the most. Many slow queries trace back to a misused JOIN or a correlated subquery you can replace with a window function.

This guide walks through the optimization loop in order: measure with EXPLAIN, understand what the numbers mean, add the right index, help the planner pick a good join, and rewrite the handful of patterns that defeat indexes entirely.

Reading EXPLAIN ANALYZE

EXPLAIN shows the planned execution. EXPLAIN ANALYZE actually runs the query and reports real timings and row counts alongside the estimates. The gap between estimated and actual rows is one of the most useful signals you have — a large gap usually means the planner is working from stale statistics and is making bad choices as a result.

EXPLAIN ANALYZE
SELECT u.name, SUM(o.total) AS revenue
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid'
GROUP BY u.name;

Read the plan from the inside out and bottom up: the most deeply indented nodes run first, feeding their rows up to their parents. Each node tells you the operation (Seq Scan, Index Scan, Hash Join), how long it took, and how many rows it produced versus how many it expected.

Add BUFFERS to see how much data was read from disk versus cache. High "read" counts on a query you run constantly often point to a missing index more clearly than the timings do.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE status = 'paid';

Understanding the cost numbers

Every node shows a cost like cost=0.00..431.00 and a row estimate like rows=8400. The first cost number is the startup cost (work before the first row appears); the second is the total cost to return all rows. The units are arbitrary planner units, not milliseconds — they only matter relative to each other.

  • rows= is the planner's estimate; "actual rows" (in ANALYZE) is the truth — compare them
  • A 10x or larger gap between estimated and actual rows means your statistics are stale
  • loops= multiplies the per-loop cost; a cheap node run 50,000 times is expensive
  • The widest "actual time" node, not the scariest-looking one, is your real bottleneck

How indexes work

An index is a sorted structure — usually a B-tree — that lets the database jump straight to matching rows instead of scanning the whole table. Think of it like the index at the back of a book: instead of reading every page to find a topic, you look it up and turn directly to the right page.

The trade-off is that every INSERT, UPDATE, and DELETE must also keep the index up to date, and the index takes disk space. That is why you index deliberately — the columns you filter and join on — rather than indexing everything.

index lookup vs full scan — Without an index the database reads every row (amber) to find a match — a sequential scan. With an index it jumps straight to the row: the difference between 8 reads and 1, and far larger at real scale.

-- Index the column you filter / join on
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- Composite index for multi-column filters (order matters!)
CREATE INDEX idx_orders_status_date ON orders (status, order_date);

Index types that matter

The default B-tree handles the vast majority of cases — equality and range comparisons on ordered values. A few specialized types solve problems B-trees cannot.

  • B-tree: the default — equality (=) and ranges (<, >, BETWEEN, ORDER BY)
  • Partial: indexes only rows matching a condition, e.g. WHERE status = 'active' — smaller and faster
  • Covering (INCLUDE): stores extra columns in the index so the query never touches the table
  • GIN: for full-text search and JSONB containment queries
-- Partial index: only the rows you actually query
CREATE INDEX idx_active_orders ON orders (created_at)
WHERE status = 'active';

-- Covering index: serve the whole query from the index alone
CREATE INDEX idx_orders_user_covering
ON orders (user_id) INCLUDE (total, status);

When to add an index

  • Always index foreign-key columns used in JOINs
  • Index high-cardinality columns frequently used in WHERE
  • Index columns used in ORDER BY on large result sets
  • Skip low-cardinality columns (a boolean rarely benefits)
  • Skip indexes on small tables — a full scan is already fast
  • Drop indexes nothing queries — they only slow down writes

How the planner joins tables

When you JOIN two tables, PostgreSQL picks one of three strategies based on table sizes and available indexes. Knowing them helps you read plans and understand why an index suddenly made a query 100x faster.

  • Nested Loop: for each row in the outer table, look up matches in the inner — fast when the inner side is indexed and the outer side is small
  • Hash Join: build a hash table from the smaller table, then probe it — great for joining two large unindexed tables
  • Merge Join: sort both inputs and walk them together — efficient when both sides are already sorted on the join key

Slow-query patterns

Some patterns defeat indexes entirely. Wrapping an indexed column in a function, or leading a LIKE with a wildcard, forces a full scan no matter how many indexes you have. Conditions the index can use are called "sargable" — the goal is to keep your WHERE clauses sargable.

-- BAD: function on the column kills the index
SELECT * FROM users WHERE LOWER(email) = 'a@b.com';

-- GOOD: store/compare consistently, or use a functional index
CREATE INDEX idx_users_email_lower ON users (LOWER(email));

-- BAD: leading wildcard can't use a normal B-tree index
SELECT * FROM products WHERE name LIKE '%phone%';

-- BAD: implicit type cast on the indexed column
SELECT * FROM orders WHERE user_id = '42';  -- user_id is an integer

OR conditions across different columns can also prevent a single index from being used. Splitting them into a UNION of two indexed queries is sometimes dramatically faster.

High-impact rewrites

  • Replace correlated subqueries with JOINs or window functions
  • Use EXISTS instead of IN for large subquery result sets
  • Select only the columns you need — avoid SELECT * over wide tables
  • Filter early: push WHERE conditions before joins where possible
  • Paginate with keyset (WHERE id > last_id) instead of large OFFSET
  • Aggregate once in a CTE instead of repeating the same subquery
-- Slow at high offsets: scans and discards 10,000 rows
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 10000;

-- Fast keyset pagination: jumps straight in
SELECT * FROM orders
WHERE id > 10000
ORDER BY id
LIMIT 20;

For the subquery-versus-CTE side of these rewrites, see the deeper breakdown in subqueries vs CTEs.

Optimization checklist

  • Run EXPLAIN (ANALYZE, BUFFERS) and find the most expensive node
  • Compare estimated vs actual rows — a big gap means stale statistics
  • Confirm foreign keys and filtered columns are indexed
  • Make sure WHERE conditions are sargable (no functions on columns)
  • Run ANALYZE so the planner has fresh statistics
  • Re-measure — optimize the real bottleneck, not a guess