Contents
Residual-filter benchmark (iterative scan): residual-c1.yaml, residual-c2.yaml
RUN 2026-10-07 on a release Postgres 17.10 (analysis: ../REPORT-residual-filter.md; raw results results/residual_{c1,c2,ext,ext2}_DIRTY_*, not committed). Added since the port: explain_probe: true (EXPLAIN ANALYZE cost probe per index point), an exact_seqscan reference system, residual-ext.yaml / residual-ext2.yaml (rescore pool, scan_fraction 1.0 and the shipping mode L). Question: when a query carries a predicate the index cannot push (a second
column), how many rows does LIMIT 10 return, at what recall and latency, and what does
pinecone.iterative_scan buy over off and over pgvector’s hnsw.iterative_scan?
| config | pushed key (index column) | residual (executor Filter) |
combined rows (of 1M) |
|---|---|---|---|
residual-c1 |
filt_correlated_0010_eq (1% correlated, 10,170 rows) |
filt_uncorrelated_0003_eq (0.3%, 3,098) / _0010_eq (1%, 10,048) |
33 / 113 |
residual-c2 |
filt_correlated_0100_eq (10% correlated, 100,844 rows) |
same two | 326 / 1,004 |
(row counts computed from the cached k-means and the harness’s own mask generator.)
wiki_dpr 1M x 768d, L2, k=10, cross-tenant queries only (filters.query_modes: [out_region]), same
artifact/seed/k-means as wikidpr_1m_pg_pinecone.yaml (dataset key real_fd937e5862449934).
category = X is the residual column: uncorrelated with the tenant key, so it passes the same share of
every cluster. Swap mode: uncorrelated for correlated in a residual to make it spatially clustered.
Sweep per filter cell, 1 and 8 clients:
pg_pinecone_native:pinecone.iterative_scan{off, relaxed_order, strict_order} xpinecone.max_scan_tuples{2000, 20000, 100000} (not set when off) xpinecone.rescore_multiplier{10, 50} xpinecone.scan_fraction{0 = auto ramp, 0.5}: 56 points.pgvector_hnsw(comparison):hnsw.iterative_scan{off, relaxed_order, strict_order} xhnsw.ef_search{40, 200} xhnsw.max_scan_tuples{20000 default, 100000} (not set when off): 20 points.
Recorded per point (results JSON systems.<s>.filtered.<cell>.query_modes.out_region.sweep[]; the
.md report prints the same as a table): recall, rows_returned_mean / rows_returned_min /
frac_filled (rows at LIMIT 10), p50_ms / p95_ms / p99_ms, qps_wall, and for pg_pinecone
scan_rescored_mean / scan_rescored_p95 / scan_batches_mean / scan_capped_frac (from
pinecone.last_scan_stats(), read after the timed query), plus plan_shape and the effective GUCs.
Setup
- A pg_pinecone build with the iterative-scan GUCs (
pinecone.iterative_scan,pinecone.max_scan_tuples,pinecone.last_scan_stats()); the harness refuses to build the index on an older build. Install it as in../README.md(“Install pg_pinecone”). The 2026-10-07 numbers come from a release Postgres 17.10 (-O2, no cassert). pgrx’s own PG 17 is a cassert build, 2.5x slower on the 50k smoke and up to 10x on rescore-heavy points: do not compare across builds. - The venv, as in
../README.md. - Dataset:
scripts/download-datasets.sh wikidpr(5.3 GB download, 3.4 GBwikidpr_768d_1m.npz). The k-means and ground-truth caches underdatasets_cache/are keyed by dataset content and recomputed on a fresh checkout; the 2026-10-07 run reused the 2026-09 caches (22 MB) and skipped the 1M brute force. - Room: ~12 GB free for
--data-dir(table 3.1 GB, hnsw 4.1 GB, pg_pinecone 0.2 GB, WAL), ~16 GB free RAM (pg/tuned.confasks for 8 GB shared_buffers, the config for 8 GB maintenance_work_mem). Close other benchmarks: 8-client points compete for cores.
Run
cd bench/vector
export PG_PINECONE_BENCH_DATA="$(cd .. && pwd)/.data"
env -u PINECONE_API_KEY nohup nice -n 10 .venv/bin/python run.py \
--config configs/residual-c1.yaml --data-dir "$PG_PINECONE_BENCH_DATA/vector/pgdata-residual-c1" -v \
> "$PG_PINECONE_BENCH_DATA/vector/residual-c1.log" 2>&1 &
Add --allow-dirty only if the git tree is dirty (the 2026-10-07 runs were made from a git-excluded directory and used it).
--data-dir is mandatory next to any other run. Poll the log (== system, build:, filtered ground truth
lines); partial results land in results/residual_c1_*.partial.json after every filter cell (no resume: a crash keeps
the partial file, a rerun starts over). Run c1, then c2,
one at a time. ./cleanup.sh "$PG_PINECONE_BENCH_DATA/vector/pgdata-residual-c1" stops a leftover scratch Postgres. Rehearse first
with configs/residual-smoke.yaml: the same
code path and the full iterative grid on 50k synthetic rows (one cell: 10% key + 0.5% residual = 24 rows, ~7 min).
Time (estimate; measured 2026-10-07 on a release build: C1 55 min, C2 55 min, builds 90 s hnsw + 110 s pg_pinecone)
| step | time |
|---|---|
| load + ANALYZE + ground truth (unfiltered 1M x 500 is cached if copied; the residual masks only touch 33-100k rows) | 3-6 min |
| hnsw build (8 GB maintenance_work_mem) | 2.5-5 min |
| pg_pinecone build | 5-15 min (cassert pgrx Postgres: ~650 s; 50k took 8 s) |
| pg_pinecone sweep, 2 cells x 56 points | 60-120 min |
| hnsw sweep, 2 cells x 20 points | 15-70 min (the top end if the planner picks a seqscan for every point of a cell and the runner repeats the grid with enable_seqscan = off) |
| total per config | about 2.5 h (1.5-3.5 h); c1 + c2 about 5 h |
Point cost = 500 queries x per-query latency (split across clients) + 5 s throughput window + ~2 s overhead.
Per-query latency is about 28 us x exact-rescored rows (measured 138 ms for 4,900 rows at 50k on this Mac):
off 30 ms (rm 10) to 140 ms (rm 50); relaxed/strict 30-300 ms (first batch is never cut, the cap bounds the rest;
C1/0.3% can rescore the whole 10k-row key set, ~280 ms); hnsw relaxed at 100k budget 50-120 ms, a seqscan ~113 ms.
To cut the time: concurrency: [1] (about half), drop max_scan_tuples 100000 or rescore_multiplier 50.
n_queries must stay 500 (changing it changes the dataset key and recomputes k-means).
Read before interpreting
mode_l_threshold: 0.005forces the 1% key into mode H; at the shipping default (0.03) it routes to mode L (exact over the key rows), where the iterative GUCs are inert and the answer is complete.- The rescore pool is cut BEFORE the residual qual: expect low recall at defaults even when rows fill; the deepen-before-widen follow-up targets exactly that.
strict_orderis “ordered but lossy” (drops rows closer than the last one served);relaxed_orderreturns batch-wise order.max_scan_tuplescounts exact-rescored rows and never cuts the first batch.- Few qualifying rows: C1’s 0.3% cell has 33 rows in 1M, so
LIMIT 10returns at most 33 and recall is noisy; the exact seqscan (recall 1.0; 25-34 ms on production builds, ~113 ms on the Mac cassert build) is the baseline an iterative scan has to beat. - Planner: a point whose plan is a seqscan is kept as its own
seqscan_sortseries (never blended into index curves, mode assertion skipped); only if EVERY point of a cell is a seqscan does the runner repeat the grid withenable_seqscan = off(forced_index_sweep), which adds up to 20 / 56 points per cell. At 50k rows this happens for both systems (residual-smoke); at 1M it should not for pg_pinecone. - pgrx Postgres is a cassert build: absolute latencies are inflated (stamped as
pg_debug_assertions).- The 2026-10-07 runs were made from a git-excluded scratch directory, so their
git_dirtyandbench_content_hashstamps carry no information. Frombench/vector/in the repo they do: commit before a run of record.
- The 2026-10-07 runs were made from a git-excluded scratch directory, so their