[QGIS-Developer] QGIS virtual layer incredibly slow
Andreas Neumann
a.neumann at carto.net
Thu May 2 08:18:17 PDT 2019
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
More information about the QGIS-Developer
mailing list