How do I find all on used projections?

Moderator: NorbertKrupa

Post Reply
jonhsame
Newbie
Newbie
Posts: 1
Joined: Fri Oct 21, 2016 5:53 am
Contact:

How do I find all on used projections?

Post by jonhsame » Fri Oct 21, 2016 5:59 am

Hi guys,

I am trying to clean up my database and want to find all the unused projections. Is the following query an accurate way to find them?

bi quyet lam giau

Code: Select all

select projection_name, projection_schema, anchor_table_name from projections
where projection_name not in (select projection_name from projection_usage)
order by projection_name, projection_schema, anchor_table_name;
Last edited by jonhsame on Mon Nov 07, 2016 6:16 pm, edited 1 time in total.
Cong ty thong cong nghet chuyen nghiep thong cong nghet hieu qua giup viec nha uy tin

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1800
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: How do I find all on used projections?

Post by JimKnicely » Mon Oct 24, 2016 6:56 pm

Hi,

Your query will show you the projections that have not been used. Well, those that have not been used based on the data available in the PROJECTION_USAGE table.

I would suggest using PROJECTION_ID in your WHERE clause:

Code: Select all

select projection_name, projection_schema, anchor_table_name from projections
where projection_id not in (select projection_id from projection_usage)
order by projection_name, projection_schema, anchor_table_name;
The PROJECTION_USAGE table has a column called QUERY_START_TIMESTAMP. You might want to take a look at the MIN value to see how far back you are looking for unused projections.

Code: Select all

dbadmin=> select min (query_start_timestamp) from projection_usage;
              min
-------------------------------
 2016-09-29 20:53:32.324724-04
(1 row)
You can also check the DC_PROJECTIONS_USED data collector table... You can control how long data is stored in this table by setting a policy.

Check out these links that discuss functions to help set this up:
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “New to Vertica Database Administration”