Automatic Index Creation Benchmarks

Methodology

These benchmarks measure the performance impact of Gold Lapel's automatic index creation across five common query patterns. The baseline dataset has no secondary indexes — only primary keys. Gold Lapel observes query patterns during a warmup phase, creates indexes using CREATE INDEX CONCURRENTLY, and the benchmark measures steady-state performance after index creation.

What the EXPLAIN looks like

The most telling measure of index effectiveness is the EXPLAIN plan transition: from sequential scan (reading the entire table) to index scan (reading only matching rows).

-- Before: Sequential scan on 5M rows
EXPLAIN ANALYZE
SELECT * FROM events WHERE user_id = 42 AND created_at > '2025-01-01';

--                          QUERY PLAN
-- -------------------------------------------------------
-- Seq Scan on events  (cost=0.00..198234.00 rows=847 ...)
--   (actual time=234.112..567.891 rows=823 loops=1)
--   Filter: ((user_id = 42) AND (created_at > ...))
--   Rows Removed by Filter: 4999177
-- Planning Time: 0.089 ms
-- Execution Time: 567.934 ms

-- After: Gold Lapel creates the composite index
-- CREATE INDEX CONCURRENTLY gl_idx_events_user_created
--   ON events (user_id, created_at);

--                          QUERY PLAN
-- -------------------------------------------------------
-- Index Scan using gl_idx_events_user_created on events
--   (cost=0.43..42.18 rows=847 ...)
--   (actual time=0.034..4.812 rows=823 loops=1)
--   Index Cond: ((user_id = 42) AND (created_at > ...))
-- Planning Time: 0.112 ms
-- Execution Time: 4.847 ms

Results

The partial index shows the highest improvement (380x) because it indexes only 6% of rows (active records), making the index 17x smaller and faster to scan than a full B-tree on the same column.

Analysis

The improvement factor varies by pattern because it depends on selectivity — how many rows the query actually needs out of the total table size.

• High-selectivity filters (equality on a specific user, text prefix match) show the largest improvements because the index eliminates the most rows. The text prefix pattern improves 636x because the LIKE query without an index must scan every row's email column.
• Composite indexes show moderate improvements (73x–118x) because the index serves both the filter and the sort, eliminating a separate Sort node in the query plan.
• Partial indexes combine selectivity with a smaller index size. When 94% of rows are in a terminal state, indexing only the active 6% produces an index that fits in a single index page — dramatically faster than scanning the full B-tree.

Index creation overhead

Gold Lapel creates all indexes using CONCURRENTLY, which does not lock the table for writes during creation. Index build times for this dataset:

The one-time build cost is amortized across every subsequent query. For a pattern executing 100 times per hour, the 3-5 second build cost is recovered within the first minute of use.

How to reproduce

# Clone the benchmark suite
git clone https://github.com/goldlapel/benchmarks
cd benchmarks/index-auto-creation

# Start test environment
docker compose up -d

# Seed dataset (no indexes beyond primary keys)
./seed.sh --no-indexes

# Run baseline
./run.sh --mode baseline --clients 16 --duration 300

# Run with Gold Lapel (indexes created during warmup)
./run.sh --mode goldlapel --clients 16 --duration 300

# Compare
./compare.sh results/baseline.json results/goldlapel.json

The seed script creates tables with no secondary indexes. Gold Lapel's warmup phase detects patterns and creates indexes before the timed benchmark begins.