7

Is there any work around to mitigate the amount of time QGIS takes when loading projects that have large tables from postgis layers . I have tried to turn off this large layers so they are not in use , save , close project and then reopen project hoping that the turned off layers would not load on project start again. But nothing it seems that it will always load the tables in memory even though they are turned off. Any suggestion ??? using 2.8.2 - Wien on windows

3
  • It surprises me a bit that the layers have that much influence. I have layers with millions of geometries and my projects behave fine. It is different though when you want to add layers from the dbase manager for tables that have no GeometryType set. Could that be what you mean? Or could it be that there are some old layer references in your QML file to stores that don't exist anymore?
    – tilt
    Feb 11, 2016 at 19:37
  • As far as I understand when you start a project that has layers with no reference qgis warns you and will not load them, the layers do have geometry types set , It indicates on the bottom bar loading and name of the layer loading plus the he progress bar keeps stainless for wile on every layer that is loading . not sure what your millions are but I'm talking about layers of more than 50 million objects that I display at certain zoom levels only. Feb 11, 2016 at 22:51
  • Could you give us a step by step explanation of what you do and notice? It makes it easier to reproduce. Another thing: can you confirm that pressing ' escape' stops the loading process? Postgis layers that are off should not load data and even when they are on only the geometries that are within the bounding box will be loaded (every time you pan or zoom). I assume the tables are indexed on geometries?
    – tilt
    Feb 12, 2016 at 8:18

2 Answers 2

1

In the Postgis connection settings, selecting the "Don't resolve type of unrestricted columns (GEOMETRY)" checkbox usually helps.

enter image description here

1
  • 2
    Does ticking this option helps for loading layers that are in the layer trees or only for displaying all available layers when connecting to a db? If it is the second case, then it does not really help when opening a project with heavy postgis layers.
    – juminet
    Jan 16, 2019 at 16:25
0

At least as of QGIS 3.18 loading layers from large database tables is still a bit slow (& seems irrespective of how much data the layer view has). Here are a few things that will delegate some of the project loading time to when you use the layers.

  1. Display of layer tree in the UI is not expanded. This should delay loading data.
  2. Set the layers bounding box point to an area with little data.
  3. Ensure db source does not check for key uniqueness as this does not scale with size.
  4. Make sure db source does estimate metadata.
  5. In the db ensure your layer tables have geospatial indices.

In your project.qgs file ensure settings for each layer look like this:

<layer-tree-layer
   providerKey="postgres"
   checked="Qt::Unchecked".        <-- SET
   id="my_table_9aeda51a_d3e9_4eeb_af46_8c577a23ff99"
   name="my_layer_name"
   expanded="0"                    <-- SET
   source="
     dbname='my_db'
     host=localhost
     port=1234
     sslmode=disable
     key='id'
     estimatedmetadata=true        <-- SET
     srid=3347
     type=Point
     checkPrimaryKeyUnicity='0'    <-- SET
     table=&quot;my_schema&quot;.&quot;my_table&quot; (geom)
  "
>

Love to know if there are other tricks one can use.

2
  • Thanks eliangius , in fact I have been using latest versions on Qgis 3x with estimatedmetada true and it apparently Speeds up the table loading time . Dec 28, 2021 at 7:37
  • @JorgeVidinha surely this setting only helps when adding a PostGIS dataset? What about when opening a QGIS project file?
    – Theo F
    Dec 2, 2022 at 21:23

Your Answer

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

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