RE: Slowness in testing any crossing straight lines in a layer
"Regina Obe" <[email protected]> Thu, 5 Feb 2026 13:38:04 -0500
| Newsgroups | gmane.comp.gis.postgis |
|---|---|
| Message-ID | <[email protected]> |
This is a multipart message in MIME format. ------=_NextPart_000_0075_01DC96A4.A8B9BCE0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable What does=20 =20 SELECT postgis_full_version(); =20 Return. I suppose doing a cross join there does result in an order of = 6,728,232,002 checks. =20 You definitely have a spatial index on your geometry column right? =20 =20 What timing do you get with below, just want to rule out some = performance issue with ST_Crosses =20 SELECT a.*, b.gad_globalid FROM _tmp_fishbone a INNER JOIN _tmp_fishbone b ON (a.gad_globalid < = b.gad_globalid AND ST_Intersects(a.geom,b.geom) ); =20 =20 From: Bo Guo <[email protected]>=20 Sent: Thursday, February 5, 2026 12:59 PM To: Regina Obe <[email protected]> Cc: PostGIS Users Discussion <[email protected]> Subject: Re: Slowness in testing any crossing straight lines in a layer =20 Hi Regina! =20 =20 Your query returned "116002, 2". =20 I ran the three variations of the ST_Crosses query, and here are the = performance results. =20 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 =20 It seems that 17 mins is the best I can get? =20 Thanks! =20 =20 On Thu, Feb 5, 2026 at 8:06=E2=80=AFAM Regina Obe <[email protected] = <mailto:[email protected]> > wrote: 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 = <http://a.id> =3D b.id <http://b.id>=20 =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 <http://a.id> = <> b.id <http://b.id> AND ST_Crosses(a.geom,b.geom) ) =20 =20 =20 From: Bo Guo <[email protected] <mailto:[email protected]> >=20 Sent: Thursday, February 5, 2026 7:53 AM To: PostGIS Users Discussion <[email protected] = <mailto:[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_0075_01DC96A4.A8B9BCE0 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:x=3D"urn:schemas-microsoft-com:office:excel" = 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;} @font-face {font-family:"Aptos Narrow";} /* 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>What does <o:p></o:p></p><p = class=3DMsoNormal><o:p> </o:p></p><p class=3DMsoNormal>SELECT = postgis_full_version();<o:p></o:p></p><p = class=3DMsoNormal><o:p> </o:p></p><p = class=3DMsoNormal>Return.=C2=A0 I suppose doing a cross join there does = result in an order of=C2=A0 <span = style=3D'font-size:11.0pt;font-family:"Aptos = Narrow",sans-serif;color:black'>6,728,232,002 = checks.<o:p></o:p></span></p><p class=3DMsoNormal><span = style=3D'font-size:11.0pt;font-family:"Aptos = Narrow",sans-serif;color:black'><o:p> </o:p></span></p><p = class=3DMsoNormal><span style=3D'font-size:11.0pt;font-family:"Aptos = Narrow",sans-serif;color:black'>You definitely have a spatial index on = your geometry column right?<o:p></o:p></span></p><p = class=3DMsoNormal><span style=3D'font-size:11.0pt;font-family:"Aptos = Narrow",sans-serif;color:black'><o:p> </o:p></span></p><p = class=3DMsoNormal><o:p> </o:p></p><p class=3DMsoNormal>What timing = do you get with below, just want to rule out some performance issue with = ST_Crosses<o:p></o:p></p><p class=3DMsoNormal><o:p> </o:p></p><p = class=3DMsoNormal>SELECT a.*, b.gad_globalid<br>FROM _tmp_fishbone a = INNER JOIN _tmp_fishbone b ON (a.gad_globalid < = =C2=A0b.gad_globalid AND ST_Intersects(a.geom,b.geom) = );<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 12:59 PM<br><b>To:</b> Regina Obe <[email protected]><br><b>Cc:</b> = PostGIS Users Discussion = <[email protected]><br><b>Subject:</b> Re: 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 = Regina! <o:p></o:p></p></div><div><p = class=3DMsoNormal><o:p> </o:p></p></div><div><p = class=3DMsoNormal>Your query returned "116002, = 2".<o:p></o:p></p></div><div><p = class=3DMsoNormal><o:p> </o:p></p></div><div><p class=3DMsoNormal>I = ran the three variations of the ST_Crosses query, and here are the = performance results.<o:p></o:p></p></div><div><p = class=3DMsoNormal><o:p> </o:p></p></div><div><p = class=3DMsoNormal>SELECT a.*, b.gad_globalid<br>FROM _tmp_fishbone a = INNER JOIN _tmp_fishbone b ON (a.gad_globalid <> = b.gad_globalid AND ST_Crosses(a.geom,b.geom) )<br>-- 33 = minutes<br><br>SELECT a.*, b.gad_globalid<br>FROM _tmp_fishbone a INNER = JOIN _tmp_fishbone b ON (a.gad_globalid < 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 JOIN _tmp_fishbone b = ON (a.gad_globalid < b.gad_globalid AND a.geom && b.geom AND = ST_Crosses(a.geom,b.geom) )<br>-- 17 minutes<o:p></o:p></p></div><div><p = class=3DMsoNormal><o:p> </o:p></p></div><div><p = class=3DMsoNormal>It seems that 17 mins is the best I can = get?<o:p></o:p></p></div><div><p = class=3DMsoNormal><o:p> </o:p></p></div><div><p = class=3DMsoNormal>Thanks!<o:p></o:p></p></div><div><p = class=3DMsoNormal><o:p> </o:p></p></div></div><p = class=3DMsoNormal><o:p> </o:p></p><div><div><p class=3DMsoNormal>On = Thu, Feb 5, 2026 at 8:06<span = style=3D'font-family:"Arial",sans-serif'>=E2=80=AF</span>AM Regina Obe = <<a href=3D"mailto:[email protected]" target=3D"_blank">[email protected]</a>> = wrote:<o:p></o:p></p></div><blockquote = style=3D'border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in = 6.0pt;margin-left:4.8pt;margin-right:0in'><div><div><div><p = class=3DMsoNormal = style=3D'mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>How = many records do you have in a / b<o:p></o:p></p><p = class=3DMsoNormal = style=3D'mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p><= /o:p></p><p class=3DMsoNormal = style=3D'mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>What does = this query return and point counts also impact = performance<o:p></o:p></p><p class=3DMsoNormal = style=3D'mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p><= /o:p></p><p class=3DMsoNormal = style=3D'mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>SELECT = COUNT(*), MAX(ST_NPoints(geom))<o:p></o:p></p><p = class=3DMsoNormal = style=3D'mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>FROM = my_fishbones<o:p></o:p></p><p class=3DMsoNormal = style=3D'mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p><= /o:p></p><p class=3DMsoNormal = style=3D'mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p><= /o:p></p><p class=3DMsoNormal = style=3D'mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>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" target=3D"_blank">b.id</a><o:p></o:p></p><p = class=3DMsoNormal = style=3D'mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p><= /o:p></p><p class=3DMsoNormal = style=3D'mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>I also = think the && is redundant as crosses already has a built in = index check<o:p></o:p></p><p class=3DMsoNormal = style=3D'mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p><= /o:p></p><p class=3DMsoNormal = style=3D'mso-margin-top-alt:auto;margin-bottom:12.0pt'>SELECT a.*, <a = href=3D"http://b.id" target=3D"_blank">b.id</a><br>FROM my_fishbones a = INNER JOIN my_fishbones b ON (<a href=3D"http://a.id" = target=3D"_blank">a.id</a> <> <a href=3D"http://b.id" = target=3D"_blank">b.id</a> AND ST_Crosses(a.geom,b.geom) = )<o:p></o:p></p><p class=3DMsoNormal = style=3D'mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p><= /o:p></p><p class=3DMsoNormal = style=3D'mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p><= /o:p></p><p class=3DMsoNormal = style=3D'mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <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 = style=3D'mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><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 <<a href=3D"mailto:[email protected]" = target=3D"_blank">[email protected]</a>> <br><b>Sent:</b> = Thursday, February 5, 2026 7:53 AM<br><b>To:</b> PostGIS Users = Discussion <<a href=3D"mailto:[email protected]" = target=3D"_blank">[email protected]</a>><br><b>Subject:</b= > Slowness in testing any crossing straight lines in a = layer</span><o:p></o:p></p></div></div><p class=3DMsoNormal = style=3D'mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p><= /o:p></p><div><div><p class=3DMsoNormal = style=3D'mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Hi = There!<o:p></o:p></p></div><div><p class=3DMsoNormal = style=3D'mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p><= /o:p></p></div><div><p class=3DMsoNormal = style=3D'mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>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 = style=3D'mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p><= /o:p></p></div><div><p class=3DMsoNormal = style=3D'mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>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)<o:p></o:p></p></div><div><p class=3DMsoNormal = style=3D'mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p><= /o:p></p></div><div><p class=3DMsoNormal = style=3D'mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Thanks in = advance!<o:p></o:p></p></div><div><p class=3DMsoNormal = style=3D'mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p><= /o:p></p></div><div><p class=3DMsoNormal = style=3D'mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Bo = Guo<o:p></o:p></p></div></div></div></div></div></div></blockquote></div>= </div></div></body></html> ------=_NextPart_000_0075_01DC96A4.A8B9BCE0--