What Projections are not Being Used

This blog post was authored by Eugenia Moreno.

It is common to create new projections to improve performance in Vertica. However, you might forget about the old projections. Vertica still loads data in projections that you might not be using. A projection that is loaded but not picked up by the Vertica optimizer consumes storage and can affect load performance.

Before you run the query to see which projections are not being used, run the following query to determine the value of the K-safety: =>SELECT get_design_ksafe() ; Use the value you get from the query above and add 1 to it. You will use this value in the following query. You can view which projections are not being used to retrieve data by querying the PROJECTION_USAGE table. In this case, the value we determined from the above query is 2, and is highlighted below.

To see projections that were loaded, but not queried, run the following: => dbadmin=> 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; The output of this query shows buddy projections named mytest_b0 and mytest_b1. This is an example of a projection that was loaded but not used. The other projection is ok, because its buddy does not appear in the output. If a projection’s buddy does not appear in the output, you can ignore it: anchor_table_schema | anchor_table_name | projection_name | last_loaded ---------------------+-------------------+---------------------------------------------------------------+------------------------------- public | mytest | mytest_b0 | 2018-02-07 20:06:18.162542+00 public | mytest | mytest_b1 | 2018-02-07 20:06:18.16259+00 public | new_final | market_paybacks_dimension_201606072_b1 | 2018-02-12 17:19:43.849961+00 To see the last time that a projection was loaded and queried, run the following: => 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; Note that this information is based on the retention of the PROJECTION_USAGE table. To verify the record retention, check using the following: => SELECT max(query_start_timestamp) last_record , min(query_start_timestamp) first_record FROM projection_usage ; You can also do this using Management Console (MC) by doing the following:

From the Activity tab, change the drop down filter to Table Utilization:



Pick a schema (this shows the public schema), then pick a table name from that schema. This opens the Table details page.

At the bottom of the page, in the Projections section, there’s a tab for Unused Projections: