SQL indexing companion lab

By Milan Jovanović · Fixture version 1, September 2026

Run the indexing decisions from the article against a fixed issue-tracker dataset. Compare the plans before and after each index, then measure the difference on your own machine.

1. Download the files

Download and extract the ZIP, then open the sql-indexing folder inside. You need Docker with Compose v2 and at least 1 GB of free disk space.

Download all files (.zip)

Includes all 8 files below. You can also preview or download each file individually.

compose.yaml

PostgreSQL 18.6 container

Download

Preview contents

name: milan-sql-index-lab

services:
  postgres:
    image: postgres:18.6-alpine
    environment:
      POSTGRES_USER: lab
      POSTGRES_PASSWORD: local-lab-only
      POSTGRES_DB: indexing
    # No host port: use `docker compose exec` for this local exercise.
    volumes:
      - data:/var/lib/postgresql
      - .:/lab:ro
    healthcheck:
      test: [CMD-SHELL, pg_isready -U lab -d indexing]
      interval: 2s
      timeout: 5s
      retries: 30

volumes:
  data:

01-seed.sql

Schema and deterministic fixture

Download

Preview contents

\set ON_ERROR_STOP on
\timing on

-- Companion fixture v1, created September 2026. This is not the original
-- newsletter dataset and does not reproduce its historical benchmark timings.
-- Rerunning this file rebuilds only the sql_index_lab schema in this database.
DROP SCHEMA IF EXISTS sql_index_lab CASCADE;
CREATE SCHEMA sql_index_lab;
SET search_path TO sql_index_lab;
SET timezone TO 'UTC';

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE issues (
    id INTEGER PRIMARY KEY,
    status TEXT NOT NULL CHECK (status IN ('open', 'closed')),
    created_at TIMESTAMPTZ NOT NULL
);

CREATE TABLE comments (
    id BIGINT PRIMARY KEY,
    issue_id INTEGER NOT NULL REFERENCES issues(id),
    user_id INTEGER NOT NULL REFERENCES users(id),
    body TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL
);

INSERT INTO users
SELECT id, 'User ' || id
FROM generate_series(1, 100) AS id;

-- Fixed dates and arithmetic keep every run independent of random() and now().
-- Every issue has 100 comments, one from each user. 6,537 issues are open.
INSERT INTO issues
SELECT id,
       CASE WHEN id <= 6537 THEN 'open' ELSE 'closed' END,
       TIMESTAMPTZ '2026-08-18 12:00:00+00' - id * INTERVAL '1 minute'
FROM generate_series(1, 10000) AS id;

INSERT INTO comments
SELECT id,
       ((id - 1) % 10000 + 1)::INTEGER,
       ((((id - 1) / 10000) * 53 + ((id - 1) % 10000) * 7) % 100 + 1)::INTEGER,
       'Comment ' || id || ': ' || repeat(md5(id::TEXT), 4),
       TIMESTAMPTZ '2026-08-18 12:00:00+00' - ((id * 37) % 7776000) * INTERVAL '1 second'
FROM generate_series(1::BIGINT, 1000000::BIGINT) AS id;

-- Populate statistics and visibility maps before comparing read-only plans.
VACUUM (ANALYZE) users;
VACUUM (ANALYZE) issues;
VACUUM (ANALYZE) comments;

DO $$
BEGIN
    IF (SELECT COUNT(*) FROM users) <> 100
       OR (SELECT COUNT(*) FROM issues) <> 10000
       OR (SELECT COUNT(*) FROM comments) <> 1000000
       OR (SELECT COUNT(*) FROM issues WHERE status = 'open') <> 6537
       OR (SELECT COUNT(*) FROM comments WHERE user_id = 1) <> 10000
       OR (SELECT COUNT(*) FROM comments
           WHERE issue_id = 10 AND user_id = 29
             AND created_at >= TIMESTAMPTZ '2026-07-18 12:00:00+00') <> 1
    THEN
        RAISE EXCEPTION 'The companion fixture does not match its expected row counts';
    END IF;
END $$;

SELECT 'users' AS relation, COUNT(*) AS rows FROM users
UNION ALL SELECT 'issues', COUNT(*) FROM issues
UNION ALL SELECT 'comments', COUNT(*) FROM comments;

02-measure.sql

Seven indexing stages and environment details

Download

Preview contents

\set ON_ERROR_STOP on
\pset pager off
SET search_path TO sql_index_lab;
SET timezone TO 'UTC';

\echo Companion fixture v1: PostgreSQL version and planner settings
SELECT version();
SELECT name, setting, unit FROM pg_settings
WHERE name IN ('shared_buffers', 'work_mem', 'effective_cache_size',
               'random_page_cost', 'seq_page_cost', 'max_parallel_workers_per_gather',
               'jit', 'default_statistics_target')
ORDER BY name;

-- Return to the baseline on every measurement run. Primary keys stay in place.
DROP INDEX IF EXISTS ix_comments_user_id;
DROP INDEX IF EXISTS ix_comments_issue_id;
DROP INDEX IF EXISTS ix_comments_issue_user_date;
DROP INDEX IF EXISTS ix_comments_issue_date;
DROP INDEX IF EXISTS ix_issues_status_date;

\echo Stage 1: primary keys only, count comments for one user
\set query count-by-user.sql
\ir repeat-query.sql

\echo Stage 2: single-column user index, count comments for one user
CREATE INDEX ix_comments_user_id ON comments (user_id);
\ir repeat-query.sql

\echo Stage 3: single-column indexes, filtered and ordered comments
CREATE INDEX ix_comments_issue_id ON comments (issue_id);
\set query comments-by-issue-user.sql
\ir repeat-query.sql

\echo Stage 4: composite index matches equality conditions and ordering
CREATE INDEX ix_comments_issue_user_date
ON comments (issue_id, user_id, created_at DESC);
\ir repeat-query.sql

-- Remove the overlapping single-column index to isolate the composite access path.
DROP INDEX ix_comments_issue_id;

\echo Stage 5: dashboard with the issue/user/date index
\set query dashboard.sql
\ir repeat-query.sql

\echo Stage 6: dashboard with an index ordered by date within each issue
CREATE INDEX ix_comments_issue_date ON comments (issue_id, created_at DESC);
\ir repeat-query.sql

\echo Stage 7: dashboard with open issues also arriving newest first
CREATE INDEX ix_issues_status_date ON issues (status, created_at DESC);
\ir repeat-query.sql

\echo Index sizes for this fixture
SELECT indexrelname AS index_name,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE schemaname = 'sql_index_lab'
ORDER BY relname, indexrelname;

repeat-query.sql

Warmup and three measured runs

Download

Preview contents

-- :query is set by 02-measure.sql. \ir resolves relative to this file.
\echo Warmup (exclude from comparison)
\ir :query
\echo Measured run 1
\ir :query
\echo Measured run 2
\ir :query
\echo Measured run 3
\ir :query

count-by-user.sql
Count comments for one user
Download
Preview contents of count-by-user.sql
```
EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*)
FROM comments
WHERE user_id = 1;
```

comments-by-issue-user.sql

Filter and order comments

Download

Preview contents

-- Fixed equivalent of the article's relative one-month cutoff.
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM comments
WHERE issue_id = 10
  AND user_id = 29
  AND created_at >= TIMESTAMPTZ '2026-07-18 12:00:00+00'
ORDER BY created_at DESC;

dashboard.sql

Latest comment for the newest open issues

Download

Preview contents

EXPLAIN (ANALYZE, BUFFERS)
SELECT i.id, c.body, c.created_at
FROM issues i
CROSS JOIN LATERAL (
    SELECT body, created_at
    FROM comments
    WHERE issue_id = i.id
    ORDER BY created_at DESC
    LIMIT 1
) c
WHERE i.status = 'open'
ORDER BY i.created_at DESC
LIMIT 25;

README.md

Setup, methodology, and cleanup instructions

Download

Preview contents

# 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

2. Run the lab

Open a terminal in that folder and run:

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 seed creates 100 users, 10,000 issues, and 1 million comments. Each issue has 100 comments, one per user. Dates and the query cutoff are fixed, so the same rows match on every run. The database stays inside the container, with no host port exposed.

The seed script rebuilds its sql_index_lab schema. The measurement script resets only the lab's named indexes, so you can rerun it against the same fixture.

3. Compare the plans

Open measurements.txt. Each stage includes one warmup and three measured runs. Compare the median execution time from the measured runs, and keep the full plans alongside your numbers.

Count a user's comments with primary keys only, then with a user_id index.
Compare single-column indexes with (issue_id, user_id, created_at DESC) for the filtered comment query.
Run the latest-comment dashboard. Add (issue_id, created_at DESC), then (status, created_at DESC) on issues.

Look at scan types, sorts, loop counts, heap fetches, and buffer hits and reads. A warmup does not guarantee that all data fits in memory. PostgreSQL can choose a different plan as statistics or settings change.

The output includes the PostgreSQL version, planner settings, and index sizes. When sharing results, include your OS, CPU, RAM, Docker resource limits, storage, and Docker version. This uniform, read-only fixture does not model production traffic or skewed data.

For the plan fields and their limits, see the PostgreSQL 18 EXPLAIN documentation and multicolumn index documentation.

4. Clean up

From the lab folder, remove this Compose project's container and its database volume:

docker compose down --volumes