RE: Slowness in testing any crossing straight lines in a layer
"Regina Obe" <[email protected]> Thu, 5 Feb 2026 10:06:19 -0500
| Newsgroups | gmane.comp.gis.postgis |
|---|---|
| Message-ID | <[email protected]> |
This is a multipart message in MIME format. ------=_NextPart_000_003D_01DC9687.1353E990 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable How many records do you have in a / b =20 What does this query return and point counts also impact performance =20 SELECT COUNT(*), MAX(ST_NPoints(geom)) FROM my_fishbones =20 =20 Also I=E2=80=99d think you=E2=80=99d want to leave out compare with a.id = =3D b.id =20 I also think the && is redundant as crosses already has a built in index = check =20 SELECT a.*, b.id <http://b.id>=20 FROM my_fishbones a INNER JOIN my_fishbones b ON (a.id <> b.id AND = ST_Crosses(a.geom,b.geom) ) =20 =20 =20 From: Bo Guo <[email protected]>=20 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 =20 Hi There! =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 SELECT a.*, b.id <http://b.id>=20 FROM my_fishbones a, my_fishbones b WHERE a.geom && b.geom=20 AND ST_Crosses(a.geom, b.geom) =20 Thanks in advance! =20 Bo Guo ------=_NextPart_000_003D_01DC9687.1353E990 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable <html xmlns:o=3D"urn:schemas-microsoft-com:office:office" = xmlns:w=3D"urn:schemas-microsoft-com:office:word" = xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" = xmlns=3D"http://www.w3.org/TR/REC-html40"><head><meta = http-equiv=3DContent-Type content=3D"text/html; charset=3Dutf-8"><meta = name=3DGenerator content=3D"Microsoft Word 15 (filtered = medium)"><style><!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4;} @font-face {font-family:Aptos;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0in; font-size:12.0pt; font-family:"Aptos",sans-serif;} a:link, span.MsoHyperlink {mso-style-priority:99; color:blue; text-decoration:underline;} span.EmailStyle18 {mso-style-type:personal-reply; font-family:"Aptos",sans-serif; color:windowtext;} .MsoChpDefault {mso-style-type:export-only;} @page WordSection1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in;} div.WordSection1 {page:WordSection1;} --></style></head><body lang=3DEN-US link=3Dblue vlink=3Dpurple = style=3D'word-wrap:break-word'><div class=3DWordSection1><p = class=3DMsoNormal>How=C2=A0 many records do you have in=C2=A0 a / = b<o:p></o:p></p><p class=3DMsoNormal><o:p> </o:p></p><p = class=3DMsoNormal>What does this query return and point counts also = impact performance<o:p></o:p></p><p = class=3DMsoNormal><o:p> </o:p></p><p class=3DMsoNormal>SELECT = =C2=A0COUNT(*),=C2=A0 MAX(ST_NPoints(geom))<o:p></o:p></p><p = class=3DMsoNormal>FROM my_fishbones<o:p></o:p></p><p = class=3DMsoNormal><o:p> </o:p></p><p = class=3DMsoNormal><o:p> </o:p></p><p class=3DMsoNormal>Also = I=E2=80=99d think you=E2=80=99d want to leave out compare with a.id =3D = b.id<o:p></o:p></p><p class=3DMsoNormal><o:p> </o:p></p><p = class=3DMsoNormal>I also think the && is redundant as crosses = already has a built in index check<o:p></o:p></p><p = class=3DMsoNormal><o:p> </o:p></p><p class=3DMsoNormal>SELECT a.*, = <a href=3D"http://b.id">b.id</a><br>FROM my_fishbones a INNER JOIN = =C2=A0my_fishbones b ON (a.id <> b.id AND = ST_Crosses(a.geom,b.geom) )<br><br><o:p></o:p></p><p = class=3DMsoNormal><o:p> </o:p></p><p = class=3DMsoNormal><o:p> </o:p></p><p = class=3DMsoNormal><o:p> </o:p></p><div = style=3D'border:none;border-left:solid blue 1.5pt;padding:0in 0in 0in = 4.0pt'><div><div style=3D'border:none;border-top:solid #E1E1E1 = 1.0pt;padding:3.0pt 0in 0in 0in'><p class=3DMsoNormal><b><span = style=3D'font-size:11.0pt;font-family:"Calibri",sans-serif'>From:</span><= /b><span style=3D'font-size:11.0pt;font-family:"Calibri",sans-serif'> Bo = Guo <[email protected]> <br><b>Sent:</b> Thursday, February 5, = 2026 7:53 AM<br><b>To:</b> PostGIS Users Discussion = <[email protected]><br><b>Subject:</b> Slowness in = testing any crossing straight lines in a = layer<o:p></o:p></span></p></div></div><p = class=3DMsoNormal><o:p> </o:p></p><div><div><p class=3DMsoNormal>Hi = There!<o:p></o:p></p></div><div><p = class=3DMsoNormal><o:p> </o:p></p></div><div><p class=3DMsoNormal>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?<o:p></o:p></p></div><div><p = class=3DMsoNormal><o:p> </o:p></p></div><div><p = class=3DMsoNormal>SELECT a.*, <a href=3D"http://b.id">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)<o:p></o:p></p></div><div><p = class=3DMsoNormal><o:p> </o:p></p></div><div><p = class=3DMsoNormal>Thanks in advance!<o:p></o:p></p></div><div><p = class=3DMsoNormal><o:p> </o:p></p></div><div><p = class=3DMsoNormal>Bo = Guo<o:p></o:p></p></div></div></div></div></body></html> ------=_NextPart_000_003D_01DC9687.1353E990--