Just out of curiosity, is it possible to efficiently get "For all rows in table ...

winslett · on Aug 30, 2023

You 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 · on Aug 30, 2023

Thanks! I've implemented this manually with HNSW and I was wondering if pgvector would be an easy replacement.