# SQL indexing companion lab

By Milan Jovanović: https://milanjovanovic.tech/about

Article: https://milanjovanovic.tech/blog/how-to-design-the-right-sql-index

Lab and downloads: https://milanjovanovic.tech/labs/sql-indexing

## What this demonstrates

This September 2026 companion illustrates the indexing decisions in the article.
It uses a new deterministic fixture, not the original benchmark dataset.
The article's published timings, hardware, and data distribution are not reproduced by this lab.

The fixture has 100 users, 10,000 issues (6,537 open), and 1,000,000 comments.
Every issue has 100 comments, one from each user.
Dates are fixed relative to August 18, 2026, and the query cutoff is July 18, 2026.
There is no dependence on the current date or a random seed.
This uniform distribution is useful for comparison, but production data often has skew.

## Run it

Install Docker with Compose v2.
Download https://milanjovanovic.tech/labs/sql-indexing.zip and extract it.
Open the `sql-indexing` folder inside. It contains all eight files:
`compose.yaml`, `01-seed.sql`, `02-measure.sql`, `repeat-query.sql`,
`count-by-user.sql`, `comments-by-issue-user.sql`, `dashboard.sql`, and `README.md`.

You can also preview and download individual files on the lab page.
If downloading them individually, save all eight in the same directory.

Run these commands in that directory in PowerShell, bash, or another terminal:

```sh
docker compose up -d --wait
docker compose exec -T postgres psql -X -U lab -d indexing -f /lab/01-seed.sql
docker compose exec -T postgres psql -X -U lab -d indexing -f /lab/02-measure.sql > measurements.txt
```

The image is pinned to PostgreSQL 18.6 Alpine. No database port is published to the host.
The local exercise credentials are in `compose.yaml`; use this isolated container for the lab.
The database lives in the Compose project's named volume. The seed script rebuilds only
its `sql_index_lab` schema and asserts the fixture's row counts before succeeding.
Allow a few minutes and at least 1 GB of free disk space for the image and database.

## Read the measurements

`02-measure.sql` resets the lab indexes and runs seven stages. Each stage executes the
query once as a warmup, then three measured times. Compare the median of the three
`Execution Time` values in each stage, excluding the labeled warmup.

1. Count one user's comments with primary keys only.
2. Repeat the count after adding a `user_id` index.
3. Filter by issue/user/date with single-column indexes available.
4. Repeat with `(issue_id, user_id, created_at DESC)`.
5. Run the latest-comment dashboard with that composite index.
6. Add `(issue_id, created_at DESC)` for each latest-comment lookup.
7. Add `(status, created_at DESC)` so the outer query can stop after 25 open issues.

Read the access path, `Sort` nodes, `loops`, actual row counts, `Heap Fetches`, and
buffer hits/reads alongside the time. PostgreSQL may choose different plans depending
on statistics, settings, hardware, and version. A BitmapAnd is not guaranteed at stage 3.
Vacuuming the unchanged fixture makes index-only scans possible, but a busy table can
still need heap fetches for visibility checks. Warmup runs do not guarantee that every
page fits in memory; report the buffer counts rather than assuming a fully cached test.

The output records PostgreSQL's version, relevant settings, full `EXPLAIN (ANALYZE,
BUFFERS)` plans, and index sizes. Also record your host OS, CPU, RAM, Docker CPU/memory
limits, storage, and Docker version when sharing results. Keep the complete output and
identify the fixture as version 1. Do not compare these read-only timings directly with
production load, network latency, or the article's original timings.

To repeat, rerun the measurement command. The script removes only its five named indexes
in `sql_index_lab`; it does not reseed the data or flush PostgreSQL/OS caches.
To inspect a query interactively:

```sh
docker compose exec postgres psql -X -U lab -d indexing
```

```sql
SET search_path TO sql_index_lab;
\i /lab/dashboard.sql
```

## Clean up

Run this in the lab directory to remove this Compose project's container and lab data:

```sh
docker compose down --volumes
```

## References

- PostgreSQL 18 EXPLAIN: https://www.postgresql.org/docs/18/using-explain.html
- Multicolumn indexes: https://www.postgresql.org/docs/18/indexes-multicolumn.html
- Index-only scans: https://www.postgresql.org/docs/18/indexes-index-only-scans.html