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

--0000000000004798aa064a1771e1
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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=E2=80=AFAM 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=E2=80=99d think you=E2=80=99d want to leave out compare with a.id =
=3D 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
>

--0000000000004798aa064a1771e1
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi Regina!=C2=A0=C2=A0</div><div><br></div><div>Your =
query returned &quot;116002, 2&quot;.</div><div><br></div><div>I ran the th=
ree variations of the ST_Crosses query, and here are the performance result=
s.</div><div><br></div><div>SELECT a.*, b.gad_globalid<br>FROM _tmp_fishbon=
e a INNER JOIN =C2=A0_tmp_fishbone b ON (a.gad_globalid &lt;&gt; b.gad_glob=
alid AND ST_Crosses(a.geom,b.geom) )<br>-- 33 minutes<br><br>SELECT a.*, b.=
gad_globalid<br>FROM _tmp_fishbone a INNER JOIN =C2=A0_tmp_fishbone b ON (a=
.gad_globalid &lt; b.gad_globalid AND ST_Crosses(a.geom,b.geom) )<br>-- 17 =
minutes<br><br>SELECT a.*, b.gad_globalid<br>FROM _tmp_fishbone a INNER JOI=
N =C2=A0_tmp_fishbone b ON (a.gad_globalid &lt; b.gad_globalid AND a.geom &=
amp;&amp; b.geom AND ST_Crosses(a.geom,b.geom) )<br>-- 17 minutes<br></div>=
<div><br></div><div>It seems that 17 mins is the best I can get?</div><div>=
<br></div><div>Thanks!</div><div><br></div></div><br><div class=3D"gmail_qu=
ote"><div dir=3D"ltr" class=3D"gmail_attr">On Thu, Feb 5, 2026 at 8:06=E2=
=80=AFAM Regina Obe &lt;<a href=3D"mailto:[email protected]" target=3D"_blank">lr=
@pcorp.us</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=
=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding=
-left:1ex"><div><div lang=3D"EN-US"><div><p class=3D"MsoNormal">How=C2=A0 m=
any records do you have in=C2=A0 a / b<u></u><u></u></p><p class=3D"MsoNorm=
al"><u></u>=C2=A0<u></u></p><p class=3D"MsoNormal">What does this query ret=
urn and point counts also impact performance<u></u><u></u></p><p class=3D"M=
soNormal"><u></u>=C2=A0<u></u></p><p class=3D"MsoNormal">SELECT =C2=A0COUNT=
(*),=C2=A0 MAX(ST_NPoints(geom))<u></u><u></u></p><p class=3D"MsoNormal">FR=
OM my_fishbones<u></u><u></u></p><p class=3D"MsoNormal"><u></u>=C2=A0<u></u=
></p><p class=3D"MsoNormal"><u></u>=C2=A0<u></u></p><p class=3D"MsoNormal">=
Also I=E2=80=99d think you=E2=80=99d want to leave out compare with <a href=
=3D"http://a.id" target=3D"_blank">a.id</a> =3D <a href=3D"http://b.id" tar=
get=3D"_blank">b.id</a><u></u><u></u></p><p class=3D"MsoNormal"><u></u>=C2=
=A0<u></u></p><p class=3D"MsoNormal">I also think the &amp;&amp; is redunda=
nt as crosses already has a built in index check<u></u><u></u></p><p class=
=3D"MsoNormal"><u></u>=C2=A0<u></u></p><p class=3D"MsoNormal">SELECT a.*, <=
a href=3D"http://b.id" target=3D"_blank">b.id</a><br>FROM my_fishbones a IN=
NER JOIN =C2=A0my_fishbones b ON (<a href=3D"http://a.id" target=3D"_blank"=
>a.id</a> &lt;&gt; <a href=3D"http://b.id" target=3D"_blank">b.id</a> AND S=
T_Crosses(a.geom,b.geom) )<br><br><u></u><u></u></p><p class=3D"MsoNormal">=
<u></u>=C2=A0<u></u></p><p class=3D"MsoNormal"><u></u>=C2=A0<u></u></p><p c=
lass=3D"MsoNormal"><u></u>=C2=A0<u></u></p><div style=3D"border-top:none;bo=
rder-right:none;border-bottom:none;border-left:1.5pt solid blue;padding:0in=
 0in 0in 4pt"><div><div style=3D"border-right:none;border-bottom:none;borde=
r-left:none;border-top:1pt solid rgb(225,225,225);padding:3pt 0in 0in"><p c=
lass=3D"MsoNormal"><b><span style=3D"font-size:11pt;font-family:Calibri,san=
s-serif">From:</span></b><span style=3D"font-size:11pt;font-family:Calibri,=
sans-serif"> Bo Guo &lt;<a href=3D"mailto:[email protected]" target=3D"_=
blank">[email protected]</a>&gt; <br><b>Sent:</b> Thursday, February 5, =
2026 7:53 AM<br><b>To:</b> PostGIS Users Discussion &lt;<a href=3D"mailto:p=
[email protected]" target=3D"_blank">[email protected]=
rg</a>&gt;<br><b>Subject:</b> Slowness in testing any crossing straight lin=
es in a layer<u></u><u></u></span></p></div></div><p class=3D"MsoNormal"><u=
></u>=C2=A0<u></u></p><div><div><p class=3D"MsoNormal">Hi There!<u></u><u><=
/u></p></div><div><p class=3D"MsoNormal"><u></u>=C2=A0<u></u></p></div><div=
><p class=3D"MsoNormal">I have 100K straight lines in a layer - my_fishbone=
 -=C2=A0 with GIST indexed. However,=C2=A0the following SQL query did not f=
inish within 20 minutes!=C2=A0 Could you advise on any=C2=A0improvement ide=
as?<u></u><u></u></p></div><div><p class=3D"MsoNormal"><u></u>=C2=A0<u></u>=
</p></div><div><p class=3D"MsoNormal">SELECT a.*, <a href=3D"http://b.id" t=
arget=3D"_blank">b.id</a><br>FROM my_fishbones a, my_fishbones b<br>WHERE a=
.geom &amp;&amp; b.geom=C2=A0<br>=C2=A0 AND ST_Crosses(a.geom, b.geom)<u></=
u><u></u></p></div><div><p class=3D"MsoNormal"><u></u>=C2=A0<u></u></p></di=
v><div><p class=3D"MsoNormal">Thanks in advance!<u></u><u></u></p></div><di=
v><p class=3D"MsoNormal"><u></u>=C2=A0<u></u></p></div><div><p class=3D"Mso=
Normal">Bo Guo<u></u><u></u></p></div></div></div></div></div></div></block=
quote></div>

--0000000000004798aa064a1771e1--