-
Use a JOIN in IssuableFinder#by_project · 8591cc02Yorick Peterse authored
When using IssuableFinder/IssuesFinder to find issues for multiple projects it's more efficient to use a JOIN + a "WHERE project_id IN" condition opposed to running a sub-query. This change means that when finding issues without labels we're now using the following SQL: SELECT issues.* FROM issues JOIN projects ON projects.id = issues.project_id LEFT JOIN label_links ON label_links.target_type = 'Issue' AND label_links.target_id = issues.id WHERE ( projects.id IN (...) OR projects.visibility_level IN (20, 10) ) AND issues.state IN ('opened','reopened') AND label_links.id IS NULL ORDER BY issues.id DESC; instead of: SELECT issues.* FROM issues LEFT JOIN label_links ON label_links.target_type = 'Issue' AND label_links.target_id = issues.id WHERE issues.project_id IN ( SELECT id FROM projects WHERE id IN (...) OR visibility_level IN (20,10) ) AND issues.state IN ('opened','reopened') AND label_links.id IS NULL ORDER BY issues.id DESC; The big benefit here is that in the last case PostgreSQL can't properly use all available indexes. In particular it ends up performing a sequence scan on the "label_links" table (processing around 290 000 rows). The new query is roughly 2x as fast as the old query.
8591cc02
×