[QGIS-Developer] QGIS virtual layer incredibly slow

[Andreas Neumann]

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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-developer/attachments/20190502/1088ba95/attachment.html>