-- Step 1
--drop table dbadmin.smart_analyze_config cascade;
 
CREATE TABLE dbadmin.smart_analyze_config (
schemaname TEXT NOT NULL,      -- schema name
tablename TEXT NOT NULL,          --  table name
noofcolindistkey integer NOT NULL, -- No of Columns in the Distribution Key
columns TEXT,  -- Comma separated list of columns to be analyzed. Default is distribution key. If this field is RANDOM or empty, all columns will be analyzed
threshold INTERVAL NOT NULL,     -- Interval (in hours) of time after which statistics for this table are considered stale
relpages integer not null,
reltuples  real not null
)
WITH (APPENDONLY=FALSE)
DISTRIBUTED RANDOMLY;
 
-- Step 2
 
--drop view dbadmin.v_table_distribution_key1;
create or replace view dbadmin.v_table_distribution_key1
as
select distinct
schemaname,
tablename,
threshold,
relpages,
reltuples,
count(distributionkey) over (partition by schemaname,tablename) as noofcolindistkey,
array_to_string(array_agg(distributionkey) over (partition by schemaname,tablename),',') as distkey
from
(
SELECT pgn.nspname as schemaname,                                                                          
pgc.relname as tablename,                                                                                                                                   
pga.attname  as distributionkey,distrokey.attnum,
'72 hours' as threshold,
 pgc.relpages,
 pgc.reltuples,
pgc.relkind,
 pgc.relstorage                                                                                                           
FROM (
     SELECT gdp.localoid,                                                                                                                                   
     CASE                                                                                                                                                         
     WHEN ( Array_upper(gdp.attrnums, 1) > 0 ) THEN Unnest(gdp.attrnums) ELSE NULL END AS attnum                                                                                                                                               
     FROM gp_distribution_policy gdp                                                                                                                             
     ORDER BY gdp.localoid
      ) AS distrokey                                                                                                                          
INNER JOIN pg_class AS pgc ON (distrokey.localoid = pgc.oid and pgc.relkind='r' and pgc.relstorage !='x' and pgc.relname not like '%_err__')                                                                                                                           
INNER JOIN pg_namespace pgn ON ( pgc.relnamespace = pgn.oid  and pgn.nspname NOT IN ('gp_toolkit','information_schema','pg_catalog'))                                                                                                                              
LEFT OUTER JOIN pg_attribute pga ON distrokey.attnum = pga.attnum                                                                                                                            
AND distrokey.localoid = pga.attrelid
ORDER by pgn.nspname,pgc.relname,distrokey.attnum
)
as dkey;
 
--select relname from pg_class pgc  where pgc.relkind='r'  and pgc.relname not like '%_err__' and pgc.relstorage !='x'
--
--delete from dbadmin.smart_analyze_config;
--select * from dbadmin.smart_analyze_config where noofcolindistkey is NULL;
-- Step 3
 
insert into dbadmin.smart_analyze_config (schemaname,tablename,columns,threshold,relpages,reltuples,noofcolindistkey)
SELECT
schemaname,
tablename,
distkey,
'72 hours',
relpages,
reltuples,
noofcolindistkey
FROM dbadmin.v_table_distribution_key1;
 
-- select * from dbadmin.smart_analyze_config;
-- Step 4
 
create or replace view dbadmin.v_smart_analyze_config1
as
select'"'||schemaname||'"'||'.'||'"'||tablename||'"' as tablename ,
CASE
WHEN columns is NULL THEN 'ANALYZE '||'"'||schemaname||'"'||'.'||'"'||tablename||'"'||';'
ELSE 'ANALYZE '||'"'||schemaname||'"'||'.'||'"'||tablename||'"'||'('||columns||')'||';'  END as analyzecommand,
lastanalyzed,
relpages,reltuples
from
(
select pso.schemaname,pso.objname as tablename,columns,max(pso.statime) as lastanalyzed,max(sac.threshold) as thresholdinterval,max(sac.relpages) as relpages,max(sac.reltuples) as reltuples
from pg_stat_operations pso ,dbadmin.smart_analyze_config sac
where pso.schemaname=sac.schemaname
and pso.objname=sac.tablename
and pso.actionname='ANALYZE'
group by 1,2,3
order by 3 desc  ) as a
where now()-lastanalyzed>thresholdinterval
order by 3 asc,4 desc,5 desc;
 
 
--update dbadmin.smart_analyze_config set threshold='8 hours';