Re: indexes missing

Thanks Fabio for your useful advice.
About index bloat and bloat in general, if i am right it is due to mvcc. So
my question is : is there any way to retrieve old data?

Thank you

Thomas

Le lun. 23 avr. 2018 à 17:22, Fabio Pardi <f(dot)pardi(at)portavita(dot)eu> a écrit :

> 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
>