How to run multiple ST_DWITHINs/ ST_INTERSECTS within a single WHERE clause

Question

PostgreSQL 11, on Windows 10.

I'm working with a post-gis 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, seemlingly 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

Your FROM clause creates an unwanted cross join between the tables. This is not related to PostGIS and geometric functions.

FROM schema2.tablea a, schema3.tableb b 

Indeed as suggested, use two separate UPDATE queries, one for each table. When you compare the EXPLAIN commands, you should see 2 nested loops for the big query instead of one for the alternative solution with two small queries.