Re: [QGIS-Developer] Feature count for DB provider.
Hello Rémi, The function you refer to https://github.com/qgis/QGIS/blob/master/src/core/vector/qgsvectorlayerfeaturecounter.cpp#L76-L90 is only called when the feature count must be done for the symbols (for instance when a categorized symbology is used, then QGIS does not use estimated metadata, and the count for each symbol is then evaluated and displayed). Normally there is no SELECT count(*) FROM or SELECT * FROM when estimated metadata is used. Regards, -- Jacky Volpes Ingénieur SIG - Oslandia Le 26/06/2022 à 20:20, Rémi Desgrange via QGIS-Developer a écrit : 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 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 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 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
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 < 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 > 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
