Re: indexes missing - Mailing list pgsql-admin
From | Fabio Pardi |
---|---|
Subject | Re: indexes missing |
Date | |
Msg-id | 4144ded0-09e2-6981-4579-8d0922d0bf65@portavita.eu Whole thread Raw |
In response to | indexes missing (Thomas Poty <thomas.poty@gmail.com>) |
Responses |
Re: indexes missing
|
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 datawhere 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 chosenby 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
pgsql-admin by date: