在Postgresql上实现KNN算法

需要了解操作符 <-> （linear distance）

1.没有索引的情况下

SELECT name, location
FROM geonames
ORDER BY location <-> '(29.9691,-95.6972)'
LIMIT 5;

2.创建GIST索引

CREATE INDEX idx_gist_geonames_location ON geonames USING gist(location);

创建索引需要时间和空间，并且数据改变时，需要重新创建索引。

再次执行查询

SELECT name, location
FROM geonames
ORDER BY location <-> '(29.9691,-95.6972)'
LIMIT 5;

3.创建SP-GIST索引

CREATE INDEX idx_spgist_geonames_location ON geonames USING spgist(location);

 创建索引需要时间和空间，并且数据改变时，需要重新创建索引。

再次执行查询

SELECT name, location
FROM geonames
ORDER BY location <-> '(29.9691,-95.6972)'
LIMIT 5;

 

索引名	创建时间	估计	查询时间	索引大小	计划时间
未使用索引	0M	418749.73	255.676	0	0.493
GIST索引	3M 1S	1.16	0.939	868MB	0.786
SP-GIST索引	1M 25S	1.09	0.358	523MB	0.122

4.结论 

SP-GiST 的执行速度是 GiST 的两倍，计划速度快 8 倍，并且大约是磁盘大小的 60%。并且（与本文相关）它还支持从 PostgreSQL 12 开始的 KNN 索引搜索。

5.其他注意事项

本例子使用Postgresql原生的Point类型，没有使用Postgis中Point类型。

SP-GiST 索引不能用于排序和支持唯一约束。此外，不能在多个列上创建这样的索引（与 GiST 不同）。但是允许使用这样的索引来支持排除约束。这里与 GiST 的不同之处在于聚类是不可能的。