[QGIS-Developer] QGIS virtual layer incredibly slow

Thu May 2 09:42:18 PDT 2019

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>
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
> >> 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
> _______________________________________________
> 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

-- 
*Ing. Salvatore Fiandaca*
*mobile*.:+39 327.493.8955
*m*: *pigrecoinfinito at gmail.com <pigrecoinfinito at gmail.com>*
*C.F*.: FNDSVT71E29Z103G
*P.IVA*: 06597870820
*membro QGIS Italia - http://qgis.it/ <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/>**
<https://www.facebook.com/qgis.it/> *
*TW:  <http://goog_95411464>**https://twitter.com/totofiandaca
<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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-developer/attachments/20190502/4a717011/attachment-0001.html>