Combining face search with context search

[DawidPietrykowski]

The new update (v1.95) brought amazing improvements to the search feature, however currently it's not possible to input context and select faces at the same time.

Question about this has already been asked in the update discussion and answered with "technical limitation" (#7252 (reply in thread)).

I personally believe that this could be extremely useful to be able to select both the face and input the context so I dug into the code and I believe the technical limitation refers to the fact that face search is using embeddings to order the results of the query and the same goes for smart search (with context). Ordering by both is indeed a technical limitation but one that could be solved in a few ways.

Authors of pgvector actually mention hybrid search in the README and provide a recommendation to use RRF (Reciprocal Rank Fusion) as a solution to this problem together with an example written in python using a raw SQL query (https://github.com/pgvector/pgvector-python/blob/master/examples/hybrid_search_rrf.py).

Looking at the query it looks like it would be impossible to implement exactly using the current ORM (typeorm), but I think a solution would be to run 2 queries and combine the results using this algorithm in the code but that poses some issues in relation to performance and pagination.

One solution would bo te get many results (more than one page) for both queries and then paginate in typescript, but that would be quite slow, another one would be to just combine pairs of pages and sort only those results, which would probably be less accurate (especially for small pages), but would be performant and should work.

I considered working on this feature but first I would love to get some feedback or a green light to work on this from more experienced maintainers of this project.

[bo0tzz]

cc @mertalev

Note that while we do to some degree support pgvector, we're primarily focused on pgvecto.rs for the search features.

[DawidPietrykowski]

Thanks for the clarification! The provided query and algorithm are universal so I don't think that's an issue, I also searched the pgvecto.rs docs but I haven't found a builtin feature to combine the results, only a Feature Request.

[mertalev]

There's a general issue with joining on one-to-many relations like with the asset_faces table due to this. Multiple rows can be returned for the same asset, so it throws a huge wrench in the pagination.

TypeORM's take on this with take/skip also isn't compatible with setting a custom sort order, whereas we need to sort by vector similarity. It would have to be this ugly triple-nested query where we sort by vector similarity, then select for distinct assets on that, and in the outer-most query sort again by vector similarity. And even then it would duplicate the asset if the first row of a page has the same asset as the last row of the previous page.

[VoVAllen]

I just saw this issue. We've tested that pgvecto.rs can support using group by to aggregate multiple assets for deduplication. Here is an example at the LLM scenario. Note that only certain way of write the query (using CTE and MIN aggregation below) will use the vector index. Otherwise it might be a full table scan.

DROP TABLE IF EXISTS document_embeddings;

CREATE TABLE document_embeddings (
	document_id INT,             		-- Unique ID for each document
	chunk_id INT,                		-- Unique ID for each chunk within a document
	embedding VECTOR(3) NOT NULL  -- Embedding vector for the chunk
);

-- CREATE INDEX ON document_embeddings USING btree (document_id);
-- CREATE INDEX ON document_embeddings USING btree (chunk_id);
CREATE INDEX ON document_embeddings USING vectors (embedding l2_ops);

INSERT INTO document_embeddings (document_id, chunk_id, embedding)
	SELECT 
		docs, chunk, ARRAY[random(), random(), random()]::real[] 
	FROM
		generate_series(1, 1000) AS docs,
		generate_series(1, 10) AS chunk;

WITH scored_documents AS (
    SELECT 
        document_id,
        embedding <-> '[0.5, 0.5, 0.5]' AS distance
    FROM 
        document_embeddings
	ORDER BY
		distance
)
SELECT 
    document_id, 
    MIN(distance) AS min_distance
FROM 
    scored_documents
GROUP BY 
    document_id
ORDER BY 
    min_distance
LIMIT 10;

--  Limit  (cost=6.02..6.04 rows=10 width=8)
--    ->  Sort  (cost=6.02..6.04 rows=10 width=8)
--          Sort Key: (min(((document_embeddings.embedding <-> '[0.5, 0.5, 0.5]'::vector))))
--          ->  HashAggregate  (cost=5.75..5.85 rows=10 width=8)
--                Group Key: document_embeddings.document_id
--                ->  Index Scan using document_embeddings_embedding_idx on document_embeddings  (cost=0.00..5.25 rows=100 width=8)
--                      Order By: (embedding <-> '[0.5, 0.5, 0.5]'::vector)

SELECT 
    document_id, 
    MIN(embedding <-> '[0.5, 0.5, 0.5]') AS min_distance
FROM 
    document_embeddings
GROUP BY 
    document_id
ORDER BY 
    min_distance
LIMIT 10;

--  Limit  (cost=3.02..3.04 rows=10 width=8)
--    ->  Sort  (cost=3.02..3.04 rows=10 width=8)
--          Sort Key: (min((embedding <-> '[0.5, 0.5, 0.5]'::vector)))
--          ->  HashAggregate  (cost=2.75..2.85 rows=10 width=8)
--                Group Key: document_id
--                ->  Seq Scan on document_embeddings  (cost=0.00..2.00 rows=100 width=25)

[VoVAllen]

Another way to do this is using cursor inside postgres. You can fetch the results in an iterator way and aggregate/filter it manually, until got enough results

[mertalev]

Thanks for the advice! We solved it by creating a cte that groups the faces by asset, then join on that cte during search. But I haven't actually tested that it uses the index or if doing the search in the cte instead would be faster.

Edit: Can confirm it does use the index.

[mertalev]

Another way to do this is using cursor inside postgres. You can fetch the results in an iterator way and aggregate/filter it manually, until got enough results

I'd love to use cursor pagination, but the ORM we use doesn't support it :( We're planning on moving to Prisma soon and that ORM supports cursor pagination, but they don't let you use their cursor implementation for raw queries and require raw queries for extensions like pgvecto.rs or pgvector.

[aviv926]

Added with #7521