Remove unused database indexes

The following indexes appear to be unused on our secondaries:

Unused indexes on a secondary:

tablename	indexname	num_rows	table_size	index_size	unique
approvals	approvals_pkey	163535	11 MB	3888 kB	Y
approver_groups	approver_groups_pkey	9772	752 kB	248 kB	Y
approver_groups	index_approver_groups_on_group_id	9772	752 kB	304 kB	N
approvers	index_approvers_on_user_id	318497	24 MB	9328 kB	N
award_emoji	award_emoji_pkey	143094	12 MB	3384 kB	Y
boards	index_boards_on_milestone_id	176610	13 MB	4072 kB	N
broadcast_messages	broadcast_messages_pkey	8	16 kB	16 kB	Y
chat_names	chat_names_pkey	700	88 kB	32 kB	Y
chat_teams	chat_teams_pkey	0	0 bytes	8192 bytes	Y
chat_teams	index_chat_teams_on_namespace_id	0	0 bytes	8192 bytes	Y
ci_pipelines	index_ci_pipelines_on_user_id	6.9105e+06	1319 MB	204 MB	N
ci_runners	index_ci_runners_on_locked	18099	116 MB	27 MB	N
deploy_keys_projects	deploy_keys_projects_pkey	89745	5416 kB	2344 kB	Y
emails	emails_pkey	30345	2352 kB	752 kB	Y
forked_project_links	forked_project_links_pkey	68230	4080 kB	1720 kB	Y
geo_nodes	geo_nodes_pkey	0	0 bytes	8192 bytes	Y
geo_nodes	index_geo_nodes_on_access_key	0	0 bytes	8192 bytes	N
geo_nodes	index_geo_nodes_on_host	0	0 bytes	8192 bytes	N
geo_nodes	index_geo_nodes_on_primary	0	0 bytes	8192 bytes	N
historical_data	historical_data_pkey	613	40 kB	32 kB	Y
identities	identities_pkey	390120	31 MB	8736 kB	Y
index_statuses	index_index_statuses_on_project_id	249	568 kB	15 MB	Y
index_statuses	index_statuses_pkey	249	568 kB	15 MB	Y
issue_metrics	index_issue_metrics	1.96058e+06	111 MB	44 MB	N
issue_metrics	issue_metrics_pkey	1.96058e+06	111 MB	43 MB	Y
issues	index_issues_on_confidential	6.03895e+06	7739 MB	768 MB	N
issues	index_issues_on_due_date	6.03895e+06	7739 MB	760 MB	N
label_links	label_links_pkey	2.75017e+06	182 MB	78 MB	Y
label_priorities	index_label_priorities_on_priority	13684	832 kB	456 kB	N
label_priorities	label_priorities_pkey	13684	832 kB	416 kB	Y
ldap_group_links	ldap_groups_pkey	0	0 bytes	8192 bytes	Y
lfs_objects_projects	lfs_objects_projects_pkey	1.6612e+06	99 MB	41 MB	Y
licenses	licenses_pkey	2	8192 bytes	16 kB	Y
lists	index_lists_on_label_id	264101	23 MB	13 MB	N
members	index_members_on_requested_at	2.72392e+06	299 MB	81 MB	N
merge_request_metrics	index_merge_request_metrics	1.893e+06	132 MB	45 MB	N
merge_request_metrics	index_merge_request_metrics_on_first_deployed_to_production_at	1.893e+06	132 MB	64 MB	N
merge_request_metrics	index_merge_request_metrics_on_pipeline_id	1.893e+06	132 MB	73 MB	N
merge_request_metrics	merge_request_metrics_pkey	1.893e+06	132 MB	44 MB	Y
merge_requests	index_merge_requests_on_title	3.30957e+06	2208 MB	521 MB	N
merge_requests_closing_issues	merge_requests_closing_issues_pkey	100203	7520 kB	29 MB	Y
milestones	index_milestones_on_due_date	153887	68 MB	16 MB	N
namespace_statistics	namespace_statistics_pkey	0	8192 bytes	16 kB	Y
namespaces	index_namespaces_on_ldap_sync_last_successful_update_at	2.22764e+06	396 MB	137 MB	N
namespaces	index_namespaces_on_ldap_sync_last_update_at	2.22764e+06	396 MB	137 MB	N
namespaces	index_namespaces_on_path	2.22764e+06	396 MB	122 MB	N
namespaces	index_on_namespaces_lower_path	2.22764e+06	396 MB	142 MB	N
notes	index_notes_on_created_at	2.24542e+07	9691 MB	780 MB	N
notes	index_notes_on_line_code	2.24542e+07	9691 MB	1300 MB	N
oauth_access_grants	oauth_access_grants_pkey	122124	24 MB	2792 kB	Y
oauth_openid_requests	oauth_openid_requests_pkey	0	8192 bytes	16 kB	Y
pages_domains	pages_domains_pkey	7656	920 kB	248 kB	Y
path_locks	index_path_locks_on_user_id	2182	176 kB	88 kB	N
path_locks	path_locks_pkey	2182	176 kB	72 kB	Y
project_features	project_features_pkey	2.54184e+06	209 MB	73 MB	Y
project_import_data	project_import_data_pkey	402718	68 MB	14 MB	Y
project_statistics	project_statistics_pkey	2.14661e+06	286 MB	105 MB	Y
projects	index_on_projects_lower_path	2.12765e+06	657 MB	104 MB	N
projects	index_projects_on_ci_id	2.12765e+06	657 MB	658 MB	N
projects	index_projects_on_last_repository_check_failed	2.12765e+06	657 MB	673 MB	N
projects	index_projects_on_mirror_last_successful_update_at	2.12765e+06	657 MB	134 MB	N
projects	index_projects_on_sync_time	2.12765e+06	657 MB	241 MB	N
protected_branch_merge_access_levels	index_protected_branch_merge_access_levels_on_user_id	1.3547e+06	79 MB	37 MB	N
protected_branch_merge_access_levels	protected_branch_merge_access_levels_pkey	1.3547e+06	79 MB	33 MB	Y
protected_branch_push_access_levels	index_protected_branch_push_access_levels_on_user_id	1.35288e+06	87 MB	37 MB	N
protected_branch_push_access_levels	protected_branch_push_access_levels_pkey	1.35288e+06	87 MB	33 MB	Y
push_rules	push_rules_pkey	1.98416e+06	154 MB	48 MB	Y
releases	releases_pkey	39943	22 MB	1000 kB	Y
remote_mirrors	index_remote_mirrors_on_last_successful_update_at	4157	16 MB	1120 kB	N
remote_mirrors	index_remote_mirrors_on_sync_time	4157	16 MB	1224 kB	N
remote_mirrors	remote_mirrors_pkey	4157	16 MB	992 kB	Y
schema_migrations	schema_migrations_version_key	763	40 kB	40 kB	Y
sent_notifications	sent_notifications_pkey	1.36598e+07	2047 MB	286 MB	Y
snippets	index_snippets_on_updated_at	132603	2164 MB	31 MB	N
subscriptions	subscriptions_pkey	48388	4344 kB	2376 kB	Y
taggings	taggings_pkey	4.61509e+06	363 MB	101 MB	Y
timelogs	index_timelogs_on_user_id	30214	2464 kB	984 kB	N
timelogs	timelogs_pkey	30214	2464 kB	1208 kB	Y
todos	index_todos_on_commit_id	2.60238e+06	247 MB	80 MB	N
todos	index_todos_on_note_id	2.60238e+06	247 MB	95 MB	N
u2f_registrations	u2f_registrations_pkey	1659	1776 kB	64 kB	Y
uploads	index_uploads_on_checksum	102678	21 MB	12 MB	N
uploads	index_uploads_on_model_id_and_model_type	102678	21 MB	4312 kB	N
user_agent_details	user_agent_details_pkey	1.22776e+06	231 MB	30 MB	Y
users	index_users_on_created_at	1.28482e+06	863 MB	76 MB	N
users	index_users_on_current_sign_in_at	1.28482e+06	863 MB	1071 MB	N
users	index_users_on_ghost	1.28482e+06	863 MB	150 MB	N
users_star_projects	users_star_projects_pkey	76242	4576 kB	2008 kB	Y

Unused indexes on the primary:

tablename	indexname	num_rows	table_size	index_size	unique
boards	index_boards_on_milestone_id	176610	13 MB	4072 kB	N
chat_teams	chat_teams_pkey	0	0 bytes	8192 bytes	Y
ci_runners	index_ci_runners_on_locked	18099	116 MB	27 MB	N
geo_nodes	geo_nodes_pkey	0	0 bytes	8192 bytes	Y
geo_nodes	index_geo_nodes_on_access_key	0	0 bytes	8192 bytes	N
geo_nodes	index_geo_nodes_on_primary	0	0 bytes	8192 bytes	N
historical_data	historical_data_pkey	613	40 kB	32 kB	Y
issues	index_issues_on_due_date	6.03895e+06	7739 MB	760 MB	N
label_priorities	index_label_priorities_on_priority	13684	832 kB	456 kB	N
ldap_group_links	ldap_groups_pkey	0	0 bytes	8192 bytes	Y
licenses	licenses_pkey	2	8192 bytes	16 kB	Y
merge_requests	index_merge_requests_on_title	3.30957e+06	2208 MB	521 MB	N
merge_requests_closing_issues	merge_requests_closing_issues_pkey	100253	7520 kB	29 MB	Y
namespaces	index_namespaces_on_deleted_at	2.22764e+06	396 MB	140 MB	N
namespaces	index_namespaces_on_ldap_sync_last_successful_update_at	2.22764e+06	396 MB	137 MB	N
namespaces	index_namespaces_on_ldap_sync_last_update_at	2.22764e+06	396 MB	137 MB	N
projects	index_on_projects_lower_path	2.12765e+06	657 MB	104 MB	N
sent_notifications	sent_notifications_pkey	1.36598e+07	2047 MB	286 MB	Y
snippets	index_snippets_on_updated_at	132603	2164 MB	31 MB	N
timelogs	index_timelogs_on_user_id	30214	2464 kB	984 kB	N
uploads	index_uploads_on_checksum	102678	21 MB	12 MB	N

This output was obtained by running:

SELECT
    t.tablename,
    indexname,
    c.reltuples AS num_rows,
    pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
    pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
    CASE WHEN indisunique THEN 'Y'
       ELSE 'N'
    END AS UNIQUE,
    idx_scan AS number_of_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
LEFT OUTER JOIN
    ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x
           JOIN pg_class c ON c.oid = x.indrelid
           JOIN pg_class ipg ON ipg.oid = x.indexrelid
           JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid )
    AS foo
    ON t.tablename = foo.ctablename
WHERE t.schemaname='public'
AND idx_scan = 0
ORDER BY 1,2;

Any indexes that are not used should be removed if possible.

Edited Dec 21, 2023 by Christina Lohr