The number of unused projections is not displayed

yamakawa

Hi.

The number of unused projections is not displayed at Management Console.
Home -> Databases and Clusters -> MyDB -> Activity Table Utilization
Right pane - Projections Summary

For items other than unused projections, the values are displayed.
Once I restarted the MC, the situation did not change.

Jim_Knicely

The count of unused projections comes from a Work Load Analyzer Rule. That rule has several parameters that are used in its underlying query.

You can display the parameters like so:

dbadmin=> SELECT parameter, current_value, default_value FROM vs_tuning_rules a JOIN vs_tuning_rule_parameters b ON b.tuning_rule = a.name WHERE observation_type = 'PROJ_UNUSED';
      parameter      | current_value | default_value
---------------------+---------------+---------------
 past_days           |            30 |            30
 table_used_at_least |            30 |            30
 min_used_bytes      |      10000000 |      10000000
(3 rows)

You can change the parameter values if you'd like.

In fact, it's planned that in Vertica 10.1 we're lowering the defaults for this WLA rule to more appropriate values per client feedback. Plus, it's also planned that you will be able to tune WLA rule parameters from within MC.

yamakawa

@Jim_Knicely

Thank you for your answer.

I ran the query you gave as an example, and the settings were left at their default values.
Is there any way I can use the query to get the unused projections if they do not show up on ManagementConsole?

mosheg

To see which projections were loaded but not queried try this:

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;

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;

yamakawa

@mosheg

Thank you for your answer.
Unused projections could be checked.

The question that remains is why the numbers are not displayed in the browser.
I can see the unused projections in the individual tables, but not in the "Projections Summary".

You can see them in MC in the development environment, which is accessible from the same terminal. I do not know the cause of the difference.