​About super projection Optimization

yamakawa

Hi.
​Use admintools to run Database Designer.
​Superprojection optimization performed.

​Upon completion of the process, we checked v _ catalog.projections.
​A projection with create _ type = 'DELAYED CREATION' existed.

​I was expecting all projections to have create _ type = 'DESIGNED'.
​Why does a projection for create _ type = 'DELAYED CREATION' exist?

mosheg

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;

mosheg

A projection with a create type of “DELAYED CREATION” is an auto-projection,
Probably created when one inserted data into the table when no projection existed yet.
See: https://www.vertica.com/blog/vertica-quick-tip-projection-create-types

yamakawa

@mosheg
Thank you for answer.
A projection with a create type of “DELAYED CREATION” is not super-projection.
(v_catalog.projections.is_super_projection is 'f')
Is it OK to remove them?