Database Indexes Explained - PostgreSQL Demo

Companion code for the tutorial Database Indexes Explained: A PostgreSQL Walkthrough With EXPLAIN ANALYZE.

A Spring Boot 4 + Testcontainers Postgres project that proves how each index type behaves under EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) against a 500,000-row seeded table. Tests parse the JSON plan tree and assert on plan node types, buffer reads, and which index (if any) the planner picked.

---

What this proves

• A query against an unindexed column does a Seq Scan over the entire table.
• A B-tree index turns equality and range queries into Index Scans.
• A composite index works when filters match its leftmost columns and not when they skip them.
• A partial index covers only rows matching its predicate and is ignored for queries outside it.
• LOWER(col) on an indexed column disables the index.
• Low-selectivity filters cause the planner to ignore an index in favor of Seq Scan.

Every claim is a passing assertion. Run mvn verify to verify.

---

How the tests work

Every IT extends PostgresContainerSupport, which starts a single postgres:16 container, runs db/init.sql (schema + 500k row seed + ANALYZE), and shares the container across all test classes through a static initializer. Each test class creates its own indexes in @BeforeAll and drops them in @AfterAll so order does not matter.

Plans are captured by ExplainAnalyzer, which wraps a query in EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON), parses the resulting JSON, and returns an ExplainResult exposing:

• topNodeType - top-level plan node ("Seq Scan", "Index Scan", "Bitmap Heap Scan", ...)
• usesIndex() / usesIndex(name) - walks the entire plan tree looking for any Index/Index Only/Bitmap Index Scan
• sharedHitBlocks / sharedReadBlocks - cache hits vs disk reads (the truth EXPLAIN ANALYZE timing hides)
• planRows / actualRows - estimated vs measured, the gap that exposes stale stats

---

Seed data

INSERT INTO orders (user_id, email, status, amount, created_at, deleted_at)
SELECT
    (i % 50000) + 1,
    'user' || i || '@example.com',
    CASE WHEN i % 20 = 0 THEN 'active'
         WHEN i %  5 = 0 THEN 'cancelled'
         ELSE 'completed' END,
    (random() * 1000)::numeric(10,2),
    NOW() - (random() * 365 * INTERVAL '1 day'),
    CASE WHEN i % 10 = 0 THEN NOW() - (random() * 30 * INTERVAL '1 day') ELSE NULL END
FROM generate_series(1, 500000) AS i;
ANALYZE orders;

500,000 rows. 50,000 distinct user_id values. status is intentionally skewed: about 5% active, 75% completed, 20% cancelled. The skew is what makes the partial-index and low-selectivity tests interesting - both depend on Postgres choosing different plans based on row distribution.

---

Test map

File	Index under test	What it asserts
BaselineSeqScanIT	none	plan is Seq Scan, > 1000 buffers touched
BTreeIndexIT	(user_id)	equality and range filters use the B-tree
CompositeIndexIT	(user_id, status)	filter on leftmost column, or both, uses the index
LeftmostPrefixIT	(user_id, status)	filter on status alone falls back to Seq Scan
PartialIndexIT	(created_at) WHERE status='active'	active query uses index, completed query does not
IndexPitfallsIT	(email), (status)	LOWER(email) and low-selectivity filters bypass the index

---

Project structure

src/
├── main/java/com/umurinan/indexes/
│   └── IndexesApplication.java
├── main/resources/
│   └── application.properties
└── test/
    ├── java/com/umurinan/indexes/
    │   ├── support/
    │   │   ├── ExplainAnalyzer.java       wraps EXPLAIN, parses JSON, returns ExplainResult
    │   │   ├── ExplainResult.java         typed plan with usesIndex(name) walker
    │   │   └── PostgresContainerSupport.java   singleton Testcontainers Postgres + Spring config
    │   ├── BaselineSeqScanIT.java
    │   ├── BTreeIndexIT.java
    │   ├── CompositeIndexIT.java
    │   ├── LeftmostPrefixIT.java
    │   ├── PartialIndexIT.java
    │   └── IndexPitfallsIT.java
    └── resources/
        ├── application.properties
        └── db/init.sql                    500k-row seed + ANALYZE

---

Running the tests

# All integration tests
mvn verify

# One scenario at a time
mvn verify -Dit.test=BaselineSeqScanIT
mvn verify -Dit.test=PartialIndexIT

# See the plan output alongside test results
mvn verify 2>&1 | grep -E "EXPLAIN|Tests run|Index Scan|Seq Scan"

Expected: 9 tests, 0 failures, BUILD SUCCESS.

The first run pulls postgres:16 and seeds 500k rows, so it takes a few seconds longer than subsequent runs that hit the cached image.

---

Tech stack

• Java 21
• Spring Boot 4.0.5
• Spring JDBC + JdbcTemplate
• Jackson for plan parsing
• JUnit 5 + AssertJ
• Testcontainers 1.21.3 (Postgres 16)
• Maven (Failsafe runs *IT classes during verify)