📌 Description
internal/handlers/leaderboard.go's leaderboardBaseQuery (shared by both the data query and the count query, per its own doc comment) computes "issue count + PR count for this contributor" as a pair of correlated subqueries in the SELECT list, to produce contribution_count:
(
SELECT COUNT(*)
FROM github_issues i
INNER JOIN projects p ON i.project_id = p.id
WHERE LOWER(i.author_login) = LOWER(ac.login) AND p.status = 'verified'
) +
(
SELECT COUNT(*)
FROM github_pull_requests pr
INNER JOIN projects p ON pr.project_id = p.id
WHERE LOWER(pr.author_login) = LOWER(ac.login) AND p.status = 'verified'
) as contribution_count,
and then, further down in the same query, the exact same pair of correlated subqueries is repeated verbatim in the WHERE clause, just to filter out contributors with zero total contributions:
WHERE (
SELECT COUNT(*)
FROM github_issues i
INNER JOIN projects p ON i.project_id = p.id
WHERE LOWER(i.author_login) = LOWER(ac.login) AND p.status = 'verified'
) +
(
SELECT COUNT(*)
FROM github_pull_requests pr
INNER JOIN projects p ON pr.project_id = p.id
WHERE LOWER(pr.author_login) = LOWER(ac.login) AND p.status = 'verified'
) > 0
Since both subquery pairs are correlated to the same outer row (ac.login), PostgreSQL has no way to know they're identical and will (in the absence of a smart-enough planner optimization, which correlated-subquery-in-WHERE-plus-SELECT patterns often defeat) execute all four correlated subqueries per output row of all_contributors — twice the necessary database work per row, on a query this handler's own doc comment says is "shared by the data and count queries" (i.e. run at least twice per request already, for the page of results and the total count). As github_issues/github_pull_requests grow, this doubles an already-expensive per-contributor correlated-subquery cost for no behavioral benefit — the WHERE clause's filter could instead simply reference the already-computed contribution_count alias (if the SQL dialect/query structure allows) or restructure the query so the count is computed once and reused for both the SELECT and the filter.
🧩 Requirements and context
- The issue-count-plus-PR-count computation for a given contributor must be computed once per row and reused for both display (
contribution_count) and filtering (> 0), not duplicated as separate correlated subqueries.
- Preserve the exact existing output columns, ordering, and filtering semantics (only contributors with at least one issue+PR total across verified projects are included) — this is a query-restructuring/performance fix, not a behavior change.
- Since this query is shared between the paginated data query and the total-count query (per the existing doc comment), the fix should benefit both call sites, not just one.
🛠️ Suggested execution
- Restructure
leaderboardBaseQuery in internal/handlers/leaderboard.go to compute the combined count once, e.g. wrap the current SELECT in an outer query so the WHERE ... > 0 filter can reference the inner contribution_count column directly (SELECT * FROM (<current select without the outer filter>) sub WHERE sub.contribution_count > 0), which lets PostgreSQL compute the correlated subqueries once per row inside the inner query and simply filter on the result.
- Alternatively, replace the two correlated subquery pairs with a single
LEFT JOIN LATERAL (or a pre-aggregated CTE joining issue/PR counts by author_login once) if that fits this codebase's existing SQL style better — check whether internal/handlers/rank.go or other ranking-adjacent queries already establish a preferred pattern for this kind of aggregate-by-login computation.
- After restructuring, run
EXPLAIN ANALYZE against a representative dataset (or add a comment noting this was checked) to confirm the correlated subqueries are now evaluated once per row instead of twice, and add/extend a test in internal/handlers/leaderboard_test.go asserting the query's output (ordering, included/excluded contributors, and count values) is identical before and after the restructuring for the same fixture data.
✅ Acceptance criteria
🔒 Security notes
No new attack surface; this is a database-load/performance fix. As the platform's contribution history grows, this query's cost was scaling worse than necessary (redundant correlated-subquery evaluation on a query already run at least twice per leaderboard request), which is exactly the kind of quietly-compounding inefficiency that turns into a real latency or database-load problem well before anyone notices from the code alone.
📋 Guidelines
- Minimum 95% test coverage
- Clear documentation
- Timeframe: 96 hours
📌 Description
internal/handlers/leaderboard.go'sleaderboardBaseQuery(shared by both the data query and the count query, per its own doc comment) computes "issue count + PR count for this contributor" as a pair of correlated subqueries in theSELECTlist, to producecontribution_count:and then, further down in the same query, the exact same pair of correlated subqueries is repeated verbatim in the
WHEREclause, just to filter out contributors with zero total contributions:Since both subquery pairs are correlated to the same outer row (
ac.login), PostgreSQL has no way to know they're identical and will (in the absence of a smart-enough planner optimization, which correlated-subquery-in-WHERE-plus-SELECT patterns often defeat) execute all four correlated subqueries per output row ofall_contributors— twice the necessary database work per row, on a query this handler's own doc comment says is "shared by the data and count queries" (i.e. run at least twice per request already, for the page of results and the total count). Asgithub_issues/github_pull_requestsgrow, this doubles an already-expensive per-contributor correlated-subquery cost for no behavioral benefit — theWHEREclause's filter could instead simply reference the already-computedcontribution_countalias (if the SQL dialect/query structure allows) or restructure the query so the count is computed once and reused for both theSELECTand the filter.🧩 Requirements and context
contribution_count) and filtering (> 0), not duplicated as separate correlated subqueries.🛠️ Suggested execution
leaderboardBaseQueryininternal/handlers/leaderboard.goto compute the combined count once, e.g. wrap the currentSELECTin an outer query so theWHERE ... > 0filter can reference the innercontribution_countcolumn directly (SELECT * FROM (<current select without the outer filter>) sub WHERE sub.contribution_count > 0), which lets PostgreSQL compute the correlated subqueries once per row inside the inner query and simply filter on the result.LEFT JOIN LATERAL(or a pre-aggregated CTE joining issue/PR counts byauthor_loginonce) if that fits this codebase's existing SQL style better — check whetherinternal/handlers/rank.goor other ranking-adjacent queries already establish a preferred pattern for this kind of aggregate-by-login computation.EXPLAIN ANALYZEagainst a representative dataset (or add a comment noting this was checked) to confirm the correlated subqueries are now evaluated once per row instead of twice, and add/extend a test ininternal/handlers/leaderboard_test.goasserting the query's output (ordering, included/excluded contributors, and count values) is identical before and after the restructuring for the same fixture data.✅ Acceptance criteria
contribution_count's underlying issue+PR count is computed once per contributor row, not twice (once for display, once for filtering).contribution_countvalues, and total count) is unchanged for the same underlying data.EXPLAIN ANALYZEor an equivalent check noted in the PR).🔒 Security notes
No new attack surface; this is a database-load/performance fix. As the platform's contribution history grows, this query's cost was scaling worse than necessary (redundant correlated-subquery evaluation on a query already run at least twice per leaderboard request), which is exactly the kind of quietly-compounding inefficiency that turns into a real latency or database-load problem well before anyone notices from the code alone.
📋 Guidelines