INDEX_VECTOR_MEMORY_ESTIMATE

The INDEX_VECTOR_MEMORY_ESTIMATE procedure is used to analyze the memory usage of vector indexes in a table.

note

The calculation of this procedure depends on statistics. Therefore, you must execute the ANALYZE TABLE statement to update the statistics before calling this procedure.

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 INDEX_VECTOR_MEMORY_ESTIMATE procedure reads the dimension and data type information of the vector column based on the table_name and column_name parameters. It also retrieves the total number of vectors and the maximum number of vectors in a partition based on the statistics. The calculation process for estimating memory is the same as that of the INDEX_VECTOR_MEMORY_ADVISOR procedure.

Parameters

Parameter	Description
table_name	The name of the table. You can directly specify the table name or specify the database and table name.
column_name	The name of the vector column.
idx_type	The type of the index. Valid values: HNSW, HNSW_SQ, HNSW_BQ, IVF_FLAT, IVF_SQ8, and IVF_PQ. The value is case-insensitive.
idx_parameters	The index parameters. The value is the string of vector index parameters used when creating the table, such as distance=l2, type=hnsw, lib=vsag.

The return value is a string that describes the estimated memory.

note

In seekdb, when you create an HNSW_BQ index for a partitioned table, the system automatically chooses to create the index in parallel or sequentially based on the memory. The suggested value returned by the INDEX_VECTOR_MEMORY_ESTIMATE procedure is the maximum memory required when creating the index sequentially. If the memory only meets this value, the index creation time may be prolonged because the index cannot be created in parallel.

Examples

1. Create a table named t1 and insert data into the table.

   CREATE 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));

2. Update the statistics of the t1 table.

   ANALYZE TABLE t1;

3. Query the memory estimation result of the vector index of the t1 table.

   -- Query the memory estimation result of 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