4 ms·
Just out of curiosity, is it possible to efficiently get "For all rows in table A, give me the two nearest neighbors in table B" in SQL?
by chpatrick 3y ago
Just out of curiosity, is it possible to efficiently get "For all rows in table A, give me the two nearest neighbors in table B" in SQL?
- winslett 3y agoYou can do this with a lateral join. SELECT * FROM A, LATERAL (SELECT * FROM B ORDER BY a.embedding <-> b.embedding LIMIT 2) AS closest_in_b ORDER BY A.id; It’s going to have so-so performance. So, don’t hose your production server. Create a vector index on b.embedding. HNSW will be nice for this use case.
- chpatrick 3y agoThanks! I've implemented this manually with HNSW and I was wondering if pgvector would be an easy replacement.