SQL Query Optimization: JOIN Strategies, Subqueries and Index Usage

About 90% of database performance problems come from slow queries. Fixing the query is roughly 100x cheaper than growing the server. This article teaches how to read EXPLAIN output, understand JOIN types and design real-world index strategies.

Start with EXPLAIN

Prefix every slow query with EXPLAIN ANALYZE. Read the plan from root to leaves (the bottom operators run first). Watch out for: Seq Scan on a big table, Nested Loop with many rows, high cost, and large differences between estimated and actual rows.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
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 > '2026-01-01'
GROUP BY u.name
ORDER BY order_count DESC
LIMIT 20;

-- In the output, look for:
-- 'Seq Scan on users'    -> no index / not used
-- 'Nested Loop'          -> OK for small sets, slow for large ones
-- 'Hash Join'            -> usually good
-- 'Sort' with high cost  -> could be skipped with an index
-- 'rows=100 actual=50000' -> planner was wrong, run ANALYZE

JOIN Types

• Nested Loop: for each outer row scan the inner table. Fast on small tables, disastrous on large-to-large
• Hash Join: hash the smaller table and scan the bigger one. Best for equality joins
• Merge Join: walk two sorted inputs in parallel. Excellent when ORDER BY matches an index

Index Strategies

-- 1) Composite index — WHERE + ORDER BY together
CREATE INDEX idx_orders_user_date
    ON orders(user_id, created_at DESC);

-- Perfect for this query
SELECT * FROM orders WHERE user_id = 42
ORDER BY created_at DESC LIMIT 10;

-- 2) Partial index — index only the rows that matter
CREATE INDEX idx_active_users ON users(email)
WHERE active = true;
-- In a user table where 5% are active, the index is 95% smaller

-- 3) Covering index — everything is in the index, no table visit
CREATE INDEX idx_orders_cover ON orders(user_id, created_at)
INCLUDE (total_amount, status);

-- 4) Expression index
CREATE INDEX idx_email_lower ON users(LOWER(email));
-- Now this query can use the index:
SELECT * FROM users WHERE LOWER(email) = 'admin@x.com';

Subquery vs JOIN vs CTE

-- Correlated subquery (SLOW: runs once per row)
SELECT u.name,
    (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u;

-- Rewrite with a JOIN (FAST)
SELECT u.name, COALESCE(c.cnt, 0) AS order_count
FROM users u
LEFT JOIN (
    SELECT user_id, COUNT(*) AS cnt
    FROM orders GROUP BY user_id
) c ON c.user_id = u.id;

-- Modern: Lateral JOIN (great in PG)
SELECT u.name, c.cnt
FROM users u
LEFT JOIN LATERAL (
    SELECT COUNT(*) AS cnt FROM orders WHERE user_id = u.id
) c ON true;

CTE Performance Trap (PostgreSQL)

Before PG 12, CTEs were an optimisation barrier — the planner could not merge them with the surrounding query and always materialised them. PG 12 fixed that; using NOT MATERIALIZED explicitly is a good idea.

-- PG 12+
WITH recent AS NOT MATERIALIZED (
    SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '7 days'
)
SELECT u.name, COUNT(*)
FROM users u JOIN recent o ON o.user_id = u.id
GROUP BY u.name;

LIMIT + ORDER BY Optimisation

-- Fetch the last 10 orders from a 1M-row table
SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;
-- Without an index: full table scan + sort, very slow
CREATE INDEX idx_orders_created ON orders(created_at DESC);
-- Now it reads 10 rows from the index and stops

Keyset Pagination (Instead of OFFSET)

-- OFFSET is SLOW — page 100,000 has to skip 100,000 rows
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 2000000;  -- slow

-- Keyset (cursor-based) pagination is FAST
SELECT * FROM orders WHERE id > 2000000 ORDER BY id LIMIT 20;  -- fast
-- Send the last id of each page as the cursor for the next one

Denormalisation

Sometimes you have to give up 3rd normal form. Storing a frequently-read but rarely-changed aggregate in its own column or table (for example user.order_count) bypasses JOINs. The trade-off is write complexity — you keep it in sync with triggers or app code.

Planner Statistics

-- Refresh stats after a big data load
ANALYZE users;
ANALYZE orders;

-- Tune the autovacuum ANALYZE threshold for write-heavy tables
ALTER TABLE orders SET (autovacuum_analyze_scale_factor = 0.02);

-- Which tables need an ANALYZE run?
SELECT schemaname, relname, n_mod_since_analyze
FROM pg_stat_user_tables
WHERE n_mod_since_analyze > 10000
ORDER BY n_mod_since_analyze DESC;

Slow Query Log

-- postgresql.conf
log_min_duration_statement = 500  -- log anything above 500ms
auto_explain.log_min_duration = 500
auto_explain.log_analyze = on

-- pg_stat_statements extension (top 10 most expensive queries)
CREATE EXTENSION pg_stat_statements;
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Conclusion

SQL optimisation is a discipline — read EXPLAIN, find the bottleneck, add the right index, measure. Before scaling the hardware, run through that loop; a 10x improvement on the same box is usually possible.