Postgres index regression lab

By Milan Jovanović · PostgreSQL 18.6 · Docker · psql

Seed a PostgreSQL 18 database with 1,000,000 comments in Docker and run the profile query from the article against three index configurations. One psql script does all of it and prints the EXPLAIN (ANALYZE, BUFFERS) plan at each stage, so you can watch a new index make the query slower and a composite index fix it.

1. Download the files

Download and extract the ZIP, then open the postgres-index-regression folder inside. You need Docker. The first docker run pulls the postgres:18.6-alpine image.

Download all files (.zip)

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

lab.sql

Schema, 1,000,000 seed rows, three index stages, and the measured plans

Download

Preview contents

\set ON_ERROR_STOP on
\pset pager off
SELECT version();
-- Use a disposable database. Running this again replaces only this lab schema.
DROP SCHEMA IF EXISTS index_lab CASCADE;
CREATE SCHEMA index_lab;
SET search_path TO index_lab;
SELECT setseed(0.212);

CREATE TABLE comments (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id INT NOT NULL,
    body TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL
);

INSERT INTO comments (user_id, body, created_at)
SELECT 1 + (g % 100),
       'Comment body ' || g,
       CASE WHEN 1 + (g % 100) = 42
            THEN TIMESTAMPTZ '2026-09-01' - INTERVAL '14 months'
                 + random() * INTERVAL '2 months'
            ELSE TIMESTAMPTZ '2026-09-01' - random() * INTERVAL '2 years'
       END
FROM generate_series(1, 1000000) AS g;

CREATE INDEX ix_comments_user_id ON comments (user_id);
VACUUM ANALYZE comments;

\echo BASELINE: WARMUP
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM comments WHERE user_id = 42 ORDER BY created_at DESC LIMIT 10;
\echo BASELINE: MEASURED
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM comments WHERE user_id = 42 ORDER BY created_at DESC LIMIT 10;

CREATE INDEX ix_comments_created_at ON comments (created_at DESC);
\echo DATE INDEX: WARMUP
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM comments WHERE user_id = 42 ORDER BY created_at DESC LIMIT 10;
\echo DATE INDEX: MEASURED
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM comments WHERE user_id = 42 ORDER BY created_at DESC LIMIT 10;

CREATE INDEX ix_comments_user_date ON comments (user_id, created_at DESC);
\echo COMPOSITE INDEX: WARMUP
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM comments WHERE user_id = 42 ORDER BY created_at DESC LIMIT 10;
\echo COMPOSITE INDEX: MEASURED
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM comments WHERE user_id = 42 ORDER BY created_at DESC LIMIT 10;

output.txt

Complete output from a local run on PostgreSQL 18.6

Download

Preview contents

Pager usage is off.
                                         version                                         
-----------------------------------------------------------------------------------------
 PostgreSQL 18.6 on x86_64-pc-linux-musl, compiled by gcc (Alpine 15.2.0) 15.2.0, 64-bit
(1 row)

DROP SCHEMA
CREATE SCHEMA
SET
 setseed 
---------
 
(1 row)

CREATE TABLE
INSERT 0 1000000
CREATE INDEX
VACUUM
BASELINE: WARMUP
                                                                     QUERY PLAN                                                                     
----------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=9193.98..9194.00 rows=10 width=35) (actual time=9.855..9.858 rows=10.00 loops=1)
   Buffers: shared hit=8333 read=11
   ->  Sort  (cost=9193.98..9218.31 rows=9733 width=35) (actual time=9.853..9.855 rows=10.00 loops=1)
         Sort Key: created_at DESC
         Sort Method: top-N heapsort  Memory: 25kB
         Buffers: shared hit=8333 read=11
         ->  Bitmap Heap Scan on comments  (cost=111.86..8983.65 rows=9733 width=35) (actual time=2.353..9.133 rows=10000.00 loops=1)
               Recheck Cond: (user_id = 42)
               Heap Blocks: exact=8333
               Buffers: shared hit=8333 read=11
               ->  Bitmap Index Scan on ix_comments_user_id  (cost=0.00..109.42 rows=9733 width=0) (actual time=1.395..1.396 rows=10000.00 loops=1)
                     Index Cond: (user_id = 42)
                     Index Searches: 1
                     Buffers: shared read=11
 Planning:
   Buffers: shared hit=40 read=3
 Planning Time: 0.519 ms
 Execution Time: 10.084 ms
(18 rows)

BASELINE: MEASURED
                                                                     QUERY PLAN                                                                     
----------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=9193.98..9194.00 rows=10 width=35) (actual time=16.326..16.329 rows=10.00 loops=1)
   Buffers: shared hit=8344
   ->  Sort  (cost=9193.98..9218.31 rows=9733 width=35) (actual time=16.324..16.325 rows=10.00 loops=1)
         Sort Key: created_at DESC
         Sort Method: top-N heapsort  Memory: 25kB
         Buffers: shared hit=8344
         ->  Bitmap Heap Scan on comments  (cost=111.86..8983.65 rows=9733 width=35) (actual time=2.206..13.236 rows=10000.00 loops=1)
               Recheck Cond: (user_id = 42)
               Heap Blocks: exact=8333
               Buffers: shared hit=8344
               ->  Bitmap Index Scan on ix_comments_user_id  (cost=0.00..109.42 rows=9733 width=0) (actual time=1.376..1.376 rows=10000.00 loops=1)
                     Index Cond: (user_id = 42)
                     Index Searches: 1
                     Buffers: shared hit=11
 Planning Time: 0.118 ms
 Execution Time: 16.537 ms
(16 rows)

CREATE INDEX
DATE INDEX: WARMUP
                                                                      QUERY PLAN                                                                       
-------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..63.93 rows=10 width=35) (actual time=240.240..240.278 rows=10.00 loops=1)
   Buffers: shared hit=495902 read=1358
   ->  Index Scan using ix_comments_created_at on comments  (cost=0.42..61814.32 rows=9733 width=35) (actual time=240.238..240.274 rows=10.00 loops=1)
         Filter: (user_id = 42)
         Rows Removed by Filter: 495944
         Index Searches: 1
         Buffers: shared hit=495902 read=1358
 Planning:
   Buffers: shared hit=17 read=1
 Planning Time: 0.279 ms
 Execution Time: 240.306 ms
(11 rows)

DATE INDEX: MEASURED
                                                                      QUERY PLAN                                                                       
-------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..63.93 rows=10 width=35) (actual time=241.245..241.279 rows=10.00 loops=1)
   Buffers: shared hit=497260
   ->  Index Scan using ix_comments_created_at on comments  (cost=0.42..61814.32 rows=9733 width=35) (actual time=241.243..241.276 rows=10.00 loops=1)
         Filter: (user_id = 42)
         Rows Removed by Filter: 495944
         Index Searches: 1
         Buffers: shared hit=497260
 Planning Time: 0.082 ms
 Execution Time: 241.354 ms
(9 rows)

CREATE INDEX
COMPOSITE INDEX: WARMUP
                                                                    QUERY PLAN                                                                    
--------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..26.01 rows=10 width=35) (actual time=0.031..0.043 rows=10.00 loops=1)
   Buffers: shared hit=10 read=3
   ->  Index Scan using ix_comments_user_date on comments  (cost=0.42..24902.26 rows=9733 width=35) (actual time=0.030..0.041 rows=10.00 loops=1)
         Index Cond: (user_id = 42)
         Index Searches: 1
         Buffers: shared hit=10 read=3
 Planning:
   Buffers: shared hit=20 read=1
 Planning Time: 0.267 ms
 Execution Time: 0.058 ms
(10 rows)

COMPOSITE INDEX: MEASURED
                                                                    QUERY PLAN                                                                    
--------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..26.01 rows=10 width=35) (actual time=0.032..0.043 rows=10.00 loops=1)
   Buffers: shared hit=13
   ->  Index Scan using ix_comments_user_date on comments  (cost=0.42..24902.26 rows=9733 width=35) (actual time=0.031..0.042 rows=10.00 loops=1)
         Index Cond: (user_id = 42)
         Index Searches: 1
         Buffers: shared hit=13
 Planning Time: 0.084 ms
 Execution Time: 0.067 ms
(8 rows)

README.md

Setup, commands, and what to look for in the plans

Download

Preview contents

# MNW 212: an index changes another query's plan

Requires Docker. From this directory, start an isolated Postgres container:

```sh
docker run -d --name mnw212-lab -e POSTGRES_PASSWORD=postgres postgres:18.6-alpine
docker exec mnw212-lab pg_isready -U postgres
docker cp lab.sql mnw212-lab:/tmp/lab.sql
docker exec mnw212-lab psql -X -U postgres -f /tmp/lab.sql
docker rm -f mnw212-lab
```

Wait until `pg_isready` reports accepting connections before running `psql`.
The script owns the `index_lab` schema and replaces it on every run.
It seeds one million comments with a fixed random seed and fixed date anchor.
User 42 owns exactly 10,000 comments, all older than a year.
It runs each plan twice; the article quotes the second run, with plan costs and buffer details trimmed.

`output.txt` is the complete local run. Timing and sampled planner estimates depend on the machine and may vary. The property to inspect is the date-only index scanning and filtering hundreds of thousands of rows, versus ten rows through the composite index.

References: [Postgres index ordering](https://www.postgresql.org/docs/18/indexes-ordering.html), [reading EXPLAIN](https://www.postgresql.org/docs/18/using-explain.html).

2. Run it

Open a terminal in that folder and run:

docker run -d --name mnw212-lab -e POSTGRES_PASSWORD=postgres postgres:18.6-alpine
docker exec mnw212-lab pg_isready -U postgres
docker cp lab.sql mnw212-lab:/tmp/lab.sql
docker exec mnw212-lab psql -X -U postgres -f /tmp/lab.sql

Repeat pg_isready until it reports accepting connections, then copy the script into the container and run it. The script owns the index_lab schema and replaces it on every run, so you can repeat it freely.

The seed is 100 users and 1,000,000 comments with a fixed random seed and a fixed date anchor. User 42 owns exactly 10,000 comments, all between 12 and 14 months old, while everyone else's comments are spread across the last two years. Each stage runs the profile query twice and the article quotes the second run. Compare your output with output.txt. Row counts and plan shapes should match, timings will not.

3. What each stage shows

Baseline: an index on user_id. A Bitmap Heap Scan fetches all 10,000 of user 42's comments and a top-N heapsort keeps ten. In output.txt that is 8,344 shared buffer hits and 16.537 ms.
Add an index on created_at DESC. The planner switches to walking that index newest-first with a filter on user_id, expecting to stop early. The estimated total cost drops from 9194.00 to 63.93, but the scan removes 495,944 rows before finding ten, touches 497,260 buffers, and takes 241.354 ms.
Add a composite index on (user_id, created_at DESC). The Index Cond covers both the filter and the ordering, so the scan reads ten entries and stops: 13 buffers and 0.067 ms.

4. Change something

Open a session with docker exec -it mnw212-lab psql -U postgres, run SET search_path TO index_lab;, and drop ix_comments_user_date. Then run the profile query for user_id = 7, whose comments are spread across the two years. The planner picks the date index for this user too, but now the newest matches sit near the front of that index, so Rows Removed by Filter should land near the thousand entries the article estimates instead of half a million.

Drop ix_comments_user_id while the composite index exists and run the profile query for user 42 again. The plan does not change, because the composite index serves the equality on its own. The article's caution still applies: check the other queries that use the single-column index before dropping it for real.

When you are done, docker rm -f mnw212-lab removes the container and its data.