<div dir="ltr">Hi Julien,<br><br>That makes sense. I added <a href="http://a.id">a.id</a> > <a href="http://b.id">b.id</a>. It still took 17 minutes, however.<br><br>Bo<br></div><br><div class="gmail_quote gmail_quote_container"><div dir="ltr" class="gmail_attr">On Thu, Feb 5, 2026 at 6:00 AM MONTICOLO Julien <<a href="mailto:Julien.MONTICOLO@strasbourg.eu">Julien.MONTICOLO@strasbourg.eu</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div class="msg-1594077599361256212">
<div lang="FR">
<div class="m_-1594077599361256212WordSection1">
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Hello,<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-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="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">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.<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">There are unnecessary crosses.<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Add a condition : <a href="http://a.id" target="_blank">a.id</a> > <a href="http://b.id" target="_blank">b.id</a> to traverse only the half of the matrix.<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Kind regards<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p>
<p class="MsoNormal"><b><span style="font-size:11pt;font-family:Calibri,sans-serif">De :</span></b><span style="font-size:11pt;font-family:Calibri,sans-serif"> Bo Guo [mailto:<a href="mailto:bo.guo@gisticinc.com" target="_blank">bo.guo@gisticinc.com</a>]
<br>
<b>Envoyé :</b> jeudi 5 février 2026 13:53<br>
<b>À :</b> PostGIS Users Discussion <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>><br>
<b>Objet :</b> [ScanMail-Spam] Slowness in testing any crossing straight lines in a layer<u></u><u></u></span></p>
<p class="MsoNormal"><u></u> <u></u></p>
<div>
<div>
<p class="MsoNormal">Hi There!<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<div>
<p class="MsoNormal">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?<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<div>
<p class="MsoNormal">SELECT a.*, <a href="http://b.id" target="_blank">b.id</a><br>
FROM my_fishbones a, my_fishbones b<br>
WHERE a.geom && b.geom <br>
AND ST_Crosses(a.geom, b.geom)<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<div>
<p class="MsoNormal">Thanks in advance!<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<div>
<p class="MsoNormal">Bo Guo<u></u><u></u></p>
</div>
</div>
</div>
<br><br>
<div style="font-size:8pt;line-height:10pt;font-family:Calibri,Arial,"sans-serif";background-color:rgb(213,234,255);border:1px dotted rgb(0,51,51);padding:0.6em">
Ce message est établi à usage exclusif de son destinataire.<br>
Toute utilisation ou diffusion, partielle ou totale, doit être préalablement autorisée.<br>
<br>
Tout message électronique est susceptible d'altération et son intégrité ne peut être assurée.<br>
L'expéditeur décline toute responsabilité au titre de ce message s'il a été modifié ou falsifié.<br>
<br>
Si vous n'êtes pas destinataire de ce message, merci de le détruire et d'avertir l'expéditeur.<br>
<br>
<span style="padding-top:10px;font-weight:bold;font-size:10pt;font-family:Calibri,Arial,"sans-serif"">Ville et Eurométropole de Strasbourg</span></div>
</div></div></blockquote></div>