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:pablogabrieldel...@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@lists.osgeo.org <mailto:qgis-developer@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@lists.osgeo.org <mailto:QGIS-Developer@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>
_______________________________________________
QGIS-Developer mailing list
QGIS-Developer@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
