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

K__1

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

Daniel_Leybovic

Hi!

1. You can try WORKLOAD_ANALYZER, but it depends how long database is active?
WLA reports about unused projection.
https://my.vertica.com/docs/7.0.x/HTML/index.htm#Authoring/AdministratorsGuide/WorkloadAnalyzer/Anal...

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]

K__1

Hi Daniel, 

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

Daniel_Leybovic

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

Daniel_Leybovic

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.