Slowness in testing any crossing straight lines in a layer

[Regina Obe]

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 <bo.guo at gisticinc.com> 
Sent: Thursday, February 5, 2026 12:59 PM
To: Regina Obe <lr at pcorp.us>
Cc: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
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 <lr at pcorp.us <mailto:lr at pcorp.us> > 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 <bo.guo at gisticinc.com <mailto:bo.guo at gisticinc.com> > 
Sent: Thursday, February 5, 2026 7:53 AM
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org <mailto: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/dcf20802/attachment.htm>