fix(postgres): index-backed RRF hybrid search (#10186) by localai-bot · Pull Request #46 · mudler/LocalRecall

localai-bot · 2026-06-05T23:00:46Z

Problem

The hybrid search query (PostgresDB.Search) wrapped the vector distance operator in a scalar similarity expression and sorted on the alias:

( COALESCE(-(full_text <@> to_bm25query(...)),0)*$2 +
  COALESCE((1 - (embedding <=> $3::vector)),0)*$4 ) as similarity
...
ORDER BY similarity DESC

pgvector's HNSW/DiskANN index can only serve a bare ORDER BY embedding <=> $vec path. Sorting on the wrapped scalar blinds the planner into a full sequential scan over every row, then an in-memory top-N sort. On multi-million-row collections this exceeds the statement timeout and returns {"count":0,"results":[]} (see mudler/LocalAI#10186).

Fix

Adopt the canonical Reciprocal Rank Fusion hybrid-search pattern recommended by both pgvector and Timescale (the pg_textsearch + pgvectorscale stack this engine runs on):

Each arm retrieves its top-N candidates via a bare operator (ORDER BY full_text <@> to_bm25query(...) / ORDER BY embedding <=> $vec, both index-served) and assigns a ROW_NUMBER() rank.
The arms are combined with a FULL OUTER JOIN and scored by weighted RRF: bm25Weight/(60+rank) + vectorWeight/(60+rank).
The fused id list is joined back to the table by primary key to fetch payloads.

RRF fuses by rank, not raw score, so it no longer mixes BM25's unbounded scores with cosine similarity's [0,1] range. The existing bm25Weight/vectorWeight knobs are preserved as per-arm RRF multipliers (equal by default). Candidate pool per arm = GREATEST(limit*10, 100).

Note: the returned similarity value is now an RRF score (small, e.g. ~0.008-0.016 at default weights) rather than the previous 0-1ish weighted sum. localrecall does not threshold on it internally.

Verification (`EXPLAIN`, DiskANN + pg_textsearch stack)

Before (wrapped scalar): Seq Scan on documents_x (cost=0.00..5095.00 rows=2000) + in-memory Sort.

After (RRF):

Sort
  -> Merge Join
       -> Hash Full Join
            -> Index Scan using idx_x_bm25       Order By: (full_text <@> ...)
            -> Index Scan using idx_x_embedding   Order By: (embedding <=> ...)
       -> Index Scan using documents_x_pkey

No Seq Scan; cost 5128 -> 648. Query executes correctly (the BM25-matching doc ranks #1).

Tests

Adds rag/engine/postgres_planning_test.go, a planning regression test that builds the real schema via setupDatabase() (no embedding model needed), seeds rows, and asserts via EXPLAIN that the vector ordering is index-served and the table is never sequentially scanned. Fusion-agnostic, so it guards the index path regardless of scoring. Runs in the existing make test-unit harness.

Fixes mudler/LocalAI#10186

The hybrid search query wrapped the vector distance operator in a scalar similarity expression and sorted on the alias (ORDER BY similarity DESC). That blinds the planner: pgvector's HNSW/DiskANN index can only serve a bare "ORDER BY embedding <=> $vec" path, so the wrapped form degraded to a full sequential scan over every row. On multi-million-row collections this exceeds the statement timeout and returns an empty result set (mudler/LocalAI#10186). Rewrite the query using the Reciprocal Rank Fusion pattern recommended by pgvector and Timescale (pg_textsearch + pgvectorscale): each arm retrieves its top-N candidates via a bare operator (index-served) and assigns a rank; the arms are combined with a FULL OUTER JOIN and scored by weighted RRF (weight/(60+rank)), then joined back to the table by primary key to fetch payloads. RRF fuses by rank rather than raw score, so it no longer mixes BM25's unbounded scores with cosine similarity's [0,1] range. The bm25 and vector weights are preserved as per-arm RRF multipliers. Add a planning regression test that asserts, via EXPLAIN, that the vector ordering is served by the index ("Order By: (embedding <=>") and that the documents table is never sequentially scanned. Signed-off-by: Ettore Di Giacinto <mudler@localai.io>

Pulls in mudler/LocalRecall#46, which rewrites the PostgreSQL hybrid search using the canonical Reciprocal Rank Fusion pattern (index-backed candidate retrieval + FULL OUTER JOIN + weighted RRF). Fixes the full sequential scan that blew past the statement timeout on large collections (mudler/LocalAI#10186). Signed-off-by: Ettore Di Giacinto <mudler@localai.io>

Bumps the agent stack to pull in the PostgreSQL hybrid-search fix: - mudler/localrecall -> v0.6.3-...-a6485bf68230 (mudler/LocalRecall#46) - mudler/LocalAGI -> ...-0ec165c86c05 (re-pins localrecall) localrecall's hybrid search previously sorted on a wrapped scalar similarity expression, which blinded the planner into a full sequential scan over every row and exceeded the statement timeout on large collections, returning an empty result set. It now uses the canonical Reciprocal Rank Fusion pattern (index-backed candidate retrieval + FULL OUTER JOIN + weighted RRF). Fixes #10186 Signed-off-by: Ettore Di Giacinto <mudler@localai.io>

docker-compose starts LocalAI with the embedding model on its command line, but the model is downloaded and loaded lazily on first request. wait-localai only checked the HTTP health endpoints, which return OK as soon as the server is up - so the test suites raced the model download and intermittently failed in BeforeEach/Store with `model "granite-embedding-107m-multilingual" not found` (404), across both the postgres and chromem engines. Warm the model as part of wait-localai: after the server is healthy, POST to /v1/embeddings and retry until it answers (up to 600s, since the first call triggers the download). This makes the model a hard readiness gate for every suite that goes through start-test-services, removing the race at its source rather than retrying each individual embedding call. Signed-off-by: Ettore Di Giacinto <mudler@localai.io>

Pulls in mudler/LocalRecall#46 (merged), which rewrites the PostgreSQL hybrid search using the canonical Reciprocal Rank Fusion pattern (index-backed candidate retrieval + FULL OUTER JOIN + weighted RRF). Fixes the full sequential scan that blew past the statement timeout on large collections (mudler/LocalAI#10186). Signed-off-by: Ettore Di Giacinto <mudler@localai.io>

Bumps the agent stack to pull in the PostgreSQL hybrid-search fix: - mudler/localrecall -> v0.6.3-...-9a3b3321a9cd (mudler/LocalRecall#46, merged) - mudler/LocalAGI -> ...-4b2bc28cc506 (re-pins localrecall) localrecall's hybrid search previously sorted on a wrapped scalar similarity expression, which blinded the planner into a full sequential scan over every row and exceeded the statement timeout on large collections, returning an empty result set. It now uses the canonical Reciprocal Rank Fusion pattern (index-backed candidate retrieval + FULL OUTER JOIN + weighted RRF). Fixes #10186 Signed-off-by: Ettore Di Giacinto <mudler@localai.io>

chore: bump localrecall to index-backed RRF hybrid search Pulls in mudler/LocalRecall#46 (merged), which rewrites the PostgreSQL hybrid search using the canonical Reciprocal Rank Fusion pattern (index-backed candidate retrieval + FULL OUTER JOIN + weighted RRF). Fixes the full sequential scan that blew past the statement timeout on large collections (mudler/LocalAI#10186). Signed-off-by: Ettore Di Giacinto <mudler@localai.io>

Bumps the agent stack to pull in the PostgreSQL hybrid-search fix: - mudler/localrecall -> v0.6.3-...-9a3b3321a9cd (mudler/LocalRecall#46, merged) - mudler/LocalAGI -> ...-14aed1ae4336 (mudler/LocalAGI#477, merged) localrecall's hybrid search previously sorted on a wrapped scalar similarity expression, which blinded the planner into a full sequential scan over every row and exceeded the statement timeout on large collections, returning an empty result set. It now uses the canonical Reciprocal Rank Fusion pattern (index-backed candidate retrieval + FULL OUTER JOIN + weighted RRF). Fixes #10186 Signed-off-by: Ettore Di Giacinto <mudler@localai.io>

#10186) (#10192) chore: bump LocalAGI and localrecall (index-backed RRF hybrid search) Bumps the agent stack to pull in the PostgreSQL hybrid-search fix: - mudler/localrecall -> v0.6.3-...-9a3b3321a9cd (mudler/LocalRecall#46, merged) - mudler/LocalAGI -> ...-14aed1ae4336 (mudler/LocalAGI#477, merged) localrecall's hybrid search previously sorted on a wrapped scalar similarity expression, which blinded the planner into a full sequential scan over every row and exceeded the statement timeout on large collections, returning an empty result set. It now uses the canonical Reciprocal Rank Fusion pattern (index-backed candidate retrieval + FULL OUTER JOIN + weighted RRF). Fixes #10186 Signed-off-by: Ettore Di Giacinto <mudler@localai.io> Co-authored-by: Ettore Di Giacinto <mudler@localai.io>

localai-bot mentioned this pull request Jun 5, 2026

chore: bump LocalAGI + localrecall (fix pgvector hybrid search seqscan, #10186) mudler/LocalAI#10192

Merged

mudler mentioned this pull request Jun 5, 2026

chore: bump localrecall to index-backed hybrid search mudler/LocalAGI#477

Merged

mudler force-pushed the fix/hybrid-search-index-pushdown-10186 branch from 15256b2 to a6485bf Compare June 5, 2026 23:35

localai-bot changed the title ~~fix(postgres): index-backed two-stage re-rank for hybrid search~~ fix(postgres): index-backed RRF hybrid search (#10186) Jun 5, 2026

mudler merged commit 9a3b332 into main Jun 6, 2026
8 of 9 checks passed

localai-bot mentioned this pull request Jun 6, 2026

bug: Vector collection search causes seqscan on 2M+ rows, hitting 10s context timeout (HNSW index ignored) mudler/LocalAI#10186

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

fix(postgres): index-backed RRF hybrid search (#10186)#46

fix(postgres): index-backed RRF hybrid search (#10186)#46
mudler merged 2 commits into
mainfrom
fix/hybrid-search-index-pushdown-10186

localai-bot commented Jun 5, 2026 •

edited

Loading

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

Conversation

localai-bot commented Jun 5, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Problem

Fix

Verification (EXPLAIN, DiskANN + pg_textsearch stack)

Tests

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

localai-bot commented Jun 5, 2026 •

edited

Loading

Verification (`EXPLAIN`, DiskANN + pg_textsearch stack)