SQL Query Optimization Techniques

The difference between a query that takes 50ms and one that takes 50 seconds is rarely about the SQL syntax. It is about how the database engine executes the query: which indexes it uses, how it joins tables, and how much data it scans. Optimization starts with understanding what the database is actually doing, then making changes that guide it toward better execution plans.

EXPLAIN: seeing the execution plan

Every major database supports EXPLAIN, which shows the execution plan without running the query. PostgreSQL’s EXPLAIN ANALYZE runs the query and shows actual execution times alongside the plan.

-- Show the plan without executing
EXPLAIN
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2025-01-01'
GROUP BY u.name
ORDER BY order_count DESC
LIMIT 10;

Limit  (cost=1523.45..1523.48 rows=10 width=40)
  ->  Sort  (cost=1523.45..1525.12 rows=667 width=40)
        Sort Key: (count(o.id)) DESC
        ->  HashAggregate  (cost=1498.23..1504.90 rows=667 width=40)
              Group Key: u.name
              ->  Hash Join  (cost=45.00..1456.89 rows=8267 width=36)
                    Hash Cond: (o.user_id = u.id)
                    ->  Seq Scan on orders o  (cost=0.00..1120.00 rows=50000 width=8)
                    ->  Hash  (cost=37.50..37.50 rows=600 width=36)
                          ->  Seq Scan on users u  (cost=0.00..37.50 rows=600 width=36)
                                Filter: (created_at > '2025-01-01')

Key things to look for:

• Seq Scan — Full table scan. Fine for small tables, problematic for large ones.
• Index Scan / Index Only Scan — Uses an index. Generally fast.
• Hash Join / Nested Loop / Merge Join — Join strategy. Each has different performance characteristics.
• cost — Estimated cost in arbitrary units. Lower is better. The first number is startup cost, the second is total cost.
• rows — Estimated number of rows the operation will produce.

-- EXPLAIN ANALYZE shows actual execution times
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2025-01-01'
GROUP BY u.name
ORDER BY order_count DESC
LIMIT 10;

Limit  (cost=1523.45..1523.48 rows=10 width=40) (actual time=12.45..12.47 rows=10 loops=1)
  ->  Sort  (cost=1523.45..1525.12 rows=667 width=40) (actual time=12.44..12.45 rows=10 loops=1)
        Sort Key: (count(o.id)) DESC
        Sort Method: top-N heapsort  Memory: 25kB
        ->  HashAggregate  (cost=1498.23..1504.90 rows=667 width=40) (actual time=12.12..12.25 rows=667 loops=1)
              ->  Hash Join  (cost=45.00..1456.89 rows=8267 width=36) (actual time=0.89..10.23 rows=8267 loops=1)
                    ...
Planning Time: 0.45 ms
Execution Time: 12.68 ms

The actual time values are the real execution times in milliseconds. Compare these with the estimated costs to see if the planner’s estimates are accurate. Large discrepancies indicate stale statistics (run ANALYZE).

Sequential scan vs index scan

A sequential scan reads every row in a table. An index scan looks up rows using an index. The planner chooses based on selectivity — how many rows match the filter.

-- With no index on email, this is a full table scan
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';
-- Seq Scan on users  (cost=0.00..2500.00 rows=1 width=...)
--   Filter: (email = 'alice@example.com')

-- After creating an index
CREATE INDEX idx_users_email ON users(email);

EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';
-- Index Scan using idx_users_email on users  (cost=0.28..8.29 rows=1 width=...)
--   Index Cond: (email = 'alice@example.com')

But indexes are not always faster. If a query matches most of the table, a sequential scan can be cheaper because it reads data in order from disk, while an index scan requires random access.

-- If 90% of users are active, the planner may prefer a seq scan
EXPLAIN SELECT * FROM users WHERE active = true;
-- Seq Scan on users  (cost=0.00..2500.00 rows=90000 width=...)
--   Filter: (active = true)
-- This is correct! Reading 90% of the table through an index would be slower.

Join strategies

The planner chooses between three join algorithms:

Nested Loop — For each row in the outer table, look up matching rows in the inner table. Best when the outer table is small and the inner table has an index on the join column.

Hash Join — Build a hash table from the smaller table, then scan the larger table and probe the hash. Best for equi-joins with medium to large tables.

Merge Join — Sort both tables on the join column and merge them. Best when both inputs are already sorted (e.g., from index scans).

-- Force a specific join strategy (for testing — don't do this in production)
SET enable_hashjoin = off;
SET enable_mergejoin = off;
-- Now the planner can only use nested loop joins

-- Reset
RESET enable_hashjoin;
RESET enable_mergejoin;

The planner usually makes the right choice. Override it only for debugging.

Fixing slow queries

Problem: missing index on WHERE clause

-- Slow: scans entire orders table
SELECT * FROM orders WHERE customer_id = 12345 AND status = 'pending';

-- Fix: composite index matching the WHERE clause
CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);

-- Now fast: index scan with both conditions

Problem: index not used due to function call

-- Slow: function on column prevents index use
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';

-- Fix: create a functional index
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- Or better: store emails in lowercase and compare directly

Problem: inefficient subquery

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

-- Fix: use a JOIN with GROUP BY
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.name;

Problem: SELECT * fetching unnecessary columns

-- Slow: fetches all columns including large text/blob fields
SELECT * FROM articles WHERE category = 'tech';

-- Fix: select only needed columns
-- If there's a covering index on (category, title, published_at),
-- this becomes an index-only scan
SELECT title, published_at FROM articles WHERE category = 'tech';

The N+1 query problem

N+1 queries happen when you fetch a list and then issue a separate query for each item. This is the most common performance problem in application code.

# N+1: 1 query for users + N queries for orders
users = db.execute("SELECT * FROM users LIMIT 100").fetchall()
for user in users:
    orders = db.execute(
        "SELECT * FROM orders WHERE user_id = %s", (user.id,)
    ).fetchall()
    user.orders = orders
# Total: 101 queries

# Fix: single query with JOIN
results = db.execute("""
    SELECT u.*, o.id AS order_id, o.total, o.created_at AS order_date
    FROM users u
    LEFT JOIN orders o ON o.user_id = u.id
    ORDER BY u.id
    LIMIT 100
""").fetchall()
# Total: 1 query

# Or: two queries with IN clause
users = db.execute("SELECT * FROM users LIMIT 100").fetchall()
user_ids = [u.id for u in users]
orders = db.execute(
    "SELECT * FROM orders WHERE user_id = ANY(%s)", (user_ids,)
).fetchall()
# Total: 2 queries

ORMs are the most common source of N+1 queries. Use eager loading (SQLAlchemy’s joinedload, Django’s select_related, Prisma’s include) to batch the queries.

# SQLAlchemy: eager load with joinedload
from sqlalchemy.orm import joinedload

users = session.query(User).options(
    joinedload(User.orders)
).limit(100).all()
# Single query with JOIN

Pagination done right

Offset-based pagination degrades as the offset grows because the database must scan and discard all rows before the offset.

-- Slow for large offsets: scans 100,000 rows, discards 99,980
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 99980;

-- Keyset pagination: fast regardless of position
SELECT * FROM products
WHERE id > 99980
ORDER BY id
LIMIT 20;

Keyset (cursor) pagination uses the last seen value as the starting point. It is always fast because it uses the index directly. The tradeoff is that you cannot jump to arbitrary pages — you can only go forward and backward.

-- Multi-column keyset pagination
SELECT * FROM products
WHERE (created_at, id) > ('2025-06-15', 12345)
ORDER BY created_at, id
LIMIT 20;

Denormalization

Normalized schemas minimize data duplication but can require expensive joins. Denormalization trades storage for read performance.

-- Normalized: requires JOIN to get order with customer name
SELECT o.id, o.total, c.name AS customer_name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > '2025-01-01';

-- Denormalized: customer_name stored directly on orders
-- No join needed, but you must update it when the customer name changes
SELECT id, total, customer_name
FROM orders
WHERE created_at > '2025-01-01';

When to denormalize:

• Read-heavy workloads where the join is a bottleneck
• The denormalized field changes rarely
• You can tolerate brief inconsistency (or enforce updates via triggers)
• Reporting tables that aggregate data from multiple sources

-- Materialized view: a managed form of denormalization
CREATE MATERIALIZED VIEW order_summary AS
SELECT
    o.id,
    o.total,
    o.created_at,
    c.name AS customer_name,
    c.email AS customer_email,
    COUNT(oi.id) AS item_count,
    SUM(oi.quantity) AS total_quantity
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id, o.total, o.created_at, c.name, c.email;

-- Refresh when data changes
REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary;

-- Query the view — no joins needed
SELECT * FROM order_summary WHERE created_at > '2025-01-01';

Statistics and planner hints

The query planner relies on table statistics to make decisions. Stale statistics lead to bad plans.

-- Update statistics for a specific table
ANALYZE orders;

-- Update statistics for all tables
ANALYZE;

-- Check the current statistics
SELECT
    schemaname, tablename, n_live_tup, n_dead_tup,
    last_vacuum, last_analyze
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;

After bulk data loads, always run ANALYZE before querying the new data. The planner might estimate 100 rows when there are actually 10 million, leading to a nested loop join instead of a hash join.

Query optimization checklist

1. Run EXPLAIN ANALYZE and read the plan
2. Look for sequential scans on large tables — add indexes if selectivity is high
3. Check for accurate row estimates — run ANALYZE if estimates are off
4. Look for N+1 patterns in application code — batch queries
5. Select only needed columns — enables covering indexes
6. Use keyset pagination instead of OFFSET for large datasets
7. Consider materialized views for complex reporting queries
8. Monitor slow query logs and address the worst offenders first

Optimization is iterative. Measure, change one thing, measure again. The execution plan tells you exactly what the database is doing — learn to read it and the answers become obvious.