• Yorick Peterse's avatar
    Refactor ProjectsFinder#init_collection · d2934722
    Yorick Peterse authored
    This changes ProjectsFinder#init_collection so it no longer relies on a
    UNION. For example, to get starred projects of a user we used to run:
    
        SELECT projects.*
        FROM projects
        WHERE projects.pending_delete = 'f'
        AND (
            projects.id IN (
                SELECT projects.id
                FROM projects
                INNER JOIN users_star_projects
                    ON users_star_projects.project_id = projects.id
                INNER JOIN project_authorizations
                    ON projects.id = project_authorizations.project_id
                WHERE projects.pending_delete = 'f'
                AND project_authorizations.user_id = 1
                AND users_star_projects.user_id = 1
    
                UNION
    
                SELECT projects.id
                FROM projects
                INNER JOIN users_star_projects
                    ON users_star_projects.project_id = projects.id
                WHERE projects.visibility_level IN (20, 10)
                AND users_star_projects.user_id = 1
            )
        )
        ORDER BY projects.id DESC;
    
    With these changes the above query is turned into the following instead:
    
        SELECT projects.*
        FROM projects
        INNER JOIN users_star_projects
            ON users_star_projects.project_id = projects.id
        WHERE projects.pending_delete = 'f'
        AND (
            EXISTS (
                SELECT 1
                FROM project_authorizations
                WHERE project_authorizations.user_id = 1
                AND (project_id = projects.id)
            )
            OR projects.visibility_level IN (20,10)
        )
        AND users_star_projects.user_id = 1
        ORDER BY projects.id DESC;
    
    This query in turn produces a better execution plan and takes less time,
    though the difference is only a few milliseconds (this however depends
    on the amount of data involved and additional conditions that may be
    added).
    d2934722
refactor-projects-finder-init-collection.yml 134 Bytes