# 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} x `pinecone.max_scan_tuples` {2000, 20000, 100000} (not set when off) x `pinecone.rescore_multiplier` {10, 50} x `pinecone.scan_fraction` {0 = auto ramp, 0.5}: 56 points. - `pgvector_hnsw` (comparison): `hnsw.iterative_scan` {off, relaxed_order, strict_order} x `hnsw.ef_search` {40, 200} x `hnsw.max_scan_tuples` {20000 default, 100000} (not set when off): 20 points. Recorded per point (results JSON `systems..filtered..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 1. 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. 2. The venv, as in `../README.md`. 3. Dataset: `scripts/download-datasets.sh wikidpr` (5.3 GB download, 3.4 GB `wikidpr_768d_1m.npz`). The k-means and ground-truth caches under `datasets_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. 4. 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.conf` asks 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.005` forces 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_order` is "ordered but lossy" (drops rows closer than the last one served); `relaxed_order` returns batch-wise order. `max_scan_tuples` counts exact-rescored rows and never cuts the first batch. - Few qualifying rows: C1's 0.3% cell has 33 rows in 1M, so `LIMIT 10` returns 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_sort` series (never blended into index curves, mode assertion skipped); only if EVERY point of a cell is a seqscan does the runner repeat the grid with `enable_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_dirty` and `bench_content_hash` stamps carry no information. From `bench/vector/` in the repo they do: commit before a run of record.