<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=iso-8859-2" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 11.00.10570.1001"></HEAD>
<BODY>
<DIV dir=ltr align=left><FONT color=#000080 size=2 face=Arial><SPAN
class=831290621-27032023>That worked perfectly:<BR>Using just join and setting
the index on the centroids geom as well</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#000080 size=2 face=Arial><SPAN
class=831290621-27032023>I had the query finish in less than a minute (compared
to three day running and not yet finished !)</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#000080 size=2 face=Arial><SPAN
class=831290621-27032023></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#000080 size=2 face=Arial><SPAN
class=831290621-27032023>Does anyone have an explanation why 'regular' join is
so much faster than 'left join' ?</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#000080 size=2 face=Arial><SPAN
class=831290621-27032023></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#000080 size=2 face=Arial><SPAN
class=831290621-27032023>Cheers</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#000080 size=2 face=Arial><SPAN
class=831290621-27032023>Karsten</SPAN></FONT></DIV><BR>
<DIV lang=en-us class=OutlookMessageHeader dir=ltr align=left>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><B>From:</B> postgis-users
[mailto:postgis-users-bounces@lists.osgeo.org] <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<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV dir=ltr>
<DIV class=gmail_default style="FONT-FAMILY: monospace">In my "explain tests",
made on much smaller tables, I get 500 times smaller costs only by changing LEFT
JOIN to "regular" JOIN.<BR></DIV></DIV><BR>
<DIV class=gmail_quote>
<DIV class=gmail_attr dir=ltr>pon., 27 mar 2023 o 01:07 Raj Talati <<A
href="mailto:rajtalati@gmail.com">rajtalati@gmail.com</A>>
napisał(a):<BR></DIV>
<BLOCKQUOTE class=gmail_quote
style="PADDING-LEFT: 1ex; BORDER-LEFT: rgb(204,204,204) 1px solid; MARGIN: 0px 0px 0px 0.8ex">
<DIV>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.</DIV>
<DIV>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 </DIV>
<DIV><BR></DIV>
<DIV>Hope this helps .</DIV>
<DIV><BR></DIV>
<DIV>Raj T</DIV>
<DIV><BR>
<DIV class=gmail_quote>
<DIV class=gmail_attr dir=ltr>On Sun, Mar 26, 2023 at 6:54 PM karsten <<A
href="mailto:karsten@terragis.net" target=_blank>karsten@terragis.net</A>>
wrote:<BR></DIV>
<BLOCKQUOTE class=gmail_quote
style="PADDING-LEFT: 1ex; BORDER-LEFT: rgb(204,204,204) 1px solid; MARGIN: 0px 0px 0px 0.8ex"><U></U>
<DIV>
<DIV><FONT style="FONT-FAMILY: Arial; COLOR: rgb(0,0,128)" size=2
face=Arial><SPAN style="FONT-FAMILY: Arial">Hi All,</SPAN></FONT></DIV>
<DIV><FONT style="FONT-FAMILY: Arial; COLOR: rgb(0,0,128)" size=2
face=Arial><SPAN style="FONT-FAMILY: Arial"></SPAN></FONT> </DIV>
<DIV><FONT style="FONT-FAMILY: Arial; COLOR: rgb(0,0,128)" size=2
face=Arial><SPAN style="FONT-FAMILY: Arial">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></FONT></DIV>
<DIV><FONT style="FONT-FAMILY: Arial; COLOR: rgb(0,0,128)" size=2
face=Arial><SPAN style="FONT-FAMILY: Arial">I have set gist indexes on both
and clustered each on a geohash, pkey is set on
objectid.<BR></SPAN></FONT></DIV>
<DIV><FONT style="FONT-FAMILY: Arial; COLOR: rgb(0,0,128)" size=2
face=Arial><SPAN style="FONT-FAMILY: Arial">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></FONT></DIV>
<DIV><FONT style="FONT-FAMILY: Arial; COLOR: rgb(0,0,128)" size=2
face=Arial><SPAN style="FONT-FAMILY: Arial">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></FONT></DIV>
<DIV><FONT style="FONT-FAMILY: Arial; COLOR: rgb(0,0,128)" size=2
face=Arial><SPAN style="FONT-FAMILY: Arial">I am wondering if anyone has an
idea how to make it more efficient ?</SPAN></FONT></DIV>
<DIV><FONT style="FONT-FAMILY: Arial; COLOR: rgb(0,0,128)" size=2
face=Arial><SPAN style="FONT-FAMILY: Arial"></SPAN></FONT> </DIV>
<DIV><FONT style="FONT-FAMILY: Arial; COLOR: rgb(0,0,128)" size=2
face=Arial><SPAN style="FONT-FAMILY: Arial">This is the
query:</SPAN></FONT></DIV>
<DIV><FONT style="FONT-FAMILY: Arial; COLOR: rgb(0,0,128)" size=2
face=Arial><SPAN style="FONT-FAMILY: Arial"></SPAN></FONT> </DIV>
<DIV><FONT style="FONT-FAMILY: Arial; COLOR: rgb(128,0,0)" size=2
face=Arial><SPAN style="FONT-FAMILY: Arial">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></FONT></DIV>
<DIV><FONT style="FONT-FAMILY: Arial; COLOR: rgb(0,0,128)" size=2
face=Arial></FONT> </DIV>
<DIV><SPAN><FONT style="FONT-FAMILY: Arial; COLOR: rgb(0,0,128)" size=2
face=Arial>Any suggestions ?</FONT></SPAN></DIV>
<DIV><SPAN><FONT style="FONT-FAMILY: Arial; COLOR: rgb(0,0,128)" size=2
face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN><FONT style="FONT-FAMILY: Arial; COLOR: rgb(0,0,128)" size=2
face=Arial>Cheers</FONT></SPAN></DIV>
<DIV align=left>
<DIV><SPAN style="FONT-SIZE: 11pt; FONT-FAMILY: Verdana,sans-serif">Karsten
Vennemann<BR></SPAN></DIV></DIV></DIV>_______________________________________________<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" rel=noreferrer
target=_blank>https://lists.osgeo.org/mailman/listinfo/postgis-users</A><BR></BLOCKQUOTE></DIV></DIV>_______________________________________________<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" rel=noreferrer
target=_blank>https://lists.osgeo.org/mailman/listinfo/postgis-users</A><BR></BLOCKQUOTE></DIV></BODY></HTML>