ST_GeomFromGeoJSON与ST_Intersects性能问题：分两次查询能否优化？

• 拆分执行确实能提升查询性能，核心原因有两点：

  1. 避免重复解析GeoJSON：原查询中，若数据库优化器未识别ST_GeomFromGeoJSON('a valid json')为常量表达式，可能会在遍历每行数据时重复执行GeoJSON解析，带来额外CPU开销。提前将GeoJSON解析为几何对象并存储到变量中，整个查询仅需解析一次。
  2. 更高效利用空间索引：提前计算出几何对象变量后，ST_Intersects(变量, boundingbox)的写法更贴合数据库空间索引的使用逻辑，优化器能直接触发boundingbox字段上的空间索引扫描，避免嵌套函数调用可能导致的索引失效。

• 正确的分步执行写法（以PostgreSQL为例）：
  用CTE临时存储解析后的几何对象：

  WITH target_geom_cte AS (
    SELECT ST_GeomFromGeoJSON('a valid json') AS geom
  )
  SELECT * 
  FROM mytable, target_geom_cte
  WHERE ST_Intersects(target_geom_cte.geom, boundingbox) 
  ORDER BY uuid, last_update_date;
  

  若在PL/pgSQL环境中，可直接用变量赋值：

  DECLARE
    target_geom geometry;
  BEGIN
    target_geom := ST_GeomFromGeoJSON('a valid json');
    
    SELECT * 
    FROM mytable
    WHERE ST_Intersects(target_geom, boundingbox) 
    ORDER BY uuid, last_update_date;
  END;
  

• 额外优化建议：

  • 确认boundingbox字段已创建空间索引：CREATE INDEX idx_mytable_boundingbox ON mytable USING GIST (boundingbox);，这是空间查询性能的基础。
  • 若该GeoJSON对象频繁使用，可将其持久化到小表中，避免每次查询重复解析。

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