# 向量标量混合索引GsDiskANN
向量标量混合索引基于GsDiskANN基础上，增加了标量非数组过滤、标量数组过滤功能；同时具备向量索引GsDiskANN在大规模数据上检索、新增、删除等能力。
#### 前置条件
- 数据库正常运行，用户正常登录。
- 已创建带有向量类型和标量类型的表。
- 数据库的GUC参数enable_vectordb设置为on。
 
#### 使用约束
- 混合索引仅支持一个向量字段和最多31个标量字段，且第一个字段必须为向量类型字段。
- 混合索引不支持备机读。
- 混合索引支持的非数组标量数据类型：
  - 整数类型（TINYINT、SMALLINT、INTEGER、BIGINT）；
  
  - 任意精度类型（NUMERIC、DECIMAL）；
  
  - 浮点类型（FLOAT、DOUBLE）；
  
  - 布尔类型（BOOLEAN）；
  
  - 字符类型（CHAR、VARCHAR），VARCHAR必须带长度参数，CHAR和VARCHAR参数\<=256；
  
  - 日期/时间类型（DATE、TIMESTAMP、TIMESTAMP WITH TIME ZONE）；
  
  - 枚举类型（ENUM）。
   
- 混合索引支持的数组标量数据类型：
  - 如果标量字段为数组类型，则数组维度为单维，且数组元素个数建议不超过128个；数组元素类型为上述支持的非数组标量数据类型；
  
  - 创建索引时，标量数组字段不能超过2个。
   
- 查询WHERE条件中包含索引标量，进行索引过滤需要遵循以下规则：
  - 非数组标量列支持比较运算符 \<、\<=、\>、\>=、=、IN；
  
  - 非数组标量列和数组标量列均支持逻辑运算符AND、OR；
  
  - 非数组标量列支持字符运算符LIKE且只支持前匹配；
  
  - 非数组标量列和数组标量列均支持IS NULL、IS NOT NULL；
  
  - 数组标量列支持操作符：=、\&\&、@\>、\<@。
   
- 查询WHERE条件中包含的非索引标量、不满足以上规则的索引标量，无法在向量标量混合索引内进行过滤，均在索引后过滤且召回率暂无保障。
- 混合索引不支持唯一索引、表达式索引、GLOBAL索引；支持多字段索引、LOCAL索引、部分索引。
- 针对分区表上的LOCAL索引，当查询语句涉及多个分区，命中多个分区索引时，可能会出现执行变慢的情况。
- 混合索引支持ustore存储和astore存储。
- 创建索引时，必须指定subgraph_count参数，且取值在\[1,32\]。其余索引参数设置请参见[向量索引GsDiskANN](https://support.huaweicloud.com/centralized-vector-devg-v10-gaussdb/gaussdb-80-0008.html)。
  ![](https://support.huaweicloud.com/centralized-vector-devg-v10-gaussdb/public_sys-resources/notice_3.0-zh-cn.png)
  - 在升级观察期时禁止创建向量标量混合索引GsDiskANN。
  
  - 非Oracle兼容的数据库下使用TINYINT存在隐式类型转换，影响向量标量混合索引的检索策略正确生成，建议采用如下方式规避该问题：
    1. 在非Oracle兼容的数据库下使用其他整型代替TINYINT。
    
    2. 使用Oracle兼容的数据库，即CREATE DATABASE mydb DBCOMPATIBILITY 'ORA'。
     
    
 
#### 示例（向量标量混合检索非数组）
```
--创建表
gaussdb=# CREATE TABLE "test" ("id" BIGINT PRIMARY KEY,"repr" floatvector(128),"score" float);
--随机插入数据
gaussdb=# CREATE OR REPLACE FUNCTION float_random_array(dims int,range int) RETURNS float[] AS $$BEGIN RETURN ARRAY(SELECT (random() * range)::int FROM generate_series(1, dims));END;$$ LANGUAGE plpgsql;
gaussdb=# CREATE OR REPLACE FUNCTION float_random(range int) RETURNS float AS $$BEGIN RETURN (random() * range)::int;END;$$ LANGUAGE plpgsql;
gaussdb=# INSERT INTO test SELECT i,floatvector(float_random_array(128,100)),float_random(100) FROM generate_series(1,10000) as i;
--创建索引
gaussdb=# SET maintenance_work_mem="2GB";
gaussdb=# CREATE INDEX diskann_sift1m_l2 on test USING GsDiskANN (repr L2, score) WITH (pq_nseg=128, pq_nclus=16, num_parallels=32, enable_pq=true, subgraph_count = 1);
--向量标量混合检索
gaussdb=# SELECT "id" FROM "test" WHERE (score < 60) ORDER BY "repr" <-> '[23.0,0.0,0.0,0.0,1.0,5.0,43.0,114.0,3.0,0.0,0.0,0.0,14.0,121.0,120.0,78.0,81.0,4.0,0.0,0.0,31.0,126.0,23.0,18.0,126.0,12.0,0.0,0.0,0.0,1.0,0.0,10.0,0.0,0.0,0.0,0.0,8.0,29.0,96.0,43.0,0.0,0.0,0.0,0.0,1.0,81.0,126.0,44.0,126.0,1.0,0.0,0.0,1.0,45.0,66.0,96.0,126.0,0.0,0.0,0.0,1.0,16.0,12.0,63.0,1.0,2.0,0.0,0.0,11.0,40.0,26.0,0.0,5.0,20.0,28.0,1.0,0.0,17.0,36.0,5.0,126.0,45.0,10.0,1.0,0.0,2.0,12.0,29.0,126.0,6.0,0.0,0.0,2.0,110.0,96.0,46.0,18.0,13.0,0.0,0.0,3.0,5.0,1.0,2.0,29.0,50.0,30.0,7.0,8.0,3.0,0.0,1.0,55.0,24.0,14.0,5.0,9.0,15.0,8.0,10.0,10.0,1.0,0.0,0.0,19.0,79.0,16.0,4.0]' LIMIT 10;
--插入数据
gaussdb=# INSERT INTO test(id,repr,score) VALUES(99999999,'[
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
]', 86.5);
gaussdb=# SELECT COUNT(*) FROM test;
--更新数据
gaussdb=# UPDATE test SET repr = '[
1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
]', score = 93 WHERE id = 99999999;
gaussdb=# SELECT * FROM test WHERE id = 99999999;
--删除数据
gaussdb=# DELETE FROM test WHERE id = 99999999;
gaussdb=# SELECT COUNT(*) FROM test;
--清理数据
gaussdb=# VACUUM test;
--查询索引健康度
gaussdb=# SELECT * FROM gs_diskann_inspect('diskann_sift1m_l2');
--删除函数
gaussdb=# DROP FUNCTION float_random_array;
gaussdb=# DROP FUNCTION float_random;
--删除索引
gaussdb=# DROP INDEX diskann_sift1m_l2;
--删除表
gaussdb=# DROP TABLE test;
```
#### 示例（向量标量混合检索支持数组）
```
--创建表
gaussdb=# CREATE TABLE "test" ("id" BIGINT PRIMARY KEY,"repr" floatvector(128),"score" float,"e" INT[]);
--随机插入数据
gaussdb=# CREATE OR REPLACE FUNCTION float_random_array(dims int,range int) RETURNS float[] AS $$BEGIN RETURN ARRAY(SELECT (random() * range)::int FROM generate_series(1, dims));END;$$ LANGUAGE plpgsql;
gaussdb=# CREATE OR REPLACE FUNCTION float_random(range int) RETURNS float AS $$BEGIN RETURN (random() * range)::int;END;$$ LANGUAGE plpgsql;
gaussdb=# INSERT INTO test (id, repr, score, e) SELECT i,floatvector(float_random_array(128, 100)),float_random(100),float_random_array(128, 100)::INT[]
FROM generate_series(1, 10000) AS i;
--创建索引
gaussdb=# SET maintenance_work_mem="2GB";
gaussdb=# CREATE INDEX diskann_sift1m_l2_array on test USING GsDiskANN (repr L2, score, e) WITH (pq_nseg=128, pq_nclus=16, num_parallels=32, enable_pq=true, subgraph_count = 1);
--向量标量混合检索
gaussdb=# SELECT "id","score" FROM "test" WHERE (score = 60) AND e @> ARRAY[1] ORDER BY "repr" <-> '[23.0,0.0,0.0,0.0,1.0,5.0,43.0,114.0,3.0,0.0,0.0,0.0,14.0,121.0,120.0,78.0,81.0,4.0,0.0,0.0,31.0,126.0,23.0,18.0,126.0,12.0,0.0,0.0,0.0,1.0,0.0,10.0,0.0,0.0,0.0,0.0,8.0,29.0,96.0,43.0,0.0,0.0,0.0,0.0,1.0,81.0,126.0,44.0,126.0,1.0,0.0,0.0,1.0,45.0,66.0,96.0,126.0,0.0,0.0,0.0,1.0,16.0,12.0,63.0,1.0,2.0,0.0,0.0,11.0,40.0,26.0,0.0,5.0,20.0,28.0,1.0,0.0,17.0,36.0,5.0,126.0,45.0,10.0,1.0,0.0,2.0,12.0,29.0,126.0,6.0,0.0,0.0,2.0,110.0,96.0,46.0,18.0,13.0,0.0,0.0,3.0,5.0,1.0,2.0,29.0,50.0,30.0,7.0,8.0,3.0,0.0,1.0,55.0,24.0,14.0,5.0,9.0,15.0,8.0,10.0,10.0,1.0,0.0,0.0,19.0,79.0,16.0,4.0]' LIMIT 10;
--插入数据
gaussdb=# INSERT INTO test(id,repr,score,e) VALUES(99999999,'[
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
]', 86.5,ARRAY[1,2,3,99,98,100]);
gaussdb=# SELECT COUNT(*) FROM test;
--更新数据
gaussdb=# UPDATE test SET e = ARRAY[1,2,3],score = 93 WHERE id = 99999999;
gaussdb=# SELECT * FROM test WHERE id = 99999999;
--删除数据
gaussdb=# DELETE FROM test WHERE id = 99999999;
gaussdb=# SELECT COUNT(*) FROM test;
--清理数据
gaussdb=# VACUUM test;
--查询索引健康度
gaussdb=# SELECT * FROM gs_diskann_inspect('diskann_sift1m_l2_array');
--删除函数
gaussdb=# DROP FUNCTION float_random_array;
gaussdb=# DROP FUNCTION float_random;
--删除索引
gaussdb=# DROP INDEX diskann_sift1m_l2_array;
--删除表
gaussdb=# DROP TABLE test;
```