As a user I can't work with H002, H004, if there are many indexes
Goal
TODO / How to implement
Details here:https://postgres-ai.slack.com/archives/CE6NYN64W/p1571768101003800
Conclusions: we need to provide just a summary, similarly to bloat reports (F004, F005), with the total number of indexes found and the total size. We also need to calculate the percentage of the database these unused indexes occupy. If it is > 5%, we mark it (and Recommendations) as P2, otherwise, it is P3. We never use P1 here.
Recommendations: we need to provide the full list of DROP INDEX CONCURRENTLY
as "DO" database migrations
, and CREATE INDEX CONCURRENTLY
as "UNDO" database migrations
. Next to "DROP INDEX CONCURRENTLY" let's use additional comments, -- size: {{XXX K/M/GiB}}}, table: {{TABLE_NAME}}
.
Additional phrases to use:
- [P2] Use the database migration provided below to drop the unused indexes. Keep in mind, that under load, it is recommended to use `DROP INDEX CONCURRENTLY` (and `CREATE INDEX CONCURRENTLY` if reverting is needed) to avoid blocking issues.
- Be careful dropping the indexes. If you have various installations of your software, the analysis of just one setup might be not enough. Some indexes might be used only on a limited number of setups. Also, in some cases, developers prepare indexes for new features in advance – in this case dropping such indexes might be not a good idea.
- If there are some doubts, consider a more careful approach. Before actual dropping, indexes are disabled using queries like `UPDATE pg_index SET indisvalid = false WHERE indexrelid::regclass = (select oid from pg_class where relname = 'u_users_email');`. Indexes will continue to get updates. In case of some performance degradations, re-enable the corresponding indexes, setting `indisvalid` to `true`. If everything looks fine, after a significant period of observations, proceed with `DROP INDEX CONCURRENTLY`.
~~~ end of quote ~~~
Acceptance criteria
===
Edited by Anna