The answer below in response to your question about "temporary spatial index". However, I don't see how a spatial index would help speed up the query in your question as that one doesn't make use of any spatial predicates, and I don't know if you can explicitly create non-spatial indexes on virtual layers (in your example, that would be an index on "objekt-id" in both of the virtual layers).
Virtual Layers use SpatiaLite lib (if I understand correctly, the tables are read into memory or virtualized, in a way, they have no "memory' of their origins, hence, no access to underlying indexes): you need to work out your queries as if you are working in a SpatiaLite db, i.e. you need to invoke the indexes explicitly ( https://www.gaia-gis.it/fossil/libspatialite/wiki?name=SpatialIndex). Luckily, there is a convenience function added by Virtual Layers: _search_frame_, so that you can skip the part about referring to the underlying index table etc.See: https://docs.qgis.org/3.16/en/docs/user_manual/managing_data_source/create_layers.html#use-of-indexes https://github.com/mhugo/qgis_vlayers#index-support http://osgeo-org.1560.x6.nabble.com/QGIS-Developer-QGIS-virtual-layer-incredibly-slow-td5403735.html E.g.: SELECT b.zip_code, a.geometry FROM buildings a, zip_code_areas b WHERE a._search_frame_ = b.geometry AND ST_Within(a.geometry, b.geometry) Note that "b._search_frame_ = a.geometry" instead of "a._search_frame_ = b.geometry" above also speeds up the query, but not by as much in my specific situation: this depends on the counts of geometries on each layer and the "average" relationship between features in one layer to the other. On Thu, May 6, 2021, 04:33 Bo Victor Thomsen <[email protected]> wrote: > Hi list - > > I have created a virtual layer using Python and shown this in my QGIS > mapping window like this: > > layer = QgsVectorlayer(?layer=*...layer "ref" definition... *&layer=*... > "cur" definition...*&query=select ref.* from ref left join cur on > ref."objekt-id" = cur."objekt-id" where cur."objekt-id" is NULL,"My layer", > "virtual") > > i.e a left join between a Postgres layer "cur" and a GeoPackage layer > "ref". And it works !!! (Thank you Hugo and whoever that has developed this > facility). > > However, it is somewhat slow to react, when you ex. are using the "info" > tool on the layer. AFAIK, it's not using the indexes in the original > layers. > > Is there any method (Python or otherwise) to speed up the response of the > virtual layer ? For example by adding a temporary spatial index to the > resulting virtual layer ?? Some "magical keywords" in the creation > parameters for the layer ??? > -- > > Med venlig hilsen / Kind regards > > Bo Victor Thomsen > > _______________________________________________ > QGIS-Developer mailing list > [email protected] > List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer > Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer >
_______________________________________________ QGIS-Developer mailing list [email protected] List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
