select sh.ctime,query_text,username, db,rsqname, avg(tfinish-tstart) as run_time, min(to_char(sh.ctime, 'Month')) as "month", min(to_char(sh.ctime, 'dd')) as "day", min(to_char(sh.ctime, 'Day')) weekday, min(to_char(sh.ctime, 'hh24')) as "hour", count(*) as cnt, round(avg(100 - cpu_idle)::numeric,2) as avg_cpu_used, round(min(100 - cpu_idle)::numeric,2) as min_cpu_used, round(max(100 - cpu_idle)::numeric,2) as max_cpu_used, round(avg(load2)::numeric,2) as avg_load2, round(min(load2)::numeric,2) as min_load2, round(max(load2)::numeric,2) as max_load2, round(avg(disk_rb_rate)::numeric,2) avg_disk_rb_rate, round(min(disk_rb_rate)::numeric,2) min_disk_rb_rate, round(max(disk_rb_rate)::numeric,2) max_disk_rb_rate, round(avg(disk_wb_rate)::numeric,2) avg_disk_wb_rate, round(min(disk_wb_rate)::numeric,2) min_disk_wb_rate, round(max(disk_wb_rate)::numeric,2) max_disk_wb_rate, round(avg(net_rb_rate)::numeric,2) avg_net_rb_rate, round(min(net_rb_rate)::numeric,2) min_net_rb_rate, round(max(net_rb_rate)::numeric,2) max_net_rb_rate, round(avg(net_wb_rate)::numeric,2) avg_net_wb_rate, round(min(net_wb_rate)::numeric,2) min_net_wb_rate, round(max(net_wb_rate)::numeric,2) max_net_wb_rate, round(avg(mem_actual_used)/power(1024,3)::numeric,2) avg_mem_actual_used_gb, round(min(mem_actual_used)/power(1024,3)::numeric,2) min_mem_actual_used_gb, round(max(mem_actual_used)/power(1024,3)::numeric,2) max_mem_actual_used_gb, round(avg(swap_used)::numeric,2) avg_swap_used, round(min(swap_used)::numeric,2) min_swap_used, round(max(swap_used)::numeric,2) max_swap_used from system_history sh,queries_history qh where sh.ctime between date_trunc('day',localtimestamp- interval '2 days') and date_trunc('day',localtimestamp) and sh.ctime=qh.ctime --and to_char(sh.ctime, 'dd')=28 -- If you want for a parerticular day --and username not in ('gpmon') and db not in ('gpperfmon') and date_part('hour',tfinish - tstart)*60+ date_part('minute',tfinish - tstart)> 30 group by sh.ctime,query_text,username, db,rsqname; |