0

QGIS 3.18 PostgreSQL 14.

We use QGIS to access data from a PostGIS server. The data are stored in 33 schemas.

  • one contains all the data in 17 tables;
  • the others schemas contain views and materialized views created from the first schema as needed
  • There are 485 objects (ordinary tables, views and materialized views)
  • autovacuum and track_counts are on by default.

The "problem" is that it takes at least around 4 minutes to list all the schemas in the PostgreSQL connection window of QGIS when we want to connect to the database.
The same behaviour can be seen when using the export to PostgreSQL (available connections) plugin : after we choose the database, the list of the schemas takes a very long time to appear.
I tried to rise shared_buffers parameter from 128 to 256MB. I did not see any effect.

Is there a way in PostgreSQL to reduce this time ?

EDIT:
As requested, here is a link to the query plan : https://explain.dalibo.com/plan/83d5f515345c5aa8#plan/node/354

Improve this question
9
  • 1
    How do you list all the schemas? Do you use a client application? Normally listing schemas means running the statement "select * from pg_namespace ".
    – Sahap Asci
    May 24 at 10:48
  • Regular users use QGIS desktop to connect to the database. So they use the proper connection window from datasource manager.
    – Leehan
    May 24 at 11:13
  • Very likely the time needed doesn't come from postgres. Calling 33 schemas and 17 tables is far from being in any way resource-intensive. Try to list these from the command line of the database. This shouldn't take more than 3s under very bad conditions.
    – MaestroGlanz
    May 24 at 12:51
  • Try to measure the time exactly. If it is very close to a multiple of 30s, it is very likely a faulty timeout.
    – MaestroGlanz
    May 24 at 12:52
  • This questions seems to be about QGIS desktop, not really about PostgreSQL server.
    – jjanes
    May 24 at 13:53

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.