Re: Slowness in testing any crossing straight lines in a layer

[Bo Guo]

*Versions:  *POSTGIS="3.6.1 f533623" [EXTENSION] PGSQL="180"
GEOS="3.10.2-CAPI-1.16.0" PROJ="8.2.1 NETWORK_ENABLED=OFF URL_ENDPOINT=
https://cdn.proj.org USER_WRITABLE_DIRECTORY=/tmp/proj
DATABASE_PATH=/usr/share/proj/proj.db" (compiled against PROJ 8.2.1)
LIBXML="2.9.13" LIBJSON="0.15" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)"
(core procs from "3.6.0 4c1967d" need upgrade) TOPOLOGY (topology procs
from "3.6.0 4c1967d" need upgrade)

Yes, this is the Explain Graphics:
[image: image.png]
The Intersects call - 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) ); took over 15 minutes before pgAdmin session
terminated. I will try it again in a bit.


On Thu, Feb 5, 2026 at 11:38 AM Regina Obe <l...@pcorp.us> wrote:

> 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....@gisticinc.com>
> *Sent:* Thursday, February 5, 2026 12:59 PM
> *To:* Regina Obe <l...@pcorp.us>
> *Cc:* PostGIS Users Discussion <postgis-users@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 <l...@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....@gisticinc.com>
> *Sent:* Thursday, February 5, 2026 7:53 AM
> *To:* PostGIS Users Discussion <postgis-users@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
>
>