QGIS slow performance with PostgreSQL/PostGIS

Question

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.

Answer 1

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)