Hi, One big difference between spatialite and PostGIS is that PostGIS is able to use spatial indexes transparently, while spatialite cannot.
Could you try with something like " ... AND lie._search_frame_ = tbap.geometry" ? On 02/05/2019 11:02, Andreas Neumann wrote: > Hi, > > One of my colleagues created a QGIS project where they loaded a parcel > layer with approx. 24000 polygons (data source Postgis) and another > layer with 3 polygons representing project perimeters of contruction > sites. That second layer is in a Geopackage. > > Now they want to select all parcels that are intersecting the project > perimeters. > > The query is straightforward: > > SELECT lie.nummer_grundstueck, lie.gemeinde, tbap.bemerkungen, lie.geometry > FROM lie_liegenschaft lie, tba_projektperimeter tbap > WHERE ST_Intersects(lie.geometry, tbap.geometry) > > Now the problem is: this query (24000 parcels, 3 project perimeters) is > just incredibly slow: about 40 seconds. In a Postgis view this would be > way less then a second! The worst thing: that virtual layer is updated > with each and every zoom and pan. Is there no way to cache a virtual layer? > > Can I do something to speed up our virtual layer? The parcel layer only > chanes once a month and the perimeters only occasionally, so it would be > best to cache the result within a QGIS session, because there are no > changes in the geometries of both layers expected. > > I understand that virtual layers somehow have to transfer all data > formats (through ogr) into SpatiaLite or SQLite to do the query, but > this is really way too slow for real world usage in my opinion (unless I > do something wrong in our query). > > If you have any ideas how to speed up virtual layers in QGIS it would be > much appreciated. > > Thanks, > Andreas > > > _______________________________________________ > QGIS-Developer mailing list > QGIS-Developer@lists.osgeo.org > List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer > Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer > _______________________________________________ QGIS-Developer mailing list QGIS-Developer@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer