indexes missing

[Thomas Poty <thomas.poty@xxxxxxxxx>]

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