PolarDB:Perform vector retrieval using the MySQL protocol

Scope

Before you use this feature, ensure that your cluster meets the following version requirements: The kernel version must be MySQL 8.0.2, and the revision version must be 8.0.2.2.30 or later.

Version update history

Features vary by kernel version. We recommend that you upgrade your cluster to the latest version.

Vector type

-- The following example defines the vector dimension of column v1 in table t1 as 4.
CREATE TABLE t1 (id INT PRIMARY KEY, v1 VECTOR(4));

SELECT STRING_TO_VECTOR('[1,2,3,4]');
+-------------------------------+
| STRING_TO_VECTOR('[1,2,3,4]') |
+-------------------------------+
|   ?   @  @@  @              |
+-------------------------------+

SELECT HEX(STRING_TO_VECTOR('[1,2,3,4]'));
+------------------------------------+
| HEX(STRING_TO_VECTOR('[1,2,3,4]')) |
+------------------------------------+
| 0000803F000000400000404000008040   |
+------------------------------------+

SELECT VECTOR_TO_STRING(0x0000803F000000400000404000008040);
+------------------------------------------------------+
| VECTOR_TO_STRING(0x0000803F000000400000404000008040) |
+------------------------------------------------------+
| [1.00000e+00,2.00000e+00,3.00000e+00,4.00000e+00]    |
+------------------------------------------------------+

Related operation examples

CREATE TABLE t1 (id INT PRIMARY KEY, v1 VECTOR(4));

ALTER TABLE t1 ADD COLUMN v1 VECTOR(4);

INSERT INTO t1 VALUES (1, STRING_TO_VECTOR('[1.0, 2.0, 3.0, 4.0]')) ON DUPLICATE KEY UPDATE v1 = STRING_TO_VECTOR('[1.0, 2.0, 3.0, 4.0]');

UPDATE t1 SET v1 = STRING_TO_VECTOR('[1.0, 2.0, 3.0, 4.0]') WHERE id = 1;

DELETE FROM t1 WHERE v1 = STRING_TO_VECTOR('[1.0, 2.0, 3.0, 4.0]');

Using vector indexes

PolarDB creates vector indexes by defining a specially formatted string in a column's COMMENT.

Prerequisites

Before you can use vector indexes, you must add a read-only node for the In-Memory Column Index (IMCI) and create an IMCI for the column or the entire table. Only IMCI read-only nodes can build vector indexes to support vector search.

COMMENT 'imci_vector_index=vector_index_algorithm(index_parameter_list) [other_comments]'

-- Method 1: Enable columnstore for the entire table, and then define a vector index on the v1 column.
CREATE TABLE t1 (
    id INT PRIMARY KEY,
    v1 VECTOR(4) COMMENT 'imci_vector_index=HNSW(metric=COSINE, max_degree=16)'
) COMMENT 'COLUMNAR=1';

-- Method 2: Enable columnstore and a vector index only for the v1 column.
CREATE TABLE t1 (
    id INT PRIMARY KEY,
    description TEXT,
    v1 VECTOR(4) COMMENT 'COLUMNAR=1, imci_vector_index=HNSW(metric=COSINE)'
);

-- Assume that the existing table is t1.
CREATE TABLE t1 (
    id int auto_increment PRIMARY KEY,
    v1 vector(4)
);

-- Method 1: Add a table-level columnstore index and modify the COMMENT of the VECTOR column to specify the vector index algorithm and parameters.
ALTER TABLE t1 comment "COLUMNAR=1", MODIFY COLUMN v1 vector(4) COMMENT "imci_vector_index=HNSW(metric=COSINE,max_degree=16,ef_construction=300)";


-- Method 2: Create a columnstore index only for the v1 column. When the columnstore index is initialized, a vector index is automatically created based on the COMMENT of the v1 column.
ALTER TABLE t1 MODIFY COLUMN v1 vector(4) COMMENT "COLUMNAR=1 imci_vector_index=HNSW(metric=COSINE,max_degree=16,ef_construction=300)";

Vector queries

SELECT VECTOR_TO_STRING(v1) FROM t1;

DISTANCE(vector1, vector2, '<metric>')

SELECT DISTANCE(v1, STRING_TO_VECTOR('[1.2,2.3,3.4,4.5]'), 'COSINE') FROM t1;

-- Find the 10 records most similar to the target
SELECT id, VECTOR_TO_STRING(v1)
FROM t1
ORDER BY DISTANCE(v1, STRING_TO_VECTOR('[...]'), 'COSINE')
LIMIT 10;

SELECT id, VECTOR_TO_STRING(v1) FROM t1 ORDER BY DISTANCE(v1, STRING_TO_VECTOR('[1.2,2.3,3.4,4.5]'), 'COSINE') LIMIT 1;

Vector queries with predicates

You can use additional columns as predicates to filter vector query results. For example:

SELECT * FROM t1 WHERE id < 10 ORDER BY DISTANCE(v1, STRING_TO_VECTOR('[1.2, 2.3, 3.4, 4.5]'), 'COSINE') LIMIT 2;

For vector queries that include predicates, you can adjust specific parameters to control the order of vector index retrieval and predicate filtering.

Execution strategies

Vector search often involves filtering by other properties. The optimizer intelligently selects an execution strategy based on the selectivity of the filter condition:

• Prefilter (prefilter): When a filter condition can significantly reduce the amount of data (for example, when the number of matching rows is less than imci_vector_search_prefilter_rows, which defaults to 10,000), the system first applies a predicate filter to the entire table to retrieve matching records, sorts these records, and then returns the number of records specified by the LIMIT clause.

• Post-filtering (postfilter): When the filter condition has poor selectivity (for example, if the match rate is higher than imci_vector_search_filter_pct, which is 20% by default), the system first uses the vector index to recall results and then performs predicate filtering on the recalled results. If the number of filtered results is less than the number specified by the LIMIT clause, this procedure is repeated.

• Inline filter: If the optimizer does not select the prefilter or postfilter strategy, it uses the inline filter strategy. The system first filters the entire table using the predicate and builds a bitmap filter from the RowIDs of the matching records. Then, it uses the vector index for retrieval. During the vector index retrieval process, the system uses the bitmap filter to ensure that the most similar records returned meet the predicate condition.

Adaptive execution optimization

The optimizer's estimate of the number of rows matched by a predicate filter can have a bias. This can lead to a suboptimal choice of execution strategy. An adaptive execution mechanism dynamically switches the execution strategy during runtime based on the actual number of matching rows.

When running a vector query with predicates using the inline filter strategy, if the actual number of matching records is less than imci_vector_search_prefilter_rows after the predicate is applied to the entire table, the system dynamically switches to the prefilter strategy.

Partition optimization

Vector queries often include predicate conditions such as tenant IDs or tags. For these queries, you can use the LIST DEFAULT HASH partitioning method. You can create separate LIST partitions for tenants or tags with large data volumes, and DEFAULT HASH partitions for other tenants or tags. The optimizer can detect these queries. Based on the results of partition pruning, the optimizer selects the postfilter strategy for vector queries on large tenants or tags. For other tenants, it uses the prefilter or inline filter strategy. This helps select the optimal execution strategy for tenants or tags of different sizes.

Parameter descriptions

Monitor vector index status

Building vector index data and advancing the columnstore index snapshot are separate background tasks. With continuous writes, the vector index snapshot offset might lag behind the columnstore data snapshot. Before you run a vector search, you must confirm that the vector index is available. The vector index status is shown in system views. This includes the snapshot offset and the status of the index on the vector column.

Examples

1. To ensure that vector indexes can be built normally with a small amount of data, you must set the imci_vector_index_dump_rows_threshold parameter to 1 in the PolarDB console.

2. Create a test table and insert data:

   CREATE TABLE t1 (
       id int auto_increment PRIMARY KEY,
       v1 vector(4) COMMENT "imci_vector_index=HNSW(metric=COSINE,max_degree=16,ef_construction=300)"
   ) comment "COLUMNAR=1";
   
   INSERT INTO t1 VALUES(1, STRING_TO_VECTOR('[1.1,1.2,1.3,1.4]')), (2, STRING_TO_VECTOR('[2,2.2,2.4,2.6]'));

3. Check the index snapshot status and the vector index status:

   SELECT schema_name,table_name,row_id FROM information_schema.imci_indexes;
   +-------------+------------+--------+
   | schema_name | table_name | row_id |
   +-------------+------------+--------+
   | test        | t1         |      2 |
   +-------------+------------+--------+
   1 row in set (0.00 sec)
   
   SELECT schema_name,table_name,vector_rows FROM information_schema.imci_index_stats;
   +-------------+------------+-------------+
   | schema_name | table_name | vector_rows |
   +-------------+------------+-------------+
   | test        | t1         |           2 |
   +-------------+------------+-------------+
   1 row in set (0.00 sec)
   
   SELECT schema_name,table_name,column_name,vectors FROM information_schema.imci_vector_index_stats;
   +-------------+------------+-------------+---------+
   | schema_name | table_name | column_name | vectors |
   +-------------+------------+-------------+---------+
   | test        | t1         | v1          |       2 |
   +-------------+------------+-------------+---------+
   1 row in set (0.00 sec)

4. Insert more data:

   INSERT INTO t1 VALUES(3, STRING_TO_VECTOR("[8.8,8.88,8.888,8.8888]"));

   The value of the VECTORS field in the query result for INFORMATION_SCHEMA.IMCI_VECTOR_INDEX_STATS is now 3, which is an increase of 1. This indicates that the background task wrote the inserted data to the vector index:

   SELECT schema_name,table_name,column_name,vectors FROM information_schema.imci_vector_index_stats;
   +-------------+------------+-------------+---------+
   | schema_name | table_name | column_name | vectors |
   +-------------+------------+-------------+---------+
   | test        | t1         | v1          |       3 |
   +-------------+------------+-------------+---------+
   1 row in set (0.00 sec)

5. Restart the columnstore index read-only node and check the index status:

   Important

   Restarting the columnstore index read-only node makes the current node unavailable for a short time.

   SELECT schema_name,table_name,row_id,state FROM information_schema.imci_indexes;
   +-------------+------------+--------+-----------+
   | schema_name | table_name | row_id | state     |
   +-------------+------------+--------+-----------+
   | test        | t1         |      3 | COMMITTED |
   +-------------+------------+--------+-----------+
   1 row in set (0.00 sec)
   
   SELECT schema_name,table_name,vector_rows FROM information_schema.imci_index_stats;
   +-------------+------------+-------------+
   | schema_name | table_name | vector_rows |
   +-------------+------------+-------------+
   | test        | t1         |           3 |
   +-------------+------------+-------------+
   1 row in set (0.00 sec)
   
   SELECT schema_name,table_name,column_name,vectors FROM information_schema.imci_vector_index_stats;
   +-------------+------------+-------------+---------+
   | schema_name | table_name | column_name | vectors |
   +-------------+------------+-------------+---------+
   | test        | t1         | v1          |       0 |
   +-------------+------------+-------------+---------+
   1 row in set (0.00 sec)

   The results show that VECTORS is 0 in the IMCI_VECTOR_INDEX_STATS system view, while the vector index snapshot offset is 3. You can use the execution plan to confirm that the vector index is available. Then, you can run an approximate query and check the vector index status again:

   SET cost_threshold_for_imci = 0;
   
   
   EXPLAIN SELECT * FROM t1 ORDER BY DISTANCE(v1, STRING_TO_VECTOR("[1.2, 2.3, 3.4, 4.5]"), "COSINE") LIMIT 1;
   +----+-----------------------+------+--------+--------+---------------------------------------------------------------------------------------+
   | ID | Operator              | Name | E-Rows | E-Cost | Extra Info                                                                            |
   +----+-----------------------+------+--------+--------+---------------------------------------------------------------------------------------+
   |  1 | Select Statement      |      |        |        | IMCI Execution Plan (max_dop = 4, max_query_mem = 858993459)                          |
   |  2 | +-Compute Scalar      |      |        |        |                                                                                       |
   |  3 |   +-Limit             |      |        |        | Offset=0 Limit=1                                                                      |
   |  4 |     +-Sort            |      |        |        | Sort Key: VECTOR_DISTANCE(t1.v1,"[1.200000,2.300000,3.400000,4.500000]","COSINE") ASC |
   |  5 |       +-Vector Search | t1   |        |        |                                                                                       |
   +----+-----------------------+------+--------+--------+---------------------------------------------------------------------------------------+
   5 rows in set (0.00 sec)
   
   SELECT id, vector_to_string(v1) FROM t1 ORDER BY DISTANCE(v1, STRING_TO_VECTOR("[1.2, 2.3, 3.4, 4.5]"), "COSINE") LIMIT 1;
   +----+---------------------------------------------------+
   | id | vector_to_string(v1)                              |
   +----+---------------------------------------------------+
   |  2 | [2.00000e+00,2.20000e+00,2.40000e+00,2.60000e+00] |
   +----+---------------------------------------------------+
   1 row in set (0.00 sec)
   
   SELECT schema_name,table_name,column_name,vectors FROM information_schema.imci_vector_index_stats;
   +-------------+------------+-------------+---------+
   | schema_name | table_name | column_name | vectors |
   +-------------+------------+-------------+---------+
   | test        | t1         | v1          |       3 |
   +-------------+------------+-------------+---------+
   1 row in set (0.00 sec)

   After the approximate query runs, the vector index is loaded into memory, and the vector count is restored to 3.

FAQ