Is there a way to configure QGIS to prevent the st_extent call when adding a PostGIS table?

Question

I'm trying to add an extremely large table with a points geometry field to QGIS and QGIS appears to hang. After some investigating in the postgresql database, I tracked it down to a st_extent on everything in the table.

Is it correct to assume that the reason QGIS does this is in order to obtain a full extent for the layer? And if so, is there a way to bypass this? Maybe either use the spatial reference extent or manually provide the extent?

QGIS 2.6 64-bit
POSTGIS="2.1.3 r12547" GEOS="3.4.2-CAPI-1.8.2 r3924" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.0, released 2013/04/24" LIBXML="2.7.8" LIBJSON="UNKNOWN" TOPOLOGY RASTER
PostgreSQL 9.3.5, compiled by Visual C++ build 1600, 64-bit

Answer 1

Have a look at the settings of PostGIS connection. There is a selection "Use estimated metadata" that is unselected by default. Check it and QGIS will use ST_Estimated_Extent http://postgis.net/docs/manual-2.0/ST_Estimated_Extent.html. Remember to run VACUUM ANALYZE before.

Answer 2

The "use estimated metadata" option on the connection works for newly created layers, but it doesn't change it on existing layers that connect to that database, nor did I find any way to change it in the layer properties (I'm using 3.8.3). I found it relatively quick to change all my existing layers by editing the QGS file to add estimatedmetadata=true to all PostGIS data source descriptions.

Here are the steps I followed using the Linux terminal. Similar steps can be done in any OS, as long as you have a way to search and replace text.

1. Extract QGS file from the QGZ:

   $ unzip map.qgz map.qgs
   

2. Add estimatedmetadata=true to all PostGIS data source descriptions in the QGS. In my particular file, I found these in XML <datasource> and <layer-tree-layer> tags.

   For my particular file, I checked and saw that all such lines had no estimatedmetadata=, so rather than switching estimatedmetadata=false to estimatedmetadata=true, I had to add in a new estimatedmetadata=true. I looked for a string that was in the middle of all of the data source descriptions, so I could do the equivalent of a search and insert by using a text replace. The string I found in my file was checkPrimaryKeyUnicity=, so I ran the following command:

   $ sed -i 's/checkPrimaryKeyUnicity/estimatedmetadata=true checkPrimaryKeyUnicity/g' map.qgs
   

   You may have to adapt this to the details of your specific file, make the changes in a more manual way, or use a tool with XML-specific editing features.

3. Replace the QGS file in the QGZ:

   $ zip -u map.qgz map.qgs