sql - 如何优化此子查询作为联接？

我注意到运行此子查询



比运行此联接要慢得多



但是，我需要扩展第二个连接版本以返回更多列，每个列都计算出给定多边形类型的面积，即

最佳答案

试试这个。

SELECT ST_AREA(ST_Union(ST_Transform(ST_Intersection(poly1.the_geom,poly2.the_geom),3857)))

AS area_of_poly

FROM poly2

LEFT JOIN poly1 on st_intersects(poly1.the_geom,poly2.the_geom)

WHERE poly2.polygon_type IN ( 'P', 'S' )

编辑:

SELECT ST_AREA(ST_Union(ST_Transform(ST_Intersection(ps.the_geom,poly2.the_geom),3857))) AS area_of_P,
       ST_AREA(ST_Union(ST_Transform(ST_Intersection(ss.the_geom,poly2.the_geom),3857))) AS area_of_S
FROM poly2
JOIN poly1 ps ON poly2.polygon_type = 'P' AND st_intersects(ps.the_geom,poly2.the_geom)
JOIN poly1 ss ON poly2.polygon_type = 'S' AND st_intersects(ss.the_geom,poly2.the_geom)

关于sql - 如何优化此子查询作为联接？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/11167845/