• Yorick Peterse's avatar
    Use a JOIN in IssuableFinder#by_project · 8591cc02
    Yorick 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
issuable_finder.rb 5.49 KB