The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

How do you check which projections are being used actively and which aren't?

I would like to drop or modify projections that aren't being used. Is there any easy way to check this?


  • Hi!

    1. You can try WORKLOAD_ANALYZER, but it depends how long database is active?
    WLA reports about unused projection.

    2. You can manually to monitor projection usage when and how it was used
    if no records => not used, or define a time threshold  for unused projections and extract all INPUT usages.
    daniel=> select * from projection_usage limit 1;
    -[ RECORD 1 ]---------+------------------------------
    query_start_timestamp | 2014-02-20 19:23:43.469894+02
    node_name | v_flex_node0001
    user_name | daniel
    session_id | synapse-6561:0xd347
    request_id | 3
    transaction_id | 45035996273747723
    statement_id | 3
    io_type | input
    projection_id | 45035996273719302
    projection_name | test_super
    anchor_table_id | 45035996273719300
    anchor_table_schema | public
    anchor_table_name | test
    Also you can find this function useful to identify projections for table:
    daniel=> \a
    Output format is unaligned.
    daniel=> \t
    Showing only tuples.
    daniel=> select get_table_projections('test');
    get_table_projections|Current system K is 0.
    # of Nodes: 1.
    Table public.test has 1 projections.

    Projection Name: [Segmented] [Seg Cols] [# of Buddies] [Buddy Projections] [Safe] [UptoDate] [Stats]
    public.test_super [Segmented: Yes] [Seg Cols: "", "public.test.c"] [K: 0] [No buddy projections] [Safe: Yes] [UptoDate: Yes] [Stats: RowCounts]
  • Hi Daniel, 

    Porjection_usage would work. But what is the time threshold assuming its a fairly active DB. 

  • (sorry for poor English)
    All table projections are populated when data is inserted/updated.
    So if a table was updated so a projection that does not used by a query, not asked, still will appear in table PROJECTION_USAGE. So don't count io_type - WRITE.

    Threshold?  Never mind - term(What is unused? Never used by select? Or last usage was year ago?)
  • Its your as database administrator decision and definitions. I didn't get a question.

    >> assuming its a fairly active DB.
    So may be try WLA? WLA created for tasks like this.

Leave a Comment

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