SingleStore: create a vector index with matching metric type and order

TL;DR

ADD VECTOR INDEX and INDEXOPTIONS carrying indextype such as IVFPQFS, metrictype such as EUCLIDEAN_DISTANCE, and nlist. Create the index with ALTER TABLE ...

Fix

1. ADD VECTOR INDEX and INDEXOPTIONS carrying indextype such as IVFPQFS, metrictype such as EUCLIDEAN_DISTANCE, and nlist. Expected: You get the expected result; the problem is gone.

When to use

• You are setting up or using this SingleStore feature.
• The symptom matches: create a vector index with matching metric type and order.

When NOT to use

• Unrelated SingleStore issues (different feature, different failure).
• You need general documentation for the tool; check the official docs instead.

Compatibility

Reported against SingleStore. Source: https://docs.singlestore.com/db/v8.9/reference/sql-reference/vector-functions/vector-indexing/.

Variant phrasings

create a vector index with matching metric type and order

Why it happens

An ANN index that does not match the query's metric and sort direction is never used. The build parameters must agree with the queries.

Edge cases

• If your error message differs even slightly, this is probably a different issue; search the exact text.
• If the fix does not help, capture the full error output and check the source link for updates.

Source

https://docs.singlestore.com/db/v8.9/reference/sql-reference/vector-functions/vector-indexing/

Source: An ANN index that does not match the query's metric and sort direction is never used. The build parameters must agree with the queries.

Verified resolution: [docs.singlestore.com]: create the index with ALTER TABLE ... ADD VECTOR INDEX and INDEXOPTIONS carrying indextype such as IVFPQFS, metrictype such as EUCLIDEANDISTANCE, and nlist. Two conditions for index use: the metric in the query (dot product vs euclidean distance) matches the metric used to create the index, and the ORDER BY direction (DESC vs ASC) matches the metric. Check the plan with EXPLAIN: the ColumnStoreFilter line shows INTERNALVECTOR_SEARCH when the index is used, and its absence means a full scan.

Source URL: https://docs.singlestore.com/db/v8.9/reference/sql-reference/vector-functions/vector-indexing/