dolthub / dolthub/dolt

Naming vec_distance column and then referring to it in order by does not use the index

Open
#8,829 0 comments 0 reactions 1 assignee Claimed by @nicktobey View on GitHub
analyzer performance vectors
Dominant language
Go
Stars
24.4k
Forks
873
Avg merge
1d 5h
Merged PRs (30d)
108

Description

Repro:

1. Clone: https://www.dolthub.com/repositories/timsehn/vector_example
2. Checkout `all-MiniLM-L6-v2` branch
3. This query uses the index:

```
$ dolt sql -q "set @query = (select embeddings from yelp where id = 1); explain plan select id, review, vec_distance(embeddings, @query) as distance from yelp order by vec_distance(embeddings, @query) limit 3;"
+-------------------------------------------------------------------------------------------------------+
| plan |
+-------------------------------------------------------------------------------------------------------+
| Limit(3) |
| └─ Project |
| ├─ columns: [yelp.id, yelp.review, VEC_DISTANCE_L2_SQUARED(yelp.embeddings, @query) as distance] |
| └─ IndexedTableAccess(yelp) |
| ├─ index: [yelp.embeddings] |
| ├─ order: VEC_DISTANCE_L2_SQUARED(yelp.embeddings, @query) LIMIT 3 (bigint) |
| └─ columns: [review id embeddings] |
+-------------------------------------------------------------------------------------------------------+
```

This query does not:

```
$ dolt sql -q "set @query = (select embeddings from yelp where id = 1); explain plan select id, review, vec_distance(embeddings, @query) as distance from yelp order by distance limit 3;"
+--------------------------------------------------------------------------------------------------------------------------------------------+
| plan |
+--------------------------------------------------------------------------------------------------------------------------------------------+
| Limit(3) |
| └─ Project |
| ├─ columns: [yelp.id, yelp.review, VEC_DISTANCE_L2_SQUARED(yelp.embeddings, @query) as distance] |
| └─ Sort(VEC_DISTANCE_L2_SQUARED(yelp.embeddings, @query) as distance ASC) |
| └─ Project |
| ├─ columns: [yelp.label, yelp.review, yelp.id, yelp.embeddings, VEC_DISTANCE_L2_SQUARED(yelp.embeddings, @query) as distance] |
| └─ Table |
| ├─ name: yelp |
| └─ columns: [label review id embeddings] |
+--------------------------------------------------------------------------------------------------------------------------------------------+

```

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.