向量分析的使用

1. DDL

1.1 创建表

语法：

CREATE TABLE [TABLE_NAME]
(  
    C1 DATATYPE,  
    C2 DATATYPE,  
    ......,  
    CN REAL[], 
    PRIMARY KEY(一列或者多列)
);

示例：在TEST空间下创建FACE_TABLE表，C2为向量列，C1为主键。NOT NULL表示字段不允许出现空值。

CREATE TABLE FACE_TABLE (  
    C1 INT,  
    C2 REAL[] NOT NULL,  
    C3 TIMESTAMP NOT NULL,  
    C4 VARCHAR(20) NOT NULL,  
    PRIMARY KEY (C1)
);

1.2 创建索引

索引是加速数据检索的一种特殊表查询，向量索引是一个指向表中数据的指针，通过对图中各个节点的遍历，找到与输入向量相似的向量。索引有助于加快向量检索中的SELECT中的WHERE子句，但是可能会降低使用INSERT和UPDATE语句写入或更新数据的效率。

语法：

CREATE INDEX [INDEX_NAME]
ON [SCHEMA_NAME].[TABLE_NAME]   
USING ANN(COLUMN_NAME) 
WITH (DIM=$DIMENSION);

每个字段 的含义：

• INDEX_NAME：索引名
• SCHEMA_NAME：模式（命名空间）名
• TABLE_NAME：表名
• COLUMN_NAME：向量索引列名
• DIMENSION：特征向量⻓度。要求不低于64，且不超过8192。该参数主要用于向量插入时候的检测，当维度不匹配的时候，系统将提示相关错误信息。

1.3 DDL例子

设有商品库一个，其中商品表products包含如下字段：

建表DDL如下：

create table products (
    Id serial primary key, 
    Name varchar(256), 
    Price real, 
    InTime timestamp, 
    Url varchar(256),
    Feature real[]
);

为了对向量查询进行加速，我们还需要建立一个向量索引：

create index on products using ann(feature) with (dim=512);

这里我们要指定使用 “ann” Access Method。此AM是FastANN插件提供的。创建向量索引时，需要通过with子句指定一些参数，其中必须指定的是“dim”，即向量的维度。

对于其他类型的距离，我们仅提供对应的距离计算UDF供暴力计算使用。目前提供的距离计算UDF列表如下：

• 点积距离：dp_distance
• 汉明距离：hm_distance
• 欧氏距离：l2_distance

为了对向量结构化融合查询提供加速，我们可以为常用的结构化列建立索引。如果某些条件经常一起出现，则可以简历组合索引：

create index on products(price, intime);

2. 数据插入

数据插入使用一般的insert语法，注意向量字面量的表达方式：

insert into products values (default, '白衬衫', 666.233, '2020-03-10', 'aaa.bbb.ccc/xxx.jpg', 
ARRAY[0.1, 0.2, 0.1, 0.3, …… 0.9]);

3. 查询

查询是使用或不使用索引，语法略有不同。

3.1 显式暴力查询

假设我们有一个“带条件的拍图查找商品”需求：查找与输入图片相似度最高的，价格在100到200元之间的，上架时间在最近一个月以内的最多100件商品。

设计查询SQL如下：

select id, price from products where 
    price > 100 and price <= 200 
    and InTime > '2019-03-01' and InTime <= '2019-03-31' 
order by 
    l2_distance(array[10,2.0,…, 512.0], feature) 
limit 100;

3.2 使用向量索引加速向量查询

如果要使用向量索引加速查询，则Order by部分必须使用 “向量列 <-> 向量” 语法代替距离UDF的调用。例如，对同样的查询需求，有如下SQL：

select id, price from products where
    price > 100 and price <= 200 
    and InTime > '2019-03-01' and InTime <= '2019-03-31' 
order by 
    feature <-> array[10,2.0,…, 512.0] 
limit 100;

• 如果要使用向量索引，配合向量查询的order by排序方向必须为ASC或不填。如果需要根据距离降序排序，或在使用向量索引后，再根据其他列排序输出，请将向量查询作为子查询，在父查询中添加想要的order by语句。
• 如果要使用向量索引，order by语句只能包含 “向量列 <-> 查询向量”这样一个条件。否则不能利用向量索引的加速能力。

3.3 向量结构化融合查询

云原生数据仓库PostgreSQL版向量分析在基本的向量查询基础上，还支持向量和结构化条件的融合查询。仍以如下sql为例：

select id, price from products where 
    price > 100 and price <=200 
    and InTime > '2019-03-01 00:00:00' and InTime <= '2019-03-31 00:00:00' 
order by 
    feature <-> array[10,2.0,…, 512.0] 
limit 100;

为了执行这条SQL，依据结构化条件列（price以及intime）上是否有索引，以及结构化条件的选择率大小不同，云原生数据仓库PostgreSQL版向量分析可能会生成3类共5种不同的执行计划，并在其中选择最优的一个执行计划，以满足用户对性能和召回的要求：

第一类：暴力查询

根据结构化条件获取所有符合条件的行，再在其中根据向量距离进行排序，选择距离最小的100条输出。这种执行计划召回为100%，但是最慢，在底库过大或符合结构化条件行数过多时查询性能低下。执行计划如下（简化部分输出信息）：

                                       QUERY PLAN
-----------------------------------------------------------------------------------
 Limit
     ->  Gather Motion 3:1  (slice1; segments: 3)   
         Merge Key: (l2_distance($0, feature))     
             ->  Limit
                 ->  Sort
                     Sort Key: (l2_distance($0, feature))
                     ->  Index Scan using products_price_idx on products
                         Index Cond: 价格过滤条件
                         Filter: 时间过滤条件

 Optimizer: Postgres query optimizer

第二类：纯向量查询 + 结构化过滤

为了加速查询，先使用向量索引查询出与输入图片最接近的N行数据，再在其中根据结构化条件进行过滤。这种执行计划的优点是速度最快。缺点是如果结构化查询的筛选率太小（即一行数据通过过滤的概率太小），则查询最终输出的数据行数可能比用户要求的limit小。执行计划如下（简化部分输出信息）：

                                       QUERY PLAN
-----------------------------------------------------------------------------------
 Limit 
    ->  Gather Motion 3:1  (slice1; segments: 3) 
        Merge Key: ((feature <-> $0))     
            ->  Limit
                ->  Ann Index Scan using products_feature_idx on products
                    Order By: (feature <-> $0)
                    Filter: 价格和时间列过滤条件

 Optimizer: Postgres query optimizer

第三类：向量结构化融合查询

向量结构化融合查询结合了第一类和第二类执行计划，既能使用索引，又能解决第二类执行计划的“返回数据变少”的问题。同时，如果结构化条件列上有索引，且索引类型支持Bitmap生成，则融合查询还可以利用其他索引来生成Bitmap，从而进一步加速融合查询。执行计划如下（简化部分输出信息）：

                                       QUERY PLAN
-----------------------------------------------------------------------------------
 Limit
     ->  Gather Motion 3:1  (slice1; segments: 3)   
         Merge Key: ((feature <-> $0))   
         ->  Limit
             ->  Fusion Ann Scan
                 ->  Bitmap Index Scan on products_price_idx
                     Index Cond: 价格过滤条件
                     ->  Ann Index Scan with filter using products_feature_idx on products
                         Order By: (feature <-> $0)
                         Filter: 时间过滤条件

 Optimizer: Postgres query optimizer

• Ann Index Scan with filter：此节点的作用是将过滤条件下压到向量索引内部，在索引的执行过程中同时考虑过滤条件。
• Fusion Ann Scan：此节点再某些结构化条件列上有索引时可能出现，其作用是根据结构化条件生成Bitmap（左子树），将Bitmap下压到向量索引（右子树）中，加速结构化条件的计算。出现Fusion Ann Scan节点时，其右子树可能是Ann Index Scan也可能是Ann Index Scan withfilter。如果右子树是Ann Index Scan，说明除了Bitmap外没有其他结构化条件下压。反之会出现Ann Index Scan withfilter。

云原生数据仓库PostgreSQL版会在保证召回的情况下，选取代价最小的执行计划。

4. 高级特性

云原生数据仓库PostgreSQL版的向量分析提供了一系列高级特性，使用户更方便，成本更低地使用向量分析功能。

4.1 特征抽取服务

为方便用户使用和体验向量分析功能，我们提供向量抽取服务，申请即可开通。用户也可以使用自己的向量抽取服务。为了让您对非结构化数据拥有更多的自主控制权，您可以把非结构化数据保存在OSS或者图片服务器上（下图使用OSS），非结构化数据的保存地址即URL存储在云原生数据仓库PostgreSQL版中。整体架构如下所示：

1. 通过云原生数据仓库PostgreSQL版控制台注册特征提取服务。
2. 非结构化数据保存到OSS，同时返回访问的URL。
3. 非结构化数据的存储地址即URL保存在云原生数据仓库PostgreSQL版中。
4. 通过Web App调用云原生数据仓库PostgreSQL版的自定义函数生成向量特征，云原生数据仓库PostgreSQL版后台通过调用特征提取服务从OSS读取非结构化数据，提取特征，并把特征向量保存在云原生数据仓库PostgreSQL版中。所有这些操作只需要一条SQL便可轻松完成，SQL语句示例如下。

   select feature_extractor('clothes','https://xxx/1036684144_687583347.jpg');  
   insert into product(id, url, feature) values(0, 'http://xxx/1036684144_687583347.jpg', feature_extractor('clothes','https://xxx/1036684144_687583347.jpg')); 

   feature_extractor为商品特征提取的自定义函数，传入商品图片URL，提取商品特征向量，该向量可以用来做商品检索和属性提取。

   说明 feature_extractor第一个参数区别要提取的属性类型，仅支持FACE、CLOTHES、TEXT。

   对于常用的人脸特征提取、文本特征提取BERT模型以及服装特征提取也已经内置于云原生数据仓库PostgreSQL版服务中，您也可以使用您自己的特征提取服务。

4.2 使用float2类型压缩存储向量

本节将通过具体示例，为您介绍半浮点数压缩数据列的定义和相关的操作。当前向量检索系统中，会将图片、声音、文本转化成高维浮点数数组进行存储，将占用大量的存储空间。为降低存储成本，压缩存储空间，为您提供了float2压缩存储模式。

4.2.1 float2类型简介

半精度浮点数（float2）是一种被计算机使用的二进制浮点数据类型。半精度浮点数使用2个字节（16位）来存储，来存储之前4个字节（32位）的float4的数据。IEEE 754标准指定了一个binary16需具备如下的格式：

• Sign bit（符号位）： 1 bit。
• Exponent width（指数位宽）： 5 bits。
• Significand precision（尾数精度）： 11 bits （有10位被显式存储）。

按如下顺序排列：除非指数位全是0，否则就会假定隐藏的起始位是1。因此只有10位尾数在内存中被显示出来，而总精度是11位。根据IEEE 754标准，虽然尾数只有10位，但是尾数精度是11位的(log10(211)≈ 3.311 十进制数)。

0 01111 0000000000 = 1
0 01111 0000000001 = 1 + 2−10 = 1.0009765625 （1之后的最接近的数）
1 10000 0000000000 = −2 

0 11110 1111111111 = 65504  （max half precision） 

0 00001 0000000000 = 2−14 ≈ 6.10352 × 10−5 （最小正指数）
0 00000 1111111111 = 2−14 - 2−24 ≈ 6.09756 × 10−5 （最大尾数）
0 00000 0000000001 = 2−24 ≈ 5.96046 × 10−8 （最小正尾数） 

0 00000 0000000000 = 0
1 00000 0000000000 = −0 

0 11111 0000000000 = infinity
1 11111 0000000000 = −infinity 

0 01101 0101010101 = 0.333251953125 ≈ 1/3

由于尾数的位数是奇数，所以默认情况下，类似1/3的数会像双精度浮点数一样四舍五入。

因此当前的浮点数的压缩是损失精度的压缩，所以在进行查询计算的时候会有一定的精度的损失。在实际应用中，这种损失是满足业务的要求的。对于精度的损失。

float2压缩存储是用两个字节，来表示之前的四个字节的存储，所以对于向量列的压缩比例在0.5，即占用磁盘空间是原来的50%。

Float2类型只能表达[-65519.99, 65519.99]之间的值。如果超过取值范围，比方说大于65519，系统会输出Infinity，如果小于-65519，系统会输出-Infinity。对于向量检索来说，向量需要进行归一化处理，将取值范围归一化到[0,1]之间。不进行归一化的向量距离计算，会非常容易超过取值范围，导致距离计算的不准确。

4.2.2 创建使用float2数据类型的表

float2是内部定义的一个数据类型，系统实现了各种类型的转换，以及相关的各种操作符。因此，在实际系统中，一般将float2数据类型当成基本数据类型来进行相关的操作。

语法：

CREATE TABLE [TABLE_NAME]
(  
    C1 INT,  
    C2 FLOAT2[],  
    C3 VARCHAR(20),  
    PRIMARY KEY(C1)
);

示例：

CREATE TABLE FACE_TABLE (  
    C1 INT PRIMARY KEY,  
    C2 FLOAT2[],  
    C3 VARCHAR(20)
);

4.2.3 插入数据

对已经建立好的float2类型的数组，插入相关的数据。可以用下述三种方式对float2的数组插入数据。在进行数据插入的时候，用户可以显示的定义出float2的数组，将相关的数据插入到表中（参见下述代码中的sql1）；或者用户采用隐示的类型转换，系统会在内部将float4类型的数组，转换成float2类型的数组，存储到对应的表中（参见下述代码中的sql2和sql3）。

示例：

sql1 = INSERT INTO FACE_TABLE (C1, C2, C3)
    VALUES (1, ARRAY[1.3, 2.4, 5.6]::FLOAT2[], 'name1'); 

sql2 = INSERT INTO FACE_TABLE (c1, c2, c3) 
    VALUES (2, ARRAY [3.4, 6.1, 7.6]::REAL[], 'name2'); 

sql3 = INSERT INTO FACE_TABLE (c1, c2, c3) 
    VALUES (3, ARRAY [9.5, 1.2, 0.6]::FLOAT4[],'name3');

4.2.4 查询数据

由于采用的是float2类型的数据，所以在显示查询结果时有一定的数据精度丢失。例如插入的是1.3，而实际查询的结果是1.2998；或者插入的是5.6，而实际查询的结果是5.60156。这种精度的损失对于向量检索来说，是可以忽略不计的。

示例：

SELECT * FROM FACE_TABLE; 
c1  |            c2             |  c3   
----+---------------------------+-------
  1 | {1.2998,2.40039,5.60156}  | name1
  2 | {3.40039,6.10156,7.60156} | name2
  3 | {9.5,1.2002,0.600098}     | name3

4.2.5 float2表数据的压缩比例

本示例中，建立两张表，一个是用float4类型的向量数据，一个是float2类型的向量数据，对比实际表的大小。

--CREATE TABLE 
CREATE TABLE TAB1(A FLOAT4[]);
CREATE TABLE TAB2(A FLOAT2[]); 

--INSERT DATA 
INSERT INTO TAB1 
SELECT GEN_RAND_F2_ARR (1, 1024) FROM GENERATE_SERIES (1,10000);
INSERT INTO TAB2 
SELECT GEN_RAND_F2_ARR (1, 1024) FROM GENERATE_SERIES (1,10000); 

--QUERY SIZE
SELECT PG_SIZE_PRETTY (PG_RELATION_SIZE('tab1'));
 PG_SIZE_PRETTY 
----------------
 45 MB(1 row)
 
SELECT PG_SIZE_PRETTY (PG_RELATION_SIZE('tab2')); 
 PG_SIZE_PRETTY
----------------
 21 MB(1 row)

从上述信息可查看到，使用float4数据类型的存储是45M，使用float2类型的数据存储是21M。由此可见，float2的存储大约是float4的一半。

4.2.6 float2表数据的压缩和解压的性能比较

当前系统提供了两个函数来进行float2与float4相互的转换：array_f16_to_f32 将float2类型的向量转化成float4类型的向量，array_f32_to_f16 将float4类型的向量转化成float2的向量。当前每个向量的长度是1024维，是在支持AVX和SSE2的指令集的机器上面进行测试的。

示例：

--CREATE TABLE 
CREATE TABLE TAB1(A FLOAT4[]);
CREATE TABLE TAB2(A FLOAT2[]); 

--INSERT TABLE
INSERT INTO TAB1 SELECT GEN_RAND_F2_ARR(1, 1024) FROM GENERATE_SERIES (1,10000);
INSERT INTO TAB2 SELECT GEN_RAND_F2_ARR(1, 1024) FROM GENERATE_SERIES (1,10000); 

\TIMING
--query size
SELECT ARRAY_F32_TO_F16(a) FROM TAB1;  
    Time: 5998.832 ms (00:05.999)
SELECT ARRAY_F16_TO_F32(a) FROM TAB2;
    Time: 5507.388 ms (00:05.507) 

4.2.7 距离计算

为了方便距离计算，当前的系统针对float2[]类型，提供了L2距离计算，系统在内部会将float2类型的数据，隐示的转成float4类型的数据，来计算相关的距离。

示例：

计算l2距离。

SELECT L2_DISTANCE(ARRAY[1.0,1.0,1.0,1.0,1.0,1.0,1.0,1.0,1.0]::FLOAT2[], 
    ARRAY[0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0]::FLOAT2[]); 

SELECT L2_DISTANCE(ARRAY[1.0,1.0,1.0,1.0,1.0,1.0,1.0,1.0,1.0]:: FLOAT4[], 
    ARRAY [0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0]:: FLOAT2[]); 

SELECT L2_DISTANCE (ARRAY[1.0,1.0,1.0,1.0,1.0,1.0,1.0,1.0,1.0]:: FLOAT2[], 
    ARRAY [0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0]::FLOAT2[]);

4.2.8 float2的实际应用案例

对于安保系统来说，每天都会定时的将监控的图片数据存在人脸表中，公安系统会输入人脸的照片，在监控系统中查找相关的监控图片。下文将介绍float2在查询检索的应用。

1. 创建一个表，用于存放人脸识别的相关数据。

   CREATE TABLE FACE_TABLE (
     C1 INT PRIMARY KEY,
     C2 FLOAT2[],
     C3 VARCHAR(20)
   );

   说明

   • C1：人脸的编号。
   • C2：人脸的向量。
   • C3：对应的人名。
2. 在FACE_TABLE表中建立向量索引。

   CREATE INDEX FACE_TABLE_IDX 
   ON FACE_TABLE 
   USING ANN(C2) WITH(dim=10);

3. 导入相关的监控数据到FACE_TABLE表中。

   INSERT INTO FACE_TABLE (C1, C2, C3)  
   VALUES (1, ARRAY[1.3, 2.4, 5.6]::FLOAT2[], 'name1'); 
   
   INSERT INTO FACE_TABLE (c1, c2, c3) 
   VALUES (2, ARRAY[3.4, 6.1, 7.6]::REAL[], 'name2'); 
   
   INSERT INTO FACE_TABLE (c1, c2, c3) 
   VALUES (3, ARRAY[9.5, 1.2, 0.6]::FLOAT4[],'name3');

4. 输入人脸的数据，进行向量查询。

   SELECT * 
   FROM FACE_TABLE 
   ORDER BY C1 <-> ARRAY[2.81574,9.84361,8.07218]:: FLOAT2[] 
   LIMIT 10;

   说明 ARRAY[2.81574,9.84361,8.07218]:: FLOAT2[]表示需要查询的图片向量，系统会在底库中检索对应的人脸信息。