Resolving the "Limited access to table column statistics" warning

You may get the following warning when running a collector test:

Warning: Limited access to table column statistics detected in database postgres. Please set up
the monitoring helper function pganalyze.get_column_stats
(https://github.com/pganalyze/collector#setting-up-a-restricted-monitoring-user)
or connect as superuser, to get column statistics for all tables.

Or see the following warning in the pganalyze app:

Your database appears to be missing column stats monitoring helper functions, which can lead to
inaccurate Index Advisor recommendations. Please review the relevant troubleshooting documentation.

This warning indicates that column statistics for the specific database are not being reported by the collector. Column 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.41.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 column statistics:

CREATE SCHEMA IF NOT EXISTS pganalyze;
GRANT USAGE ON SCHEMA pganalyze TO pganalyze;
DROP FUNCTION IF EXISTS pganalyze.get_column_stats;
CREATE FUNCTION pganalyze.get_column_stats() RETURNS TABLE(
  schemaname name, tablename name, attname name, inherited bool, null_frac real, avg_width int, n_distinct real, correlation real
) AS $$
  /* pganalyze-collector */
  SELECT schemaname, tablename, attname, inherited, null_frac, avg_width, n_distinct, correlation
  FROM pg_catalog.pg_stats
  WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND tablename <> 'pg_subscription';
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

Note: We never collect actual table data through this method (see the NULL values in the function), but we do collect statistics about the distribution of values in your tables. You can skip creating the get_column_stats 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.