hello World,
Soon, Il will have to "monitor" indexes of a database in production...
I know pg_stattuple may help with this job. I also have several query to help me :
This query indicates if an index is invalid :
select ir.relname as indexname,
it.relname as tablename,
n.nspname as schemaname
from pg_index i
join pg_class ir on ir.oid = i.indexrelid
join pg_class it on it.oid = i.inderelid
join pg_namespace n on n.oid = it.relnamespace
where not i.indisvalid;
This one indicates if there are duplicated indexes
select il.table_name,
il.index_columns,
array_agg(il.index_name) as implied_indexes_name
from (
select
distinct(pct.relname,pci.relname,pi.indkey) as key,
pct.oid as table_oid,
pct.relname as table_name,
pci.relname as index_name,
pi.indkey as index_columns
from pg_index pi
join pg_class pci
on pi.indexrelid=pci.oid
join pg_class pct
on pi.indrelid=pct.oid
join pg_attribute pa
on pa.attrelid=pct.oid
where pct.relkind='r'
and pa.attnum=any(pi.indkey)
) il
group by il.table_name, il.index_columns
having count(*)>1;
I have found this one but i am not sure if it is technically correct :
- the table must be greater then 100 kB
- the way of a "missing index" is calculated ( Can i have your opinion?)
SELECT relname AS TableName,
seq_scan-idx_scan AS TotalSeqScan,
CASE WHEN seq_scan-idx_scan > 0
THEN 'Missing Index Found'
ELSE 'Missing Index Not Found'
END AS MissingIndex,
pg_size_pretty(pg_relation_size(concat(schemaname,'.',relname)::regclass)) AS TableSize,
idx_scan AS TotalIndexScan
FROM pg_stat_all_tables
WHERE schemaname !~'pg_catalog|pg_temp'
AND pg_relation_size(concat(schemaname,'.',relname)::regclass)>100000
ORDER BY 2 DESC;
Is there any others stuffs to keep an eye?
Is there any remarks about my queries?
Thanks a lot,
Thomas
