Syntax

The syntax for a vector index has to adhere to the following rules:

• Vector indexes can only be built on columnstore tables.

• A vector index must be built on a single column.

• Column type is restricted toVector Type(<N>, F32]), where<N> is the number of dimensions. Currently, the only supported element type isF32.

Similarity Metrics

Two similarity functions are available for creating and searching vector indexes in SingleStore:DOT_PRODUCT andEUCLICEAN_DISTANCE. 

DOT_PRODUCT: Calculates the cosine similarity metric when used with vectors normalized to length 1. Cosine similarity measures the similarity of vectors without taking into account the length of the vectors. The results ofDOT_PRODUCT on vectors normalized to length 1 range from -1 to 1; values closer to 1 indicate that the vectors are similar, whearas values closer to -1 indicate that the vectors are less similar.

EUCLIDEAN_DISTANCE: Calculates the Euclidean distance between two vectors. Euclidean distance measures the distance between vectors taking into account the length of the vectors. The results ofEUCLIDEAN_DISTANCE range from 0 to infinity; values closer to 0 indicate that the vectors are similar, values closer to infinity indicate that vectors are less similar.

Creating a Vector Index

A vector index can be created using theCREATE TABLE command:

CREATETABLE<table_name>(<column_definitions>, VECTOR {INDEX|KEY }[<index_name>](<column>)[INDEX_OPTIONS'<json>']);

Alternatively, a vector index may be added using theALTER TABLE command:

ALTERTABLE<table_name>ADD VECTOR {INDEX|KEY }[<index_name>](<column>)[INDEX_OPTIONS'<json>'];

Multiple vector indexes can be created on the same table or even on the same column.

A vector index may be dropped with theALTER TABLE command:

ALTERTABLE<table_name>DROPINDEX<index_name>;
OR
DROPINDEX<index_name>ON<table_name>;

Searching a Vector Index

A vector search query to find k ANNs can be done with standard SQL:

SELECT<columns>,
   DOT_PRODUCT| EUCLICEAN_DISTANCE(<table_name>.v,@query_vector)AS distance
FROM<table_name>
WHERE<pre-filters>
ORDERBY distance[USE {INDEX|KEY}([<index_name>])]
[SEARCH_OPTIONS[=]'<json>'][{DESC|ASC}]
LIMIT k;

Infix syntax is available.

• <*> forDOT_PRODUCT

• <-> forEUCLICEAN_DISTNCE

SELECT<columns>,
<table_name>.v {<*>|<->}@query_vectorAS distance
FROM<table_name>
WHERE<pre-filters>
ORDERBY distance[USE {INDEX|KEY}([<index_name>])]
[SEARCH_OPTIONS[=]'<json>'][{DESC|ASC}]
LIMIT k;

Important:

• ForDOT_PRODUCT (<*>), higher values indicate higher vector similarity; use descending (DESC) order in theORDER BY clause.

• ForEUCLIDEAN_DISTANCE(<->), lower values indicate smaller distance between values; use ascending (ASC) theORDER BY clause, which is the default.

• Search queries must use the same distance metric (DOT_PRODUCT orEUCLIDEAN_DISTANCE) as used in the vector index in order to utilize that vector index.

• The query vector (@query_vector in the syntax above) must be a runtime constant.

Index Options

A JSON config string can be used withINDEX_OPTIONS to specify the vector index algorithm and its parameters to use. Search options can also be used as a JSON config string toSEARCH_OPTIONS.

Below are the genericINDEX_OPTIONS andSEARCH_OPTIONS. These generic index building parameters (INDEX_OPTIONS) and index searching parameters (SEARCH_OPTIONS) can be used with all index types.

• Index building parameters

  • index_type: index type to use. Must be one of AUTO, FLAT, IVF_FLAT, IVF_PQ, IVF_PQFS, HNSW_FLAT, HNSW_PQ. The default is AUTO. SingleStore recommends IVF_PQFS and HNSW_FLAT.

  • metric_type: distance metric. EitherEUCLIDEAN_DISTANCE orDOT_PRODUCT. The default isDOT_PRODUCT.

• Search parameters

  • k: number of rows outputted by vector index scan. k must be >= limit, where limit is fromORDER BY … LIMIT clause. The default is the limit.

Index Hints

When there are multiple vector indices available or when you want to disable any available vector index, the index hint[USE {INDEX|KEY} ([<index_name>])] can be used to specify a specific index to use or to disable the use of an index.

Specify the exact index you want to use as<index_name> or omit the<index_name> part, i.e., USE {INDEX|KEY} () to disable ANN search. SeeIndex Hints Example for an example.

Index Types and Parameters

The following index types and parameters are supported.

All index search parameters can also be specified as index building parameters inINDEX_OPTIONS. When search parameters are specified inINDEX_OPTIONS, that value will be used as the default value for searches using that index so you do not need to specify those values each time you use that index.

Vector Index Architecture

When a vector index is built, a table-level index is created for the vector column. This table-level index is composed of smaller pieces; one per-segment index per columnstore segment. The per-segment indexes are built in memory and stored on disk and remain on disk until they are used by a query.

Refer toManaging Columnstore Segments for more information about segments.

OPTIMIZETABLE<table_name> VECTOR_INDEX<index_name>;

OPTIMIZETABLE<table_name>FULL;

Tracking Vector Index Memory Use

Use theSHOW STATUS EXTENDED command to track vector index memory use.

The total memory used by all vector indexes can be viewed by using theSHOW STATUS EXTENDED command as shown below.

SHOWSTATUSEXTENDEDLIKE'alloc_vector_index'

+--------------------+-------------------+| Variable_name      | Value             |+--------------------+-------------------+| alloc_vector_index | 5.942 (+5.942) MB |+--------------------+-------------------+

The results ofSHOW STATUS EXTENDED LIKE 'alloc_vector_index' include the memory used by all per-segment vector indexes that are being used by vector search or vector range search.Only per-segment vector index segments that currently reside in memory are included; the results do not include indexes that are on disk. A breakdown by individual index is current unavailable.

The commandSHOW STATUS EXTENDED LIKE 'alloc_vector_index' does not output any rows if the memory used by vector indexes is zero.

Refer toTuning Vector Indexes and Queries for an example of identifying the memory used by a specific vector index.

Example 1

Create a table with aVECTOR attribute and then create anIVF_PQFS index on the vector column with index building parameternlist set to 1024 and search parameternprobe set to 20.

CREATETABLE vect(kint, v VECTOR(2)NOTNULL);
INSERTINTO vectVALUES
(1,'[-10, 1]'),
(2,'[10, 2]'),
(3,'[20, 3]');
ALTERTABLE vectADD VECTORINDEX(v) INDEX_OPTIONS
'{"index_type":"IVF_PQFS", "nlist":1024, "nprobe":20}';

Note that the search parameternprobe is used in this index creation command. All search parameters can be specified inINDEX_OPTIONS during index creation. The value provided for the search parameter (nprobe in this example) in index creation will be used as the default value for that search parameter for that index.

Optimize the table to ensure all results are indexed.

OPTIMIZETABLE vect FLUSH;

Searching the nearest neighbor can be done with the commands below.

SET vector_type_project_format= JSON;  /* to make vector output readable */
SET@query_vec=('[9,0]'):> VECTOR(2);
/* run the SET commands before this query */
SELECT k, v, v<*>@query_vecAS score
FROM vect
ORDERBY scoreDESCLIMIT1;

+------+--------+------------------+| k    | v      | score            |+------+--------+------------------+|    3 | [20,3] |              180 |+------+--------+------------------+

You can increase  k, as shown in the example below, to increase the number of rows output by the vector index scan. Increasingk will increase the search space and likely increase the recall, but will also increase the execution time of the query.

SET vector_type_project_format= JSON;  /* to make vector output readable */
SELECT k, v, v<*>'[9, 0]'AS score
FROM vect
ORDERBY score SEARCH_OPTIONS'{"k" : 30 }'DESC
LIMIT3;

+------+---------+-------------------------------+| k    | v       | score                         |+------+---------+-------------------------------+|    3 | [20,3]  |                           180 ||    2 | [10,2]  |                            90 ||    1 | [-10,1] |                           -90 |+------+---------+-------------------------------+

Example 2

Below is a small, self-contained example. Real vectors for AI use cases such as retrieval-augmented generation (RAG) with LLMs would normally have a much higher dimensionality (e.g., 64 to a few thousand). Two-dimensional vectors are used to keep it simple.

Create and use a new database:

CREATEDATABASE ann_vectors; 
USE ann_vectors;

Set the SQL Mode:

SET sql_mode= pipes_as_concat;

Create a table:

CREATETABLE ann_test(idINT, v VECTOR(2)NOTNULL, SHARDKEY(id),KEY(id));

Use the following SQL script to generate data:

DELIMITER//
DO
DECLARE sINT=1*1024*1024;
DECLARE cBIGINT;
BEGIN
INSERT ann_testVALUES(1,"[0,0]");
LOOP
INSERTINTO ann_test
SELECT id+(SELECTMAX(id)FROM ann_test),
"["|| s*rand()||","|| s*rand()||"]"
FROM ann_test;
SELECTCOUNT(*)INTO cFROM ann_test;
IF c>= sthen
EXIT;
ENDIF;
ENDLOOP;
END
//
DELIMITER;

Add a vector index to the table:

ALTERTABLE ann_testADD VECTORINDEX(v)
   INDEX_OPTIONS'{"index_type":"IVF_PQFS", "nlist":1024,
   "metric_type":"EUCLIDEAN_DISTANCE"}';

Find a query vector as the vector for row 1000:

SET@qv=(SELECT vFROM ann_testWHERE id=1000);

Find top five closest matches to the query vector:

SELECT id, v, v<->@qvAS score
FROM ann_test
ORDERBY score
LIMIT5;

Verify a Vector Index is Being Used

It is important to verify that your query is using a vector index. A vector index can be used in the following conditions:

• The metric in the query (dot product vs. euclidean distance) matches the metric used to create the index.

• The order in the ORDER BY clause (DESC vs.ASC) in the search query matches the metric.

To get a detailed view of query plan for vector similarity queries, useEXPLAIN. The contents of theEXPLAIN plan for the previous example is shown below. The ColumnStoreFilter operator does the indexed ANN search.

EXPLAINSELECT id, v, v<->@qvAS score
FROM ann_test
ORDERBY score
LIMIT5;

+----------------------------------------------------------------------------------------------+| EXPLAIN                                                                                      |+----------------------------------------------------------------------------------------------+| Project [remote_0.id, remote_0.v, remote_0.score]                                            |              | TopSort limit:5 [remote_0.score]                                                             || Gather partitions:all alias:remote_0 parallelism_level:segment                               || Project [ann_test.id, ann_test.v, EUCLIDEAN_DISTANCE(ann_test.v,@qv) AS score]               || TopSort limit:5 [EUCLIDEAN_DISTANCE(ann_test.v,@qv)]                                         || ColumnStoreFilter [INTERNAL_VECTOR_SEARCH(0, @qv, 5, '') index]                              || ColumnStoreScan ann_vectors.ann_test, SORT KEY __UNORDERED () table_type:sharded_columnstore |+----------------------------------------------------------------------------------------------+

The ColumnStoreFilter line of theEXPLAIN output above showsINTERNAL_VECTOR_SEARCH(...) which indicates that the vector index is being used. IfINTERNAL_VECTOR_SEARCH is not in theColumnStoreFilter line, the vector index is not being used.

Index Hints Example

The query below will specify the use of the index with key namev. If that index is not available, the query will return an error. SeeSHOW INDEXES for information on how to list the indexes for a table.

Use thevect table fromExample 1 above for this query and the following one.

SELECT k, v<*>('[9, 0]' :> VECTOR(2))AS score
FROM vect
ORDERBY scoreUSEINDEX(v)DESC
LIMIT2;

The query below, which contains aUSE INDEX clause without an index name, will disable the use of the vector (ANN) indexes.

SELECT k, v<*>('[9, 0]' :> VECTOR(2))AS score
FROM vect
ORDERBY scoreUSEINDEX()DESC
LIMIT2;

The query below specifies that the indexv should be used and specifies a search option for the parameterk. TheUSE_INDEX clause and the SEARCH_OPTIONS clause must appear immediately after theORDER BY clause and USE_INDEX must appear before SEARCH_OPTIONS. 

SELECT k, v<*>('[9, 0]' :> VECTOR(2))AS score
FROM vect
ORDERBY score
USEINDEX(v) SEARCH_OPTIONS'{"k":30}'DESC
LIMIT2;

As described above,EXPLAIN can be used to verify if the query does or does not use an index.

When to use an ANN Index to Support Vector Search

In general, you should use ANN search for large sets of vectors when you do not have selective filters on other columns in the query. In the example above, if there are billions of comments, having an index to find the similarity scores for the query vector is very useful and will provide a significant performance improvement.

On the other hand, if you have a smaller set of vectors, say less than a few million, and selective filters in your queries on other non-vector columns, you're often better off not using ANN indexes. This is true for both vector search and KNN search. You can instead rely on SingleStore's other query processing capabilities and you will get good performance. This will also be less complex because you won't need to think about what index to create, or the impact the index may have on recall, load time and disk and memory usage.

Related Topics

• Vector Type

• Working with Vector Data

• Tuning Vector Indexes and Queries

• DOT_PRODUCT

• EUCLIDEAN_DISTANCE