I am running QGIS Desktop 3.18.2 on Ubuntu, but I have observed this issue on other operating systems.

When I query a relatively small table (~300 simple polygons) from PostGIS on a remote server, it takes time to load the data as a layer through the DB Manager. When the data are loaded as a "QueryLayer", zooming in and out becomes so painfully slow that data exploration becomes virtually impossible.

However, when I save the QueryLayer as a geojson file to my local drive, rendering becomes lightning fast and the issue is fixed. Is it expected behavior that data originating from external database connections render slowly? If read/write operations are not being performed, why is rendering so slow on datasets that have been fully uploaded (in other words, when the read operation has finished)? Is there a setting in QGIS that would bring the layer into local memory and alleviate the need for saving layers as local files?

  • 4
    I think that every time you pan or zoom the query is run again. I usually create a temporary table and then delete when I'm done (create table abc_temp as) – BERA Apr 30 at 5:29
  • make sure that the table has a spatial index – JGH Apr 30 at 12:56
  • It could depend on the speed of the server hosting your database or the connection speed between your computer and DB. – Corentin Lemaitre Apr 30 at 21:39
  • You could also try creating a materialized view from your query. Like commented earlier, the query is running every time you do something on your project which makes it very slow. – tjukanov May 3 at 7:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.