Updating table statistics?

rjs_docs

Is there a way to update statistics automatically?
@bat @rbankula

From Optimizing Query Performance and Resource Pool Tuning.

Jim_Knicely

You can have Vertica build ANALYZE_STATISTICS commands to analyze stats on projections that do not have stats... Then have crontab run them.

Simple Example:

dbadmin=> SELECT DISTINCT 'SELECT analyze_statistics(''' || p.projection_schema || '.' || p.projection_basename || ''');' run_these FROM projections p JOIN projection_storage ps USING (projection_id) WHERE NOT p.has_statistics AND NOT p.is_aggregate_projection AND ps.row_count > 0;
                   run_these
------------------------------------------------
 SELECT analyze_statistics('public.some_fact');
 SELECT analyze_statistics('public.no_stats');
(2 rows)

dbadmin=> \! vsql -Atc "SELECT DISTINCT 'SELECT analyze_statistics(''' || p.projection_schema || '.' || p.projection_basename || ''');' run_these FROM projections p JOIN projection_storage ps USING (projection_id) WHERE NOT p.has_statistics AND NOT p.is_aggregate_projection AND ps.row_count > 0;" | vsql -a
SELECT analyze_statistics('public.some_fact');
 analyze_statistics
--------------------
                  0
(1 row)

SELECT analyze_statistics('public.no_stats');
 analyze_statistics
--------------------
                  0
(1 row)

dbadmin=> SELECT DISTINCT 'SELECT analyze_statistics(''' || p.projection_schema || '.' || p.projection_basename || ''');' run_these FROM projections p JOIN projection_storage ps USING (projection_id) WHERE NOT p.has_statistics AND NOT p.is_aggregate_projection AND ps.row_count > 0;
 run_these
-----------
(0 rows)

So your crontab entry might run once a day like this:

00 00 * * * /home/dbadmin/scripts/analyze_stats.sh > /home/dbadmin/scripts/analyze_stats.out 2>&1

Where the analyze_stats.sh script looks like this:

dbadmin=> \! cat /home/dbadmin/scripts/analyze_stats.sh
vsql -Atc "SELECT DISTINCT 'SELECT analyze_statistics(''' || p.projection_schema || '.' || p.projection_basename || ''');' run_these FROM projections p JOIN projection_storage ps USING (projection_id) WHERE NOT p.has_statistics AND NOT p.is_aggregate_projection AND ps.row_count > 1;" | vsql -a