Vector Indexing

Syntax

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

• Vector indices can only be built on columnstore tables.

• Index must be built on a single column that stores the vector.

• Column type restricted to Vector Type(dimensions[, F32]) NOT NULL where dimensions is the number of dimensions. Currently the only supported element type is F32.

If you have a text column and an associated varbinary column to hold the vector embedding for the text, and you do not immediately have available the vector for the text, consider making the vector all zeros or another dummy value that would never show up in top-K searches.

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

For dot product <*>, use descending (DESC) order in the ORDER BY clause. For euclidean distance <->, use ascending (ASC) order, which is the default.

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.

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.

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

SET @query_vec = ('[9,0]'):> VECTOR(2);

SELECT k, v, v <*> @query_vec AS score
FROM vect
ORDER BY score DESC LIMIT 1;

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

Optionally optimize the table to enhance performance .

OPTIMIZE TABLE vect FULL;

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.

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

New Infix Operators for euclidean_distance and dot_product

The following infix operators are available for calculating vector similarity:

• <*> (DOT_PRODUCT)

• <-> (EUCLIDEAN_DISTANCE)

They are equivalent to the existing functions specified.

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.

Understanding Behavior for DELETEs and UPDATEs

Rows that have been deleted and old versions of rows that have been updated will be filtered out from results for indexed ANN search. This may cause fewer than N rows to be returned for a top-N search if a significant fraction of the rows have been deleted or updated.

You can work around this to ensure you actually get N rows of output by using a larger value of the search option k – the number of rows output by a vector index scan. For example, if you have a filter that eliminates half the rows, use a k value of 2 or more. Or, use a larger value for N in a LIMIT N query – you may not get exactly N but you can generate more output, which may be sufficient for many applications like RAG where the exact amount of rows retrieved is not critical.

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