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.

[github-actions]

This Issue has been inactive for 90 days. As per RFC4, in order to manage maintenance burden, it will be automatically closed in 7 days.

[mikemahoney218-usgs]

It might be nice for the sql provider to let users configure this on a collection-by-collection basis

[github-actions]

This Issue has been inactive for 90 days. As per RFC4, in order to manage maintenance burden, it will be automatically closed in 7 days.

[mikemahoney218-usgs]

Wanted to bump this; there's a PR for discussion in #2174 and I do still think disabling numberMatched on a collection basis for the postgres provider would be of broad benefit

[webb-ben]

I do worry about overloading the provider block but I do agree that this issue should/can be closed with the introduction of behavior like:

        providers:  # list of 1..n required connections information
            - type: feature  # underlying data geospatial type. Allowed values are: feature, coverage, record, tile, edr
              name: CSV  # required: plugin name or import path. See Plugins section for more information.
              data: tests/data/obs.csv  # required: the data filesystem path or URL, depending on plugin setup
              id_field: id
              always_count: true # option: should provider always attempt to include numberMatched in the response