perf(core): follow up on RUM shortlists for slow single-word search after security release #1808

Priority and status

Deferred until after the security release. Preserve the experiment now; this issue does not authorize a production rollout. The user accepted approximate top results in exchange for substantially faster search.

Follow-up to #1791 and merged PR #1802. #1802 improves exact multiword GIN retrieval; broad single-word ranking remains a separate bottleneck. The RUM work is a SQL prototype, not integrated application behavior.

Preserved implementation

• Branch: experiment/postgres-rum-shortlist
• Experiment commit: 4198b05afc838b96eced2e1d9cb7bacee3d5fa3b
• Detailed experiment report
• Runnable RUM benchmark
• Fixture seed harness

The branch retains the original #1802 ancestry. When implementing, start from current main and carry forward the experiment commit; do not reintroduce the already-merged search changes.

Findings

GIN finds matching rows efficiently, but exhaustive ranking still reads and scores large vectors across all matching chunks. RUM can retrieve ranked source hits before computing our existing ts_rank scores.

Dedicated local PostgreSQL 17 + RUM 1.3.15 container: 1 CPU, 2 GB memory, 128 MB shared buffers; synthetic 32,000 notes / 320,000 chunks across four projects. Parallel query and JIT disabled. SQL execution only: excludes API/network, hydration, and exact counts; no controlled OS-cache reset.

RUM meeting query: 0.830 / 0.070 / 0.027 s. Final runs returned 130 notes. Archive buffer accesses fell from roughly 3.1 million to 21,881 (inclusive accesses, not unique pages).

Proposed strategy

Retrieve up to 500 eligible parent hits and 2,000 eligible chunk hits through RUM, compute existing ts_rank on those source hits, and take the maximum score per (project_id, id, type). Apply structured filters before each budget. Keep exhaustive retrieval for unsupported query forms and exact counts.

This is approximate: RUM ordering differs from ts_rank, especially for prefix queries, and omitted chunks can change a note's score. Do not turn the experimental compiler into a universal replacement.

The prototype uses rum_tsvector_ops for prefixes. A correlated parent eligibility EXISTS with OFFSET 0 prevents a planner reversal that fetched all chunks. Multi-project predicates needed special query shaping to preserve ordered scans. Transaction-local enable_seqscan=off and enable_bitmapscan=off were used only for this experiment; revisit the plan robustness before integration.

Quality evidence and limits

A separate 2,000-note / 8,000-chunk fixture varies frequencies, late-body matches, titles, and prefix lexemes. Nine cases cover archive, arch*, and meeting across multiple scopes.

• Budget 500 parents / 2,000 chunks matched all nine exhaustive top-130 identities and scores.
• Budget 250 / 1,000 matched top-20, but top-130 quality fell as low as score-based NDCG 0.844.
• Eighteen additional project/filter checks covered late-body permalinks, titles, metadata, paths, updated dates, and item types.
• This measures agreement with ts_rank on synthetic data, not human relevance or production latency.
• Category/valid-time filters, skewed project sizes, duplicate-heavy notes, deep pagination, mixed scripts, and concurrency remain unvalidated.

Index tradeoff: chunk RUM index was about 330 MB vs 181 MB GIN, with roughly six minutes to build on this constrained fixture. Ingestion/write-maintenance cost remains unmeasured.

Resume after the security release

• Rebase the research onto current main without duplicating perf(core): collect multiword FTS operand matches through GIN #1802.
• Turn the useful quality fixtures into checked-in reproducible regressions; add skewed-project and duplicate-heavy-document cases.
• Verify actual Neon engine/extension support and tenant provisioning. Prior investigation found RUM available for Neon PG17, not PG18; refresh that evidence before rollout.
• Define explicit eligibility for the fast path, candidate budgets, result hydration, pagination limits, exact-count behavior, and fallback for unsupported queries.
• Implement an optional ranked retrieval strategy with safe extension/index provisioning; preserve SQLite behavior.
• Benchmark complete requests and representative relevance, including restrictive filters, concurrency, index size/build time, and ingestion overhead.
• Open the implementation PR with regression tests and rollout/rollback guidance. Enable only after review and explicit rollout authorization.

Reproduction and local-only artifacts

The linked report documents seeding and running:

uv run python scripts/benchmark_postgres_ranked_candidates.py --create-indexes
uv run python scripts/benchmark_postgres_ranked_candidates.py \
  --budget 500 --limit 130 --output /tmp/rum-final.json

The harness rejects remote hosts and non-single-operand queries; index creation requires the explicit flag. No production database was changed.

Additional exploratory runners, quality-fixture setup, and raw plans remain machine-local, outside the pushed branch, at /Users/phernandez/dev/basicmachines/search-1791-experiments. The dedicated bm-search-1791-bench Docker container holds the synthetic benchmark databases (port 55491). Do not depend on those artifacts surviving indefinitely; use the committed harness/report and promote relevant quality fixtures when resuming.

Previously completed verification: benchmark harness Ruff/format/type checks, nine quality comparisons at several budgets, and eighteen filter/project checks. No production application integration tests exist yet.