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) 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 > List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer > Unsubscribe: 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
