Index optimization: index_ci_pending_builds_on_partition_id_build_id
Everyone can contribute. Help move this issue forward while earning points, leveling up and collecting rewards.
Summary
The index index_ci_pending_builds_on_partition_id_build_id was originally introduced to support our CI partitioning efforts related to FKs (#498968 (comment 2454944661)). It is no longer needed for that specific purpose but we can't just drop it since we still need to enforce uniqueness on (partition_id, build_id) (#498968 (comment 2497789884)):
The others are unique indexes and for partitioned tables each unique index must contain the partition key. We assume that the job_id is unique, but if we want to be sure it must include the partition if since that pair is unique on the builds table.
Currently, the table ci_pending_builds has this index schema:
Indexes:
"ci_pending_builds_pkey" PRIMARY KEY, btree (id)
"index_ci_pending_builds_id_on_protected_partial" btree (id) WHERE protected = true
"index_ci_pending_builds_on_build_id" UNIQUE, btree (build_id)
"index_ci_pending_builds_on_namespace_id" btree (namespace_id)
"index_ci_pending_builds_on_partition_id_build_id" UNIQUE, btree (partition_id, build_id)
"index_ci_pending_builds_on_plan_id" btree (plan_id)
"index_ci_pending_builds_on_project_id" btree (project_id)
"index_ci_pending_builds_on_tag_ids" btree (tag_ids) WHERE cardinality(tag_ids) > 0
"index_gin_ci_pending_builds_on_namespace_traversal_ids" gin (namespace_traversal_ids)In terms of indexing, there is redundancy on build_id because of the index index_ci_pending_builds_on_build_id. We have an opportunity here to remove the indexing on just (build_id) and replace it with one on (build_id, partition_id).
Proposal
Reorder the columns on index_ci_pending_builds_on_partition_id_build_id to be (build_id, partition_id) and then drop "index_ci_pending_builds_on_build_id" UNIQUE, btree (build_id).
Since this index is currently fairly small, this is a low priority issue.