PostGIS: Running multiple intersections within a single SQL query?

Question

PostgreSQL 11 + PostGIS, on Windows 10.

I'm working with a PostGIS-enabled database. I want to UPDATE a large polygon table (100,000+ rows) WHERE the geometry of that host table intersects the geometry of another table (table a) AND another table (table b). 'Table a' also has a WHERE field = value attribute filter placed on it.

Using this statement, it takes an indefinte amount of time, seemingly never to complete (I even left it overnight and it still didn't finish):

UPDATE schema1.polygontable poly 
SET ref_id = '5z' 
FROM schema2.tablea a, schema3.tableb b 
WHERE (ST_DWITHIN(a.geom, poly.geom,0) AND (a.type = 'Ground')) OR (ST_DWITHIN(b.geom, poly.geom,0));

However, If I split the query at the OR clause, the now separate 2 functions take 5 seconds and 1 second respectively:

UPDATE schema1.polygontable poly 
SET ref_id = '5z' 
FROM schema2.tablea a 
WHERE ST_DWITHIN(a.geom, poly.geom,0) AND (a.type = 'Ground');

and

UPDATE schema1.polygontable poly 
SET ref_id = '5z' 
FROM schema3.tableb b 
WHERE ST_DWITHIN(b.geom, poly.geom,0);

Nb: all three tables have a spatial index each. And they are all hosted on an Amazon AWS instance.

So, why does my query freeze/hang when I combine the functions? My syntax looks fine I think

Answer 1

The query is taking a long time because it is doing a cross products between tables a and b (and poly). So if poly has 1 polygon only, and both a and b have 1,000,000 features, you end up computing st_DWithin 1,000,000,000,000 times.

You can try to combine the two update using a join instead. You may have to use a subquery to achieve it though, as described in this post