Remove various redundant indexes

One can see which indexes are used in PostgreSQL by running the
following query:

    SELECT relname as table_name, indexrelname as index_name, idx_scan, idx_tup_read, idx_tup_fetch, pg_size_pretty(pg_relation_size(indexrelname::regclass))
    FROM pg_stat_all_indexes
    WHERE schemaname = 'public'
    AND "idx_scan" = 0
    ORDER BY pg_relation_size(indexrelname::regclass) desc;

Using this query I built a list of indexes that could be potentially
removed. After checking every single one by hand to make sure they
really aren't used I only found 1 index that _would_ be used. This was a
GitLab GEO index (EE) specific that's currently not used simply because
the table is empty.

Apart from this one index all indexes could be removed. The migration
also takes care of 6 composite indexes that can be replaced with a
single column index, which in most cases was already present.

For more information see gitlab-org/gitlab-ce#20767.

1 files changed, 1 insertions, 0 deletions

diff --git a/CHANGELOG b/CHANGELOG
index 3b567d9ec2c..848080e5ed7 100644
--- a/CHANGELOG
+++ b/CHANGELOG
@@ -26,6 +26,7 @@ v 8.11.0 (unreleased)
   - Add experimental Redis Sentinel support !1877
   - Fix branches page dropdown sort initial state (ClemMakesApps)
   - Environments have an url to link to
+  - Various redundant database indexes have been removed
   - Update `timeago` plugin to use multiple string/locale settings
   - Remove unused images (ClemMakesApps)
   - Limit git rev-list output count to one in forced push check