Performance Tips

This section features a checklist for your published data / project to ensure Mappin performs at its best. Always ensure your data and project meet the requirements laid out in this section.

Layer Constraints

When QGIS loads a PostGIS layer, it needs to check:

  • What type of geometry (e.g. Point or Polygon) it will be displaying
  • That there’s a column with unique integer values

If QGIS cannot be sure of this information based on the layer’s constraints, it will have to do a full scan of the relevant table columns, ensuring that there’s a column with unique integer values and also checking each feature’s geometry type.

To help QGIS, we can ensure the layer has appropriate constraints set. For example, if QGIS sees that a layer has a geometry constraint of type MultiPolygon, it knows the layer’s geometry without needing to scan it. To check a PostGIS layer’s constraints, locate it in the database using pgAdmin. Select the table in question and select the SQL tab to display detailed information:

_images/pgadmin_table.png

First let’s check there’s a PRIMARY KEY constraint referencing an integer column:

_images/primary_key.png

Next ensure the geometry column (having type geometry) explicitly defined a geometry type (e.g. MultiPolygon) and that the SRID is also defined there (e.g. 27700):

_images/geometry_type.png

Indices

In order for the database to quickly return features requested by Mappin, indices should be defined, the most important being the spatial index.

To check for the existence of a spatial index, examine the table as described in the previous step and ensure there’s an index defined using the gist method on the table’s geometry column:

_images/spatial_index.png

If you publish database views as layers via Mappin, you should also ensure indices are defined for columns included in any WHERE clauses.

checkPrimaryKeyUnicity

As described above, when loading a PostGIS layer with a primary key on an integer column, QGIS should not need to scan the whole table to ensure the values are unique. Unfortunately in some versions, QGIS still does this regardless. If you hover over a loaded layer, you should see checkPrimaryKeyUnicity=‘0’.

If you instead see this parameter set to ‘1’ as shown below, QGIS is still doing a full table scan when it loads the layer:

_images/checkprimarykeyunicity.png

If you are confident that all the PostGIS layers you reference in a QGIS project have a primary key as described in previous sections, then you may do a global search and replace in the .qgs file to ensure checkPrimaryKeyUnicity is set to ‘0’.

Ordnance Survey Zoomstack

OS Zoomstack is a national UK background map dataset. Although a large dataset, good performance can be achieved by following the guide in the Using Ordnance Survey Zoomstack section.