查找未在交叉路口分割的路段。（查詢優化）

我有一個查詢，我試圖在其中找到相交但未在交叉路口分割的路段，我還在 geom 列上創建了 gist 索引。這是postgerSQL srcipt

SELECT a.id touched, b.id touching,  ST_Intersection(a.geom, b.geom)
FROM   pak_roads a, pak_roads b
WHERE ST_Crosses(a.geom, b.geom) and a.id < b.id and (a.MODE IS NULL
and b.MODE IS NULL )
and (a.grade_t IS NULL and a.grade_f IS NULL)
and (b.grade_t IS NULL and b.grade_f IS NULL) OR
-- "vertical" T bar, touching
(
-- The "vertical" start node touches, but not on either of the "horizonal" nodes
ST_Equals(ST_Intersection(a.geom, b.geom), ST_StartPoint(b.geom))
AND NOT ST_Equals(ST_StartPoint(a.geom), ST_StartPoint(b.geom))
AND NOT ST_Equals(ST_EndPoint(a.geom), ST_StartPoint(b.geom))
) OR (
-- The "vertical" end node touches, but not on either of the "horizonal" nodes
ST_Equals(ST_Intersection(a.geom, b.geom), ST_EndPoint(b.geom))
AND NOT ST_Equals(ST_StartPoint(a.geom), ST_EndPoint(b.geom))
AND NOT ST_Equals(ST_EndPoint(a.geom), ST_EndPoint(b.geom))
)

邏輯工作正常，但完成此任務需要很多時間，有人可以告訴我如何優化此查詢。

我剛剛在第二個和第三個查詢開始時添加了 st_intersects(a.geom,b.geom)，因為 st_intersects 速度很快，所以它只計算 st_intersects 返回“True”的地方，因此查詢的性能得到了增強。下面是修改後的查詢。

SELECT a.id touched, b.id touching,  ST_Intersection(a.geom, b.geom)
FROM   pak_roads a, pak_roads b
WHERE ST_Crosses(a.geom, b.geom) and a.id < b.id and (a.MODE IS NULL
and b.MODE IS NULL )
and (a.grade_t IS NULL and a.grade_f IS NULL)
and (b.grade_t IS NULL and b.grade_f IS NULL) OR
-- "vertical" T bar, touching
(
-- The "vertical" start node touches, but not on either of the "horizonal" --nodes
st_intersects(a.geom,b.geom) and ST_Equals(ST_Intersection(a.geom, b.geom), 
ST_StartPoint(b.geom))
AND NOT ST_Equals(ST_StartPoint(a.geom), ST_StartPoint(b.geom))
AND NOT ST_Equals(ST_EndPoint(a.geom), ST_StartPoint(b.geom))
) OR (
-- The "vertical" end node touches, but not on either of the "horizonal" 
--nodes
st_intersects(a.geom,b.geom) and ST_Equals(ST_Intersection(a.geom, b.geom), ST_EndPoint(b.geom))
AND NOT ST_Equals(ST_StartPoint(a.geom), ST_EndPoint(b.geom))
AND NOT ST_Equals(ST_EndPoint(a.geom), ST_EndPoint(b.geom))
)

但我認為還有一些改進的空間。

引用自：https://dba.stackexchange.com/questions/221318