​About super projection Optimization

Best Answer

• Options

  mosheg Vertica Employee Administrator

  November 2020 Answer ✓

  Before dropping a projection check the following:
  1. Removing that specific projection will not hurt queries performance.
  2. The projection cannot be the only anchor table's superprojection.
  3. You cannot drop a buddy projection if doing so violates system K-safety.
  4. Another projection must be available to enforce the same primary or unique key constraint.

  To create a list of candidate projections for deletion check first which projections were loaded but not queried:

  SELECT anchor_table_schema, anchor_table_name ,projection_name ,max(query_start_timestamp) last_loaded 
  FROM projection_usage 
  WHERE io_type = 'output' AND projection_id NOT IN ( SELECT projection_id FROM projection_usage WHERE io_type = 'input' ) 
  AND anchor_table_id in (SELECT anchor_table_id FROM projections WHERE is_segmented group by 1 having count(*) > 2 ) 
  GROUP BY 1,2,3 
  ORDER BY 1,2,3;
  

  Or check the last time a projection was loaded and queried:

  SELECT anchor_table_schema ,anchor_table_name ,projection_name
     ,max(DECODE(io_type, 'input', query_start_timestamp, NULL)) AS 'last_query'
     ,max(DECODE(io_type, 'output', query_start_timestamp, NULL)) AS 'last_ load'
  ,count(DECODE(io_type, 'input', query_start_timestamp, NULL)) AS 'count_query'
     ,count(DECODE(io_type, 'output', query_start_timestamp, NULL)) AS 'count_load'
  FROM projection_usage
  GROUP BY 1,2,3
  ORDER BY 4 DESC;
  

  0