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 to Vector Type(<N>, [F32]), where <N> is the number of dimensions. Currently, the only supported element type is F32.

Similarity Metrics

Two similarity functions are available for creating and searching vector indexes in SingleStore: DOT_PRODUCT and EUCLICEAN_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 of DOT_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 of EUCLIDEAN_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 the CREATE TABLE command:

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

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

ALTER TABLE <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 the ALTER TABLE command:

ALTER TABLE <table_name> DROP INDEX <index_name>;

OR

DROP INDEX <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>
ORDER BY distance [USE {INDEX|KEY} ([<index_name>])]
[SEARCH_OPTIONS [=] '<json>'] [{DESC|ASC}]
LIMIT k;

Infix syntax is available.

• <*> for DOT_PRODUCT

• <-> for EUCLICEAN_DISTNCE

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

Important:

• For DOT_PRODUCT (<*>), higher values indicate higher vector similarity; use descending (DESC) order in the ORDER BY clause.

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

• Search queries must use the same distance metric (DOT_PRODUCT or EUCLIDEAN_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 with INDEX_OPTIONS to specify the vector index algorithm and its parameters to use. Search options can also be used as a JSON config string to SEARCH_OPTIONS.

Below are the generic INDEX_OPTIONS and SEARCH_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. Either EUCLIDEAN_DISTANCE or DOT_PRODUCT. The default is DOT_PRODUCT.

• Search parameters

  • k: number of rows outputted by vector index scan. k must be >= limit, where limit is from ORDER 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. See Index 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 in INDEX_OPTIONS. When search parameters are specified in INDEX_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 to Managing Columnstore Segments for more information about segments.

OPTIMIZE TABLE <table_name> VECTOR_INDEX <index_name>;

OPTIMIZE TABLE <table_name> FULL;

Tracking Vector Index Memory Use

Use the VECTOR_INDEX information schema view and the SHOW STATUS EXTENDED command to track vector index memory use.

The VECTOR_INDEX information schema view shows the amount of memory and disk used by each vector index. The memory usage information includes memory used by per-segment vector indexes that have been loaded into the vector index cache by vector search or Vector Range Search.

Example output from the VECTOR_INDEX information schema view is shown below.

SELECT * FROM INFORMATION_SCHEMA.VECTOR_INDEX;

+---------------+------------+---------+------------+-------------------+------------------------+-----------------+----------------------+
| DATABASE_NAME | TABLE_NAME | NODE_ID | INDEX_TYPE | MEMORY_SIZE_BYTES | INDEX_CHUNKS_IN_MEMORY | DISK_SIZE_BYTES | INDEX_CHUNKS_ON_DISK |
+---------------+------------+---------+------------+-------------------+------------------------+-----------------+----------------------+
| test          | ann_test   |       2 | HnswFlat   |         138460664 |                      1 |       276962270 |                    3 |
| test          | ann_test   |       2 | IvfPqfs    |          10488248 |                      3 |         9557524 |                    3 |
+---------------+------------+---------+------------+-------------------+------------------------+-----------------+----------------------+

In this output, there are two indexes on the table ann_test. A HNSW_FLAT index and an IVF_PQFS index. The HNSW_FLAT index is partially loaded in memory, with 1 of 3 segments in memory, and occupies 138460664 bytes of memory. The IVF_PQFS index is fully loaded in memory, 3 of 3 segments are loaded in memory and it occupies 10488248 bytes of memory.

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

SHOW STATUS EXTENDED LIKE 'alloc_vector_index'

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

The results of SHOW 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.

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

Example 1

Create a table with a VECTOR attribute and then create an IVF_PQFS index on the vector column with index building parameter nlist set to 1024 and search parameter nprobe set to 20.

CREATE TABLE vect (k int, v VECTOR(2) NOT NULL);

INSERT INTO vect VALUES
      (1, '[-10, 1]'),
      (2, '[10, 2]'),
      (3, '[20, 3]');

ALTER TABLE vect ADD VECTOR INDEX (v) INDEX_OPTIONS
 '{"index_type":"IVF_PQFS", "nlist":1024, "nprobe":20}';

Note that the search parameter nprobe is used in this index creation command. All search parameters can be specified in INDEX_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.

OPTIMIZE TABLE 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_vec AS score
FROM vect
ORDER BY score DESC LIMIT 1;

+------+--------+------------------+
| 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. Increasing k 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
ORDER BY score SEARCH_OPTIONS '{"k" : 30 }' DESC
LIMIT 3;

+------+---------+-------------------------------+
| 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:

CREATE DATABASE ann_vectors; 

USE ann_vectors;

Set the SQL Mode:

SET sql_mode = pipes_as_concat;

Create a table:

CREATE TABLE ann_test(id INT, v VECTOR(2) NOT NULL, SHARD KEY(id), KEY (id));

Use the following SQL script to generate data:

DELIMITER //
DO
DECLARE s INT = 1*1024*1024;
DECLARE c BIGINT;
BEGIN
INSERT ann_test VALUES(1,"[0,0]");
LOOP
    INSERT INTO ann_test
    SELECT id+(SELECT MAX(id) FROM ann_test),
               "[" || s*rand() || "," || s*rand() || "]"
    FROM ann_test;
    SELECT COUNT(*) INTO c FROM ann_test;
    IF c >= s then
      EXIT;
    END IF;
END LOOP;
END
//
DELIMITER ;

Add a vector index to the table:

ALTER TABLE ann_test ADD VECTOR INDEX(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 v FROM ann_test WHERE id = 1000);

Find top five closest matches to the query vector:

SELECT id, v, v <-> @qv AS score
FROM ann_test
ORDER BY score
LIMIT 5;

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, use EXPLAIN. The contents of the EXPLAIN plan for the previous example is shown below. The ColumnStoreFilter operator does the indexed ANN search.

EXPLAIN SELECT id, v, v <-> @qv AS score
FROM ann_test
ORDER BY score
LIMIT 5;

+----------------------------------------------------------------------------------------------+
| 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 the EXPLAIN output above shows INTERNAL_VECTOR_SEARCH(...) which indicates that the vector index is being used. If INTERNAL_VECTOR_SEARCH is not in the ColumnStoreFilter line, the vector index is not being used.

Index Hints Example

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

Use the vect table from Example 1 above for this query and the following one.

SELECT k, v <*> ('[9, 0]' :> VECTOR(2)) AS score
FROM vect
ORDER BY score USE INDEX (v) DESC
LIMIT 2;

The query below, which contains a USE 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
ORDER BY score USE INDEX () DESC
LIMIT 2;

The query below specifies that the index v should be used and specifies a search option for the parameter k. The USE_INDEX clause and the SEARCH_OPTIONS clause must appear immediately after the ORDER BY clause and USE_INDEX must appear before SEARCH_OPTIONS. 

SELECT k, v <*> ('[9, 0]' :> VECTOR(2)) AS score
FROM vect
ORDER BY score
USE INDEX (v) SEARCH_OPTIONS '{"k":30}' DESC
LIMIT 2;

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