mertalev
commented
2 weeks ago You can get it to use the index and still do pre-filtering with a query like this:
SELECT
segment.*,
embedding <=> :embedding AS distance
FROM
segment
INNER JOIN
asset ON segment.asset_id = asset.id
WHERE
distance < 0.99
AND segment.category = 'visual'
AND asset.collection_id = :collection_id
ORDER BY
embedding <=> :embedding
LIMIT 100This will still be slower than the post-filtering approach because it needs to evaluate the cosine distance for (potentially many) assets in the WHERE condition, but the recall should be correct.
I used embedding <=> :embedding instead of distance for ordering because I remember this being necessary for the index to get used. If the query plan is the same with distance, feel free to use that instead.
VoVAllen
rsomani95
Originally started this discussion in Discord (I think with @VoVAllen) but posting here to keep the discussion more focused: https://discord.com/channels/974584200327991326/1220203706553860227/1245115744224542770
In spirit, what I'm trying to do is a standard vector query with an additional condition where the results are filtered by the cosine distance to the query vector.
My dataset has 3 tables `collection`, `asset`, and `segment`, setup like so: ```sql CREATE TABLE collection ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), name TEXT NOT NULL ); CREATE TABLE asset ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), collection_id UUID REFERENCES collection(id) ON DELETE CASCADE, title TEXT NOT NULL ); CREATE TABLE segment ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), asset_id UUID REFERENCES asset(id) ON DELETE CASCADE, embedding vector(640) NOT NULL, -- 640 dimensions category TEXT NOT NULL ); ``` I'm using the `0.3.0-alpha2` docker container for all my tests. I then build some standard indexes on theese tables, followed by the HNSW index. Here is a fully reproducible script to reproduce the dataset: https://gist.github.com/rsomani95/56194e33364208019a09846cb9bacabb#file-build_dataset_with_indexes-sql-L1-L113Dataset Context
Queries
Before running any queries, I've set
vectors.hnsw_ef_search=100globally.1. Correct results, but no index usage
This query does a brute force search. However, the results are useful because they serve as a ground truth for checking future results for other queries. In my case, I got 96 results (but this will vary because the embeddings are randomised)
2. Adding
LIMITto use index, but incorrect resultsAs suggested in the thread here, I added a
LIMITunder theORDER BYclause to activate the index.Here's the query with the
LIMITadded:This does activate the index, but now only returns 1 row as opposed to 96 earlier. I suspect this is because the query is formulated incorrectly; in its current formulation, we must be doing post filtering (I am assuming that the vbase implementation is correct and not the root cause of the issue here)
I tried another approach, adding
LIMITto the bottom of the query:and while this uses the index, the query runs a lot slower, and still produces incorrect results.
I'd love some guidance on how to formulate the SQL above correctly to use the index and get correct results.