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
|Microsoft SQL Server|
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;
Normally, this query should return no rows which indicates that all tables have been properly analyzed.
If a row is returned, it provides the table name which has not been analyzed.
As mentioned above, this can be re mediated through the use of cssoptimize.
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 );
Normally, this query should return no rows which indicates that the postgres server statistics and the cast central database statistics database are in sync.
If a row is returned, it provides the table name which is out of sync.
As mentioned above, this can potentially be remedied through the use of cssoptimize.