What does this mean when the stats_off column in the svv_table_info table is 99%?

Question

For one of the columns stats_offin the table, the svv_table_infovalue is 99%. What does it mean? And how to fix it?

I tried to get anaylse and vaccum history for this table. Does Vacuum also analyze any role for this column value?

Answer 1

The team will VACUUMlook at the table and, if necessary, change the data on the disk, which will affect the columns unsortedand empty. Closer to 0 is better.

The team ANALYZEwill check the table and adjust the statistics as appropriate, which will affect the column stats_off. Closer to 0 is better.

, ANALYZE . , VACUUM. , - Redshift , - . , VACUUM , ANALYZE .

, , , . , , - , , . , . , - , ...

SELECT DISTINCT 'ANALYZE ' + feedback_tbl.schema_name + '.' + feedback_tbl.table_name + ';' AS command
FROM ((SELECT
         TRIM(n.nspname) schema_name,
         c.relname       table_name
       FROM (SELECT
               TRIM(SPLIT_PART(SPLIT_PART(a.plannode, ':', 2), ' ', 2)) AS Table_Name,
               COUNT(a.query),
               DENSE_RANK()
               OVER (
                 ORDER BY COUNT(a.query) DESC)                          AS qry_rnk
             FROM stl_explain a,
               stl_query b
             WHERE a.query = b.query
                   AND CAST(b.starttime AS DATE) >= dateadd(DAY, -1, CURRENT_DATE)
                   AND a.userid > 1
                   AND a.plannode LIKE '%%missing statistics%%'
                   AND a.plannode NOT LIKE '%%_bkp_%%'
             GROUP BY Table_Name) miss_tbl
         LEFT JOIN pg_class c ON c.relname = TRIM(miss_tbl.table_name)
         LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
       WHERE miss_tbl.qry_rnk <= 25)
      -- Get the top N rank tables based on the stl_alert_event_log alerts
      UNION
      SELECT
        schema_name,
        table_name
      FROM (SELECT
              TRIM(n.nspname)              schema_name,
              c.relname                    table_name,
              DENSE_RANK()
              OVER (
                ORDER BY COUNT(*) DESC) AS qry_rnk,
              COUNT(*)
            FROM stl_alert_event_log AS l
              JOIN (SELECT
                      query,
                      tbl,
                      perm_table_name
                    FROM stl_scan
                    WHERE perm_table_name <> 'Internal Worktable'
                    GROUP BY query,
                      tbl,
                      perm_table_name) AS s ON s.query = l.query
              JOIN pg_class c ON c.oid = s.tbl
              JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
            WHERE l.userid > 1
                  AND l.event_time >= dateadd(DAY, -1, CURRENT_DATE)
                  AND l.Solution LIKE '%%ANALYZE command%%'
            GROUP BY TRIM(n.nspname),
              c.relname) anlyz_tbl
      WHERE anlyz_tbl.qry_rnk < 25) feedback_tbl
  JOIN svv_table_info info_tbl
    ON info_tbl.schema = feedback_tbl.schema_name
       AND info_tbl.table = feedback_tbl.table_name
WHERE info_tbl.stats_off :: DECIMAL(32, 4) > 10 :: DECIMAL(32, 4)
      AND TRIM(info_tbl.schema) = 'public'
ORDER BY info_tbl.size ASC;

, VACUUM...

SELECT 'VACUUM FULL ' + "schema" + '.' + "table" + ';' AS command
FROM svv_table_info
WHERE (unsorted > 5 OR empty > 5)
      AND size < 716800;

, Amazon, Python Redshift .