Bug report #9510

[TopoViewer] improve speed in creation of view layers

Added by Sandro Santilli about 10 years ago. Updated about 10 years ago.

Status:Closed
Priority:Normal
Assignee:Sandro Santilli
Category:DB Manager
Affected QGIS version:master Regression?:No
Operating System: Easy fix?:No
Pull Request or Patch supplied:No Resolution:
Crashes QGIS or corrupts data:No Copied to github as #:18101

Description

The Face geometries and Face centroid layers are views, on creation qgis scans them all to find out about their type and srid.
Both type and srid are known in advance so the creation would be much faster by signalling them directly.

For PostGIS-2.0+ this can be done with tymod, but prior versions would need another way. PostGIS topology was introduced in 2.0 but was available as a custom extension before so I'd like not to loose that support. We could not improve the speed for 1.5, or we could tell qgis provider the SRID and TYPE of a layer. Is that possible, @jef ?

Associated revisions

Revision 73b49867
Added by Sandro Santilli about 10 years ago

Speed up topology loading

When loading topology viewer on a PostGIS-2.0+ database, it includes
type and srid typmod in the view layers, avoiding a full scan of
the datasets.

Fix #9510

Revision 188b373b
Added by Sandro Santilli about 10 years ago

Fix loading of face layers after breaking it with 73b49867

Fix #9510 again

Revision 37015614
Added by Jürgen Fischer about 10 years ago

postgres provider: speedup loading of layers by not verifing srid and geometry if given in the uri (fixes #9510)

Revision 500116bf
Added by Jürgen Fischer about 10 years ago

vector layer: calculate extents lazily (fixes #9510)

Revision 02409e99
Added by Sandro Santilli about 10 years ago

Pass extent of loaded views to the provider via URI

Completes the speed up loading of view layers in TopoViewer.
Finally and definitely fixes #9510.

NOTE: the commit also adds a layer for face's MBR, which serves
the purpose (among others) to allow for easy computing extent.

History

#1 Updated by Sandro Santilli about 10 years ago

  • % Done changed from 0 to 50
  • Assignee set to Sandro Santilli

#2 Updated by Sandro Santilli about 10 years ago

  • Status changed from Open to Closed

#3 Updated by Sandro Santilli about 10 years ago

  • Target version set to Version 2.2
  • % Done changed from 50 to 100
  • Resolution set to fixed/implemented

#4 Updated by Sandro Santilli about 10 years ago

  • Priority changed from Normal to Severe/Regression
  • Status changed from Closed to Reopened

it looks like I broke the face layers, need to further debug this

#5 Updated by Sandro Santilli about 10 years ago

  • Status changed from Reopened to Closed

#6 Updated by Sandro Santilli about 10 years ago

  • Priority changed from Severe/Regression to Normal
  • Resolution deleted (fixed/implemented)
  • % Done changed from 100 to 0
  • Status changed from Closed to Reopened

Reopening again, as the cast to typmod geometry does not provent core from checking again for the type, which makes the loading back to extremely slow :/

src/providers/postgres/qgspostgresconn.cpp: 1166: (retrieveLayerTypes) Retrieving geometry types: SELECT DISTINCT upper(geometrytype("geom")),st_srid("geom") FROM (SELECT "geom" FROM (SELECT face_id, topology.ST_GetFaceGeometry('million_poly_topo1', face_id)::geometry(polygon,25833) as geom FROM "million_poly_topo1".face WHERE face_id > 0
) AS "subQuery_0" LIMIT 100) AS t

#7 Updated by Jürgen Fischer about 10 years ago

  • Status changed from Reopened to Closed

#8 Updated by Sandro Santilli about 10 years ago

  • Status changed from Closed to Reopened

Reopening as it's still slow due to extent query :/
You'd need an few hundred thousand faces topology to tell...
If you're looking for one: http://lists.osgeo.org/pipermail/postgis-devel/2014-January/024099.html

#9 Updated by Sandro Santilli about 10 years ago

Extent seems to be called at VectorLayer initialization:

#0  0x00007ffff035cf77 in __GI_raise (sig=sig@entry=6) at ../nptl/sysdeps/unix/sysv/linux/raise.c:56
#1  0x00007ffff03605e8 in __GI_abort () at abort.c:90
#2  0x00007fffd30322f8 in QgsPostgresProvider::extent (this=0x4ae4110)
    at /usr/src/qgis/qgis-master/src/providers/postgres/qgspostgresprovider.cpp:2390
#3  0x00007ffff4355ca7 in QgsVectorLayer::setDataProvider (this=0x4ae3250, provider=...)
    at /usr/src/qgis/qgis-master/src/core/qgsvectorlayer.cpp:1693
#4  0x00007ffff434caf3 in QgsVectorLayer::QgsVectorLayer (this=0x4ae3250, vectorLayerPath=..., baseName=..., 
    providerKey=..., loadDefaultStyleFlag=true) at /usr/src/qgis/qgis-master/src/core/qgsvectorlayer.cpp:148
#5  0x00007fffcf223763 in sipQgsVectorLayer::sipQgsVectorLayer (this=0x4ae3250, a0=..., a1=..., a2=..., a3=true)
    at /usr/src/qgis/build/master/python/core/sipcorepart2.cpp:111988
#6  0x00007fffcf239758 in init_QgsVectorLayer (sipSelf=0x4199b98, sipArgs=0x42cb190, sipKwds=0x0, 
    sipUnused=0x7fffffffb670, sipParseErr=0x7fffffffb688)
    at /usr/src/qgis/build/master/python/core/sipcorepart2.cpp:120286
#7  0x00007fffd061287a in ?? () from /usr/lib/python2.7/dist-packages/sip.so

#10 Updated by Sandro Santilli about 10 years ago

Dropping the call from QgsVectorLayer::setDataProvider gets me the speed I was trying to obtain. Seconds vs. minutes.

#11 Updated by Jürgen Fischer about 10 years ago

  • Status changed from Reopened to Closed

#12 Updated by Sandro Santilli about 10 years ago

  • % Done changed from 0 to 80
  • Status changed from Closed to Reopened

We're almost there, I still have to make the TopoViewer make the setExtent calls :)

#13 Updated by Sandro Santilli about 10 years ago

  • Status changed from Reopened to Closed

Also available in: Atom PDF