PostgreSQL 13支持距离搜索的索引类型及系统表查询方法

支持距离搜索的索引类型 #

• GiST索引：通用搜索树索引，是PostgreSQL中支持距离搜索最常用的索引类型，除了你已经用到的gist_trgm_ops文本操作符类，还支持几何类型、时间戳类型、范围类型、地理位置类型等多种数据类型的距离算子与KNN搜索，适配timestamp的距离搜索需求时无需额外安装扩展，内置的时间操作符类原生支持。
• SP-GiST索引：空间分区GiST索引，针对非平衡数据结构优化，支持和GiST类似的距离搜索能力，适合点数据、IP地址、范围类型等场景的距离查询，在高基数非均匀分布数据上的性能优于常规GiST。
• GIN索引：倒排索引，默认仅支持包含类操作，部分操作符类（比如针对数组的array_ops、全文搜索的tsvector_ops、文本相似度的gin_trgm_ops）扩展支持了距离类搜索算子，比如数组元素的相似度距离计算。
• BRIN索引：块范围索引，仅在数据物理存储有序的场景下支持简单的距离过滤类搜索，相比前三种索引不支持精确的KNN排序，仅能做范围类的距离筛选。

通过pg_catalog查询索引距离搜索支持属性的方法 #

距离搜索能力本质是索引的操作符类绑定了距离算子（通常以<->作为距离算子符号），你可以通过查询pg_am、pg_opclass、pg_operator、pg_amop这几张系统表获取完整的支持列表，查询语句如下：

SELECT 
    am.amname AS 索引类型,
    opc.opcname AS 操作符类名,
    typ.typname AS 对应数据类型,
    opr.oprname AS 距离算子符号,
    opr.oprleft::regtype AS 左操作数类型,
    opr.oprright::regtype AS 右操作数类型
FROM pg_am am
JOIN pg_opclass opc ON am.oid = opc.opcmethod
JOIN pg_type typ ON opc.opcintype = typ.oid
JOIN pg_amop amop ON opc.oid = amop.amopclaid
JOIN pg_operator opr ON amop.amopopr = opr.oid
WHERE 
    -- 过滤距离类算子，通常返回双精度数值类型的算子为距离算子
    opr.oprresult = 'double precision'::regtype
    -- 可追加条件过滤指定索引类型：AND am.amname = 'gist'
    -- 可追加条件过滤指定数据类型：AND typ.typname = 'timestamp'
ORDER BY am.amname, opc.opcname;

其中pg_am表存储所有支持的索引访问方法，pg_opclass存储每个索引类型对应的操作符类，pg_amop存储操作符类绑定的算子，pg_operator存储算子的具体定义，上述查询会返回所有索引类型下支持的、返回值为数值类型的距离类算子，你可以通过调整WHERE条件过滤你需要的特定数据类型或者索引类型。

如果要验证实际创建的索引是否能被距离搜索命中，可以执行EXPLAIN ANALYZE加对应查询语句，确认执行计划中是否出现索引扫描的相关条目即可。

内容的提问来源于stack exchange，提问作者Morris de Oryx