Skip to content

Query Optimization and EXPLAIN

Medium ~45 min

What you'll build

An optimized set of queries for a large orders/customers schema, with before/after EXPLAIN plans showing the impact of indexes and query rewrites.

Goals

  • Read and interpret EXPLAIN output
  • Identify sequential scans and high-cost nodes
  • Create effective indexes for query patterns
  • Rewrite queries for better performance

Prerequisites

  • JOINs and WHERE clauses
  • Basic index knowledge
  • Aggregate queries

Steps

  1. 1

    Set up a large dataset

    Create orders and customers tables with sample data to demonstrate optimization techniques.

    Code
    CREATE TABLE customers (
      id    SERIAL PRIMARY KEY,
      name  TEXT NOT NULL,
      city  TEXT NOT NULL
    );
    
    CREATE TABLE orders (
      id          SERIAL PRIMARY KEY,
      customer_id INTEGER REFERENCES customers(id),
      total       NUMERIC(10,2) NOT NULL,
      status      TEXT NOT NULL,
      created_at  TIMESTAMP DEFAULT NOW()
    );
    
    -- Insert sample data (run multiple times for scale)
    INSERT INTO customers (name, city) SELECT 'Customer ' || i, 'City ' || (i % 10) FROM generate_series(1, 10000) i;
    INSERT INTO orders (customer_id, total, status) SELECT (i % 10000) + 1, (i % 1000)::numeric, CASE WHEN i % 3 = 0 THEN 'shipped' ELSE 'pending' END FROM generate_series(1, 100000) i;

    Expected result

    10000 customers and 100000 orders inserted.

  2. 2

    Analyze a slow query with EXPLAIN

    Run EXPLAIN ANALYZE on a query that filters orders by status and joins to customers. Look for Seq Scan (full table scan) and high cost numbers.

    Code
    EXPLAIN ANALYZE
    SELECT c.name, o.total, o.status
    FROM orders o
    JOIN customers c ON c.id = o.customer_id
    WHERE o.status = 'shipped'
    ORDER BY o.created_at DESC
    LIMIT 100;

    Expected result

    The plan shows a Seq Scan on orders (no index) and a sort step — both are slow on large tables.

  3. 3

    Add indexes and re-analyze

    Create indexes on the filter and sort columns, then re-run EXPLAIN ANALYZE to see the improvement.

  4. 4

    Optimize an aggregate query

    Find the total revenue per city. First write the naive version, then optimize by ensuring the join and group-by columns are indexed.

  5. 5

    Rewrite a correlated subquery

    A correlated subquery can be slow. Rewrite 'customers who have orders over 500' from a correlated subquery to a JOIN for better performance.

    Code
    -- Slow: correlated subquery
    EXPLAIN ANALYZE
    SELECT name FROM customers c
    WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.total > 500);
    
    -- Fast: JOIN with DISTINCT
    EXPLAIN ANALYZE
    SELECT DISTINCT c.name
    FROM customers c
    JOIN orders o ON o.customer_id = c.id
    WHERE o.total > 500;

    Expected result

    The JOIN version typically produces a better plan with a hash join instead of nested loops.

Try it yourself

What you learned

You learned to read EXPLAIN plans, identify bottlenecks (Seq Scans, sorts, nested loops), and optimize with targeted indexes and query rewrites. This is the core skill of database performance tuning.

Related Resources

Related Lessons

Related Glossary

Related Glossary

A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.

Decode error messages

Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.

View errors

← Back to topic