What does
SELECT postgis_full_version(); Return. I suppose doing a cross join there does result in an order of 6,728,232,002 checks. You definitely have a spatial index on your geometry column right? What timing do you get with below, just want to rule out some performance issue with ST_Crosses SELECT a.*, b.gad_globalid FROM _tmp_fishbone a INNER JOIN _tmp_fishbone b ON (a.gad_globalid < b.gad_globalid AND ST_Intersects(a.geom,b.geom) ); From: Bo Guo <[email protected]> Sent: Thursday, February 5, 2026 12:59 PM To: Regina Obe <[email protected]> Cc: PostGIS Users Discussion <[email protected]> Subject: Re: Slowness in testing any crossing straight lines in a layer Hi Regina! Your query returned "116002, 2". I ran the three variations of the ST_Crosses query, and here are the performance results. SELECT a.*, b.gad_globalid FROM _tmp_fishbone a INNER JOIN _tmp_fishbone b ON (a.gad_globalid <> b.gad_globalid AND ST_Crosses(a.geom,b.geom) ) -- 33 minutes SELECT a.*, b.gad_globalid FROM _tmp_fishbone a INNER JOIN _tmp_fishbone b ON (a.gad_globalid < b.gad_globalid AND ST_Crosses(a.geom,b.geom) ) -- 17 minutes SELECT a.*, b.gad_globalid FROM _tmp_fishbone a INNER JOIN _tmp_fishbone b ON (a.gad_globalid < b.gad_globalid AND a.geom && b.geom AND ST_Crosses(a.geom,b.geom) ) -- 17 minutes It seems that 17 mins is the best I can get? Thanks! On Thu, Feb 5, 2026 at 8:06 AM Regina Obe <[email protected] <mailto:[email protected]> > wrote: 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 <http://a.id> = b.id <http://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 <http://a.id> <> b.id <http://b.id> AND ST_Crosses(a.geom,b.geom) ) From: Bo Guo <[email protected] <mailto:[email protected]> > Sent: Thursday, February 5, 2026 7:53 AM To: PostGIS Users Discussion <[email protected] <mailto:[email protected]> > 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
