[QGIS-Developer] Slow Joins on Virtual Layers

[Gabriel De Luca]

Hi,

  What I have noticed is that the virtual layer performance drop occurs
only joining spatial tables in PostGIS. If the original tables do not
contain geometry, or if they are stored in another format (I tried
spatialite and shapefile) the performance is normal (a few seconds for an
attributes join, output of 10,000 rows without geometry).

  It also depends on the number of vertices of the source geometries (one
second or less for an attributes join of PostGIS spatial tables, if the
source geometries has only ten vertices). I have not yet tried trimming the
decimal places of the vertex coordinates of the original geometries.

  Despite the fact that the selection does not include the geometry of the
original tables, a minute or more of high network traffic with the server
and cpu usage is observed, as if the geometries resulting from the join of
the features were fully scanned.


Regards,
Gabriel

El vie, 19 may 2023 a la(s) 11:45, Alexandre Neto via QGIS-Developer (
qgis-developer at lists.osgeo.org) escribió:

> Hi,
>
> I am trying to create a virtual layer that involves an attributes join
> between two layers.
> something like this:
>
> SELECT
>   l.seg_via_rodov_id,
>   l.via_rodov_id,
>   vr.nome
> FROM lig_segviarodov_viarodov as l,
>   "Via rodoviária" as vr
> where vr.identificador = l.via_rodov_id;
>
> These two layer are saved in postgreSQL \ PostGIS and have and there's
> actually a foreign key constraint on .via_rodov_id referencing the
> vr.identificador.
>
> As a virtual layer, this is painfully slow. Slow to the point of waiting 1
> or 2 minutes to get the answer, while the same query on postgresql takes
> less than one second.
>
> Not sure if this is expected or not or if it's a bug.
>
> The reason I want to make this query as a virtual layer, instead of
> loading it as a postgresql query directly, is because I want to use this
> relation information in labels, and I wanted those labels to get updated
> before I save the data in the database (to help the editor to understand if
> a certain object is already updated or not)
>
> I wonder if there's some workaround to help me with this.
>
> Thanks
>
> Alexandre Neto
> _______________________________________________
> 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/20230520/3d3a13e3/attachment.htm>