Re: [ScanMail-Spam] Slowness in testing any crossing straight lines in a layer

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

Hi Julien,

That makes sense. I added a.id > b.id.  It still took 17 minutes, however.

Bo

On Thu, Feb 5, 2026 at 6:00=E2=80=AFAM MONTICOLO Julien <
[email protected]> wrote:

> Hello,
>
>
>
> When doing a cross join (my_fishbones a, my_fishbones b), you multiply a
> by b, so 100K by 100K.
>
> If your table has 3 records : 1, 2, 3, the cross join outputs : 1|1, 1|2,
> 1|3, 2|1, 2|2, 2|3, 3|1, 3|2, 3|3.
>
> There are unnecessary crosses.
>
>
>
> Add a condition : a.id > b.id to traverse only the half of the matrix.
>
>
>
> Kind regards
>
>
>
> *De :* Bo Guo [mailto:[email protected]]
> *Envoy=C3=A9 :* jeudi 5 f=C3=A9vrier 2026 13:53
> *=C3=80 :* PostGIS Users Discussion <[email protected]>
> *Objet :* [ScanMail-Spam] 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
>
>
> Ce message est =C3=A9tabli =C3=A0 usage exclusif de son destinataire.
> Toute utilisation ou diffusion, partielle ou totale, doit =C3=AAtre
> pr=C3=A9alablement autoris=C3=A9e.
>
> Tout message =C3=A9lectronique est susceptible d'alt=C3=A9ration et son i=
nt=C3=A9grit=C3=A9 ne
> peut =C3=AAtre assur=C3=A9e.
> L'exp=C3=A9diteur d=C3=A9cline toute responsabilit=C3=A9 au titre de ce m=
essage s'il a
> =C3=A9t=C3=A9 modifi=C3=A9 ou falsifi=C3=A9.
>
> Si vous n'=C3=AAtes pas destinataire de ce message, merci de le d=C3=A9tr=
uire et
> d'avertir l'exp=C3=A9diteur.
>
> Ville et Eurom=C3=A9tropole de Strasbourg
>

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

<div dir=3D"ltr">Hi Julien,<br><br>That makes sense. I=C2=A0added <a href=
=3D"http://a.id">a.id</a> &gt; <a href=3D"http://b.id">b.id</a>.=C2=A0 It s=
till took 17 minutes, however.<br><br>Bo<br></div><br><div class=3D"gmail_q=
uote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Thu, F=
eb 5, 2026 at 6:00=E2=80=AFAM MONTICOLO Julien &lt;<a href=3D"mailto:Julien=
[email protected]">[email protected]</a>&gt; wrote:<br>=
</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;b=
order-left:1px solid rgb(204,204,204);padding-left:1ex"><div class=3D"msg-1=
594077599361256212">





<div lang=3D"FR">
<div class=3D"m_-1594077599361256212WordSection1">
<p class=3D"MsoNormal"><span style=3D"font-size:11pt;font-family:Calibri,sa=
ns-serif;color:rgb(31,73,125)">Hello,<u></u><u></u></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:11pt;font-family:Calibri,sa=
ns-serif;color:rgb(31,73,125)"><u></u>=C2=A0<u></u></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:11pt;font-family:Calibri,sa=
ns-serif;color:rgb(31,73,125)">When doing a cross join (my_fishbones a, my_=
fishbones b), you multiply a by b, so 100K by 100K.<u></u><u></u></span></p=
>
<p class=3D"MsoNormal"><span style=3D"font-size:11pt;font-family:Calibri,sa=
ns-serif;color:rgb(31,73,125)">If your table has 3 records=C2=A0: 1, 2, 3, =
the cross join outputs=C2=A0: 1|1, 1|2, 1|3, 2|1, 2|2, 2|3, 3|1, 3|2, 3|3.<=
u></u><u></u></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:11pt;font-family:Calibri,sa=
ns-serif;color:rgb(31,73,125)">There are unnecessary crosses.<u></u><u></u>=
</span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:11pt;font-family:Calibri,sa=
ns-serif;color:rgb(31,73,125)"><u></u>=C2=A0<u></u></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:11pt;font-family:Calibri,sa=
ns-serif;color:rgb(31,73,125)">Add a condition=C2=A0: <a href=3D"http://a.i=
d" target=3D"_blank">a.id</a> &gt; <a href=3D"http://b.id" target=3D"_blank=
">b.id</a> to traverse only the half of the matrix.<u></u><u></u></span></p=
>
<p class=3D"MsoNormal"><span style=3D"font-size:11pt;font-family:Calibri,sa=
ns-serif;color:rgb(31,73,125)"><u></u>=C2=A0<u></u></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:11pt;font-family:Calibri,sa=
ns-serif;color:rgb(31,73,125)">Kind regards<u></u><u></u></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:11pt;font-family:Calibri,sa=
ns-serif;color:rgb(31,73,125)"><u></u>=C2=A0<u></u></span></p>
<p class=3D"MsoNormal"><b><span style=3D"font-size:11pt;font-family:Calibri=
,sans-serif">De=C2=A0:</span></b><span style=3D"font-size:11pt;font-family:=
Calibri,sans-serif"> Bo Guo [mailto:<a href=3D"mailto:[email protected]"=
 target=3D"_blank">[email protected]</a>]
<br>
<b>Envoy=C3=A9=C2=A0:</b> jeudi 5 f=C3=A9vrier 2026 13:53<br>
<b>=C3=80=C2=A0:</b> PostGIS Users Discussion &lt;<a href=3D"mailto:postgis=
[email protected]" target=3D"_blank">[email protected]</a>=
&gt;<br>
<b>Objet=C2=A0:</b> [ScanMail-Spam] Slowness in testing any crossing straig=
ht lines in a layer<u></u><u></u></span></p>
<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 fi=
nish within 20 minutes!=C2=A0 Could you advise on any=C2=A0improvement idea=
s?<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" target=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>
</div>
<div>
<p class=3D"MsoNormal">Thanks in advance!<u></u><u></u></p>
</div>
<div>
<p class=3D"MsoNormal"><u></u>=C2=A0<u></u></p>
</div>
<div>
<p class=3D"MsoNormal">Bo Guo<u></u><u></u></p>
</div>
</div>
</div>
<br><br>

<div style=3D"font-size:8pt;line-height:10pt;font-family:Calibri,Arial,&quo=
t;sans-serif&quot;;background-color:rgb(213,234,255);border:1px dotted rgb(=
0,51,51);padding:0.6em">

Ce message est =C3=A9tabli =C3=A0 usage exclusif de son destinataire.<br>

Toute utilisation ou diffusion, partielle ou totale, doit =C3=AAtre pr=C3=
=A9alablement autoris=C3=A9e.<br>

<br>

Tout message =C3=A9lectronique est susceptible d&#39;alt=C3=A9ration et son=
 int=C3=A9grit=C3=A9 ne peut =C3=AAtre assur=C3=A9e.<br>

 L&#39;exp=C3=A9diteur d=C3=A9cline toute responsabilit=C3=A9 au titre de c=
e message s&#39;il a =C3=A9t=C3=A9 modifi=C3=A9 ou falsifi=C3=A9.<br>

<br>

Si vous n&#39;=C3=AAtes pas destinataire de ce message, merci de le d=C3=A9=
truire et d&#39;avertir l&#39;exp=C3=A9diteur.<br>

<br>

<span style=3D"padding-top:10px;font-weight:bold;font-size:10pt;font-family=
:Calibri,Arial,&quot;sans-serif&quot;">Ville et Eurom=C3=A9tropole de Stras=
bourg</span></div>

</div></div></blockquote></div>

--0000000000007f2982064a14174e--