Postgres/PostGIS skip intersect on features already intersected

Question

I have a collection of polygons(building outlines called "Build_poly") that I am intersecting with another table of flood defenses (also polygons called Flood_poly). When ever one of the building polygons intersect with a flood defense polygon I am updating the "defense" field of building polygon from 0 to 1.

What I have been trying to do to speed up my query is put in a condition where: if a building_poly has successfully intersected with a flood polygon for it to skip trying to intersect with any other flood defense polygons. This is what I've got so far, but I haven't managed to get any form of skip to work as this is my first trying to use postgresql.

UPDATE Build_poly
SET Defenses = 1
FROM Flood_poly
WHERE ST_Intersects(Build_poly.geom,Flood_poly.geom)"

Answer 1

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)

WITH
  lim AS (
    SELECT <id>
    FROM Build_poly AS bp
    JOIN LATERAL (
      SELECT 1
      FROM Flood_poly AS fp
      WHERE ST_Intersects(fp.geom, bp.geom)
      LIMIT 1
    ) AS q
    ON true
  )

UPDATE Build_poly AS bp
  SET Defenses = 1
  FROM lim
  WHERE bp.<id> = lim.<id>

finished about 3 times faster.

Here, the 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).

---

Update:

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.

Some thoughts:

• 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_Centroid or 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)

Answer 2

Why not only test buildings that doesn't already have Defenses = 1

UPDATE Build_poly
SET Defenses = 1
FROM Flood_poly
WHERE Build_poly.Defenses != 1 and ST_Intersects(Build_poly.geom,Flood_poly.geom);

The planner will check the integer test first since that test has a lower cost than the intersects check.

Also be sure to have working indexed both on Build_poly.Defenses and on the geometry columns.

If your flood poly table is static you can also add a field that keeps information about if the test already is done. That way you will not have to recheck buildings outside flood_polygons again either.

alter table Build_poly add column already_checked int default 0;
     UPDATE Build_poly
    SET Defenses = 1
    FROM Flood_poly
    WHERE Build_poly.already_checked = 0 and ST_Intersects(Build_poly.geom,Flood_poly.geom);

A third way might be to add a trigger to the Build_poly table which only fires on the row that gets inserted or updated. Then put the test in a row level trigger and do the update from there.