[QGIS-Developer] QGIS virtual layer incredibly slow

Mon May 6 03:05:55 PDT 2019

Hi Andreas and Totò,

Thanks for the confirmation.

I agree this is not well documented. Even the small section in the
manual is a bit ... enigmatic :-/

Andreas' suggestion to have a link in the GUI to the manual seems good.
This, with a bit more explanations in the manual :) I'll see what I can do.

Totò: yes, this hidden column is always there when you create a virtual
layer. An alternative would have been to do as Spatialite does, by
joining on another special table
(https://www.gaia-gis.it/fossil/libspatialite/wiki?name=SpatialIndex),
but I find it even worse.

For something as "transparent" as we have in PostGIS, I think we would
need a query rewriter: something that parses the input SQL query and add
references to _search_frame_ for spatial predicates. That would require
to be compliant with the different SQLite versions (that may have little
differences in their support of SQL) or to ship a fixed version of
SQLite with QGIS.

On 02/05/2019 18:42, Totò Fiandaca wrote:
> Hi Hugo  
> but the _search_frame_ is always usable ?? or only in special cases?
> 
> Il giorno gio 2 mag 2019 alle ore 17:18 Andreas Neumann
> <a.neumann at carto.net <mailto:a.neumann at carto.net>> ha scritto:
> 
>     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
>     <mailto: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 <mailto: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 <mailto: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
> 
> 
> 
> -- 
> *Ing. Salvatore Fiandaca*
> *mobile*.:+39 327.493.8955 
> *m*: *pigrecoinfinito at gmail.com <mailto:pigrecoinfinito at gmail.com>*
> *C.F*.: FNDSVT71E29Z103G
> *P.IVA*: 06597870820
> *membro QGIS Italia - http://qgis.it/*
> *socio GFOSS.it - *http://gfoss.it/
> *blog:** https://pigrecoinfinito.wordpress.com/
> <https://pigrecoinfinito.wordpress.com/> 
> FB: Co-admin - https://www.facebook.com/qgis.it/** 
> <https://www.facebook.com/qgis.it/> *
> *TW:  <http://goog_95411464>**https://twitter.com/totofiandaca*
> 
> 43°51'0.54"N  10°34'27.62"E - EPSG:4326
> 
> “Se la conoscenza deve essere aperta a tutti,
> perchè mai limitarne l’accesso?” 
> R. Stallman
> 
> Questo documento, allegati inclusi, contiene informazioni di proprietà
> di FIANDACA SALVATORE e deve essere utilizzato esclusivamente dal
> destinatario in relazione alle finalità per le quali è stato ricevuto.
> E' vietata qualsiasi forma di riproduzione o divulgazione senza
> l'esplicito consenso di FIANDACA SALVATORE. Qualora fosse stato ricevuto
> per errore si prega di informare tempestivamente il mittente e
> distruggere la copia in proprio possesso.
> 
> 
> 
> _______________________________________________
> 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
> 