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.pardi@xxxxxxxxxxxx> 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
