[QGIS-Developer] QGIS virtual layer incredibly slow

[Andreas Neumann]

Hi Hugo,

Indeed  - when I add the test against the _search_frame_ - everthing 
works almost equally fast than within Postgis. Certainly now the virtual 
layer suddenly becomes useful and fast enough for us.

But to be honest - I would never have guessed that. I was searching for 
the "&&" operator that was necessary in the old days of Postgis (the 
bounding box test). Is this something similar?

Perhaps there should be a visible hint for that in the Virtual layer 
creation dialogue? Googling for it reveals a section in the manual ;-) 
https://docs.qgis.org/3.4/en/docs/user_manual/managing_data_source/create_layers.html#use-of-indexes

Would be useful to add a button to the dialogue - a button like 
"Performance hints" that would open this section in the manual.

Many thanks - you will make my colleagues happy! I told them they have 
to do everything in Postgis and not mix data formats.

Matteo and Toto: can you try if that helps in your cases as well? Would 
be interesting to know.

Greetings,

Andreas

PS: with the 40 seconds before I thought - well - I as a slow human 
might be even faster than that stupid computer. Who knows ... I could 
have been faster! Now with Hugos hint is also a sub-second query.

Am 02.05.19 um 16:36 schrieb Hugo Mercier:
> 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 at 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 at lists.osgeo.org
> List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer