SQL Query Optimization: Complete Performance Guide

In this tutorial, you'll learn about SQL Query Optimization: Complete Performance Guide. We cover key concepts, practical examples, and best practices to help you understand and apply this topic effectively.

SQL query optimization is the Process of improving query execution time by analyzing execution plans, restructuring queries, adding appropriate indexes, and reducing data scan volume without changing the result set.

What You'll Learn

You'll understand how to read EXPLAIN plans, identify slow table scans, optimize JOINs, rewrite subqueries, fix N+1 query problems, and use materialized views for reporting queries.

Why Query Optimization Matters

A poorly written query that scans millions of rows can bring a production database to its knees. Durga Antivirus Pro processes threat signatures across billions of file records; a 100ms optimization on one query saves 100 hours of CPU time per billion scans.

Query Optimization Learning Path

flowchart LR
  A[SQL Basics] --> B[Query Optimization]
  B --> C[Database Indexing]
  C --> D[Performance Monitoring]
  B:::current
  classDef current fill:#f90,color:#fff,stroke:#333,stroke-width:2px

Understanding EXPLAIN Plans

The EXPLAIN command shows how the database executes a query. It reveals table scan methods, index usage, join order, and row estimates.

EXPLAIN ANALYZE
SELECT o.id, o.total, u.name
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'shipped'
  AND o.created_at > '2026-01-01';

Output:

Hash Join  (cost=12.34..156.78 rows=543 width=42)
  Hash Cond: (o.user_id = u.id)
  ->  Index Scan using idx_orders_status_date on orders o
        Index Cond: ((status = 'shipped'::text) AND (created_at > '2026-01-01'::date))
        (cost=5.67..89.01 rows=543 width=22)
  ->  Hash  (cost=4.56..4.56 rows=256 width=24)
        ->  Seq Scan on users u  (cost=0.00..4.56 rows=256 width=24)

Key metrics to check:

Optimizing JOINs

JOIN performance depends on index availability, join order, and data distribution.

Nested Loop Joins

Best for small result sets where one table is small and the other is indexed.

-- Forces a nested loop join (PostgreSQL)
SELECT /*+ NestLoop(o oi) */
  o.id, oi.product_id, oi.quantity
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.id = 12345;

Hash Joins

Best for large, unsorted datasets. The database builds a Hash Table on one side and probes it with the other.

-- Hash join happens automatically for large unindexed joins
SELECT c.name, SUM(o.total)
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;

Merge Joins

Best for sorted data on both sides of the join condition.

-- Merge join when both sides are sorted by the join key
SELECT *
FROM employees e
JOIN departments d ON d.id = e.dept_id
ORDER BY e.dept_id;

JOIN type performance comparison:

Subquery Optimization

Subqueries often perform worse than equivalent JOINs or CTEs.

IN Subquery Problem

-- SLOW: Re-executes the subquery for each row
SELECT * FROM products
WHERE category_id IN (
  SELECT id FROM categories WHERE active = true
);

Rewrite as a JOIN:

-- FAST: Single execution
SELECT p.*
FROM products p
JOIN categories c ON c.id = p.category_id
WHERE c.active = true;

Correlated Subquery Problem

-- SLOW: Executes once per outer row
SELECT e.name, (
  SELECT MAX(salary)
  FROM salaries s
  WHERE s.employee_id = e.id
) AS max_salary
FROM employees e;

Rewrite with a window function:

-- FAST: Single pass
SELECT e.name, MAX(s.salary) AS max_salary
FROM employees e
JOIN salaries s ON s.employee_id = e.id
GROUP BY e.id, e.name;

The N+1 Query Problem

N+1 happens when an application query triggers one query for the parent and N queries for children.

Example

# BAD: N+1 queries
orders = db.query("SELECT * FROM orders")  -- 1 query
for order in orders:                       # N iterations
    items = db.query(f"SELECT * FROM order_items WHERE order_id = {order['id']}")
    # N additional queries

Fix with JOIN

# GOOD: Single query
orders_with_items = db.query("""
    SELECT o.*, oi.product_id, oi.quantity
    FROM orders o
    LEFT JOIN order_items oi ON oi.order_id = o.id
""")

Fix with Batch Loading (SQLAlchemy)

from sqlalchemy.orm import joinedload

# Eager loading: one query with JOIN
orders = session.query(Order).options(
    joinedload(Order.items)
).all()
# Result: 2 queries total (one for orders, one for all items)

Materialized Views

Materialized views store query results as a physical table, refreshed periodically.

-- PostgreSQL: Create a materialized view for a slow report query
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT
  date_trunc('month', o.created_at) AS month,
  p.category_id,
  SUM(oi.quantity * oi.unit_price) AS revenue,
  COUNT(DISTINCT o.id) AS order_count
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
GROUP BY month, p.category_id
WITH DATA;

-- Refresh on schedule
REFRESH MATERIALIZED VIEW monthly_sales;

Expected performance improvement: Report queries on the materialized view run in milliseconds instead of minutes, at the cost of slightly stale data.

Query Rewriting Patterns

Use EXISTS Instead of COUNT for Existence Checks

-- SLOW: Counts all matching rows
SELECT * FROM users
WHERE (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) > 0;

-- FAST: Stops at first match
SELECT * FROM users
WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id);

Avoid SELECT DISTINCT with JOINs

-- SLOW: DISTINCT forces sorting of all columns
SELECT DISTINCT u.name, u.email
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.status = 'shipped';

-- FAST: EXISTS without deduplication
SELECT u.name, u.email
FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id AND o.status = 'shipped'
);

Use UNION ALL Instead of UNION

-- SLOW: UNION removes duplicates (extra sort)
SELECT name FROM current_employees
UNION
SELECT name FROM former_employees;

-- FAST: UNION ALL skips dedup
SELECT name FROM current_employees
UNION ALL
SELECT name FROM former_employees;
-- Only use UNION ALL when you know there are no duplicates

Common Query Optimization Errors

1. Not Using EXPLAIN Before Optimizing

Guessing is slower than measuring. Always run EXPLAIN ANALYZE first to identify the actual bottleneck before changing anything.

2. Over-Optimizing Queries That Run Once a Day

Focus optimization effort on queries that run most frequently or affect user-facing response times. A nightly batch job running 10 seconds is fine.

3. Ignoring Data Distribution

An index that works well on a test database with 10,000 rows may fail on production with 10 million rows. Always test with production-scale data.

4. Using Functions on Indexed Columns

-- Index on created_at is useless here because of the function wrapper
SELECT * FROM orders WHERE DATE(created_at) = '2026-01-01';

-- Fix: Use a range query that can use the index
SELECT * FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2026-01-02';

5. Fetching More Columns Than Needed

-- BAD: Returns all columns (especially bad with SELECT *)
SELECT * FROM users WHERE email = 'test@example.com';

-- GOOD: Returns only needed columns
SELECT id, name FROM users WHERE email = 'test@example.com';

6. Missing Foreign Key Indexes

Every foreign key column should have an index. MySQL InnoDB auto-indexes foreign keys; PostgreSQL does not.

7. Not Paging Large Result Sets

Returning 100,000 rows to the application wastes memory and network bandwidth. Always use LIMIT/OFFSET or keyset pagination.

Practice Questions

1. What does a Seq Scan mean in an EXPLAIN plan?

It means the database is reading every row in the table sequentially. For large tables, this indicates a missing index.

2. Why does SELECT * slow down queries?

It returns all columns, which may include large text or blob fields. The database also cannot use covering indexes when extra columns are requested.

3. When should you use a materialized view instead of a regular view?

When the underlying query is slow and the data does not need to be real-time. Materialized views pre-compute and store results.

4. How do you fix the N+1 query problem?

Use JOIN to fetch parent and children in one query, or use eager loading (joinedload in SQLAlchemy, include in Prisma, populate in Mongoose).

5. Challenge: Optimize a slow reporting query.

Given this slow query:

SELECT DISTINCT u.name, u.email, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.status = 'shipped'
  AND YEAR(o.created_at) = 2026
GROUP BY u.id, u.name, u.email
ORDER BY order_count DESC;

Identify three issues and rewrite it. Answer: Issues: (1) YEAR(o.created_at) prevents index usage, (2) DISTINCT with GROUP BY is redundant, (3) LEFT JOIN filters on o.status making it effectively an INNER JOIN. Rewrite: Use WHERE o.created_at >= '2026-01-01' AND o.created_at < '2027-01-01', remove DISTINCT, change to INNER JOIN, add index on orders(created_at, status, user_id).

FAQ

Try It Yourself

Set up a slow query log and analyze it:

1. Enable slow query logging in PostgreSQL: Set log_min_duration_statement = 1000 in PostgreSQL.conf
2. Run your application for 1 hour under normal load
3. Review the slow query log
4. For each slow query, run EXPLAIN ANALYZE
5. Identify the bottleneck and apply one optimization
6. Re-run the query and compare execution time

What's Next

You have learned how to read EXPLAIN plans, optimize JOINs and subqueries, fix N+1 problems, and use materialized views. Apply these techniques to your slowest production queries first and measure the improvement before and after.