with cte_indexes as (
		select
		p.schemaname, 
		p.relname as table_name,
		p.indexrelname as index_name,
    pg_relation_size(p.indexrelid) as index_size,
    pg_relation_size(p.relid) as table_size,
    p.idx_scan,
		p.indexrelid,
		c.reloptions
		
		--,case when POSITION ('.' in p.indexrelid::regclass::text) > 0 then split_part(p.indexrelid::regclass::text, '.', 2) else p.indexrelid::regclass::text end as idx
	from pg_stat_user_indexes p
    inner join pg_class c on p.indexrelid = c.oid
    where 1=1
		and pg_get_indexdef(p.indexrelid) like '%USING btree%'
    and p.relname like'evn%'
		and pg_relation_size(p.relid) > 1024*1024
	  --and ( pg_relation_size(p.relid) <= pg_relation_size(p.indexrelid)) 
		)
		
	select cte_indexes.* ,
	
	   round((fs.free_space*100/index_size)::numeric, 1) as waste_percent,
		 		pg_size_pretty(fs.free_space) as waste
	from cte_indexes
		
		left join LATERAL
		
	    (select (case when avg_leaf_density = 'NaN' then 0 
        else greatest(ceil(index_size * (1 - avg_leaf_density / (coalesce((SELECT (regexp_matches(reloptions::text, E'.*fillfactor=(\\d+).*'))[1]),'90')::real)))::bigint, 0) end) as free_space
        from pgstatindex(cte_indexes.schemaname || '.' || cte_indexes.index_name)
				--from pgstatindex(cte_indexes.indexrelid)
    ) as FS on true
		
where round((fs.free_space*100/index_size)::numeric, 1) >=50
	order by fs.free_space desc;	
		--select * from  pgstattuple ('idx_evnlabsample_evn_deleted')

