I have a QGIS project with several Geopackage-Layers. The project is quite slow in comparison to a project with shapefile or database layers. I started QGIS together with DebugView to find out what happens and recognised that every time I zoom in/out or select features QGIS makes a SQL select which queries every feature of the layers (Select * from...):

[248] ..\..\..\..\..\..\src\providers\ogr\qgsogrprovider.cpp(3504) : (QgsOgrProviderUtils::setSubsetString) [7000ms] SQL: SELECT *, fid as orig_ogc_fid FROM "lines" WHERE layer IN ('Verm. Randsteine und Belag') AND space=0 AND block=-1

I opened the file with SQLiteDatabaseBrowser to check if a spatial index exists and found a table "rtree_lines_geom" which seems to be used as a index:

CREATE VIRTUAL TABLE "rtree_lines_geom" USING rtree(id, minx, maxx, miny, maxy)

Is this a bug that QGIS always selects all features from the table instead of using an index?

  • Each layer needs its own spatial index. What you have found is the index for table "lines" on column "geom". Do you have also rtree table for table "Verm. Randsteine und Belag"? BTW. you are begging for troubles by using such table names. – user30184 Sep 10 '18 at 9:24
  • 1
    @user30184: the tables/tablenames are the output of the DWG-Importer in QGIS. The features are stored in the layer "lines" and the subset which is loaded in QGIS are the features which have a string 'Verm. Randsteine und Belag' in a column called "layer" – markgraeflerland Sep 10 '18 at 11:15
  • Sorry, should have read the SQL better. Anyway, QGIS should utilize the rtree index when it is available. What if you add the "lines" table as raw without filtering, is zooming fast then? – user30184 Sep 10 '18 at 11:24
  • @user30184: no. the whole layer with 34062 features needs 3771ms, the filtered layer with 143 features needs 968ms. Even if one second for renderung does not sound long it get's slow with lager layers and as I have several Geopackages in the project. – markgraeflerland Sep 10 '18 at 12:50
  • It really seems that spatial index is not utilized. I suppose that you have zoomed well in so that the index can have an effect. Unfortunately it does not help you at all if I say that it works for me with my data. Could you prepare a test database from some non-sensible data with your workflow and put it available? – user30184 Sep 10 '18 at 13:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.