Feature request #14356

Improve postgis topology loading performance

Added by Sandro Santilli about 1 year ago. Updated about 1 year ago.

Status:Closed Start Date:02/22/2016
Priority:Normal Due date:
Assigned to:Sandro Santilli % Done:

0%

Category:DBManager
Target version:Version 2.14
Platform: Resolution:fixed/implemented
Platform version: Pull Request or Patch supplied:No
Status info: Tag:

Description

The face layers are currently triggering creation of all face geometries upfront, which is very very expensive.
GDB found qgis busy in this query:

SELECT DISTINCT upper(geometrytype("geom")),st_srid("geom"),st_ndims("geom") FROM (SELECT face_id, ST_PointOnSurface(topology.ST_GetFaceGeometry('rt09_wgs84_topo', face_id)) as geom FROM "rt09_wgs84_topo".face WHERE face_id > 0) AS "subQuery_0" 

The query seem so be aiming at figuring geometry type, srid and number of dimensions from the query.

The provider URI already contained geometry type and srid information (but no dimension, if that matters):

URI: dbname='rt' port=5493 sslmode=disable key='face_id' srid=3003 type=Polygon table="(SELECT face_id,
              topology.ST_GetFaceGeometry('rt09_wgs84_topo', face_id) as geom FROM \"rt09_wgs84_topo\".face WHERE face_id > 0

This ticket is to find a way to avoid the upfront full scan, as the layer is intentionally started invisible specifically to avoid the full scan.

BTW, another scan might be needed to compute extent but that also could be passed by the layer creator (dbmanager).

History

Updated by Sandro Santilli about 1 year ago

  • Status changed from Open to In Progress
  • Assigned to changed from Jürgen Fischer to Sandro Santilli

Jürgen Fischer suggested to look at 'setUseEstimatedMetadata':
http://qgis.org/api/classQgsDataSourceURI.html#aa93f799954aee0467b7ed9fa41daecff

Updated by Sandro Santilli about 1 year ago

  • Status changed from In Progress to Closed
  • Resolution set to fixed/implemented

Updated by Sandro Santilli about 1 year ago

Just a note: all the commit does is setting a type-mod based constraint on the geometry column of the query to specify a type and a srid. As the URI already contained both the type and the srid information, I'm wondering if a regression is really hiding here (why should the provider NOT TRUST the uri info?)

Updated by Sandro Santilli about 1 year ago

Could the problem be related to the deprecation of setWkbType (QGis::WkbType type) ?

Updated by Sandro Santilli about 1 year ago

976015a38dedb22c16a84cf8c741d052905f20b9 fixes the previous.

While the performances are better, there's still a full primary key column scan.
I'll file a separate ticket for that.

Also available in: Atom