Slow Query Performance in Postgres Provider Due to Full count on Large Tables

[webb-ben]

Description
The Postgres provider currently performs a full .count() on every request to populate numberMatched, which significantly slows down queries on large tables. This makes API responses much slower than necessary, especially when querying large datasets for a small subset of the features.

Steps to Reproduce

1. Set up pygeoapi with a large Postgres table as a data source.
2. Make a request to an endpoint that queries the table.
3. Observe that the response time is impacted by the .count() operation.

Expected behavior
Queries should return results faster by avoiding expensive .count() operations on large tables.

Potential workarounds are:

• Introduce an environment variable to control whether the .count() is always run.
• Implement an upper limit on .count() queries, with a fallback mechanism such as TABLESAMPLE or estimated counts from Postgres' pg_stat tables.
• Use an approximate count method when exact counts are not required. This is implemented in https://github.com/cgs-earth/pygeoapi-plugins/blob/master/pygeoapi_plugins/provider/postgresql.py

Screenshots/Tracebacks
If applicable, add screenshots to help explain your problem.

Environment

• OS: Mac
• Python version: 3.10
• pygeoapi version: 0.20.dev0

Additional context
Similar constraints exist in other providers, with some only doing a count of features when resulttype=hits

[jratike80]

It is impossible to make everybody happy at the same time, see #1007.
Personally I prefer speed over seeing the numberMatched value, but maybe the behavior could be configured on the server side, or with a special query parameter.

[mikemahoney218-usgs]

I just want to note that numberMatched is allowed to be dropped in requirement /req/core/fc-numberMatched:

A server MAY omit this information in a response, if the information about the number of matching features is not known or difficult to compute.

So providers are permitted to drop the count entirely if it's inefficient.