Feature request #14356

Improve postgis topology loading performance

Added by Sandro Santilli over 8 years ago. Updated over 8 years ago.

Status:Closed
Priority:Normal
Assignee:Sandro Santilli
Category:DB Manager
Pull Request or Patch supplied:No Resolution:fixed/implemented
Easy fix?:No Copied to github as #:22340

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

#1 Updated by Sandro Santilli over 8 years ago

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

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

#2 Updated by Sandro Santilli over 8 years ago

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

#3 Updated by Sandro Santilli over 8 years 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?)

#4 Updated by Sandro Santilli over 8 years ago

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

#5 Updated by Sandro Santilli over 8 years 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 PDF