Updating table statistics?

Answers

• Options

  Jim_Knicely - Select Field - Administrator

  April 2020

  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
  

  0