<html 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" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=iso-8859-2"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><!--[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 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:Verdana;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle18
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
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 link=blue vlink=purple><div class=WordSection1><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Okay that is old version, but I think the fixes were done like 6 or 7 years ago such as detailed here:<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><a href="https://www.postgresql.org/message-id/011f01c61653$96c509f0$8300a8c0@tridecap.com">https://www.postgresql.org/message-id/011f01c61653$96c509f0$8300a8c0@tridecap.com</a><o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>As far as if a inner join is always better than a left join, I would say no.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>For example here is one that showed at least for this use case, a LEFT JOIN was faster:<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><a href="https://towardsdatascience.com/how-we-optimized-postgresql-queries-100x-ff52555eabe">https://towardsdatascience.com/how-we-optimized-postgresql-queries-100x-ff52555eabe</a><o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>And here where it shows a INNER + UNION ALL was faster than a LEFT<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><a href="https://www.crunchydata.com/blog/postgres-query-optimization-left-join-vs-union-all">https://www.crunchydata.com/blog/postgres-query-optimization-left-join-vs-union-all</a><o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Since you have to update every single parcel anyway, I am curious how something like this would fair:<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial",sans-serif;color:maroon'>UPDATE parcels p SET<br>(building_count, building_fp) = <br>(select count(*) as num, sum(st_area(b.geom)) as sumarea FROM buildings b on st_intersects(p.geom,st_centroid(b.geom) ) ;<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial",sans-serif;color:maroon'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial",sans-serif;color:maroon'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial",sans-serif;color:maroon'>I suspect it would be worse, but curious.<o:p></o:p></span></p><p class=MsoNormal> <o:p></o:p></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><div style='border:none;border-left:solid blue 1.5pt;padding:0in 0in 0in 4.0pt'><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal><b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>From:</span></b><span style='font-size:11.0pt;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 6:24 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:10.0pt;font-family:"Arial",sans-serif;color:navy'>Regina,</span><o:p></o:p></p><p class=MsoNormal> <o:p></o:p></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial",sans-serif;color:gray'> >>>> </span><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:gray'>So you had the centroids indexed before you switched to regular join and that took days too?</span><span style='font-size:10.0pt;font-family:"Arial",sans-serif;color:gray'> </span><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:gray'>I would expect the index on the centroid to make much more of a difference than the LEFT / INNER JOIN change.</span><span style='font-size:10.0pt;font-family:"Arial",sans-serif;color:navy'> </span><o:p></o:p></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial",sans-serif;color:navy'>No at that time I had not the centroids indexed just the polygon boundaries themselves</span><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><span style='font-size:10.0pt;font-family:"Arial",sans-serif;color:navy'> ...</span><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial",sans-serif;color:gray'> >> </span><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:gray'>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.</span><span style='font-size:10.0pt;font-family:"Arial",sans-serif;color:gray'> <br></span><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:gray'>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></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:gray'>It might be some left over stuff with our poor spatial stats estimates.</span><span style='font-size:10.0pt;font-family:"Arial",sans-serif;color:gray'> >>></span><o:p></o:p></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial",sans-serif;color:navy'>Interesting</span><o:p></o:p></p><p class=MsoNormal> <o:p></o:p></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial",sans-serif;color:gray'>>> </span><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>SELECT version(), postgis_full_version();<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:gray'>shows:</span><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p></o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial",sans-serif;color:navy'>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)"</span><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p></o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial",sans-serif;color:navy'> </span><o:p></o:p></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial",sans-serif;color:navy'>Cheers</span><o:p></o:p></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial",sans-serif;color:navy'>Karsten</span><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> <o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><div style='border:none;border-left:solid blue 1.5pt;padding:0in 0in 0in 4.0pt'><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal><b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>From:</span></b><span style='font-size:11.0pt;font-family:"Calibri",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>karsten<br><b>Sent:</b> Monday, March 27, 2023 5:16 PM<br><b>To:</b> 'PostGIS Users Discussion' <<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>><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:10.0pt;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:10.0pt;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:10.0pt;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:10.0pt;font-family:"Arial",sans-serif;color:navy'>Cheers</span><o:p></o:p></p><p class=MsoNormal><span style='font-size:10.0pt;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 align=center style='text-align:center'><hr size=2 width="100%" align=center></div><p class=MsoNormal style='margin-bottom:12.0pt'><b><span style='font-size:10.0pt;font-family:"Tahoma",sans-serif'>From:</span></b><span style='font-size:10.0pt;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:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0in;margin-bottom:5.0pt'><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:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0in;margin-bottom:5.0pt'><div><div><p class=MsoNormal><span style='font-size:10.0pt;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:10.0pt;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:10.0pt;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:10.0pt;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:10.0pt;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:10.0pt;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:10.0pt;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:10.0pt;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:10.0pt;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:10.0pt;font-family:"Arial",sans-serif;color:navy'>Cheers</span><o:p></o:p></p></div><div><p class=MsoNormal><span style='font-size:11.0pt;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></div></body></html>