Skip to content

Draft: Drop redundant indexes from projects

Krasimir Angelov requested to merge ka/redundant-indexes/projects into master

What does this MR do and why?

See https://gitlab.com/groups/gitlab-org/-/epics/10638+ why redundnat indexes are problematic.

projects is on of the frequently used tables identified in gitlab-com/gl-infra/scalability#2301 (closed), reducing the number of indexes it has can help with avoiding LWLock contention.

This removes redundant indexes from the projects table, that were identified in the PG checkup report.

Indexes to be removed:

"index_on_projects_path" btree (path), covered by "index_projects_on_path_and_id" btree (path, id). Example query that needs this index:

	select * from projects where path = 'gitlab'

"index_projects_api_path_id_desc" btree (path, id DESC), covered by "index_projects_on_path_and_id" btree (path, id). Example query that needs this index:

select * from projects where path = 'gitlab' and id > 10000 order by id desc

"index_projects_api_created_at_id_desc" btree (created_at, id DESC), covered by "index_projects_on_created_at_and_id" btree (created_at, id). Example query that needs this index:

select * from projects where created_at > now() - interval '1 day' order by id desc

"index_projects_api_last_activity_at_id_desc" btree (last_activity_at, id DESC), covered by "index_projects_on_last_activity_at_and_id" btree (last_activity_at, id). Example query that needs this index:

select * from projects where last_activity_at > now() - interval '1 day' order by id desc

"index_projects_api_name_id_desc" btree (name, id DESC), covered by "index_projects_on_name_and_id" btree (name, id). Example query that needs this index:

select * from projects where name = 'GitLab' order by id desc

"index_projects_api_updated_at_id_desc" btree (updated_at, id DESC), covered by "index_projects_on_updated_at_and_id" btree (updated_at, id). Example query that needs this index:

select * from projects  where updated_at > now() - interval '1 day' order by id desc

"index_projects_on_id_service_desk_enabled" btree (id) WHERE service_desk_enabled = true, covered by "index_service_desk_enabled_projects_on_id_creator_id_created_at" btree (id, creator_id, created_at) WHERE service_desk_enabled = true. Example query that needs this index:

select * from projects where service_desk_enabled = true order by id limit 10

Screenshots or screen recordings

Screenshots are required for UI changes, and strongly recommended for all other merge requests.

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Krasimir Angelov

Merge request reports