In my "explain tests", made on much smaller tables, I get 500 times smaller costs only by changing LEFT JOIN to "regular" JOIN.
pon., 27 mar 2023 o 01:07 Raj Talati <[email protected]> napisał(a): > 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. > 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 > > Hope this helps . > > Raj T > > On Sun, Mar 26, 2023 at 6:54 PM karsten <[email protected]> wrote: > >> Hi All, >> >> I have two tables, a parcel (polygon) table and a building >> (polygon) table in a PostGIS db. >> There are about 1 million parcels and over a million buildings in these >> tables ... >> I have set gist indexes on both and clustered each on a geohash, pkey is >> set on objectid. >> 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). >> 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 ... >> I am wondering if anyone has an idea how to make it more efficient ? >> >> This is the query: >> >> UPDATE parcels p SET >> building_count = subquery.num, >> building_fp = subquery.sumarea >> from >> (select objectid, count(*) as num, sum(st_area(b.geom)) as sumarea FROM >> parcels p >> left join buildings b on st_intersects(p.geom,st_centroid(b.geom)) group >> by objectid ) subquery >> where p.objectid = subquery.objectid ; >> >> Any suggestions ? >> >> Cheers >> Karsten Vennemann >> _______________________________________________ >> postgis-users mailing list >> [email protected] >> https://lists.osgeo.org/mailman/listinfo/postgis-users >> > _______________________________________________ > postgis-users mailing list > [email protected] > https://lists.osgeo.org/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users
