[QGIS-Developer] Speeding up a virtual layer

[i-s-o]

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 <bo.victor.thomsen at gmail.com>
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
> 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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-developer/attachments/20210506/0d340232/attachment.html>