[QGIS-Developer] Feature count for DB provider.

[Rémi Desgrange]

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 <regis.haubourg at gmail.com>
Date: Saturday, 25 June 2022 at 12:54
To: Rémi Desgrange <remi.desgrange at ielo.net>
Cc: QGIS Developers List <qgis-developer at lists.osgeo.org>
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 <qgis-developer at lists.osgeo.org<mailto:qgis-developer at 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 at lists.osgeo.org<mailto:QGIS-Developer at lists.osgeo.org>
List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-developer/attachments/20220626/e4ce7d42/attachment.htm>