Record count regression on views

[ponceta]

Describe the bug
Feature count goes wrong on some views on QGIS 3.16.X

Works fine on same project on QGIS 3.10.X

QGIS 3.16.X comes again with annoying windows message for every feature count executed.

How to Reproduce

QGIS : 3.10.X

QGIS : 3.16.X

QGIS and OS versions

QGIS 3.16.3 Hannover Windows 10 x64

Additional context

Project issue : QGEP/QGEP#627

[roya0045]

These are non-geometric data sources, its completely normal that no features are counted since they don't display any features.

[ponceta]

These are non-geometric data sources, its completely normal that no features are counted since they don't display any features.

These are empty views, they should display [0]

A select count (*) vw_qgep_maintenance in pgadmin shows 0 entities.

[roya0045]

If you import those layers in a new project, is the count still the same? Maybe the cached value was corrupted/altered.

[ponceta]

No, count is back to normal when reloading these views.

But reload the project and you get altered/corrupted values again

[roya0045]

Were the tables always empty or was the 90 and 80 count valid at some point?

[gioman]

Project issue : QGEP/QGEP#627

@ponceta that project cannot be opened, the layers are from a postgresql server that is not accessible remotely.

[ponceta]

No, they never had any entities.

Ok on a blank project loading these layers don't end with the same result. :( That's a qgs project based issue! Erf...

Any idea where to look?

[gioman]

@ponceta can you provide a dump (and a project) we can use to test on our machines?

[ponceta]

https://wetransfer.com/downloads/a5169232b2ecfa9d1cd1abb5508048dc20210126164605/eafa2223b2368971121c47617a0d7e8520210126164635/add638

Data dump from pgadmin 4 and qgs project

[gioman]

Data dump from pgadmin 4 and qgs project

@ponceta

[ponceta]

If you look at the Dégâts layer which is also a view, it shows 80 entities but it is empty and should display [0].

But it seems that you have a different behaviour.

On my side I have 90 displayed as entity count for Maintenance view and 1 entity filtered.

[Gustry]

Linked to #40162 ?

[gioman]

Linked to #40162 ?

@Gustry I'm using master so it should contain that fix, and I still see the problem.

[troopa81]

@ponceta

The estimated count for view was not working in 3.10 (see my comment here ) and was returning always 0. I fixed it in #32114, and then fixed the following issue related to tables in #40162

The thing is, it's an estimation and it could be wrong. I use the Plan rows from EXPLAIN command to get the estimated count. For instance, on QGEP data, I have

EXPLAIN (FORMAT JSON) SELECT 1 FROM qgep_od.infiltration_installation;
                     QUERY PLAN                      
-----------------------------------------------------
 [                                                  +
   {                                                +
     "Plan": {                                      +
       "Node Type": "Seq Scan",                     +
       "Parallel Aware": false,                     +
       "Relation Name": "infiltration_installation",+
       "Alias": "infiltration_installation",        +
       "Startup Cost": 0.00,                        +
       "Total Cost": 13.70,                         +
       "Plan Rows": 370,                            +
       "Plan Width": 4                              +
     }                                              +
   }                                                +
 ]
(1 ligne)

qgep_prod=# select count(*) from qgep_od.infiltration_installation; 
 count 
-------
     0
(1 ligne)

Even VACUUM ANALYSE won't change anything. I don't see any way to have a better estimation.

I propose to close the ticket, except if somebody has a better idea on how to deal with this

[github-actions]

The QGIS project highly values your report and would love to see it addressed. However, this issue has been left in feedback mode for the last 14 days and is being automatically marked as "stale".
If you would like to continue with this issue, please provide any missing information or answer any open questions. If you could resolve the issue yourself meanwhile, please leave a note for future readers with the same problem and close the issue.
In case you should have any uncertainty, please leave a comment and we will be happy to help you proceed with this issue.
If there is no further activity on this issue, it will be closed in a week.

[gioman]

I don't see any way to have a better estimation.

@troopa81 is this a common problem to all clients (i.e. pgadmin, dbeaver, etc.)?

if yes, than I guess we should raise a very clear warning when enabling feature count on views.

[roya0045]

@gioman The other way would be to count and not estimate.

[troopa81]

if yes, than I guess we should raise a very clear warning when enabling feature count on views.

The option is called Use estimated table metadata, so it's quite clear that the metadata will be estimated and not exact. But still maybe the user don't understand what metadata means (feature count, geometry type for some provider) and what mean estimated.

We can maybe improve the tooltip

[gioman]

We can maybe improve the tooltip

@Gustry I'm afraid most of the users will overlook the tooltip, in this case I would see better a warning in the message bar.

[gioman]

@gioman The other way would be to count and not estimate.

@roya0045 agree.

[troopa81]

@Gustry I'm afraid most of the users will overlook the tooltip, in this case I would see better a warning in the message bar.

When you check the option ? or when you load the layers ? The second one seems a little bit too much but why not for the first one

[gioman]

@troopa81 yeah I guess that if would be enough when checking the option, after that is up to the user of course. But at the moment there isn't anything that clearly warns the users of the downsides of that option, while it is often suggested to activate it to "speed up" things.