[QGIS-Developer] Slow Joins on Virtual Layers

[Alexandre Neto]

Uhmm... That's good to know.

In this case both layers are non-spatial. I will try saving them in shp and geopackage to test their performance. If the performance is worst with postgis tables, It might be worth opening a ticket.

Alexandre Neto

On Sat May 20, 2023, 07:19 PM GMT, Gabriel De Luca <mailto:pablogabrieldeluca at gmail.com> wrote:
> 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 <mailto: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 <mailto:QGIS-Developer at lists.osgeo.org>
>> List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer <https://lists.osgeo.org/mailman/listinfo/qgis-developer>
>> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer <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/85d855f3/attachment.htm>