Indexing and Query Optimisation Practice

What You'll Build

You will take a deliberately slow analytics query against a large orders dataset and optimise it methodically: measure the baseline plan, identify the dominant cost, design targeted indexes, and confirm each change with EXPLAIN ANALYZE. By the end you will have driven a multi-second sequential scan down to a fast index-driven plan, with evidence at every step.

The dataset is a million-row orders table joined to customers, filtered by date and status and sorted by total — a realistic dashboard query. Rather than guessing at indexes, you will let the query plan dictate exactly which index to build, practising the measure-fix-verify loop that defines real optimisation work.

Prerequisites

• Completion of lessons 11–14, or equivalent familiarity with index types, EXPLAIN ANALYZE, and partition pruning.
• A running PostgreSQL 15+ instance and a client such as psql.
• Permission to create tables and indexes and to run ANALYZE.
• Roughly 200 MB of free space for the generated sample data.
• Comfort reading an EXPLAIN ANALYZE plan and distinguishing Seq Scan, Index Scan, and Bitmap Heap Scan.

Setup & Project Structure

Create a customers table and a large orders table, then generate sample data with generate_series so the orders table holds about a million rows with realistic skew on status and a spread of dates. Run ANALYZE afterwards so the planner has accurate statistics to work from.

Keep everything in a scratch schema you can drop afterwards. The goal is a table big enough that a sequential scan is visibly slow, so the impact of each index is unmistakable in the timings.

CREATE TABLE customers (
    id   bigint PRIMARY KEY,
    name text NOT NULL
);
INSERT INTO customers
SELECT g, 'Customer ' || g FROM generate_series(1, 50000) g;

CREATE TABLE orders (
    id          bigint PRIMARY KEY,
    customer_id bigint REFERENCES customers(id),
    status      text NOT NULL,
    total       numeric(10,2) NOT NULL,
    placed_at   timestamptz NOT NULL
);
INSERT INTO orders
SELECT g,
       1 + (random()*49999)::int,
       (ARRAY['completed','completed','completed','pending','cancelled'])[1+(random()*4)::int],
       (random()*500)::numeric(10,2),
       now() - (random()*365)::int * interval '1 day'
FROM generate_series(1, 1000000) g;

ANALYZE customers; ANALYZE orders;

Step 1 — Capture the Baseline Plan

Run the target dashboard query under EXPLAIN (ANALYZE, BUFFERS) before adding any indexes. With no supporting index, expect a Seq Scan on orders, a sort for the ORDER BY, and a total time of hundreds of milliseconds to seconds. Record the execution time and the plan shape as your baseline.

Read the plan bottom-up and name the dominant cost: almost certainly the sequential scan of a million rows feeding a sort. This is your evidence — the plan, not intuition, tells you the orders table scan and the sort are what you must eliminate.

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.placed_at >= now() - interval '30 days'
  AND o.status = 'pending'
ORDER BY o.total DESC
LIMIT 20;

-- Expect (baseline): Seq Scan on orders + Sort; note Execution Time and rows.

Step 2 — Add a Targeted Composite/Partial Index

The query filters on status = 'pending' (a small, skewed slice) and a recent placed_at range, then sorts by total. Build a partial index restricted to pending orders, keyed to support the date filter, so the planner scans a tiny structure instead of the whole table. Re-run the EXPLAIN ANALYZE and compare.

Because 'pending' is a small fraction of rows, a partial index on WHERE status = 'pending' is far smaller than a full index and is exactly aligned with the query. Watch the plan switch from Seq Scan to an index or bitmap scan and the execution time drop sharply.

-- Partial index aligned to the query's predicate, ordered for the range filter
CREATE INDEX idx_orders_pending_date ON orders (placed_at DESC)
    WHERE status = 'pending';

ANALYZE orders;

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total, c.name
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.placed_at >= now() - interval '30 days' AND o.status = 'pending'
ORDER BY o.total DESC
LIMIT 20;
-- Expect: Index/Bitmap Scan using idx_orders_pending_date; far lower time.

Step 3 — Optimise the Join and the Sort

With the orders filter fast, examine what remains: the join to customers and the ORDER BY total. The join on customers.id already uses the primary key, so confirm it is a fast Index Scan or Nested Loop over the few filtered rows. If the sort still appears as a cost, consider an index that supplies total order within the pending slice.

Add total to the partial index ordering so the LIMIT can stop early without a separate sort, and verify the Sort node disappears from the plan. This is the final squeeze: turning a filter-then-sort into a single ordered index scan that returns the top 20 directly.

-- Order the partial index by total so the top-N needs no separate sort
DROP INDEX idx_orders_pending_date;
CREATE INDEX idx_orders_pending_total ON orders (total DESC)
    WHERE status = 'pending';

ANALYZE orders;

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total, c.name
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
ORDER BY o.total DESC
LIMIT 20;
-- Expect: Index Scan in total order, no Sort node, single-digit milliseconds.

Step 4 — Testing & Verification

Confirm the full story end to end: the baseline plan showed a Seq Scan and a Sort costing hundreds of milliseconds or more, and the optimised plan shows an index scan with no separate sort, completing in a handful of milliseconds. Re-run each EXPLAIN ANALYZE and check that the dominant node changed and the execution time fell by one to two orders of magnitude.

-- Side-by-side check: drop the index to see the baseline, recreate to see the win
EXPLAIN (ANALYZE, BUFFERS) /* with index */
SELECT o.id, o.total, c.name FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending' ORDER BY o.total DESC LIMIT 20;

-- Confirm the index is actually being used and counted:
SELECT indexrelname, idx_scan FROM pg_stat_user_indexes
WHERE relname = 'orders';

-- Clean up the scratch objects when done:
-- DROP TABLE orders; DROP TABLE customers;

• Capture a baseline plan with EXPLAIN (ANALYZE, BUFFERS) before changing anything.
• Let the plan identify the dominant cost (here, a Seq Scan feeding a Sort) and target that node.
• A partial index on a small, skewed predicate (status='pending') is tiny and precisely matches the query.
• Ordering the index by the sort column lets a top-N LIMIT skip the separate Sort node entirely.
• Run ANALYZE after building indexes so the planner has fresh statistics to choose them.
• Verify every change by re-running EXPLAIN ANALYZE and confirming the plan and timing improved.