0

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.

  • Please show explain (analyze, buffers) for the statements that finish, and explain for the one that does not.. – jjanes May 21 at 14:08
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.