This is kind of expected.The search is approximate and it might skip some rows — this is why recall isn't 100%.

Optimizer shouldn't use the index when retrieving the whole table, it's MDEV-8306

This looks really strange. It's one thing to skip rows when you still return the requested amount of data, just not necessarily the best matches – that's quite understandable, this is approximate search.
But here the "approximate" part is not just the search, it is the limit itself. I don't really see how it can be explained to a user that the table has 20 rows, and the query with LIMIT 30 only returns 19 rows.

It's MDEV-8306 — the index shouldn't have been used. If a table has thousand rows, then LIMIT 20 will return 20 rows. It might skip 15th best, and return 21st best instead (because the search is approximate), but it'll return 20 rows.

Here the table only has 20 rows, so if it'll skip one, you'll get less rows in the result set. Vector index is slower than filesort when you need all rows, so the optimizer shouldn't have used it unless the LIMIT is much smaller than the table size.

I'm not certain it is this simple.
While I couldn't reproduce the exact issue described initially, I came up with a different case derived from that one. I think it is related.

--source include/have_sequence.inc

create or replace table t (a int, v vector(1) not null, vector(v));

insert into t select seq, vec_fromtext(concat('[',seq,']')) from seq_1_to_200;

update t set v = vec_fromtext(concat('[33]')) where a <= 15;

select a, vec_totext(v) from t order by vec_distance_euclidean(v,vec_fromtext('[33]')) limit 25;

# Cleanup

drop table t;

MariaDB [test]> select a, vec_totext(v) from t order by vec_distance_euclidean(v,vec_fromtext('[33]')) limit 25;

+------+---------------+

| a    | vec_totext(v) |

+------+---------------+

|    4 | [33]          |

|   15 | [33]          |

|   10 | [33]          |

|   33 | [33]          |

|    8 | [33]          |

|    5 | [33]          |

|    1 | [33]          |

|    2 | [33]          |

|    3 | [33]          |

|    6 | [33]          |

|    9 | [33]          |

|   11 | [33]          |

|   14 | [33]          |

|   13 | [33]          |

|   12 | [33]          |

|    7 | [33]          |

+------+---------------+

16 rows in set (0.002 sec)

So, here the table has 200 rows total, limit is 25 (which should take MDEV-8306 off the table), but the query only returns 16 rows (and it will return 15 if I use [0] instead of [33], I took the latter to see if it will actually find anything that was there before UPDATE). Does it still fit your previous explanation?

To me it looks more like something special about having exact matches maybe, but of course I'm just guessing here.

Yes, absolutely, this is definitely expected, it's how the streaming mode works. It tends to skip vectors that have exactly the same distance to the target. Which is your case here. And it is basically never the case in real world data. The algorithm is tuned and optimized for real world use cases, and does not perform well on artificial ones.

I don't understand. It didn't skip vectors which have exactly the same distance to the target, it returned only vectors which have exactly the same distance to the target (zero), and skipped everything else.

Of course it can happen in real life that records have identical vectors – duplicate records, plagiary, different sources quoting the same text, and so on. I think it's more unrealistic to expect that in the large amount of data all vectors will always be unique.

Of course it can happen in real life that records have identical vectors – duplicate records, plagiary, different sources quoting the same text, and so on. I think it's more unrealistic to expect that in the large amount of data all vectors will always be unique.

Yes. And it does't always skip them. It can but doesn't have to. You're saying, basically, that identical vectors are caused by identical documents. In that case, potentially omitting some of them from the output (which then will be used as a context for RAG) is acceptable. Or may be not, benchmark is still running. So this behavior might still change.

In that case, potentially omitting some of them from the output (which then will be used as a context for RAG) is acceptable.

I quite agree, skipping identical documents and returning next alternatives instead makes a lot of sense (I'm not sure it is acceptable when it leads to returning less rows than requested, but let it be for now).
But as I said before, in the example above it doesn't skip identical rows, it does the opposite – it returns only identical rows and skips everything else.

The logical outcomes would be
a) straightforward: return 16 identical (closest) rows and then 9 other good enough results;
b) "skip vectors that have exactly the same distance to the target" logic: return 1 row out of 16 identical ones, plus 24 other good enough results;
c) something in between (it's approximate after all): return N rows out of 16 identical ones (skipping 16-N), plus 25-N other good enough results;
d) flawed "something in between" logic (affecting the size of the result set): find N identical rows and 25-N other good enough results, after that throw away N-1 duplicates, thus returning 25-N+1 rows, out of which one is a representative of the "identical" group, and 25-N are other good enough results.

Any of them could be explained, but I don't see how "return 16 identical rows and nothing else when 25 rows are requested" can be justified.