0

Normally the following SQL Command takes a couple of minutes:

SELECT 
        AX14004X11003_ID, polygons.objid, points.objid 
from 
    polygons
    join points 
    on ST_Touches(points.shape, polygons.shape) or ST_Within(points.shape, polygons.shape);

After I have updated my PostgreSQL from Version 10 to 13, the same statement with the same database takes 11 hours. Additionally, the PostGIS Version changed from 2.5.1 r17027 to 3.2.0 3.2.0.

Are there any changes in the newer versions that would explain why it takes 11h for this particular command? Does anyone have an idea what the problem might be? The database is exactly the same...

Improve this question
1
  • 1
    Do you have geometry indexes on both tables? Try to rebuild these indexes.
    – RainForest
    Jul 19 at 6:33

1 Answer 1

Reset to default
4

Please check that indexes exists on the table, if does not exist create new index or if they exist, rebuild those index. As upgrading from one version to another would change the index structure sometimes for geometries.

Also, try to run a vacuums clean on the table as well.

Improve this answer
1
  • Thanks for the advice. I will try to do that this week.
    – DGIS
    Jul 19 at 12:13

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.