SQL Queries - CAST Central Base - How to check the status of the statistics in the CAST central base

This page provides information on how to check if the statistics on the CAST central base are the same as what is actually on the postgres server.

This can provide insight into performance problems with the CAST central base on snapshots.

Running cssoptimize should also assist with setting these statistics correctly. 

Remember that statistics must be updated with cssoptimize after a database restoration as the restoration does not restore the statistics.   

See the documentation on cssoptimize for more details on running cssoptimize:  Running CSSoptimize

This query provides information on which tables in the central database have not being analyzed on the  postgres server (the central database name is required to be provided in the query below):

SELECT relname ,
n_live_tup ,
last_analyze,
last_autoanalyze
FROM pg_stat_all_tables
WHERE schemaname = <central database name>
AND last_autoanalyze IS NULL
AND last_analyze IS NULL;

The following query then compares the information on the postgres server in regard to statistics and compares it to the statistic information in the central database:

SELECT psat.relname ,
psat.n_live_tup ,
psat.last_analyze ,
psat.last_autoanalyze,
cps.n_prev_tup ,
cps.last_analyze
FROM <central schema name>.CAST_pg_stat cps
JOIN pg_stat_all_tables psat
ON psat.schemaname = '<central schema name>'
AND psat.relname = cps.relname
WHERE (
psat.last_analyze < cps.last_analyze
AND psat.n_live_tup = 0
)
OR
(
psat.last_autoanalyze < cps.last_analyze
AND psat.n_live_tup = 0
)
OR
(
psat.last_analyze IS NULL
AND psat.last_autoanalyze IS NULL
AND cps.n_prev_tup != 0
)
OR
(
psat.n_live_tup = 0
AND cps.n_prev_tup != 0
)
OR
(
psat.n_live_tup != 0
AND cps.n_prev_tup = 0
);