Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

​About super projection Optimization

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?

Answers

  • moshegmosheg Employee

    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

  • @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?

  • moshegmosheg Employee

    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;
    

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.