And you can: create index buildings_centroid_idx on buildings using gist (ST_Centroid(buildings.geom))
In my test case it yields another 10x improvement. pon., 27 mar 2023 o 08:33 Marcin Mionskowski <[email protected]> napisał(a): > 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
