The INDEX_VECTOR_MEMORY_ESTIMATE function is used to analyze the memory usage of vector indexes for existing tables.
Notice
The calculation of this function depends on statistics. Before calling it, you must execute ANALYZE TABLE to update the statistics.
Syntax
FUNCTION INDEX_VECTOR_MEMORY_ESTIMATE (
IN TABLE_NAME VARCHAR(65535),
IN COLUMN_NAME VARCHAR(65535),
IN IDX_TYPE VARCHAR(65535),
IN IDX_PARAMETERS LONGTEXT DEFAULT NULL
);
The description is as follows:
INDEX_VECTOR_MEMORY_ESTIMATE: Reads the dimensionality and data type information of the vector column based ontable_nameandcolumn_name, and obtains the total number of vectors and the maximum number of vectors per partition from statistics. The memory usage estimate is calculated in the same way as forINDEX_VECTOR_MEMORY_ADVISOR.- For sparse vectors, this function randomly samples 100 vectors from the table, calculates the average number of non-zero dimensions for these 100 vectors as the average length of the sparse vector, and estimates the memory usage based on this average length.
Parameters
Parameter |
Description |
|---|---|
| table_name | Table name. It can be the table name directly or the database and table names. |
| column_name | Vector column name. |
| idx_type | Index type. Valid values: HNSW/HNSW_SQ/HNSW_BQ/IVF_FLAT/IVF_PQ/SINDI/SINDI_SQ, which is case-insensitive. |
| idx_parameters | Index parameters, which are the vector index parameter strings used during table creation. For example: distance=l2, type=hnsw, lib=vsag For more parameters, see Vector index memory management. |
The return value is a string describing the estimated memory usage.
Notice
In OceanBase AI Database, when creating an HNSW_BQ index, the system automatically chooses between parallel or serial mode based on memory availability. The recommended value returned by the INDEX_VECTOR_MEMORY_ESTIMATE function is the maximum memory requirement for serial index creation. If the available memory only meets this value, index creation time may be prolonged due to the inability to create the index in parallel.
Examples
Create table
t1and insert dataCREATE TABLE t1 (id int auto_increment, c1 vector(3)); INSERT INTO t1(c1) SELECT array(1/UNIFORM(1,1000,RANDOM(1)), 1/UNIFORM(1,1000,RANDOM(2)) , 1/UNIFORM(1,1000,RANDOM(3))) FROM table(generator(10000));Update the statistics of table
t1.ANALYZE TABLE t1;Query the memory usage estimate for the vector index of table
t1.-- Query the memory estimation result for the HNSW index. SELECT dbms_vector.index_vector_memory_estimate('t1','c1','HNSW','M=10,TYPE=HNSW'); +-----------------------------------------------------------------------------+ | dbms_vector.index_vector_memory_estimate('t1','c1','HNSW','M=10,TYPE=HNSW') | +-----------------------------------------------------------------------------+ | Suggested minimum vector memory is 11.5 MB | +-----------------------------------------------------------------------------+ 1 row in set -- Query the memory estimation result of the IVF index. SELECT dbms_vector.index_vector_memory_estimate('t1','c1','IVF_FLAT','SAMPLE_PER_NLIST=100'); +---------------------------------------------------------------------------------------------------------+ | dbms_vector.index_vector_memory_estimate('t1','c1','IVF_FLAT','SAMPLE_PER_NLIST=100') | +---------------------------------------------------------------------------------------------------------+ | Suggested minimum vector memory is 151.5 KB, memory consumption when providing search service is 1.5 KB | +---------------------------------------------------------------------------------------------------------+ 1 row in set -- Query the memory estimation result for the SINDI index. SELECT dbms_vector.index_vector_memory_estimate('t1','c1','SINDI','TYPE=SINDI,LIB=VSAG,DISTANCE=INNER_PRODUCT'); +----------------------------------------------------------------------------------------------------------+ | dbms_vector.index_vector_memory_estimate('t1','c1','SINDI','TYPE=SINDI,LIB=VSAG,DISTANCE=INNER_PRODUCT') | +----------------------------------------------------------------------------------------------------------+ | Suggested minimum vector memory is 73.6 MB | +----------------------------------------------------------------------------------------------------------+ 1 row in set