ANALYZE statistics for PostgreSQL

avalyze_stats.sql

WITH table_opts AS (
	SELECT pg_class.oid, relname, nspname, array_to_string(reloptions, '''') AS relopts 
	FROM pg_class 
	INNER JOIN pg_namespace ns ON relnamespace = ns.oid), 
analyze_settings AS (
	SELECT oid, relname, nspname, 
	CASE 
		WHEN relopts LIKE ''%autovacuum_analyze_threshold%'' 
			THEN substring(relopts, ''.*autovacuum_analyze_threshold=([0-9.]+).*'')::integer 
		ELSE current_setting(''autovacuum_analyze_threshold'')::integer 
	END 
	AS autovacuum_analyze_threshold, 
	CASE 
		WHEN relopts LIKE ''%autovacuum_analyze_scale_factor%'' 
			THEN substring(relopts, ''.*autovacuum_analyze_scale_factor=([0-9.]+).*'')::real 
		ELSE current_setting(''autovacuum_analyze_scale_factor'')::real 
	END 
	AS autovacuum_analyze_scale_factor 
	FROM table_opts) 
SELECT analyze_settings.nspname AS schema, analyze_settings.relname AS table, 
to_char(psut.last_analyze, ''YYYY-MM-DD HH24:MI'') AS last_analyze, 
to_char(psut.last_autoanalyze, ''YYYY-MM-DD HH24:MI'') AS last_autoanalyze, 
to_char(pg_class.reltuples, ''9G999G999G999'') AS rowcount, 
to_char(psut.n_dead_tup, ''9G999G999G999'') AS dead_rowcount, 
to_char(autovacuum_analyze_threshold + (autovacuum_analyze_scale_factor::numeric * pg_class.reltuples), ''9G999G999G999'') AS autovacuum_threshold, 
CASE 
	WHEN autovacuum_analyze_threshold + (autovacuum_analyze_scale_factor::numeric * pg_class.reltuples) < psut.n_dead_tup 
		THEN ''yes'' 
END 
AS expect_autoanalyze 
FROM pg_stat_user_tables psut 
INNER JOIN pg_class ON psut.relid = pg_class.oid 
INNER JOIN analyze_settings ON pg_class.oid = analyze_settings.oid 
ORDER BY 1, 2;