SQL Query Optimization — EXPLAIN ANALYZE, Indexes, and Fixing N+1 Queries

Where Slow Queries Come From

Most database performance problems fall into three categories: missing indexes (sequential scans on large tables), N+1 queries (100 users = 101 queries instead of 2), and poorly structured queries that prevent the query planner from choosing the optimal execution path.

The good news: fixing any one of these three problems routinely delivers 10x–100x speedups. This guide shows you how to diagnose and fix each one using PostgreSQL (most examples) with notes for MySQL where they differ.

While working through query results, use the DevKits JSON Formatter to format JSON output from PostgreSQL's EXPLAIN FORMAT JSON, and the SQL Formatter to clean up complex queries before analyzing them.

Reading EXPLAIN ANALYZE

EXPLAIN shows the query plan. EXPLAIN ANALYZE actually runs the query and shows real execution times. Always use EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) for a complete picture.

-- Basic explain
EXPLAIN SELECT * FROM orders WHERE user_id = 42;

-- Full analysis with buffer stats
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT)
SELECT
    o.id,
    o.total,
    u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.created_at > NOW() - INTERVAL '30 days'
ORDER BY o.total DESC
LIMIT 100;

Key fields to understand:

Seq Scan — reading the whole table row by row. Acceptable for small tables, terrible for large ones.
Index Scan — using an index to find rows. Fast for selective queries.
Index Only Scan — index contains all needed columns, heap not accessed. Very fast.
Hash Join / Merge Join / Nested Loop — different strategies for JOINs. Hash joins are usually best for large tables.
rows=X (actual rows=Y) — if estimated vs actual rows differ by 10x+, statistics are stale. Run ANALYZE tablename.
Buffers: shared hit=X read=Y — hits are from cache; reads are from disk. High reads = cold cache or missing index.

Index Types and When to Use Each

-- B-Tree (default): equality, range queries, ORDER BY
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);

-- Composite B-Tree: multi-column filtering
-- Order matters: put the equality column first
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
-- Supports: WHERE user_id = ? AND status = ?
-- Also supports: WHERE user_id = ?  (leading column alone)
-- Does NOT use: WHERE status = ?   (non-leading column alone)

-- Partial index: index a subset of rows (much smaller, faster)
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';
-- Only pending orders are indexed — 10% of the table instead of 100%

-- GIN index: for arrays, JSONB, and full-text search
CREATE INDEX idx_products_tags ON products USING GIN(tags);
-- Supports: WHERE tags @> ARRAY['python', 'tutorial']

CREATE INDEX idx_events_data ON events USING GIN(data jsonb_path_ops);
-- Supports: WHERE data @> '{"type": "click"}'

-- GiST index: for geometric data and full-text (tsvector)
CREATE INDEX idx_locations_coords ON locations USING GIST(coords);

-- Expression index: index the result of a function
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
-- Supports: WHERE LOWER(email) = '[email protected]'
-- Without this, a function call in WHERE defeats a regular email index

The N+1 Query Problem

The most common ORM performance mistake. You load N users, then for each user you make an additional query to load their orders — that's N+1 queries instead of 2.

Identifying N+1

# Bad: N+1 with SQLAlchemy
users = session.query(User).limit(100).all()
for user in users:
    # This fires a new SQL query for EACH user — 100 extra queries!
    print(user.orders)  # lazy loaded

Fix 1: Eager Loading with JOIN

from sqlalchemy.orm import joinedload

# Good: 1 query with JOIN
users = (
    session.query(User)
    .options(joinedload(User.orders))
    .limit(100)
    .all()
)
# Only 1 query:
# SELECT users.*, orders.* FROM users
# LEFT OUTER JOIN orders ON orders.user_id = users.id
# LIMIT 100

Fix 2: Subquery Load (better for large collections)

from sqlalchemy.orm import subqueryload

# 2 queries total: one for users, one for all their orders
users = (
    session.query(User)
    .options(subqueryload(User.orders))
    .limit(100)
    .all()
)

Fix 3: Raw SQL with JOIN (maximum control)

-- Fetch users with order counts in one query
SELECT
    u.id,
    u.name,
    u.email,
    COUNT(o.id) AS order_count,
    SUM(o.total) AS lifetime_value
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2025-01-01'
GROUP BY u.id, u.name, u.email
ORDER BY lifetime_value DESC NULLS LAST
LIMIT 50;

Optimizing JOINs

-- Slow: function call on indexed column disables index use
SELECT * FROM orders
WHERE DATE(created_at) = '2026-03-01';

-- Fast: use a range comparison instead
SELECT * FROM orders
WHERE created_at >= '2026-03-01'
  AND created_at < '2026-03-02';

-- Slow: implicit type cast kills the index
SELECT * FROM users WHERE id = '42';  -- id is integer, '42' is text

-- Fast: use correct type
SELECT * FROM users WHERE id = 42;

-- Slow: OR across multiple columns — can't use a single index efficiently
SELECT * FROM products
WHERE category = 'books' OR tag = 'python';

-- Fast: UNION (each branch can use its own index)
SELECT * FROM products WHERE category = 'books'
UNION ALL
SELECT * FROM products WHERE tag = 'python';

Window Functions Instead of Subqueries

-- Slow: correlated subquery for running total
SELECT
    o.id,
    o.total,
    (SELECT SUM(o2.total) FROM orders o2
     WHERE o2.user_id = o.user_id
       AND o2.created_at <= o.created_at) AS running_total
FROM orders o
WHERE o.user_id = 42;

-- Fast: window function (single pass)
SELECT
    id,
    total,
    SUM(total) OVER (
        PARTITION BY user_id
        ORDER BY created_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM orders
WHERE user_id = 42;

CTEs and Materialization

-- PostgreSQL 12+: CTEs are NOT materialized by default (inlined)
-- Use MATERIALIZED to force a temporary result set (useful for large repeated CTEs)
WITH RECURSIVE category_tree AS MATERIALIZED (
    SELECT id, name, parent_id, 0 AS depth
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    SELECT c.id, c.name, c.parent_id, ct.depth + 1
    FROM categories c
    JOIN category_tree ct ON ct.id = c.parent_id
    WHERE ct.depth < 10
)
SELECT * FROM category_tree ORDER BY depth, name;

-- NOT MATERIALIZED: let the planner inline the CTE (default in PG12+)
WITH active_users AS NOT MATERIALIZED (
    SELECT id FROM users WHERE last_active > NOW() - INTERVAL '30 days'
)
SELECT u.email, COUNT(o.id) AS orders
FROM active_users au
JOIN users u ON u.id = au.id
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.email;

Pagination: OFFSET vs Keyset

-- Slow OFFSET pagination: database scans and discards N rows every time
SELECT * FROM posts
ORDER BY created_at DESC
LIMIT 20 OFFSET 10000;  -- scans 10,020 rows to return 20

-- Fast keyset pagination: uses index on created_at
-- Pass the last seen created_at from the previous page
SELECT * FROM posts
WHERE created_at < '2026-03-01 12:00:00'  -- cursor from last page
ORDER BY created_at DESC
LIMIT 20;
-- Only reads 20 rows via index — O(1) regardless of page depth

Useful Diagnostic Queries

-- Find the slowest queries (requires pg_stat_statements extension)
SELECT
    query,
    calls,
    round(total_exec_time::numeric, 2) AS total_ms,
    round(mean_exec_time::numeric, 2) AS avg_ms,
    round((100 * total_exec_time / SUM(total_exec_time) OVER ())::numeric, 2) AS pct
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- Find tables with sequential scans (missing indexes)
SELECT
    schemaname,
    relname AS table_name,
    seq_scan,
    idx_scan,
    n_live_tup AS row_count
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_scan DESC;

-- Find unused indexes (wasteful writes, no read benefit)
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan AS times_used
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND schemaname = 'public'
ORDER BY tablename;

Common Pitfalls

Index on low-cardinality column

An index on a boolean column (is_active) with 90% TRUE rows is useless — the planner will prefer a sequential scan. Only index high-cardinality columns or use partial indexes for minority values.

Too many indexes on write-heavy tables

Every index slows down INSERT, UPDATE, and DELETE. A table with 15 indexes that processes 10,000 writes/second will bottleneck on index maintenance. Audit with pg_stat_user_indexes and drop unused ones.

Forgetting VACUUM and ANALYZE

PostgreSQL's autovacuum handles this automatically, but after a bulk load of millions of rows, run VACUUM ANALYZE tablename immediately so the planner has fresh statistics.

Summary

Start every optimization with EXPLAIN (ANALYZE, BUFFERS) — look for Seq Scans, high row estimates, and disk reads
Fix N+1 queries with eager loading or JOINs — this is almost always the biggest win
Use composite indexes with equality columns first; use partial indexes to index only the rows you query
Replace OFFSET pagination with keyset cursors for consistent performance at scale
Use window functions instead of correlated subqueries for analytics
Monitor with pg_stat_statements in production to find slow queries before users report them

Host Your Database Apps — Recommended Providers

🌐

Hostinger

Web Hosting from $2.99/mo

💧

DigitalOcean

$200 Free Credit