Resolving the "missing extended stats monitoring helper functions" warning

You may see the following warning in the pganalyze app:

Your database appears to be missing extended stats monitoring helper functions,
so the data of extended statistics are not available. Please review the relevant
troubleshooting documentation.

This warning indicates that extended statistics for the specific database are not being reported by the collector. Extended statistics are used by pganalyze to improve Index Advisor recommendations. Index Advisor will still work without the helper functions, but index recommendations may be less accurate.

To resolve this, first, make sure you are running at least collector version 0.53.0 (and ideally the latest version). You can see which version you are currently running by checking the Debug Info panel on the Server Settings tab of the Settings page for your server. Then, find the name for the database role that's configured as the monitoring user in your collector installation (we'll assume "pganalyze" here).

Then, connect to each database that you plan to monitor on this server as a superuser (or equivalent) and run the following to enable the collection of additional extended statistics:

CREATE SCHEMA IF NOT EXISTS pganalyze;
GRANT USAGE ON SCHEMA pganalyze TO pganalyze;
CREATE OR REPLACE FUNCTION pganalyze.get_relation_stats_ext() RETURNS TABLE(
  statistics_schemaname text, statistics_name text,
  inherited boolean, n_distinct pg_ndistinct, dependencies pg_dependencies,
  most_common_val_nulls boolean[], most_common_freqs float8[], most_common_base_freqs float8[]
) AS
$$
  /* pganalyze-collector */ SELECT statistics_schemaname::text, statistics_name::text,
  (row_to_json(se.*)::jsonb ->> 'inherited')::boolean AS inherited, n_distinct, dependencies,
  most_common_val_nulls, most_common_freqs, most_common_base_freqs
  FROM pg_catalog.pg_stats_ext se
  WHERE schemaname NOT IN ('pg_catalog', 'information_schema');
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

Note: We never collect actual table data through this method (we omit fetching fields like most_common_vals from the pg_stats_ext view, as you can see in the function definition), but we do collect statistics about the distribution of values in your tables. You can skip creating the get_relation_stats_ext helper function if the database contains highly sensitive information and statistics about it should not be collected. This will impact the accuracy of Index Advisor recommendations.

Note that unlike other pganalyze helper functions, this function must be installed in every database that you intend to monitor separately.

After creating the helper function in all monitored databases, re-run the collector test to confirm the warning is no longer displayed.