Slowness in testing any crossing straight lines in a layer

[Regina Obe]

How  many records do you have in  a / b

 

What does this query return and point counts also impact performance

 

SELECT  COUNT(*),  MAX(ST_NPoints(geom))

FROM my_fishbones

 

 

Also I’d think you’d want to leave out compare with a.id = b.id

 

I also think the && is redundant as crosses already has a built in index check

 

SELECT a.*, b.id <http://b.id> 
FROM my_fishbones a INNER JOIN  my_fishbones b ON (a.id <> b.id AND ST_Crosses(a.geom,b.geom) )



 

 

 

From: Bo Guo <bo.guo at gisticinc.com> 
Sent: Thursday, February 5, 2026 7:53 AM
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
Subject: Slowness in testing any crossing straight lines in a layer

 

Hi There!

 

I have 100K straight lines in a layer - my_fishbone -  with GIST indexed. However, the following SQL query did not finish within 20 minutes!  Could you advise on any improvement ideas?

 

SELECT a.*, b.id <http://b.id> 
FROM my_fishbones a, my_fishbones b
WHERE a.geom && b.geom 
  AND ST_Crosses(a.geom, b.geom)

 

Thanks in advance!

 

Bo Guo

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20260205/7b057789/attachment-0001.htm>