Re: indexes missing - Mailing list pgsql-admin

Hi Thomas,

I usually also take a look to 'Unused Indexes' and 'Index Bloat' too.


Unused indexes:

after some time you use your database, you should be able to identify indexes that are never used.

Is good to have in your database only indexes you use, given the impact of indexes into your db (space, time to write data where indexes are present), and remove the unused ones.



Index bloat:

https://wiki.postgresql.org/wiki/Index_Maintenance#Index_Bloat



On the same wiki page you can read more about indexes maintenance, which might clarify some other doubt you have.


About your question on missing indexes, I m not sure what to answer.
I think it depends a lot on your setup and how data is queried, eg: if my memory serves me well, sequential scan can be chosen by the query plan in some cases, even where an index is present.

A way I use to tackle slow queries is to periodically analyse statistics and to have slow queries reported in the logs (eg: queries slower than X ms)


regards,

fabio pardi



On 04/23/2018 12:22 PM, Thomas Poty wrote:
> 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