PostgreSQL index comparison

Commit 59f2bac41f98c35e3519bc57b94d574952f7cafd. Profile: focused. Run status: complete. Started 2026-11-02T05:49:55Z; completed 2026-11-02T05:58:57Z.

2,758 timed observations, 394 successful exact result-multiset checks, 0 recorded errors, and 0 explicitly unmeasured configurations after maintenance failures. A correctness check is separate from EXPLAIN timing; the suite does not mistake an aggregate's output row count for a proof of correctness. An operation timeout is a failed attempt, not a successful duration; dependent checks are skipped and identified below.

Build failures: 0 recorded failed index builds. Statements have a 60-second limit. A failed index is omitted from the remaining portfolio and is not retried in subsequent build rounds; the family remains explicitly partial. Query results for that family use its surviving indexes or a planner fallback. They are not measurements of the missing index. See the completeness column and individual-index status below. Resume history and any earlier fatal attempt are preserved in metadata.

Hardware: AMD Ryzen 7 5700X3D 8-Core Processor, 16 logical CPUs, 23.47 GiB visible RAM. Server: PostgreSQL 18.6 (Ubuntu 18.6-1.pgdg24.04+2) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04.1) 13.3.0, 64-bit. Platform: Linux-6.18.54.1-microsoft-standard-WSL2-x86_64-with-glibc2.39. See metadata.json for compiler flags, all server settings, seed, and run arguments. Shared buffers are 2GB; normal work_mem is 64 MB and maintenance_work_mem is 512 MB. Parallel query and JIT are disabled to isolate access paths; index builds use the recorded maintenance-worker setting. Durability is enabled. Autovacuum is disabled; vacuum and visibility transitions are explicit.

This is a synthetic, single-machine comparison, inspired by the presentation of the Biscuit benchmark. It measures this extension's integer, array, and full-text workloads, not Biscuit's wildcard workload. The data and workload SQL are in queries.json; generation and execution are in the runner.

Interpretation and fairness

Each selected scalar family attempts the same single-column portfolio on a freshly generated heap: seven indexes in the focused profile, eight in the full profile. The focused profile omits clustered; its document portfolio omits grp. Exact definitions are saved in queries.json. If selected, btree_tuned additionally receives a composite (c200,c20,c2) index and a covering (c200) INCLUDE (id,payload) index; its larger portfolio is charged in build time, bytes, and maintenance. Scalar GIN and GiST use btree_gin and btree_gist. GIN fastupdate is on; BRIN uses 32-page ranges. Roaring uses extension defaults. roaring_bitmap reuses the roaring portfolio with count pushdown disabled. roaring_btree is the roaring portfolio plus a B-tree on c1m, the ORDER BY column of the ordered_* cases, and measures only those cases: with the B-tree beside the lion indexes the planner may choose the LionOrdered scan (the lion filter's TID set tested against a walk of the B-tree), core's ordered B-tree walk, or a lion bitmap and a Sort.

Focused document comparisons use GIN and roaring for text arrays and full-text search. The full profile also includes GiST text search; GiST has no text[] containment opclass here, so its array queries are fallbacks. The range and ordered-LIMIT cases measure distinct access patterns: consult the recorded plans for LionCount, bitmap scans, or sequential fallbacks. Lion indexes do not return rows in order themselves; with a B-tree on the ORDER BY column (roaring_btree) the LionOrdered scan walks that B-tree and filters it with lion's answer. Sequential execution is always the correctness reference, but is timed as a separate portfolio only when seq is selected. This suite does not claim coverage of geometric, vector, JSON, wildcard, or arbitrary extension indexes.

default uses normal planner preferences. prefer_index disables sequential scans as a diagnostic; it is not a production tuning recommendation and does not guarantee use of an index. The access column and raw plans identify the actual route. A fallback is a query execution result, not a successful measurement of the named index algorithm.

Each query is verified against a forced sequential-scan result, warmed independently, and measured in randomized complete rounds within a portfolio. Families are shuffled with a fixed seed. Families run sequentially; there is no interleaved cross-family crossover trial. Warm means no deliberate cache eviction, not a guarantee that the working set fits shared buffers. shared_buffers_cold restarts the dedicated server before each measured query; the OS page cache remains warm, and planning can warm metadata pages before execution. No claim about cold physical-disk performance is made.

clean follows VACUUM FREEZE. The focused profile dirties every hundredth row's non-indexed payload directly from the clean state. The full profile first measures dirty_clustered_5pct (the first 5% of rows updated), then adds scattered updates. Dirty-state timings from the two profiles are not interchangeable. operations.jsonl records measured all-visible and heap-page counts. low_work_mem uses 64 kB and records lossy bitmap/temp-block evidence in samples.jsonl. The normal setting is 64 MB. Maintenance is measured once per portfolio, with a checkpoint before each operation; WAL includes full-page images and index WAL. Insert batches contain 1% of initial rows; indexed updates affect the first 1%, and deletes use id % 100 = 1. Both profiles measure VACUUM; only the full profile adds REINDEX. Build repeats are recorded separately.

Read latency is server EXPLAIN ANALYZE execution time with per-node timing off, including EXPLAIN instrumentation but excluding planning and result transfer. Planning time and full buffer/WAL plans are retained in samples.jsonl and plans.jsonl.gz. Medians, interpolated p95, sample CV, and a seeded 2,000-resample percentile bootstrap 95% CI of the median are shown. With few samples, especially cold samples, intervals and p95 are descriptive and imprecise. Speedups divide matching sequential-scan medians by the variant median; they are not cross-workload averages. Sub-millisecond ratios are especially sensitive to timer resolution and instrumentation overhead. Timeout/error observations remain errors and are excluded from latency summaries, never converted to zero.

Concurrency measures closed-loop SELECTs through Python threads and synchronous libpq, including client dispatch, result transfer, and decoding. It can become client-bound for very fast counts; it is not a maximum server-throughput claim. The separate growth/churn/write supplement (stress.py) covers empty-index growth, changing-key churn, prepared statements, fully dirty low-memory counts, and short concurrent-write bursts. Crash recovery, replication, filesystem cache eviction, repeated maintenance trials, variable GIN pending-list settings, and tuning sweeps remain outside these runs. The benchmark is exhaustive over its published matrix, not every PostgreSQL workload.

Storage columns labeled Heap MiB use pg_table_size, including auxiliary forks and any TOAST storage; portfolio bytes use pg_indexes_size. Individual-index bytes use pg_relation_size (main fork). MiB means 1,048,576 bytes.