The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

How to find the projections that are not used by the query optimizer from long time?

Hello guys,

 

Can we find the projections that were not used by the query optimizer ??

 

 

Comments

  • Hi,

     

    There is a table that has the list of projection used.

     

    dbadmin=> \d v_monitor.projection_usage
                                                      List of Fields by Tables
      Schema   |      Table       |        Column         |     Type     | Size | Default | Not Null | Primary Key | Foreign Key
    -----------+------------------+-----------------------+--------------+------+---------+----------+-------------+-------------
     v_monitor | projection_usage | anchor_table_id       | int          |    8 |         | f        | f           |
     v_monitor | projection_usage | anchor_table_name     | varchar(128) |  128 |         | f        | f           |
     v_monitor | projection_usage | anchor_table_schema   | varchar(128) |  128 |         | f        | f           |
     v_monitor | projection_usage | io_type               | varchar(128) |  128 |         | f        | f           |
     v_monitor | projection_usage | node_name             | varchar(128) |  128 |         | f        | f           |
     v_monitor | projection_usage | projection_id         | int          |    8 |         | f        | f           |
     v_monitor | projection_usage | projection_name       | varchar(128) |  128 |         | f        | f           |
     v_monitor | projection_usage | query_start_timestamp | timestamptz  |    8 |         | f        | f           |
     v_monitor | projection_usage | request_id            | int          |    8 |         | f        | f           |
     v_monitor | projection_usage | session_id            | varchar(128) |  128 |         | f        | f           |
     v_monitor | projection_usage | statement_id          | int          |    8 |         | f        | f           |
     v_monitor | projection_usage | transaction_id        | int          |    8 |         | f        | f           |
     v_monitor |

     

    you could do a left join with the projection tables and see the one that had not been used.

     

    will this help?

     

    Eugenia

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.