如何在PostgreSQL中计算CUBE类型向量的余弦距离？

不用自己写PL/pgSQL函数啦！PostgreSQL的cube扩展已经内置了高效的余弦距离计算函数，完全能满足你的需求，而且性能比自定义函数好很多（毕竟是底层C实现的）。

直接使用内置函数计算 #

cube模块提供了cube_cosine_distance(cube, cube)函数，专门用来计算两个cube向量的余弦距离。针对你的示例数据，直接运行以下查询就能得到结果：

SELECT cube_cosine_distance(
    '(109, 568, 787, 938, 948, 126, 271, 499)'::cube,
    '(139, 365, 222, 653, 313, 103, 215, 796)'::cube
);

针对你的表进行批量计算 #

如果要计算表中某个向量和其他所有向量的余弦距离，可以这样写查询：

SELECT 
    id,
    vector,
    cube_cosine_distance(vector, target_vector) AS cosine_distance
FROM vectors,
     (SELECT vector AS target_vector FROM vectors WHERE id = 2000000) AS sub
ORDER BY cosine_distance;

这个查询会把id为2000000的向量作为目标，计算表中所有向量和它的余弦距离，并且按距离从小到大排序。

优化查询性能 #

如果你的表数据量很大（比如像你示例里有200万条记录），频繁做向量距离查询的话，建议创建GiST索引来加速：

CREATE INDEX idx_vectors_cube_gist ON vectors USING gist(vector);

GiST索引对cube类型的距离查询支持很好，能大幅缩小查询时需要扫描的数据范围，提升排序和过滤的效率。

小提醒 #

• 确保你的PostgreSQL已经启用了cube扩展（如果还没启用，执行CREATE EXTENSION IF NOT EXISTS cube;即可）。
• 余弦距离的取值范围是0到2：值越小表示两个向量越相似，值为0时完全相同，值为2时完全相反。

内容的提问来源于stack exchange，提问作者Anant