Re: [QGIS-Developer] Project loader profiler Postgres Layers
@Julien sorry for not being clear, I used the Debugging/Developement tool 😊 @René-Luc Thanks I’ll look into it. From: QGIS-Developer on behalf of René-Luc Dhont via QGIS-Developer Date: Wednesday, 16 November 2022 at 10:06 To: qgis-developer@lists.osgeo.org Subject: Re: [QGIS-Developer] Project loader profiler Postgres Layers Hi Rémi, During the provider creation, a lot of SQL requests are made to check a lot of thing even if the use estimated metadata is checked : * Fields list * geometry type * primary key * extent * capabilities (read, insert, update, delete) You can take a look at these PRs to have an idea on all the SQL request made at the creation : * https://github.com/qgis/QGIS/pull/49440 * https://github.com/qgis/QGIS/pull/50186 Regards, René-Luc Le 16/11/2022 à 09:55, Julien Cabieces via QGIS-Developer a écrit : Hi Remi, Which profiler are you talking about ? Did you try to use the the new Debugging/Developement tool ? It's new since QGIS 3.26 and will give you the list of requests and the time spent for each one when loading a project/layer or opening the attribute table. Regards, Julien ___ 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] Project loader profiler Postgres Layers
Hi, I’m profiling large project loading, I think this mailing list doesn’t support images, so I won’t post any. I’m posting in the dev mailing list since I dived into the code and found nothing 😅. When you load a Postgres layer, the profiler list 3 steps: * Read layer metadata * Read layer fields * Create postgres provider This “Create postgres provider” takes a while on some table (I have ‘use estimated table metadata’. In the connection.) I `git greped` the code and cannot found where this “span” happened and what requests are made to the server, I could look out on the server logs, sadly I don’t have access to it :-/ I already saw some weird behavior on 3.28 where QGIS launch ~25 SQL requests when loading a new layer, but I’d like to have solid fact before posting an issue on github. Thx, Rémi. ___ 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
Re: [QGIS-Developer] Feature count for DB provider.
Thanks Regis. I tried with an existing project, and with no project. The information of the layers is: service='mycomp_prod' sslmode=require key='gid' estimatedmetadata=true srid=27572 type=Point checkPrimaryKeyUnicity='1' table="public"."mylayer" with service in `.pg_service.conf` : [mycomp_prod] user=rd sslmode=require host=gis.mycomp.fr port=5432 dbname=mycomp (don’t blame me for the 27572 OK 😅) I think I’ll open an issue because it _seems_ to do a SELECT count(*) FROM mylayer + SELECT * FROM mylayer until all features have been fetched. In my example, I have a 11M Points layer so it takes a while 😊. From: Régis Haubourg Date: Saturday, 25 June 2022 at 12:54 To: Rémi Desgrange Cc: QGIS Developers List Subject: Re: [QGIS-Developer] Feature count for DB provider. Hi Rémy, There had been a lot of work for postGIS and oracle providers. A count(*) might be really expensive for big tables, so there are options to use db statistics instead of real count. I bet the loop you point out occurs only in fallback situations but is not the main case. One is here for instance by there are numerous PRs like this one https://github.com/qgis/QGIS/pull/37619 In think @troopa81 has a clear view of the current state. all the best Régis Le sam. 25 juin 2022 à 10:38, Rémi Desgrange via QGIS-Developer mailto:qgis-developer@lists.osgeo.org>> a écrit : Hi, Just launched the new QGIS 3.24, and tests the SQL debugger, I just found out that the “feature count” does a while loop on all features in the layer to count. While it’s probably necessary for shapefiles or stuff like that, it appears suboptimal for databases access. I found this part in the code that looks like to be responsible for the feature counts, https://github.com/qgis/QGIS/blob/master/src/core/vector/qgsvectorlayerfeaturecounter.cpp#L76-L90 Do you think I could make a special case for database-based provider (geopackage, postgres/oracle/mysql/etc…) that launch a “SELECT count(*) FROM table” instead of looping on all features. Thanks. ___ 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 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
[QGIS-Developer] Feature count for DB provider.
Hi, Just launched the new QGIS 3.24, and tests the SQL debugger, I just found out that the “feature count” does a while loop on all features in the layer to count. While it’s probably necessary for shapefiles or stuff like that, it appears suboptimal for databases access. I found this part in the code that looks like to be responsible for the feature counts, https://github.com/qgis/QGIS/blob/master/src/core/vector/qgsvectorlayerfeaturecounter.cpp#L76-L90 Do you think I could make a special case for database-based provider (geopackage, postgres/oracle/mysql/etc…) that launch a “SELECT count(*) FROM table” instead of looping on all features. Thanks. ___ 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
