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]> 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 = b.id > > > > I also think the && is redundant as crosses already has a built in index > check > > > > SELECT a.*, 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 <[email protected]> > *Sent:* Thursday, February 5, 2026 7:53 AM > *To:* PostGIS Users Discussion <[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 > FROM my_fishbones a, my_fishbones b > WHERE a.geom && b.geom > AND ST_Crosses(a.geom, b.geom) > > > > Thanks in advance! > > > > Bo Guo >
