SqliteVec delete using IN is slow and scans every row

Summary

Deleting vector rows with a single WHERE key IN (@p0, ...) statement makes SQLite scan every row of the vec0 virtual table, because the vec0 module does not expose IN constraint support, so batch deletes and upserts get slower as the table grows regardless of how many rows actually match.

Steps to reproduce

1. Create a vec0 virtual table with a text primary key and a 256-dimension vector:

CREATE VIRTUAL TABLE vec_chunks USING vec0(chunk_id TEXT PRIMARY KEY, embedding float[256])

2. Insert 100,000 rows.
3. Time these for 200 keys that do not exist in the table, each inside one transaction:

DELETE FROM vec_chunks WHERE chunk_id IN (@d0, @d1, /* 198 more */)

DELETE FROM vec_chunks WHERE chunk_id = @d0;  -- repeated per key, reusing one prepared statement

Expected behavior

Deleting by primary key, whether through an IN list or one key at a time, costs roughly the same and scales with the number of keys, not the table size.

Actual behavior

At 100,000 rows the IN-list delete took ~102 ms per 200-key batch. The same 200 keys deleted one at a time took 1.1 to 1.3 ms, about 100x faster. The gap is the same whether the keys exist in the table or not, so it grows with table size rather than match count. The behavior is identical with the newest sqlite-vec release (v0.1.10-alpha.4), so it is not fixed by upgrading the extension.

Analysis

The connector deletes the vector rows of a batch with a single IN-list statement, both in the upsert path and in the batch delete path:

https://github.com/CommunityToolkit/AI/blob/215a5bad3307aaafe45ba2ee9e40c071f5f7ce2c/MEVD/src/SqliteVec/SqliteCollection.cs#L620-L623

The IN list is generated by the where condition:

https://github.com/CommunityToolkit/AI/blob/215a5bad3307aaafe45ba2ee9e40c071f5f7ce2c/MEVD/src/SqliteVec/Conditions/SqliteWhereInCondition.cs#L9-L15

Cause

For a virtual table, SQLite can only use a WHERE constraint if the module advertises it through xBestIndex. vec0 advertises the primary key equality constraint, but it does not register IN constraint handling, so with an IN list SQLite falls back to a full scan of the virtual table, visiting every row through xNext and evaluating the IN predicate against each one. The data table is a regular SQLite table with an indexed primary key, so the IN form is fine there, only the vector table is affected.

The same pattern exists on the read path, where vector searches filter the virtual table with key IN (SELECT ...):

https://github.com/CommunityToolkit/AI/blob/215a5bad3307aaafe45ba2ee9e40c071f5f7ce2c/MEVD/src/SqliteVec/SqliteCommandBuilder.cs#L321-L321

That path has not been measured separately, but it plausibly triggers the same scan when combined with data-side filters.

Possible fix

In DoUpsertAsync, For the vector table, replace the IN-list delete with one fixed single-key DELETE (DELETE FROM "vec_t" WHERE "chunk_id" = @key) prepared once per operation.

Environment

• Package: CommunityToolkit.VectorData.SqliteVec 1.0.1-preview
• sqlite-vec 0.1.7-alpha.2.1 and v0.1.10-alpha.4 (both measured)
• Table: 100,000 rows, text primary key, 256-dimension vectors