PostgreSQL使用GIST索引查询GEOGRAPHY类型缺失distance函数报错如何排查

1. PostGIS扩展未完成版本升级
   从你提供的PostGIS版本输出可以看到明确的升级提示raster procs from "3.0.0 r17983" need upgrade，说明你仅更新了PostGIS的二进制文件，未执行数据库层面的扩展升级命令，导致内置的GIST索引距离支持函数（编号8）和现有索引元数据不匹配，索引调用时找不到对应函数。
2. 索引操作符类配置错误
   创建GEOGRAPHY类型的GIST索引时如果未显式指定PostGIS专属的操作符类gist_geography_ops，默认的GIST索引不会绑定distance支持函数，也会触发该报错。
3. 运算类型不匹配
   ST_MakePoint默认返回的是GEOMETRY类型，和GEOGRAPHY类型字段做<->距离运算时会触发隐式类型转换，导致查询计划器无法匹配到索引绑定的distance支持函数。

• 第一步完成PostGIS扩展升级
  先执行扩展升级命令：

ALTER EXTENSION postgis UPDATE;

执行完成后运行SELECT PostGIS_Full_Version();验证，确认输出中没有need upgrade的提示后，重建索引，创建时显式指定操作符类：

DROP INDEX IF EXISTS myindex;
CREATE INDEX myindex ON mytable USING GIST (geo_coords gist_geography_ops);

• 第二步修正查询语句的类型一致性
  将查询语句中的坐标点显式转换为GEOGRAPHY类型，避免隐式转换：

SELECT * FROM mytable
ORDER BY geo_coords <-> ST_MakePoint(<some_lon>, <some_lat>)::GEOGRAPHY;

• 第三步验证GIST支持函数绑定
  如果以上操作后仍报错，执行以下查询确认GIST索引的distance函数是否正常绑定：

SELECT amp.amopfamily, opr.oprname, proc.proname 
FROM pg_am am
JOIN pg_amop amp ON am.oid = amp.amopmethod
JOIN pg_operator opr ON amp.amopopr = opr.oid
JOIN pg_amproc apc ON amp.amopfamily = apc.amprocfamily
JOIN pg_proc proc ON apc.amproc = proc.oid
WHERE am.amname = 'gist' 
AND apc.amprocnum = 8
AND opr.oprname = '<->';

如果查询结果中没有对应GEOGRAPHY类型的函数记录，说明PostGIS扩展安装损坏，需要重新安装扩展。

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