HNSW index

This topic describes the syntax and examples of creating, searching, and dropping HNSW indexes in OceanBase Database.

Syntax and description of the index

Create

The HNSW series index includes three types of indexes: HNSW, HNSW_SQ, and HNSW_BQ. You can create an HNSW series index in two ways: create it when you create a table or create it later. When you create an HNSW series index, note the following:

• You must specify the VECTOR keyword.
• The parameters and descriptions for creating an HNSW series index later are the same as those for creating it when you create a table.
• If the amount of data is large, we recommend that you write the data and then create the index to achieve the best search performance.
• We recommend that you create an HNSW_SQ index after you write the data and rebuild the index after you write a large amount of incremental data. For more information about how to create an HNSW series index, see the following examples.
• When you create an HNSW index, the index name must be no longer than 25 characters. Otherwise, an error may occur because the index auxiliary table name exceeds the index_name limit. In later versions, the index name can be longer.
• We recommend that you create an HNSW series index on a heap table to achieve the best search performance.

CREATE TABLE table_name (
    column_name1 data_type1,
    column_name2 VECTOR(dim),
    ...,
    VECTOR INDEX index_name (column_name2) WITH (param1=value1, param2=value2, ...)
);

-- You can set the degree of parallelism (DOP) for an index created later to improve index construction performance. The maximum DOP setting is no more than CPU cores × 2.
CREATE [/*+ paralell $value*/] VECTOR INDEX index_name ON table_name(column_name2) WITH (param1=value1, param2=value2, ...);

The following table describes the param parameters. | Parameter | Default value | Value range | Required | Description | Notes | |------|--------|----------|----------|------|------| | distance | | l2/inner_product/cosine | Yes | The vector distance algorithm. | l2 indicates the Euclidean distance, inner_product indicates the inner product distance, and cosine indicates the cosine distance. | | type | | hnsw/hnsw_sq/hnsw_bq | Yes | The index type. | | | lib | vsag | vsag | No | The vector index library type. | Only the VSAG vector library is supported. | | m | 16 | [5,128] | No | The maximum number of neighbors for each node. | A larger value indicates a slower index construction but better search performance. | | ef_construction | 200 | [5,1000] | No | The size of the candidate set during index construction. | A larger value indicates a slower index construction but better index quality. ef_construction must be greater than m. | | ef_search | 64 | [1,1000] | No | The size of the candidate set during search. | A larger value indicates a slower search but higher recall rate. | | extra_info_max_size | 0 | [0,16384] | No | The maximum size of each primary key (unit: bytes). The primary keys are stored in the index to speed up the search. | 0: Do not store the primary key information.
1: Forcefully store the primary key information and ignore the size limit. In this case, the primary key type must be a supported type (see below).
Greater than 1: The maximum size of the primary key information (unit: bytes). In this case, the following conditions must be met:

• The size of the primary key information (calculated as described below) must be less than the specified size limit.
• The primary key type must be a supported type.
• The table is not a table without a primary key.

The following primary key types are supported for extra_info_max_size:

• Numeric types: Integer types, float types, and Bit_value types.
• Date and time types
• Character types: VARCHAR.

SET @table_name = 'test'; -- Replace with the table name to query
    SELECT 
        CASE 
        WHEN COUNT(*) <> COUNT(result_value) THEN 'not support' 
    ELSE COALESCE(SUM(result_value), 'not support') 
END AS extra_info_size
    FROM (
        SELECT
            CASE 
            WHEN vdt.data_type_class IN (1, 2, 3, 4, 6, 8, 9, 14, 27, 28) THEN 8 -- For numeric types. extra_info_size += 8
            WHEN oc.data_type = 22 THEN oc.data_length -- For varchar type, extra_info_size += data_length
        ELSE NULL -- Other types are not supported.
    END AS result_value
        FROM 
    oceanbase.__all_column oc
        JOIN 
    oceanbase.__all_virtual_data_type vdt 
        ON 
    oc.data_type = vdt.data_type
        WHERE 
        oc.rowkey_position != 0 
AND oc.table_id = (SELECT table_id FROM oceanbase.__all_table WHERE table_name = @table_name)
) AS result_table;
-- The calculation result is 8 bytes.

Search

The search in the HNSW index series is an approximate nearest neighbor (ANN) search, and does not guarantee 100% accuracy in the search results. The recall rate is used to measure the accuracy of the search. For example, if 9 of the 10 nearest neighbors are returned stably, the recall rate is 90%.

SELECT ... FROM table_name 
ORDER BY distance_function(column_name, vector_expr) [APPROXIMATE|APPROX] 
LIMIT num [(OFFSET num)]

[PARAMETERS(param1=value1, param2=value2, ...)];

Syntax requirements for search:

• You must specify the APPROXIMATE/APPROX keyword. Otherwise, the search will not use the vector index but will instead perform a full table scan.

• The ORDER BY and LIMIT clauses must be specified.

• The ORDER BY clause must specify only one vector condition.

• The value of LIMIT + OFFSET must be in the range of (0, 16384]. A single approximate search returns at most 16,384 results. For larger-scale similar recall, you can use the search iterator. In PARAMETERS, you can continue to fetch data in batches using last_distance (supported starting from V4.6.0 BP1).

• If you do not specify the LIMIT clause, an error is returned.

• PARAMETERS are optional query-level parameters that allow you to set ef_search, refine_k, and last_distance. Usage rules for distance functions:

• If you specify the APPROXIMATE/APPROX keyword, the search will use the vector index. In this case, the distance function must be supported by the current version and must match the vector index algorithm.

• If you specify the APPROXIMATE/APPROX keyword but the distance function does not match the vector index algorithm, the search will not use the vector index and no error is returned.

• If you specify the APPROXIMATE/APPROX keyword and the distance function is not supported by the current version, the search will not use the vector index and an error is returned.

• If you do not specify the APPROXIMATE/APPROX keyword, the search will not use the vector index. However, no error is returned if the distance function is supported by the current version. Other considerations:

• The WHERE clause specifies the filter conditions for the vector index search.

• The recall rate is affected by the build parameters and search parameters.

• You can specify the index search parameters when you create the index. After the index is created, you can modify the table-level attributes by using DBMS_VECTOR.REBUILD_INDEX or specify the query-level parameter PARAMETERS in the query.

• When the SELECT list contains a distance function (such as l2_distance()) and the query accesses the vector index path, the system can reuse the precomputed distance values on the index side, reducing overhead in large LIMIT scenarios. Search parameters: | Parameter | Default value | Value range | Required | Description | Remarks | |------|--------|----------|----------|------|------| | ef_search | 64 | [1,1000] | No | The size of the candidate set during search. | A larger value results in slower search but higher recall. You can set this parameter when creating the index or specify it using PARAMETERS during the search. If not specified during the search, the value used during index creation is applied. | | refine_k | 4.0 | [1.0,1000.0] | No |

  Note

  This parameter applies only to HNSW_BQ indexes.

  Adjusts the rearrangement ratio of quantized vector indexes. | You can set this parameter when creating the index or specify it using PARAMETERS during the search. If not specified during the search, the value used during index creation is applied. | | last_distance | | | No |

  Note

  This parameter is supported starting with V4.6.0 BP1.

  Used in iterative paging queries (Search Iterator) to fetch similar results in batches when the single TopK limit is exceeded. | This parameter is not set for the first batch of queries. For subsequent batches, pass the distance value from the last row of the previous batch to continue fetching the next batch. Passing 0 for the first batch is not recommended. When a batch returns fewer than LIMIT rows or no results, it indicates all results have been fetched. This parameter is valid only for APPROXIMATE/APPROX queries and does not support HNSW_BQ indexes. |

Drop

The syntax for dropping a vector index is as follows:

DROP INDEX index_name ON table_name;

Create, search, and delete examples

Create during table creation

HNSW example

Create a test table.

CREATE TABLE t1(c1 INT, c0 INT, c2 VECTOR(10), c3 VECTOR(10), PRIMARY KEY(c1), VECTOR INDEX idx1(c2) WITH (distance=l2, type=hnsw, lib=vsag),  VECTOR INDEX idx2(c3) WITH (distance=l2, type=hnsw, lib=vsag));

Write test data.

INSERT INTO t1 VALUES(1, 1,'[0.203846,0.205289,0.880265,0.824340,0.615737,0.496899,0.983632,0.865571,0.248373,0.542833]', '[0.203846,0.205289,0.880265,0.824340,0.615737,0.496899,0.983632,0.865571,0.248373,0.542833]');
INSERT INTO t1 VALUES(2, 2, '[0.735541,0.670776,0.903237,0.447223,0.232028,0.659316,0.765661,0.226980,0.579658,0.933939]', '[0.213846,0.205289,0.880265,0.824340,0.615737,0.496899,0.983632,0.865571,0.248373,0.542833]');

INSERT INTO t1 VALUES(3, 3, '[0.327936,0.048756,0.084670,0.389642,0.970982,0.370915,0.181664,0.940780,0.013905,0.628127]', '[0.223846,0.205289,0.880265,0.824340,0.615737,0.496899,0.983632,0.865571,0.248373,0.542833]');

Perform approximate nearest neighbor search.

SELECT * FROM t1 ORDER BY l2_distance(c2, [0.712338,0.603321,0.133444,0.428146,0.876387,0.763293,0.408760,0.765300,0.560072,0.900498]) APPROXIMATE LIMIT 1;

+----+------+-------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------+
| c1 | c0   | c2                                                                                        | c3                                                                                         |
+----+------+-------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------+
|  3 |    3 | [0.327936,0.048756,0.08467,0.389642,0.970982,0.370915,0.181664,0.94078,0.013905,0.628127] | [0.223846,0.205289,0.880265,0.82434,0.615737,0.496899,0.983632,0.865571,0.248373,0.542833] |
+----+------+-------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------+
1 row in set

HNSW_SQ example

CREATE TABLE t2 (c1 INT AUTO_INCREMENT, c2 VECTOR(3), PRIMARY KEY(c1), VECTOR INDEX idx1(c2) WITH (distance=l2, type=hnsw_sq, lib=vsag));

HNSW_BQ example

CREATE TABLE t3 (c1 INT AUTO_INCREMENT, c2 VECTOR(3), PRIMARY KEY(c1), VECTOR INDEX idx3(c2) WITH (distance=l2, type=hnsw_bq, lib=vsag));

The distance parameter of the HNSW_BQ index supports l2 and cosine.

Create after table creation

HNSW example

Create a test table.

CREATE TABLE vec_table_hnsw (id INT, c2 VECTOR(10));

Create an HNSW index.

CREATE VECTOR INDEX vec_idx1 ON vec_table_hnsw(c2) WITH (distance=l2, type=hnsw);

View the created table.

SHOW CREATE TABLE vec_table_hnsw;

+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                                                                                                                                                         |
  +-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | vec_table_hnsw | CREATE TABLE `vec_table_hnsw` (
  `id` int(11) DEFAULT NULL,
`c2` VECTOR(10) DEFAULT NULL,
VECTOR KEY `vec_idx1` (`c2`) WITH (DISTANCE=L2, TYPE=HNSW, LIB=VSAG, M=16, EF_CONSTRUCTION=200, EF_SEARCH=64) BLOCK_SIZE 16384
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 2 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

View the created index.

SHOW INDEX FROM vec_table_hnsw;

+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
| vec_table |          1 | vec_idx1 |            1 | c2          | A         |        NULL | NULL     | NULL   | YES  | VECTOR     | available |               | YES     | NULL       |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
1 row in set

HNSW_SQ example

Create a test table.

CREATE TABLE vec_table_hnsw_sq (c1 INT AUTO_INCREMENT, c2 VECTOR(3), PRIMARY KEY(c1));

Create an HNSW_SQ index.

CREATE VECTOR INDEX vec_idx2 ON vec_table_hnsw_sq(c2) WITH (distance=l2, type=hnsw_sq, lib=vsag, m=16, ef_construction = 200);

HNSW_BQ example

CREATE VECTOR INDEX vec_idx3 ON vec_table_hnsw_bq(c2) WITH (distance=l2, type=hnsw_bq, lib=vsag, m=16, ef_construction = 200);

The distance parameter of the HNSW_BQ index supports l2 and cosine.

Search

An example of a search iterator is as follows:

-- First batch: do not pass last_distance
SELECT pk, l2_distance(vec, '[1,1,2]') AS dist
FROM image_embeddings
ORDER BY l2_distance(vec, '[1,1,2]') APPROXIMATE LIMIT 1000

PARAMETERS(ef_search=200);
-- Assume the last line is dist = 0.35
-- Batch 2: Pass in the maximum distance from the previous batch and continue to select results where dist > 0.35
SELECT pk, l2_distance(vec, '[1,1,2]') AS dist
FROM image_embeddings
ORDER BY l2_distance(vec, '[1,1,2]') APPROXIMATE LIMIT 1000

PARAMETERS(ef_search=200, last_distance=0.35);

Delete

DROP INDEX vec_idx1 ON vec_table;

View the deleted index.

SHOW INDEX FROM vec_table;

The result is as follows:

Empty set

References

• For memory estimation and actual usage queries of HNSW series indexes, see Index memory management.
• For monitoring and maintenance of HNSW series indexes, see Index monitoring and maintenance.