<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML xmlns="http://www.w3.org/TR/REC-html40" xmlns:v =
"urn:schemas-microsoft-com:vml" xmlns:o =
"urn:schemas-microsoft-com:office:office" xmlns:w =
"urn:schemas-microsoft-com:office:word" xmlns:m =
"http://schemas.microsoft.com/office/2004/12/omml"><HEAD>
<META content="text/html; charset=iso-8859-2" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 11.00.10570.1001"><!--[if !mso]>
<STYLE>v\:* {
BEHAVIOR: url(#default#VML)
}
o\:* {
BEHAVIOR: url(#default#VML)
}
w\:* {
BEHAVIOR: url(#default#VML)
}
.shape {
BEHAVIOR: url(#default#VML)
}
</STYLE>
<![endif]-->
<STYLE>@font-face {
font-family: Cambria Math;
}
@font-face {
font-family: Calibri;
}
@font-face {
font-family: Verdana;
}
@font-face {
font-family: Tahoma;
}
@page WordSection1 {size: 8.5in 11.0in; margin: 1.0in 1.0in 1.0in 1.0in; }
P.MsoNormal {
FONT-SIZE: 12pt; FONT-FAMILY: "Times New Roman",serif; MARGIN: 0in 0in 0pt
}
LI.MsoNormal {
FONT-SIZE: 12pt; FONT-FAMILY: "Times New Roman",serif; MARGIN: 0in 0in 0pt
}
DIV.MsoNormal {
FONT-SIZE: 12pt; FONT-FAMILY: "Times New Roman",serif; MARGIN: 0in 0in 0pt
}
A:link {
TEXT-DECORATION: underline; COLOR: blue; mso-style-priority: 99
}
SPAN.MsoHyperlink {
TEXT-DECORATION: underline; COLOR: blue; mso-style-priority: 99
}
A:visited {
TEXT-DECORATION: underline; COLOR: purple; mso-style-priority: 99
}
SPAN.MsoHyperlinkFollowed {
TEXT-DECORATION: underline; COLOR: purple; mso-style-priority: 99
}
SPAN.EmailStyle17 {
FONT-FAMILY: "Calibri",sans-serif; COLOR: #1f497d; mso-style-type: personal-reply
}
.MsoChpDefault {
FONT-SIZE: 10pt; mso-style-type: export-only
}
DIV.WordSection1 {
page: WordSection1
}
</STYLE>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></HEAD>
<BODY lang=EN-US vLink=purple link=blue>
<DIV dir=ltr align=left><SPAN class=343162122-27032023><FONT color=#000080
size=2 face=Arial>Regina,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=343162122-27032023><FONT color=#000080
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN
style='FONT-SIZE: 11pt; FONT-FAMILY: "Calibri",sans-serif; COLOR: #1f497d'><FONT
color=#808080><SPAN class=343162122-27032023><FONT size=2
face=Arial> >>>> </FONT></SPAN>So you had the centroids
indexed before you switched to regular join and that took days too?<SPAN
class=343162122-27032023><FONT size=2 face=Arial>
</FONT></SPAN></FONT></SPAN><SPAN
style='FONT-SIZE: 11pt; FONT-FAMILY: "Calibri",sans-serif; COLOR: #1f497d'><FONT
color=#808080>I would expect the index on the centroid to make much more of a
difference than the LEFT / INNER JOIN change.</FONT><SPAN
class=343162122-27032023><FONT color=#000080 size=2
face=Arial> </FONT></SPAN></SPAN></DIV>
<DIV dir=ltr align=left><SPAN
style='FONT-SIZE: 11pt; FONT-FAMILY: "Calibri",sans-serif; COLOR: #1f497d'><SPAN
class=343162122-27032023><FONT color=#000080 size=2 face=Arial>No at that time I
had not the centroids indexed just the polygon boundaries
themselves</FONT> <FONT color=#000080 size=2 face=Arial>
...</FONT></SPAN><o:p></o:p></SPAN></DIV>
<DIV class=WordSection1>
<P class=MsoNormal><SPAN
style='FONT-SIZE: 11pt; FONT-FAMILY: "Calibri",sans-serif; COLOR: #1f497d'><o:p> </o:p></SPAN></P>
<P class=MsoNormal><SPAN
style='FONT-SIZE: 11pt; FONT-FAMILY: "Calibri",sans-serif; COLOR: #1f497d'><FONT
color=#808080><SPAN class=343162122-27032023><FONT size=2
face=Arial> >> </FONT></SPAN>I think the LEFT / INNER JOIN
performance would have a lot to do with number of rows in each table and whether
it resorts to a nested vs. hash.</FONT><SPAN class=343162122-27032023><FONT
color=#808080 size=2 face=Arial> <BR></FONT></SPAN></SPAN><SPAN
style='FONT-SIZE: 11pt; FONT-FAMILY: "Calibri",sans-serif; COLOR: #1f497d'><FONT
color=#808080>There have been performance issues in PostgreSQL that cause LEFT
to be much slower than INNER, but I think most of those have been long
resolved.<o:p></o:p></FONT></SPAN></P>
<P class=MsoNormal><SPAN
style='FONT-SIZE: 11pt; FONT-FAMILY: "Calibri",sans-serif; COLOR: #1f497d'><FONT
color=#808080>It might be some left over stuff with our poor spatial stats
estimates.<SPAN class=343162122-27032023><FONT size=2 face=Arial>
>>></FONT></SPAN></FONT></SPAN></P>
<P class=MsoNormal><SPAN
style='FONT-SIZE: 11pt; FONT-FAMILY: "Calibri",sans-serif; COLOR: #1f497d'><SPAN
class=343162122-27032023><FONT color=#000080 size=2
face=Arial>Interesting</FONT></SPAN></SPAN></P>
<P class=MsoNormal><SPAN
style='FONT-SIZE: 11pt; FONT-FAMILY: "Calibri",sans-serif; COLOR: #1f497d'><SPAN
class=343162122-27032023></SPAN></SPAN> </P><SPAN
style='FONT-SIZE: 11pt; FONT-FAMILY: "Calibri",sans-serif; COLOR: #1f497d'><SPAN
class=343162122-27032023>
<DIV dir=ltr align=left><SPAN class=343162122-27032023><FONT size=2
face=Arial><FONT color=#808080>>> <SPAN
style='FONT-SIZE: 11pt; FONT-FAMILY: "Calibri",sans-serif; COLOR: #1f497d'>SELECT
version(), postgis_full_version();</SPAN></FONT></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=343162122-27032023><FONT color=#000080
size=2 face=Arial><SPAN
style='FONT-SIZE: 11pt; FONT-FAMILY: "Calibri",sans-serif; COLOR: #1f497d'><FONT
color=#808080>shows:</FONT></SPAN><SPAN
style='FONT-SIZE: 11pt; FONT-FAMILY: "Calibri",sans-serif; COLOR: #1f497d'><o:p> </o:p></SPAN></DIV></FONT></SPAN>
<DIV dir=ltr align=left><SPAN class=343162122-27032023><FONT color=#000080
size=2 face=Arial>PostgreSQL 12.8 (Ubuntu 12.8-0ubuntu0.20.04.1) on
x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0,
64-bit | POSTGIS="3.1.4 ded6c34" [EXTENSION] PGSQL="120"
GEOS="3.10.1-CAPI-1.16.0" PROJ="7.2.1" LIBXML="2.9.10" LIBJSON="0.13.1"
LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)"<BR></FONT></SPAN></DIV>
<P class=MsoNormal></SPAN></SPAN><SPAN
style='FONT-SIZE: 11pt; FONT-FAMILY: "Calibri",sans-serif; COLOR: #1f497d'><SPAN
class=343162122-27032023><FONT color=#000080 size=2
face=Arial> </FONT></SPAN></SPAN></P>
<P class=MsoNormal><SPAN
style='FONT-SIZE: 11pt; FONT-FAMILY: "Calibri",sans-serif; COLOR: #1f497d'><SPAN
class=343162122-27032023><FONT color=#000080 size=2
face=Arial>Cheers</FONT></SPAN></SPAN></P>
<P class=MsoNormal><SPAN
style='FONT-SIZE: 11pt; FONT-FAMILY: "Calibri",sans-serif; COLOR: #1f497d'><SPAN
class=343162122-27032023><FONT color=#000080 size=2
face=Arial>Karsten</FONT> </SPAN>
<o:p></o:p></SPAN></P>
<P class=MsoNormal><SPAN
style='FONT-SIZE: 11pt; FONT-FAMILY: "Calibri",sans-serif; COLOR: #1f497d'><o:p> </o:p></SPAN></P>
<P class=MsoNormal><SPAN
style='FONT-SIZE: 11pt; FONT-FAMILY: "Calibri",sans-serif; COLOR: #1f497d'><o:p> </o:p></SPAN></P>
<DIV
style="BORDER-TOP: medium none; BORDER-RIGHT: medium none; BORDER-BOTTOM: medium none; PADDING-BOTTOM: 0in; PADDING-TOP: 0in; PADDING-LEFT: 4pt; BORDER-LEFT: blue 1.5pt solid; PADDING-RIGHT: 0in">
<DIV>
<DIV
style="BORDER-TOP: #e1e1e1 1pt solid; BORDER-RIGHT: medium none; BORDER-BOTTOM: medium none; PADDING-BOTTOM: 0in; PADDING-TOP: 3pt; PADDING-LEFT: 0in; BORDER-LEFT: medium none; PADDING-RIGHT: 0in">
<P class=MsoNormal><B><SPAN
style='FONT-SIZE: 11pt; FONT-FAMILY: "Calibri",sans-serif'>From:</SPAN></B><SPAN
style='FONT-SIZE: 11pt; FONT-FAMILY: "Calibri",sans-serif'> postgis-users
[mailto:postgis-users-bounces@lists.osgeo.org] <B>On Behalf Of
</B>karsten<BR><B>Sent:</B> Monday, March 27, 2023 5:16 PM<BR><B>To:</B>
'PostGIS Users Discussion'
<postgis-users@lists.osgeo.org><BR><B>Subject:</B> Re: [postgis-users]
improve query - summarize building count and area on each
parcel<o:p></o:p></SPAN></P></DIV></DIV>
<P class=MsoNormal><o:p> </o:p></P>
<P class=MsoNormal><SPAN
style='FONT-SIZE: 10pt; FONT-FAMILY: "Arial",sans-serif; COLOR: navy'>That
worked perfectly:<BR>Using just join and setting the index on the centroids geom
as well</SPAN><o:p></o:p></P>
<P class=MsoNormal><SPAN
style='FONT-SIZE: 10pt; FONT-FAMILY: "Arial",sans-serif; COLOR: navy'>I had the
query finish in less than a minute (compared to three day running and not yet
finished !)</SPAN><o:p></o:p></P>
<P class=MsoNormal> <o:p></o:p></P>
<P class=MsoNormal><SPAN
style='FONT-SIZE: 10pt; FONT-FAMILY: "Arial",sans-serif; COLOR: navy'>Does
anyone have an explanation why 'regular' join is so much faster than 'left join'
?</SPAN><o:p></o:p></P>
<P class=MsoNormal> <o:p></o:p></P>
<P class=MsoNormal><SPAN
style='FONT-SIZE: 10pt; FONT-FAMILY: "Arial",sans-serif; COLOR: navy'>Cheers</SPAN><o:p></o:p></P>
<P class=MsoNormal><SPAN
style='FONT-SIZE: 10pt; FONT-FAMILY: "Arial",sans-serif; COLOR: navy'>Karsten</SPAN><o:p></o:p></P>
<P class=MsoNormal><o:p> </o:p></P>
<DIV class=MsoNormal style="TEXT-ALIGN: center" align=center>
<HR align=center SIZE=2 width="100%">
</DIV>
<P class=MsoNormal style="MARGIN-BOTTOM: 12pt"><B><SPAN
style='FONT-SIZE: 10pt; FONT-FAMILY: "Tahoma",sans-serif'>From:</SPAN></B><SPAN
style='FONT-SIZE: 10pt; FONT-FAMILY: "Tahoma",sans-serif'> postgis-users [<A
href="mailto:postgis-users-bounces@lists.osgeo.org">mailto:postgis-users-bounces@lists.osgeo.org</A>]
<B>On Behalf Of </B>Marcin Mionskowski<BR><B>Sent:</B> Sunday, March 26, 2023
23:34<BR><B>To:</B> PostGIS Users Discussion<BR><B>Subject:</B> Re:
[postgis-users] improve query - summarize building count and area on each
parcel</SPAN><o:p></o:p></P>
<DIV>
<DIV>
<P class=MsoNormal><SPAN style='FONT-FAMILY: "Courier New"'>In my "explain
tests", made on much smaller tables, I get 500 times smaller costs only by
changing LEFT JOIN to "regular" JOIN.<o:p></o:p></SPAN></P></DIV></DIV>
<P class=MsoNormal><o:p> </o:p></P>
<DIV>
<DIV>
<P class=MsoNormal>pon., 27 mar 2023 o 01:07 Raj Talati <<A
href="mailto:rajtalati@gmail.com">rajtalati@gmail.com</A>>
napisał(a):<o:p></o:p></P></DIV>
<BLOCKQUOTE
style="BORDER-TOP: medium none; BORDER-RIGHT: medium none; BORDER-BOTTOM: medium none; PADDING-BOTTOM: 0in; PADDING-TOP: 0in; PADDING-LEFT: 6pt; MARGIN-LEFT: 4.8pt; BORDER-LEFT: #cccccc 1pt solid; PADDING-RIGHT: 0in; MARGIN-RIGHT: 0in">
<DIV>
<P class=MsoNormal>I prefer doing insert instead of update if we doing all
rows update :) so create New table with new table and copy from old and later
drop it you can go without index and do index later after copy. Postgres
update is delete and insert internally and direct big update will bloat
table.<o:p></o:p></P></DIV>
<DIV>
<P class=MsoNormal>Other thing you can do multi thread if you have 16 core
with nothing else can go 12-14 threads updates which can do portion of your
big table easy if no good criteria can be mod function on
PK <o:p></o:p></P></DIV>
<DIV>
<P class=MsoNormal><o:p> </o:p></P></DIV>
<DIV>
<P class=MsoNormal>Hope this helps .<o:p></o:p></P></DIV>
<DIV>
<P class=MsoNormal><o:p> </o:p></P></DIV>
<DIV>
<P class=MsoNormal>Raj T<o:p></o:p></P></DIV>
<DIV>
<P class=MsoNormal><o:p> </o:p></P>
<DIV>
<DIV>
<P class=MsoNormal>On Sun, Mar 26, 2023 at 6:54 PM karsten <<A
href="mailto:karsten@terragis.net" target=_blank>karsten@terragis.net</A>>
wrote:<o:p></o:p></P></DIV>
<BLOCKQUOTE
style="BORDER-TOP: medium none; BORDER-RIGHT: medium none; BORDER-BOTTOM: medium none; PADDING-BOTTOM: 0in; PADDING-TOP: 0in; PADDING-LEFT: 6pt; MARGIN-LEFT: 4.8pt; BORDER-LEFT: #cccccc 1pt solid; PADDING-RIGHT: 0in; MARGIN-RIGHT: 0in">
<DIV>
<DIV>
<P class=MsoNormal><SPAN
style='FONT-SIZE: 10pt; FONT-FAMILY: "Arial",sans-serif; COLOR: navy'>Hi
All,</SPAN><o:p></o:p></P></DIV>
<DIV>
<P class=MsoNormal> <o:p></o:p></P></DIV>
<DIV>
<P class=MsoNormal><SPAN
style='FONT-SIZE: 10pt; FONT-FAMILY: "Arial",sans-serif; COLOR: navy'>I have
two tables, a parcel (polygon) table and a building
(polygon) table in a PostGIS db. <BR>There are about 1 million
parcels and over a million buildings in these tables
...</SPAN><o:p></o:p></P></DIV>
<DIV>
<P class=MsoNormal><SPAN
style='FONT-SIZE: 10pt; FONT-FAMILY: "Arial",sans-serif; COLOR: navy'>I have
set gist indexes on both and clustered each on a geohash, pkey is set
on objectid.</SPAN><o:p></o:p></P></DIV>
<DIV>
<P class=MsoNormal><SPAN
style='FONT-SIZE: 10pt; FONT-FAMILY: "Arial",sans-serif; COLOR: navy'>I
would like to fill 2 additional columns for the parcels layer to have the
number of building on each parcel (building_count) and the summed up
area of all those building footprints
(building_fp).</SPAN><o:p></o:p></P></DIV>
<DIV>
<P class=MsoNormal><SPAN
style='FONT-SIZE: 10pt; FONT-FAMILY: "Arial",sans-serif; COLOR: navy'>Below is an
update query but it is extremely lengthy and inefficient (on a beefy 16
core Ubuntu server with 128 GB tons of RAM) : I had to stop an after
running 3 days when it was not finished ...</SPAN><o:p></o:p></P></DIV>
<DIV>
<P class=MsoNormal><SPAN
style='FONT-SIZE: 10pt; FONT-FAMILY: "Arial",sans-serif; COLOR: navy'>I am
wondering if anyone has an idea how to make it more efficient
?</SPAN><o:p></o:p></P></DIV>
<DIV>
<P class=MsoNormal> <o:p></o:p></P></DIV>
<DIV>
<P class=MsoNormal><SPAN
style='FONT-SIZE: 10pt; FONT-FAMILY: "Arial",sans-serif; COLOR: navy'>This
is the query:</SPAN><o:p></o:p></P></DIV>
<DIV>
<P class=MsoNormal> <o:p></o:p></P></DIV>
<DIV>
<P class=MsoNormal><SPAN
style='FONT-SIZE: 10pt; FONT-FAMILY: "Arial",sans-serif; COLOR: maroon'>UPDATE
parcels p SET<BR>building_count = subquery.num,<BR>building_fp =
subquery.sumarea<BR>from<BR>(select objectid, count(*) as num,
sum(st_area(b.geom)) as sumarea FROM parcels p<BR>left join buildings b on
st_intersects(p.geom,st_centroid(b.geom)) group by objectid )
subquery<BR>where p.objectid = subquery.objectid
;</SPAN><o:p></o:p></P></DIV>
<DIV>
<P class=MsoNormal> <o:p></o:p></P></DIV>
<DIV>
<P class=MsoNormal><SPAN
style='FONT-SIZE: 10pt; FONT-FAMILY: "Arial",sans-serif; COLOR: navy'>Any
suggestions ?</SPAN><o:p></o:p></P></DIV>
<DIV>
<P class=MsoNormal> <o:p></o:p></P></DIV>
<DIV>
<P class=MsoNormal><SPAN
style='FONT-SIZE: 10pt; FONT-FAMILY: "Arial",sans-serif; COLOR: navy'>Cheers</SPAN><o:p></o:p></P></DIV>
<DIV>
<P class=MsoNormal><SPAN
style='FONT-SIZE: 11pt; FONT-FAMILY: "Verdana",sans-serif'>Karsten
Vennemann</SPAN><o:p></o:p></P></DIV></DIV>
<P
class=MsoNormal>_______________________________________________<BR>postgis-users
mailing list<BR><A href="mailto:postgis-users@lists.osgeo.org"
target=_blank>postgis-users@lists.osgeo.org</A><BR><A
href="https://lists.osgeo.org/mailman/listinfo/postgis-users"
target=_blank>https://lists.osgeo.org/mailman/listinfo/postgis-users</A><o:p></o:p></P></BLOCKQUOTE></DIV></DIV>
<P
class=MsoNormal>_______________________________________________<BR>postgis-users
mailing list<BR><A href="mailto:postgis-users@lists.osgeo.org"
target=_blank>postgis-users@lists.osgeo.org</A><BR><A
href="https://lists.osgeo.org/mailman/listinfo/postgis-users"
target=_blank>https://lists.osgeo.org/mailman/listinfo/postgis-users</A><o:p></o:p></P></BLOCKQUOTE></DIV></DIV></DIV></BODY></HTML>