geomtry空间索引sql查询慢优化

原来sql，两个表想要通过空间索引查询交集，但是glTable的SRID是4490；cesTable 是4325
查询失败，将两个表的SRID统一成4490，而且两张表没有加空间索引
使用函数查询交集查询速度特别慢；

//慢sql 执行时间长达100s
SELECT 
a.geom, b.geometry FROM glTable as a JOIN cesTable as b ON a.geom && b.geometry AND ST_Intersects(a.geom, b.geometry);

使用执行计划查看是否走索引，发现走了索引,但是查询速度还是特别慢

EXPLAIN ANALYZE
SELECT a.geom,  b.geometry  
FROM glTable a
JOIN cesTable b 
ON a.geom && b.geometry AND ST_Intersects(a.geom, b.geometry);

继续优化

//最终版查询速度优化到0.1s
SELECT a.xse,b.*
FROM glTable a
JOIN LATERAL (SELECT * FROM cesTable b WHERE a.geom && b.geometry AND ST_Intersects(a.geom, b.geometry)
) b ON TRUE;

原因总结：

语法没有问题，但之所以执行 非常慢，
根本原因在于以下几点（尤其是你提到“两个差不对”——说明数据之间几乎不相交）根本原因分析数据量乘积过大 + 实际匹配度极低
如果：glTable 有 1,000 个 PolygoncesTable 有 100,000 个 Point➡️ 你这句 SQL 逻辑上相当于：对每个 polygon 去遍历所有摄像头，看是否相交（即点落在面内）理论计算量：1,000 × 100,000 = 1亿次 ST_Intersects() 判断。但现实是你说的：“感觉两个差不对”，也就是说——大多数面和点根本没有交集，相当于 99.999% 的计算都白费。PostGIS 的空间 JOIN 即使加了索引，依旧是近似 O(N×M)
虽然你用了：a.geom && b.geometry AND ST_Intersects(a.geom, b.geometry)
其中 && 是用了 GiST 空间索引的快速预筛选（bounding box），但只要两个表都很大，性能依旧撑不住。

建议改为“以面为主、点为辅”的逐面计算方式
推荐写法（逐面 LATERAL 方式）：

SELECT a.id_1,b.cam,b.geometry
FROM glTable a
JOIN LATERAL (SELECT cam, geometry FROM cesTable b WHERE a.geom && b.geometry AND ST_Intersects(a.geom, b.geometry)
) b ON TRUE;

✅ 以每个 polygon 为主，查其范围内可能的点

✅ 利用空间索引缩小搜索空间

✅ 极大减少无效 ST_Intersects 的计算次数

✅ 加强优化建议：
➤ 1. 看看是否真的有交集（先打印有几条）

SELECT COUNT(*) FROM glTable ;
SELECT COUNT(*) FROM cesTable ;SELECT COUNT(*) 
FROM glTable a
JOIN cesTable b ON a.geom && b.geometry AND ST_Intersects(a.geom, b.geometry);

👉 如果第三条结果非常少（<1000），那就绝对不应该全表 JOIN。

➤ 2. 打印所有相交 ID 看看“哪里交集了”

SELECT a.id_1, b.cameraindexcode
FROM glTable a
JOIN LATERAL (SELECT cam, geometry FROM cesTable b WHERE a.geom && b.geometry AND ST_Intersects(a.geom, b.geometry)
) b ON TRUE;

➤ 3. 如仍需全量匹配，可用以下方式并行优化：

– 允许并行处理

SET max_parallel_workers_per_gather = 4;
SET parallel_tuple_cost = 0.1;
SET parallel_setup_cost = 0.1;

✅ 总结