10

We experience slow performance in QGIS 3.xx (also tried with 3.20 with no noticable improvement). Data are stored in a PostgreSQL/PostGIS database and we have done enough testing that we do not believe that the database is the problem.

The slowness shows its ugly face when for instance the program freezes for a long time when performing even simple tasks, like e.g. saving the project or moving the map in the window.

The issue increases with the size of the project and we don't experience any noticable slowness in small to medium sized projects. The project I tested with most recently was about 4-5 MB when saved in QGIS 3 format (qgz) and about 40 MB when saved unzipped (qgs).

The project contains perhaps +/-100 vector layers from PostgreSQL/PostGIS data and a few online layers (basemap, etc.). Has anybody encountered anything similar and perhaps even found the reason and a solution? Or is anybody aware of some software that could help debug/analyze QGIS to find out what takes all that time?

As implied earlier we have tried and tested a lot including running QGIS on the same physical server as the database to eliminate network issues.

Improve this question
2
  • 1
    To debug this, you must look at what is being executed on the DB during slow operations and then find the corresponding QGIS command/action that triggers it
    – JGH
    Commented Aug 18, 2021 at 13:40
  • Where is your Postgis vs the map software? My worst experience has been with a pg instance on Amazon RDS. pg on the local machine is usually excellent. Installing a local pg db is not too difficult in the interests of troubleshooting. Online layers can also be a big culprit and there are too many scenarios to provide any guidance.
    – BJW
    Commented Apr 30, 2022 at 13:10

1 Answer 1

Reset to default
16

This is a broad question... here are a few things I have gathered through the years:

  1. Make sure the geom/geog column is using a single type (ex avoid geometry but instead use geometry(point,4326)). For views, MV, use a cast (select geom::geometry(point,4326) as geom from...)
  2. Add the layer using estimated metadata. Else, on every load and save, the layer extent is computed using st_extent which does a seq scan on the entire layer (SLOW!). For new layers, tick the “estimated metadata” on the connection property (should be done once only). For existing projects, open the QGS file and transform <datasource>dbname=... to <datasource>estimatedmetadata=true dbname=... (ref)
  3. Add spatial indexes
  4. Add attribute indexes on the fields used to filter the layer
  5. Add attributes indexes on the fields used for styling (actual benefit of this step is not clear)
Improve this answer

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