I often need to quickly make sure a database has all its stats analysed properly. Quite often I find a database that has just been fully pg_restore’d but is missing all its stats. One of the first things I do when investigating sudden performance issues is to do a quick check to make sure they are there.

I wrote a little script to summarise each schema within the current database:

select schemaname, sum(stats) as has_stats, sum(nostats) as no_stats from (
	select schemaname, 1 as stats, 0 as nostats
	from pg_stat_all_tables
	where last_analyze is not null and schemaname != 'pg_toast'
	UNION ALL
	select schemaname, 0 as stats, 1 as nostats
	from pg_stat_all_tables
	where last_analyze is null and schemaname != 'pg_toast'
) x
group by schemaname;

When I run it, I get something like:

mydb=# \i sql/stats
     schemaname     | has_stats | no_stats
--------------------+-----------+----------
 dm_asx             |         2 |        1
 information_schema |         5 |        2
 working            |         2 |        4
 pitcopy            |        15 |        0
 app_asx            |         2 |        3
 pg_catalog         |        19 |       21
(6 rows)

This one looks fine, there are often objects with no stats because they are too small. However, sometimes I see:

mydb=# \i sql/stats
     schemaname     | has_stats | no_stats
--------------------+-----------+----------
 myschema           |         0 |      294
 information_schema |         0 |        7
 pg_catalog         |        36 |        4
(3 rows)

When 0 tables have stats and 294 have none, something is probably wrong! I then get out my trusty db_analyze.sh script to analyze every table in the schema using parallel processes.

[postgres@epplin01-postg dba]$ db_analyze.sh -d mydb -n myschema -p 4
Starting analyze at Thu Mar 10 15:45:41 EST 2011
ANALYZE
INFO:  analyzing "myschema.nt"
INFO:  "nt": scanned 6 of 6 pages, containing 226 live rows and 0 dead rows; 226 rows in sample, 226 estimated total rows
...
Completed analyze at Thu Mar 10 15:49:03 EST 2011

Both the stats.sql and db_analyze.sh scripts can be downloaded here.