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?


  • Options

    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.id", "public.test.c"] [K: 0] [No buddy projections] [Safe: Yes] [UptoDate: Yes] [Stats: RowCounts]
  • Options
    Hi Daniel, 

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

  • Options
    (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?)
  • Options
    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