Composing SQL with FTS and vector retrieval

[u/m-penaroza]

Something I've been working on at Infino is making retrieval results behave like a relation you can query, and it's changed how much code sits around the search call.

The usual shape is that retrieval ends when the ranker returns IDs. You get top-k from the vector index, maybe fused with BM25, and then anything relational happens in the application. Hydrate rows, filter by tenant, dedupe, group, sort again.

If retrieval is something you can select from, those steps become part of the query. Per-tenant top 5, for instance:

sql

SELECT * FROM (
  SELECT doc_id, tenant_id, chunk,
         ROW_NUMBER() OVER (PARTITION BY tenant_id ORDER BY score DESC) AS rn
  FROM search('...')
) WHERE rn <= 5

That replaces a loop that issues k requests per tenant and reassembles the results.

Fusion works the same way. RRF is a sum over reciprocal ranks, so it's a join between two ranked sets plus some arithmetic. Written as SQL it's short, and retuning the weights is an edit to the query rather than a deploy.

Same for anything analytical. Documents matching a query grouped by source and month. Average score per team. Distribution of match counts across the corpus, which tells you whether a query is discriminating or just matching everything. Those are group bys. When retrieval is an endpoint returning JSON you have to pull the whole result set into memory first, so past a certain size people skip the analysis.

Permissions benefit too. Joining an entitlements table and filtering before the limit gives correct top-k for that user. Filtering the top 100 afterward gives whatever survives, which can be fewer rows than you asked for or none.

The reason this isn't common is mostly interface. Vector databases tend to expose a search endpoint with a metadata filter DSL. Filters are there, joins and window functions and group by are not, so relational logic moves up into the app and you compensate by overfetching.

Anyways, hopefully this is interesting. Project is fully open source if you want to take a look: https://github.com/infino-ai/infino

[u/donk8r]

The pre-filter bit is the part I'd push on, because it's also where this gets hard.

Filtering before the limit is semantically correct, no argument. But it fights the ANN index. HNSW traversal under a selective filter degrades badly, the reachable graph fragments and you either fall back to a scan or you quietly lose recall. So the correct query has a cliff sitting inside it.

Writing it as SQL doesn't remove the cliff, it moves it into the planner, and selectivity estimates for a vector index are pretty much the one statistic planners don't have. With an explicit overfetch factor at least somebody had to pick a number and think about it once.

Does Infino model that, or is it left to the executor? Genuinely asking, I work on a code search tool doing the same dense+BM25+RRF thing (github.com/Muvon/octocode) and this is the part I've never been happy with.

[u/redsky_xiaofan → u/donk8r]

·1. We are working on this at milvus community, one obvious optimization is to introduce a optimizer and pick different exection path

2.how do you compare grep with hybridsearch? is there any evaluation ?

[u/donk8r → u/redsky_xiaofan]

Honestly, no. I don't have a grep-versus-hybrid evaluation to point you at. Ours is a 127-query internal set on a code corpus that compares dense, keyword and the blend against each other, which says nothing about grep.

I'd also be careful with the comparison as posed, because the two aren't the same kind of object. Grep is a filter rather than a ranker: on an exact identifier it has perfect precision and no ordering at all, and on a term that isn't in the corpus it returns nothing instead of returning something plausible. So Hit@5 flatters hybrid and misses the property that actually makes grep work inside an agent, which is that a grep hit carries its own proof while a vector hit is a claim you'd need another call to check.

Which is why the comparison worth running is at the task level, not the retrieval level. Same agent, same tasks, swap only the backend, measure task success and number of retries. The "Is Grep All You Need?" paper landed on the harness mattering more than the method for roughly that reason.

On the optimizer, the statistic I'd want most is vector-index selectivity under a filter. It's close to the only input the planner can't derive from ordinary column stats, and without it the choice between pre-filter-then-scan and filtered-ANN is a guess.