Day 8 of 10 · Harbour Mutual

Query optimisation

Make queries fast on big tables: read a query plan with EXPLAIN and EXPLAIN ANALYZE, and choose the indexes that turn a scan of every row into a quick lookup.

Theory for today: Claims development, How PostgreSQL plans a query.

The lesson

Harbour Mutual's own tables are small: PostgreSQL reads all 1,534 claims in a blink. A larger insurer's claims history runs to millions of rows, and a report that reads every one of them every time becomes the slowest thing in the building. Today you work on claims_archive, ten years of claims (200,000 rows) that you create in brief 8.1; the rest of the data is untouched.

Asking PostgreSQL how it will run a query

Put EXPLAIN before a query and PostgreSQL shows its plan instead of the result: the steps it will take, and its estimate of the cost and of the rows each step returns. EXPLAIN ANALYZE also runs the query, and adds the actual time and rows.

EXPLAIN ANALYZE
SELECT *
FROM claims_archive
WHERE policy_id = 4242;

Without an index: Seq Scan, every row read and 199,989 of them thrown away (Rows Removed by Filter). About 28 ms. Try it in the playground

The plan nodes you will see most:

Node	What PostgreSQL does	Good when
`Seq Scan`	reads every row of the table	most rows are wanted, or the table is small
`Index Scan`	looks the rows up in an index, then fetches each	a few rows are wanted
`Bitmap Index Scan` + `Bitmap Heap Scan`	collects the matches from the index, then reads the pages that hold them in order	a moderate number of rows
`Index Only Scan`	answers from the index alone	the index holds every column the query needs

Indexes

An index is a sorted copy of one or more columns, with a pointer to each row, like the index at the back of a book. With one on policy_id, the query above reads 11 rows instead of 200,000.

CREATE INDEX claims_archive_policy_id ON claims_archive (policy_id);

EXPLAIN ANALYZE
SELECT *
FROM claims_archive
WHERE policy_id = 4242;

Now a Bitmap Index Scan on the new index: about 0.2 ms, over a hundred times faster. Try it in the playground

An index helps when the query can use the column as it is stored. Wrap the column in a function and the index no longer matches:

EXPLAIN
SELECT COUNT(*)
FROM claims_archive
WHERE EXTRACT(YEAR FROM loss_date) = 2025;

A Seq Scan, even with an index on loss_date: the index is sorted by date, not by EXTRACT(YEAR ...). Try it in the playground

EXPLAIN
SELECT COUNT(*)
FROM claims_archive
WHERE loss_date >= DATE '2025-01-01'
  AND loss_date <  DATE '2026-01-01';

The same question as a range on the column itself can use the index. Try it in the playground

Composite and partial indexes

A composite index covers several columns, in order: an index on (loss_date, cause) helps a query filtering on loss_date, or on both, but not one filtering on cause alone, just as a phone book sorted by surname then first name cannot find everyone called Anna. A partial index holds only the rows matching a WHERE: small, and quick to maintain, when queries always ask for the same subset.

CREATE INDEX claims_archive_open_by_date ON claims_archive (loss_date)
WHERE status = 'open';

Only 4,000 of the 200,000 claims are open: the index holds just those. Try it in the playground

Indexes are not free: every insert and update must maintain them, and they take space. Index what your queries actually filter, join and sort on, then check with EXPLAIN that they are used.

PostgreSQL chooses a plan from statistics about each table. After loading a lot of data, run ANALYZE table_name; so the estimates are right.

Briefs from the business

0 of 5 done

Write SQL to answer each brief, run it, then check your answer against Harbour Mutual's. Your database is the same one as in the playground.

Starting PostgreSQL in your browser…

Brief 8.1 · Ten years of claims

Create the archive: run the script in the editor. It generates claims_archive, 200,000 claims from 2016 to 2025, then updates PostgreSQL's statistics. (It takes a few seconds.)

Tables: claims_archive (yours)

Brief 8.2 · One policy's claims, instantly

The claims portal shows a policy's claims history, looked up by policy_id, thousands of times a day. Make SELECT * FROM claims_archive WHERE policy_id = 4242 use an index. Compare EXPLAIN ANALYZE before and after.

Tables: claims_archive

Brief 8.3 · The month-end claims report

Finance's month-end report: for losses in December 2025 in the archive, the number of claims and the total incurred by cause. Show cause, claims and incurred.

Tables: claims_archive

Brief 8.4 · Make the report fast

The month-end report scans all 200,000 claims to find 1,700. Add an index so the query of brief 8.3 uses it, and compare the plans.

Tables: claims_archive

Brief 8.5 · The open claims queue

Claims handlers work from a queue of open claims by loss date, such as SELECT * FROM claims_archive WHERE status = 'open' AND loss_date >= DATE '2025-01-01'. Only 2% of the archive is open: create a partial index that holds just the open claims, ordered by loss date.

Tables: claims_archive

5 briefs to go today.

Day 9: JSONB and extensions