1

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

  • 2
    Whenever you have a SQL performance issue, you should always use the SQL engine's "explain plan" mechanism to determine what is actually happening in the query. Including that plan in the question will help others to determine the problem. – Vince May 21 at 12:52
3

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

  • Ok I now sort of understand you. It'd be great if you could help me write the Join statement for my example...? I'll read that link too. – Theo F May 21 at 13:17

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.