Slowness in testing any crossing straight lines in a layer

[Bo Guo]

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> 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 <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
> 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/496637cb/attachment.htm>