Re: [ScanMail-Spam] Slowness in testing any crossing straight lines in a layer
Paul Ramsey via postgis-users <[email protected]> Thu, 5 Feb 2026 07:00:48 -0800
| Newsgroups | gmane.comp.gis.postgis |
|---|---|
| Message-ID | <[email protected]> |
--Apple-Mail=_5586D7FE-769B-44C6-B8B0-FFC282901D09 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Depends on your data. If the && operator is not particularly selective = in your case, you=E2=80=99re evaluating 100K * 100K =3D 10000000000 = crossing tests. P > On Feb 5, 2026, at 5:59=E2=80=AFAM, Bo Guo <[email protected]> = wrote: >=20 > Hi Julien, >=20 > That makes sense. I added a.id <http://a.id/> > b.id <http://b.id/>. = It still took 17 minutes, however. >=20 > Bo >=20 > On Thu, Feb 5, 2026 at 6:00=E2=80=AFAM MONTICOLO Julien = <[email protected] <mailto:[email protected]>> = wrote: >> Hello, >>=20 >> =20 >>=20 >> When doing a cross join (my_fishbones a, my_fishbones b), you = multiply a by b, so 100K by 100K. >>=20 >> 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. >>=20 >> There are unnecessary crosses. >>=20 >> =20 >>=20 >> Add a condition : a.id <http://a.id/> > b.id <http://b.id/> to = traverse only the half of the matrix. >>=20 >> =20 >>=20 >> Kind regards >>=20 >> =20 >>=20 >> De : Bo Guo [mailto:[email protected] = <mailto:[email protected]>]=20 >> Envoy=C3=A9 : jeudi 5 f=C3=A9vrier 2026 13:53 >> =C3=80 : PostGIS Users Discussion <[email protected] = <mailto:[email protected]>> >> Objet : [ScanMail-Spam] Slowness in testing any crossing straight = lines in a layer >>=20 >> =20 >>=20 >> Hi There! >>=20 >> =20 >>=20 >> 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? >>=20 >> =20 >>=20 >> SELECT a.*, b.id <http://b.id/> >> FROM my_fishbones a, my_fishbones b >> WHERE a.geom && b.geom=20 >> AND ST_Crosses(a.geom, b.geom) >>=20 >> =20 >>=20 >> Thanks in advance! >>=20 >> =20 >>=20 >> Bo Guo >>=20 >>=20 >>=20 >> 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. >>=20 >> Tout message =C3=A9lectronique est susceptible d'alt=C3=A9ration et = son int=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 message s'il a =C3=A9t=C3=A9 modifi=C3=A9 ou falsifi=C3=A9. >>=20 >> Si vous n'=C3=AAtes pas destinataire de ce message, merci de le = d=C3=A9truire et d'avertir l'exp=C3=A9diteur. >>=20 >> Ville et Eurom=C3=A9tropole de Strasbourg --Apple-Mail=_5586D7FE-769B-44C6-B8B0-FFC282901D09 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 <html><head><meta http-equiv=3D"content-type" content=3D"text/html; = charset=3Dutf-8"></head><body style=3D"overflow-wrap: break-word; = -webkit-nbsp-mode: space; line-break: after-white-space;">Depends on = your data. If the && operator is not particularly selective in = your case, you=E2=80=99re evaluating 100K * 100K =3D 10000000000 = crossing tests.<div>P<br = id=3D"lineBreakAtBeginningOfMessage"><div><br><blockquote = type=3D"cite"><div>On Feb 5, 2026, at 5:59=E2=80=AFAM, Bo Guo = <[email protected]> wrote:</div><br = class=3D"Apple-interchange-newline"><div><div dir=3D"ltr">Hi = Julien,<br><br>That makes sense. I added <a = href=3D"http://a.id/">a.id</a> > <a = href=3D"http://b.id/">b.id</a>. It still took 17 minutes, = however.<br><br>Bo<br></div><br><div class=3D"gmail_quote = gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Thu, Feb = 5, 2026 at 6:00=E2=80=AFAM MONTICOLO Julien <<a = href=3D"mailto:[email protected]">Julien.MONTICOLO@strasbourg= .eu</a>> 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 class=3D"msg-1594077599361256212">= <div lang=3D"FR"> <div class=3D"m_-1594077599361256212WordSection1"><p = class=3D"MsoNormal"><span = style=3D"font-size:11pt;font-family:Calibri,sans-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,sans-serif;color:rgb(31,73,125= )"><u></u> <u></u></span></p><p class=3D"MsoNormal"><span = style=3D"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=3D"MsoNormal"><span = style=3D"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=3D"MsoNormal"><span = style=3D"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=3D"MsoNormal"><span = style=3D"font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125= )"><u></u> <u></u></span></p><p class=3D"MsoNormal"><span = style=3D"font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125= )">Add a condition : <a href=3D"http://a.id/" = target=3D"_blank">a.id</a> > <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,sans-serif;color:rgb(31,73,125= )"><u></u> <u></u></span></p><p class=3D"MsoNormal"><span = style=3D"font-size:11pt;font-family:Calibri,sans-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,sans-serif;color:rgb(31,73,125= )"><u></u> <u></u></span></p><p class=3D"MsoNormal"><b><span = style=3D"font-size:11pt;font-family:Calibri,sans-serif">De :</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 :</b> jeudi 5 f=C3=A9vrier 2026 13:53<br> <b>=C3=80 :</b> PostGIS Users Discussion <<a = href=3D"mailto:[email protected]" = target=3D"_blank">[email protected]</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=3D"MsoNormal"><u></u> <u></u></p> <div> <div><p class=3D"MsoNormal">Hi There!<u></u><u></u></p> </div> <div><p class=3D"MsoNormal"><u></u> <u></u></p> </div> <div><p class=3D"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=3D"MsoNormal"><u></u> <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 && b.geom <br> AND ST_Crosses(a.geom, b.geom)<u></u><u></u></p> </div> <div><p class=3D"MsoNormal"><u></u> <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> <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,"sa= ns-serif";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'alt=C3=A9ration et son = int=C3=A9grit=C3=A9 ne peut =C3=AAtre assur=C3=A9e.<br> L'exp=C3=A9diteur d=C3=A9cline toute responsabilit=C3=A9 au titre de ce = message s'il a =C3=A9t=C3=A9 modifi=C3=A9 ou falsifi=C3=A9.<br> <br> Si vous n'=C3=AAtes pas destinataire de ce message, merci de le = d=C3=A9truire et d'avertir l'exp=C3=A9diteur.<br> <br> <span = style=3D"padding-top:10px;font-weight:bold;font-size:10pt;font-family:Cali= bri,Arial,"sans-serif"">Ville et Eurom=C3=A9tropole de = Strasbourg</span></div> </div></div></blockquote></div> </div></blockquote></div><br></div></body></html>= --Apple-Mail=_5586D7FE-769B-44C6-B8B0-FFC282901D09--