Skip to content

database: Optimize IterateRepoGitserverStatus

Warren Gifford requested to merge ef/optimize-iterate-repo-gitsever-status into main

Created by: efritz

Reduces query times of IterateRepoGitserverStatus from 11s (rare) / 9s (often) -> 10s (rare) / 3.5s (often). This is in the top 10 queries contributing to database load according to query insights.

Insight: Breaking the LEFT JOIN into an INNER JOIN and a NOT EXISTS query that when combined forms the same result set has good performance benefits. It allows us to use indexes that are otherwise not available due to spurious filter conditions. The UNION ALL is especially nice since we don't care about order and each of the subqueries are non-intersecting (thus we neither need a sort nor a gather to reduce duplicates, both which are expensive in memory, and usually by extension latency).

Merge request reports