#### **Summary** GitLab Geo's `ReverificationBatchWorker` issues periodic UPDATE queries against `ci_job_artifact_states` to reset artifacts for re-verification. These queries filter on `verification_state` and `verified_at` and order by `verified_at` ASC, but no index exists covering these columns together. This causes PostgreSQL to fall back to a full sequential scan on every batch, resulting in execution times of 5–15 seconds at \~13,000 queries/day on production instances with significant data. The missing index `idx_cjas_verified_state_partition on (verification_state, verified_at, partition_id) WHERE partition_id IS NOT NULL` does not exist in any GitLab migration. It is absent from `structure.sql` and unknown to `gitlab:db:repair_index`. This was verified on both a production instance running 18.8.5 (upgraded) and a clean Docker install of `gitlab/gitlab-ee:18.8.5-ee.0`. #### **Steps to reproduce** ``` docker run --detach \ --hostname gitlab.local \ --publish 80:80 --publish 443:443 \ --name gitlab1885 \ gitlab/gitlab-ee:18.8.5-ee.0 ``` Once running: `docker exec -it gitlab1885 gitlab-psql` ``` -- Confirm index is absent SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'ci_job_artifact_states' AND indexname = 'idx_cjas_verified_state_partition'; -- Returns: (0 rows) -- Full index list on the table for reference SELECT indexname, indexdefFROM pg_indexesWHERE tablename = 'ci_job_artifact_states' ORDER BY indexname; ``` Confirm the repair task has no knowledge of it: ``` docker exec gitlab1885 bash -c "DRY_RUN=true gitlab-rake gitlab:db:repair_index 2>/dev/null" # idx_cjas_verified_state_partition does not appear anywhere in the output ``` #### **Observed behavior** Six indexes exist on `ci_job_artifact_states` in a fresh 18.8.5 install: ``` ci_job_artifact_states_pkey index_job_artifact_states_failed_verification index_job_artifact_states_needs_verification index_job_artifact_states_on_verification_state index_job_artifact_states_pending_verification index_on_job_artifact_id_partition_id_verification_state ``` `idx_cjas_verified_state_partition` is not among them. The schema checker does not flag it as missing (confirming it was never added to `structure.sql`).\  \ On a production instance with significant data, the re-verification UPDATE query performs a sequential scan over the full table, with \~26 million rows removed by filter and execution times of 4,300–5,428 ms per call. #### **Expected behavior** The index should be created by a migration and present in `structure.sql`: ``` CREATE INDEX idx_cjas_verified_state_partitionON ci_job_artifact_states (verification_state, verified_at, partition_id) WHERE partition_id IS NOT NULL; ``` After manual creation, execution time drops from \~4,300 ms to \~0.09 ms and the query planner uses the index correctly. #### **Affected query** Issued by `Geo::ReverificationBatchWorker`: ``` UPDATE ci_job_artifact_statesSET verification_state = 0 WHERE job_artifact_id IN (SELECT job_artifact_id FROM p_ci_job_artifacts INNER JOIN ci_job_artifact_states cjas ON cjas.job_artifact_id = p_ci_job_artifacts.id AND cjas.partition_id = p_ci_job_artifacts.partition_id WHERE cjas.partition_id IS NOT NULL AND cjas.verification_state = 2 AND cjas.verified_at < '<timestamp>' ORDER BY cjas.verified_at ASC LIMIT 1000 ); ``` #### **Workaround** ``` CREATE INDEX CONCURRENTLY idx_cjas_verified_state_partitionON ci_job_artifact_states (verification_state, verified_at, partition_id) WHERE partition_id IS NOT NULL; ``` #### **Environment** * **GitLab version:** 18.8.5-ee (reproduced on both fresh Docker install and upgraded production instance) * **PostgreSQL version:** 16.11 * **Architecture:** Reproduced on `aarch64` (Apple Silicon Docker, likely affects all platforms * **Installation type:** Docker (fresh), Self-managed (production) ## Implementation Plan 1. **Local verification (Phase 1 closeout)**: in GDK, seed some `ci_job_artifact_states` rows, run the reverification UPDATE under `EXPLAIN (ANALYZE, BUFFERS)`, confirm seq scan; create `(verified_at) WHERE verification_state = 2` manually, re-run EXPLAIN to confirm index scan + plan time drop. Paste both plans into the issue. 2. **Async index workflow** (two MRs, since `ci_job_artifact_states` is a very large table - see [Create indexes asynchronously](https://docs.gitlab.com/development/database/adding_database_indexes/#create-indexes-asynchronously)): - **MR 1** — post-deploy migration calling `prepare_async_index` on `ci_job_artifact_states` for `[:verified_at]` with `where: 'verification_state = 2'` and `name: 'index_job_artifact_states_reverification'`. Pair with `unprepare_async_index_by_name` in `down`. Add a TODO comment linking the follow-up issue for MR 2. - **MR 2** (after async build on .com succeeds, verified via ChatOps + Database Lab) — post-deploy migration with `disable_ddl_transaction!`, `milestone`, and `add_concurrent_index :ci_job_artifact_states, :verified_at, where: 'verification_state = 2', name: 'index_job_artifact_states_reverification'`. `down` uses `remove_concurrent_index_by_name`. No-op on .com, does the work on self-managed. 3. **Regenerate `db/structure.sql`** and add the new migration version under `db/schema_migrations/` (in MR 2). 4. **Migration spec** under `spec/migrations/` verifying index existence after `migrate!` and removal after `schema_migrate_down!` (in MR 2). 5. **Database review** — EXPLAIN before/after on Database Lab, index size estimate. The partial-on-state shape mirrors the existing `index_job_artifact_states_pending_verification` (state=0) and `index_job_artifact_states_failed_verification` (state=3) pattern.