Following my comment I run a simple test on OSM data (osm2pgsql import; 1.7M polygons on 1M points; 340k updates);
Running (the equivalent to)
lim AS (
FROM Build_poly AS bp
JOIN LATERAL (
FROM Flood_poly AS fp
WHERE ST_Intersects(fp.geom, bp.geom)
) AS q
UPDATE Build_poly AS bp
SET Defenses = 1
WHERE bp.<id> = lim.<id>
finished about 3 times faster.
LATERAL JOIN finds only one match per row in
Build_poly, since you can use
LIMIT 1 in the inner query. The
UPDATE will then be executed on comparing the
<id> column (make sure that column of yours has an index in place).
You mentioned in the comments this approach is 5 times slower than yours and I think, at this point, it's no matter of query design, it's the resource demand that's slowing down; an
UPDATE in general is a resource heavy operation, and adding a
LATERAL JOIN, with a sub-query execution for each row in memory, will enlarge the resource footprint quickly to a point where PG will need to work with inefficient intermediate/temporary files on disk (this might as well happen with your query, albeit not as extensive maybe). From my experience and following a suggestion I read years ago, creating a new table with the updates and dropping the old one instead usually is the better idea.
leave it be, let it run overnight and put a trigger in place to update any new inserts on the spot
create a new table with a foreign key each on the primary key of both your polygon tables and the
Defense column only - it's a relational database after all...
- create a separate table with centroids (
ST_PointOnSurface, depending on your polygons) of your
Flood-poly table to run the intersection against
- try optimizing your PostgreSQL setup, e.g.
work_mem etc. - this is where I usually head over to DBA.SE (you could even migrate this question)