drizzle-team / drizzle-team/drizzle-orm-docs
Vector similarity search Guide isn't using the vector index
- Dominant language
- MDX
- Stars
- 241
- Forks
- 401
- Avg merge
- 5h 44m
- Merged PRs (30d)
- 1
Description
In the guide for [Drizzle | Vector similarity search with pgvector extension](https://orm.drizzle.team/docs/guides/vector-similarity-search) postgres isn't using the vector index, because it is looking for the 1 - cosineDistance, while the index is built for the cosine distance only.
Text from the same issue I opened on https://github.com/vercel/examples/issues/984:
---
I built a project on top of the postgres-pgvector template, but the database queries were really slow. I had 2.8 million rows in the database with 512 dimensional embedding vectors. Queries took like 12 seconds. (After using an ivfflat index it took 100ms)
Reproduction repo is here: [https://github.com/martinloretzzz/nextjs-drizzle-pgvector](https://github.com/martinloretzzz/nextjs-drizzle-pgvector)
After investigating the issue, it turns out the queries don't use the vector index, because we're looking for the 1 - cosineDistance, while the index is built for the cosine distance only.
The fix is quite simple, need to look for the smallest cosineDistance, instead of the largest 1-cosineDistance:
```typescript
const similarity = sql`${cosineDistance(pokemons.embedding, vectorQuery)}`
const pokemon = await db
.select({ id: pokemons.id, name: pokemons.name, similarity })
.from(pokemons)
.where(lt(similarity, 0.5))
.orderBy((t) => asc(t.similarity))
.limit(8)
```
Note:
Use postgres `EXPLAIN` to see the query plan.
For this small dataset of 150 pokemons, for both queries the index aren't used, to test if index would be used on big datasets, so set `SET SESSION enable_seqscan=false;`
Exported sql queries: [before](https://github.com/martinloretzzz/nextjs-drizzle-pgvector/blob/main/sql-dragon-before.txt), [after](https://github.com/martinloretzzz/nextjs-drizzle-pgvector/blob/main/sql-dragon-after.txt), use with `SET SESSION enable_seqscan=false; EXPLAIN {query}` is psql/pgadmin
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.