Improve SSO querysets #12447
Merged
Improve SSO querysets #12447
+14
−32
Conversation
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
There was a problem hiding this comment.
This looks sensible to me as an approach. Given the permissions involved, would like to see @humitos or @agjohnson review it as well.
|
Re-ping @humitos @agjohnson for review this week. |
stsewd
added a commit
that referenced
this pull request
Oct 8, 2025
This was introduced in #12447. Even locally the previous query is slow, after trying a couple of things I was able to fix it. This is the comparison running explain in production: Current code (rolled back) ``` Out[24]: 'Sort (cost=7188.29..7209.50 rows=8486 width=715)\n Sort Key: projects_project.slug\n -> Hash Left Join (cost=3517.65..3936.04 rows=8486 width=715)\n Hash Cond: (organizations_organization.id = sso_ssointegration.organi zation_id)\n Filter: ((organizations_team_projects.team_id = 2457) OR (organizations_team_projects.team_id = 6031) OR (organizations_team_projects.team_id = 2302) OR (organizations_team_projects.team_id = 5976) OR (organizations_team _projects.team_id = 6032) OR (organizations_organization_projects.organization_id = 5511) OR (organizations_organization_projects.organization_id = 11709) OR (organizations_organization_projects.organization_id = 1236) OR (organizations_org anization_projects.organization_id = 2991) OR (organizations_organization_projects.organization_id = 8804) OR (organizations_organization_projects.organization_id = 12806) OR (organizations_organization_projects.organization_id = 13165) OR ((sso_ssointegration.id IS NOT NULL) AND (hashed SubPlan 1)) OR ((sso_ssointegration.id IS NOT NULL) AND (hashed SubPlan 2)))\n -> Hash Left Join (cost=1639.26..2028.00 rows=11278 width=727)\n Hash Cond: (organizations _organization_projects.organization_id = organizations_organization.id)\n -> Hash Left Join (cost=1033.89..1393.01 rows=11278 width=723)\n Hash Cond: (projects_project.id = organizations_organization_projec ts.project_id)\n -> Hash Right Join (cost=790.75..995.15 rows=11278 width=719)\n Hash Cond: (organizations_team_projects.project_id = projects_project.id)\n -> Seq Scan on organizations_team_projects (cost=0.00..174.78 rows=11278 width=8)\n -> Hash (cost=682.00..682.00 rows=8700 width=715)\n -> Seq Scan on projects_project (cost=0.00..682.00 row s=8700 width=715)\n -> Hash (cost=134.73..134.73 rows=8673 width=8)\n -> Seq Scan on organizations_organization_projects (cost=0.00..134.73 rows=8673 width=8)\n -> Hash (cost=43 5.72..435.72 rows=13572 width=4)\n -> Seq Scan on organizations_organization (cost=0.00..435.72 rows=13572 width=4)\n -> Hash (cost=5.87..5.87 rows=287 width=8)\n -> Seq Scan on sso_ssointegration (cost=0.00..5.87 rows=287 width=8)\n SubPlan 1\n -> Index Scan using oauth_remoterepositoryrelation_user_id_3c2baf85 on oauth_remoterepositoryrelation u0 (cost=0.43..934.05 rows=100 width=4)\n Index Cond: (user_id = 2939)\n Filter: admin\n SubPlan 2\n -> Index Scan using oauth_remoterepositoryrelation_user_id_3c2baf85 on oauth_remoterepositoryrelation u0_1 (cost=0.43..934.05 rows=232 width=4)\n Index Cond: (user_id = 2939)\n Filter: (NOT admin)' ``` Code from main ``` Out[13]: "Sort (cost=303529.85..303541.53 rows=4672 width=715)\n Sort Key: projects_project.slug\n -> Hash Left Join (cost=3726.61..303245.09 rows=4672 width=715)\n Hash Cond: (organizations_organization.id = sso_ssointegration. organization_id)\n Filter: ((((sso_ssointegration.provider)::text = 'allauth'::text) AND (hashed SubPlan 1)) OR ((((organizations_team.access)::text = 'admin'::text) OR ((organizations_team.access)::text = 'readonly'::text)) AND (org anizations_teammember.member_id = 2939) AND (NOT (hashed SubPlan 3))) OR ((organizations_organizationowner.owner_id = 2939) AND (NOT (hashed SubPlan 5))))\n -> Hash Left Join (cost=2782.27..302239.85 rows=10402 width=734)\n Hash Cond: (projects_project.id = organizations_team_projects.project_id)\n -> Nested Loop Left Join (cost=936.03..300038.84 rows=8024 width=723)\n -> Nested Loop Left Join (cost=935.75..297563.62 r ows=7612 width=719)\n -> Merge Left Join (cost=935.45..295996.09 rows=7612 width=719)\n Merge Cond: (projects_project.id = organizations_organization_projects.project_id)\n -> Index Scan using projects_project_pkey on projects_project (cost=935.17..295550.85 rows=7612 width=715)\n Filter: ((hashed SubPlan 1) OR (NOT (hashed SubPlan 3)) OR (NOT (hashe d SubPlan 5)))\n SubPlan 1\n -> Index Scan using oauth_remoterepositoryrelation_user_id_3c2baf85 on oauth_remoterepositoryrelation u0 (cost=0.43..934.05 rows=332 width=4)\n Index Cond: (user_id = 2939)\n SubPlan 3\n -> Nested Loop (cost=10.10..451.03 rows=165 width=4)\n -> Hash Join (cost=9.81..167.34 rows=679 width=12)\n Hash Cond: (u1.organization_id = u3.organization_id)\n -> Seq Scan on organizations_organization_projects u1 (cost=0.00..134.73 rows=8673 width=8)\n -> Hash (cost=6.59..6.59 rows=258 width=4)\n -> Seq Scan on sso_ssointegration u3 (cost=0.00..6.59 rows=258 width=4)\n Filter: ((provider)::text = 'allauth'::text)\n -> Index Only Scan using organizations_organization_pkey on organizations_organization u2 (cost=0.29..0.42 rows=1 width=4)\n Index Cond: (id = u1.organization_id)\n SubPlan 5\n -> Nested Loop (cost=10.10..451.03 rows=165 width=4)\n -> Hash Join (cost=9.81..167.34 rows=679 width=12)\n Hash Cond: (u1_1.organization_id = u3_1.organization_id)\n -> Seq Scan on organizations_organization_projects u1_1 (cost=0.00..134.73 rows=867 3 width=8)\n -> Hash (cost=6.59..6.59 rows=258 width=4)\n -> Seq Scan on sso_ssointegration u3_1 (cost=0.00..6.59 rows=258 width =4)\n Filter: ((provider)::text = 'allauth'::text)\n -> Index Only Scan using organizations_organization_pkey on organizations_orga nization u2_1 (cost=0.29..0.42 rows=1 width=4)\n Index Cond: (id = u1_1.organization_id)\n -> Index Scan using organizations_organization_projects_project_i d on organizations_organization_projects (cost=0.29..328.65 rows=8673 width=8)\n -> Memoize (cost=0.30..0.43 rows=1 width=4)\n Cache Key: organizations_organization_projects.organiz ation_id\n Cache Mode: logical\n -> Index Only Scan using organizations_organization_pkey on organizations_organization (cost=0.29..0.42 rows=1 width=4)\n Index Cond: (id = organizations_organization_projects.organization_id)\n -> Index Only Scan using organizations_organization_owners_organization_id_user_id_key on organizations_organizationowner (cost=0. 29..0.32 rows=1 width=8)\n Index Cond: (organization_id = organizations_organization.id)\n -> Hash (cost=1705.27..1705.27 rows=11278 width=15)\n -> Hash Right Join (cost=1293.80.. 1705.27 rows=11278 width=15)\n Hash Cond: (organizations_teammember.team_id = organizations_team.id)\n -> Seq Scan on organizations_teammember (cost=0.00..275.29 rows=17629 width=8)\n -> Hash (cost=1152.82..1152.82 rows=11278 width=15)\n -> Hash Left Join (cost=948.44..1152.82 rows=11278 width=15)\n Hash Cond: (organizations_team _projects.team_id = organizations_team.id)\n -> Seq Scan on organizations_team_projects (cost=0.00..174.78 rows=11278 width=8)\n -> Hash (cost=593.75..593.75 rows =28375 width=11)\n -> Seq Scan on organizations_team (cost=0.00..593.75 rows=28375 width=11)\n -> Hash (cost=5.87..5.87 rows=287 width=11)\n -> Seq Scan on sso_ssointegrati on (cost=0.00..5.87 rows=287 width=11)" ``` Fixed code ``` Out[25]: "Index Scan using projects_project_slug_uniq on projects_project (cost=2417.89..5720.59 rows=7612 width=715)\n Filter: ((hashed SubPlan 1) OR (hashed SubPlan 2) OR (hashed SubPlan 3))\n SubPlan 1\n -> Nested Loop Semi Join (cost=10.81..2307.67 rows=1 width=4)\n Join Filter: (v0.remote_repository_id = u0.remote_repository_id)\n -> Nested Loop (cost=10.38..551.09 rows=165 width=8)\n -> Nested Loop (cost=10.10..451.03 rows=16 5 width=4)\n -> Hash Join (cost=9.81..167.34 rows=679 width=12)\n Hash Cond: (v1.organization_id = v3.organization_id)\n -> Seq Scan on organizations_organization _projects v1 (cost=0.00..134.73 rows=8673 width=8)\n -> Hash (cost=6.59..6.59 rows=258 width=4)\n -> Seq Scan on sso_ssointegration v3 (cost=0.00..6.59 rows=258 width=4)\n Filter: ((provider)::text = 'allauth'::text)\n -> Index Only Scan using organizations_organization_pkey on organizations_organization v2 (cost=0.29..0.42 rows=1 width=4)\n Index Cond: (id = v1.organization_id)\n -> Index Scan using projects_project_pkey on projects_project v0 (cost=0.29..0.61 rows=1 width=8)\n Index Cond: (id = v1.project_id)\n -> Materialize (cost=0.43..935.71 rows=332 width=4)\n -> Index Scan using oauth_remoterepositoryrelation_user_id_3c2baf85 on oauth_remoterepositoryrelation u0 (cost=0.43..934.05 rows=332 width=4)\n In dex Cond: (user_id = 2939)\n SubPlan 2\n -> Nested Loop Anti Join (cost=1.86..58.55 rows=1 width=4)\n -> Nested Loop (cost=1.15..57.61 rows=1 width=4)\n -> Nested Loop (cost=0.86..57.25 rows=1 width=4)\n -> Nested Loop (cost=0.57..55.72 rows=4 width=8)\n -> Index Scan using organizations_team_members_user_id on organizations_teammember v3_1 (cost=0.29..14.17 rows=5 width=4)\n Index Cond: (member_id = 2939)\n -> Index Scan using organizations_team_pkey on organizations_team v2_1 (cost=0.29..8.31 rows=1 width=4)\n Index Cond: (id = v3_ 1.team_id)\n Filter: (((access)::text = 'admin'::text) OR ((access)::text = 'readonly'::text))\n -> Index Only Scan using organizations_team_projects_team_id_project_id_key on organizat ions_team_projects v1_1 (cost=0.29..0.35 rows=3 width=8)\n Index Cond: (team_id = v2_1.id)\n -> Index Only Scan using projects_project_pkey on projects_project v0_1 (cost=0.29..0.36 rows=1 width= 4)\n Index Cond: (id = v1_1.project_id)\n -> Nested Loop (cost=0.72..0.93 rows=1 width=4)\n -> Nested Loop (cost=0.57..0.76 rows=1 width=12)\n -> Index Scan using organi zations_organization_projects_project_id on organizations_organization_projects u1 (cost=0.29..0.34 rows=1 width=8)\n Index Cond: (project_id = v0_1.id)\n -> Index Only Scan using organizati ons_organization_pkey on organizations_organization u2 (cost=0.29..0.42 rows=1 width=4)\n Index Cond: (id = u1.organization_id)\n -> Index Scan using sso_ssointegration_organization_id_key on sso_ ssointegration u3 (cost=0.15..0.17 rows=1 width=4)\n Index Cond: (organization_id = u1.organization_id)\n Filter: ((provider)::text = 'allauth'::text)\n SubPlan 3\n -> Nested Loop Anti Join ( cost=1.86..51.38 rows=4 width=4)\n -> Nested Loop (cost=1.14..47.63 rows=4 width=4)\n -> Nested Loop (cost=0.86..46.14 rows=4 width=4)\n -> Nested Loop (cost=0.57..41.54 rows=11 width=12)\n -> Index Scan using organizations_organization_owners_user_id on organizations_organizationowner v3_2 (cost=0.29..10.97 rows=7 width=4)\n Index Cond: (owner_id = 2939)\n -> Index Only Scan using organizations_organization_proje_organization_id_project_id_key on organizations_organization_projects v1_2 (cost=0.29..4.34 rows=3 width=8)\n Index Cond: (orga nization_id = v3_2.organization_id)\n -> Index Only Scan using organizations_organization_pkey on organizations_organization v2_2 (cost=0.29..0.42 rows=1 width=4)\n Index Cond: (id = v1_2.or ganization_id)\n -> Index Only Scan using projects_project_pkey on projects_project v0_2 (cost=0.29..0.37 rows=1 width=4)\n Index Cond: (id = v1_2.project_id)\n -> Nested Loop (cost=0.72..0.9 3 rows=1 width=4)\n -> Nested Loop (cost=0.57..0.76 rows=1 width=12)\n -> Index Scan using organizations_organization_projects_project_id on organizations_organization_projects u1_1 (cost=0.29..0.34 r ows=1 width=8)\n Index Cond: (project_id = v0_2.id)\n -> Index Only Scan using organizations_organization_pkey on organizations_organization u2_1 (cost=0.29..0.42 rows=1 width=4)\n Index Cond: (id = u1_1.organization_id)\n -> Index Scan using sso_ssointegration_organization_id_key on sso_ssointegration u3_1 (cost=0.15..0.17 rows=1 width=4)\n Index Cond: (organiza tion_id = u1_1.organization_id)\n Filter: ((provider)::text = 'allauth'::text)" ``` The query generated before (slow) ``` Out[12]: 'SELECT "projects_project"."id", "projects_project"."pub_date", "projects_project"."modified_date", "projects_project"."name", "projects_project"."slug", "projects_project"."description", "projects_project"."git_checkout_command", "projects_project"."repo", "projects_project"."repo_type", "projects_project"."project_url", "projects_project"."canonical_url", "projects_project"."versioning_scheme", "projects_project"."single_version", "projects_project"."default_versio n", "projects_project"."default_branch", "projects_project"."custom_prefix", "projects_project"."custom_subproject_prefix", "projects_project"."external_builds_enabled", "projects_project"."external_builds_privacy_level", "projects_project" ."show_build_overview_in_comment", "projects_project"."cdn_enabled", "projects_project"."analytics_code", "projects_project"."analytics_disabled", "projects_project"."container_image", "projects_project"."container_mem_limit", "projects_pro ject"."container_time_limit", "projects_project"."build_queue", "projects_project"."max_concurrent_builds", "projects_project"."allow_promos", "projects_project"."ad_free", "projects_project"."is_spam", "projects_project"."show_version_warn ing", "projects_project"."readthedocs_yaml_path", "projects_project"."featured", "projects_project"."skip", "projects_project"."delisted", "projects_project"."privacy_level", "projects_project"."language", "projects_project"."programming_la nguage", "projects_project"."main_language_project_id", "projects_project"."has_valid_webhook", "projects_project"."has_valid_clone", "projects_project"."remote_repository_id", "projects_project"."documentation_type", "projects_project"."ha s_ssh_key_with_write_access", "projects_project"."latest_build_id" FROM "projects_project" LEFT OUTER JOIN "organizations_organization_projects" ON ("projects_project"."id" = "organizations_organization_projects"."project_id") LEFT OUTER JO IN "organizations_organization" ON ("organizations_organization_projects"."organization_id" = "organizations_organization"."id") LEFT OUTER JOIN "sso_ssointegration" ON ("organizations_organization"."id" = "sso_ssointegration"."organization _id") LEFT OUTER JOIN "organizations_team_projects" ON ("projects_project"."id" = "organizations_team_projects"."project_id") LEFT OUTER JOIN "organizations_team" ON ("organizations_team_projects"."team_id" = "organizations_team"."id") LEFT OUTER JOIN "organizations_teammember" ON ("organizations_team"."id" = "organizations_teammember"."team_id") LEFT OUTER JOIN "organizations_organizationowner" ON ("organizations_organization"."id" = "organizations_organizationowner"."organi zation_id") WHERE (("sso_ssointegration"."provider" = allauth AND "projects_project"."remote_repository_id" IN (SELECT U0."remote_repository_id" AS "remote_repository" FROM "oauth_remoterepositoryrelation" U0 WHERE U0."user_id" = 2939)) OR (("organizations_team"."access" = admin OR "organizations_team"."access" = readonly) AND "organizations_teammember"."member_id" = 2939 AND NOT (EXISTS(SELECT 1 AS "a" FROM "organizations_organization_projects" U1 INNER JOIN "organizations_o rganization" U2 ON (U1."organization_id" = U2."id") INNER JOIN "sso_ssointegration" U3 ON (U2."id" = U3."organization_id") WHERE (U3."provider" = allauth AND U1."project_id" = ("projects_project"."id")) LIMIT 1))) OR ("organizations_organiz ationowner"."owner_id" = 2939 AND NOT (EXISTS(SELECT 1 AS "a" FROM "organizations_organization_projects" U1 INNER JOIN "organizations_organization" U2 ON (U1."organization_id" = U2."id") INNER JOIN "sso_ssointegration" U3 ON (U2."id" = U3." organization_id") WHERE (U3."provider" = allauth AND U1."project_id" = ("projects_project"."id")) LIMIT 1)))) ORDER BY "projects_project"."slug" ASC' ``` The query now ``` Out[11]: 'SELECT "projects_project"."id", "projects_project"."pub_date", "projects_project"."modified_date", "projects_project"."name", "projects_project"."slug", "projects_project"."description", "projects_project"."git_checkout_command", "projects_project"."repo", "projects_project"."repo_type", "projects_project"."project_url", "projects_project"."canonical_url", "projects_project"."versioning_scheme", "projects_project"."single_version", "projects_project"."default_versio n", "projects_project"."default_branch", "projects_project"."custom_prefix", "projects_project"."custom_subproject_prefix", "projects_project"."external_builds_enabled", "projects_project"."external_builds_privacy_level", "projects_project" ."show_build_overview_in_comment", "projects_project"."cdn_enabled", "projects_project"."analytics_code", "projects_project"."analytics_disabled", "projects_project"."container_image", "projects_project"."container_mem_limit", "projects_pro ject"."container_time_limit", "projects_project"."build_queue", "projects_project"."max_concurrent_builds", "projects_project"."allow_promos", "projects_project"."ad_free", "projects_project"."is_spam", "projects_project"."show_version_warn ing", "projects_project"."readthedocs_yaml_path", "projects_project"."featured", "projects_project"."skip", "projects_project"."delisted", "projects_project"."privacy_level", "projects_project"."language", "projects_project"."programming_la nguage", "projects_project"."main_language_project_id", "projects_project"."has_valid_webhook", "projects_project"."has_valid_clone", "projects_project"."remote_repository_id", "projects_project"."documentation_type", "projects_project"."ha s_ssh_key_with_write_access", "projects_project"."latest_build_id" FROM "projects_project" WHERE ("projects_project"."id" IN (SELECT V0."id" FROM "projects_project" V0 INNER JOIN "organizations_organization_projects" V1 ON (V0."id" = V1."pr oject_id") INNER JOIN "organizations_organization" V2 ON (V1."organization_id" = V2."id") INNER JOIN "sso_ssointegration" V3 ON (V2."id" = V3."organization_id") WHERE (V3."provider" = allauth AND V0."remote_repository_id" IN (SELECT U0."rem ote_repository_id" AS "remote_repository" FROM "oauth_remoterepositoryrelation" U0 WHERE U0."user_id" = 2939))) OR "projects_project"."id" IN (SELECT V0."id" FROM "projects_project" V0 INNER JOIN "organizations_team_projects" V1 ON (V0."id" = V1."project_id") INNER JOIN "organizations_team" V2 ON (V1."team_id" = V2."id") INNER JOIN "organizations_teammember" V3 ON (V2."id" = V3."team_id") WHERE ((V2."access" = admin OR V2."access" = readonly) AND V3."member_id" = 2939 AND NOT (EXISTS(SELECT 1 AS "a" FROM "organizations_organization_projects" U1 INNER JOIN "organizations_organization" U2 ON (U1."organization_id" = U2."id") INNER JOIN "sso_ssointegration" U3 ON (U2."id" = U3."organization_id") WHERE (U3."provider " = allauth AND U1."project_id" = (V0."id")) LIMIT 1)))) OR "projects_project"."id" IN (SELECT V0."id" FROM "projects_project" V0 INNER JOIN "organizations_organization_projects" V1 ON (V0."id" = V1."project_id") INNER JOIN "organizations_o rganization" V2 ON (V1."organization_id" = V2."id") INNER JOIN "organizations_organizationowner" V3 ON (V2."id" = V3."organization_id") WHERE (V3."owner_id" = 2939 AND NOT (EXISTS(SELECT 1 AS "a" FROM "organizations_organization_projects" U 1 INNER JOIN "organizations_organization" U2 ON (U1."organization_id" = U2."id") INNER JOIN "sso_ssointegration" U3 ON (U2."id" = U3."organization_id") WHERE (U3."provider" = allauth AND U1."project_id" = (V0."id")) LIMIT 1))))) ORDER BY "p rojects_project"."slug" ASC' ```
stsewd
added a commit
that referenced
this pull request
Oct 8, 2025
This was introduced in #12447. Even locally the previous query is slow, after trying a couple of things I was able to fix it. This is the comparison running explain in production: Current code (rolled back) ``` Out[24]: 'Sort (cost=7188.29..7209.50 rows=8486 width=715)\n Sort Key: projects_project.slug\n -> Hash Left Join (cost=3517.65..3936.04 rows=8486 width=715)\n Hash Cond: (organizations_organization.id = sso_ssointegration.organi zation_id)\n Filter: ((organizations_team_projects.team_id = 2457) OR (organizations_team_projects.team_id = 6031) OR (organizations_team_projects.team_id = 2302) OR (organizations_team_projects.team_id = 5976) OR (organizations_team _projects.team_id = 6032) OR (organizations_organization_projects.organization_id = 5511) OR (organizations_organization_projects.organization_id = 11709) OR (organizations_organization_projects.organization_id = 1236) OR (organizations_org anization_projects.organization_id = 2991) OR (organizations_organization_projects.organization_id = 8804) OR (organizations_organization_projects.organization_id = 12806) OR (organizations_organization_projects.organization_id = 13165) OR ((sso_ssointegration.id IS NOT NULL) AND (hashed SubPlan 1)) OR ((sso_ssointegration.id IS NOT NULL) AND (hashed SubPlan 2)))\n -> Hash Left Join (cost=1639.26..2028.00 rows=11278 width=727)\n Hash Cond: (organizations _organization_projects.organization_id = organizations_organization.id)\n -> Hash Left Join (cost=1033.89..1393.01 rows=11278 width=723)\n Hash Cond: (projects_project.id = organizations_organization_projec ts.project_id)\n -> Hash Right Join (cost=790.75..995.15 rows=11278 width=719)\n Hash Cond: (organizations_team_projects.project_id = projects_project.id)\n -> Seq Scan on organizations_team_projects (cost=0.00..174.78 rows=11278 width=8)\n -> Hash (cost=682.00..682.00 rows=8700 width=715)\n -> Seq Scan on projects_project (cost=0.00..682.00 row s=8700 width=715)\n -> Hash (cost=134.73..134.73 rows=8673 width=8)\n -> Seq Scan on organizations_organization_projects (cost=0.00..134.73 rows=8673 width=8)\n -> Hash (cost=43 5.72..435.72 rows=13572 width=4)\n -> Seq Scan on organizations_organization (cost=0.00..435.72 rows=13572 width=4)\n -> Hash (cost=5.87..5.87 rows=287 width=8)\n -> Seq Scan on sso_ssointegration (cost=0.00..5.87 rows=287 width=8)\n SubPlan 1\n -> Index Scan using oauth_remoterepositoryrelation_user_id_3c2baf85 on oauth_remoterepositoryrelation u0 (cost=0.43..934.05 rows=100 width=4)\n Index Cond: (user_id = 2939)\n Filter: admin\n SubPlan 2\n -> Index Scan using oauth_remoterepositoryrelation_user_id_3c2baf85 on oauth_remoterepositoryrelation u0_1 (cost=0.43..934.05 rows=232 width=4)\n Index Cond: (user_id = 2939)\n Filter: (NOT admin)' ``` Code from main ``` Out[13]: "Sort (cost=303529.85..303541.53 rows=4672 width=715)\n Sort Key: projects_project.slug\n -> Hash Left Join (cost=3726.61..303245.09 rows=4672 width=715)\n Hash Cond: (organizations_organization.id = sso_ssointegration. organization_id)\n Filter: ((((sso_ssointegration.provider)::text = 'allauth'::text) AND (hashed SubPlan 1)) OR ((((organizations_team.access)::text = 'admin'::text) OR ((organizations_team.access)::text = 'readonly'::text)) AND (org anizations_teammember.member_id = 2939) AND (NOT (hashed SubPlan 3))) OR ((organizations_organizationowner.owner_id = 2939) AND (NOT (hashed SubPlan 5))))\n -> Hash Left Join (cost=2782.27..302239.85 rows=10402 width=734)\n Hash Cond: (projects_project.id = organizations_team_projects.project_id)\n -> Nested Loop Left Join (cost=936.03..300038.84 rows=8024 width=723)\n -> Nested Loop Left Join (cost=935.75..297563.62 r ows=7612 width=719)\n -> Merge Left Join (cost=935.45..295996.09 rows=7612 width=719)\n Merge Cond: (projects_project.id = organizations_organization_projects.project_id)\n -> Index Scan using projects_project_pkey on projects_project (cost=935.17..295550.85 rows=7612 width=715)\n Filter: ((hashed SubPlan 1) OR (NOT (hashed SubPlan 3)) OR (NOT (hashe d SubPlan 5)))\n SubPlan 1\n -> Index Scan using oauth_remoterepositoryrelation_user_id_3c2baf85 on oauth_remoterepositoryrelation u0 (cost=0.43..934.05 rows=332 width=4)\n Index Cond: (user_id = 2939)\n SubPlan 3\n -> Nested Loop (cost=10.10..451.03 rows=165 width=4)\n -> Hash Join (cost=9.81..167.34 rows=679 width=12)\n Hash Cond: (u1.organization_id = u3.organization_id)\n -> Seq Scan on organizations_organization_projects u1 (cost=0.00..134.73 rows=8673 width=8)\n -> Hash (cost=6.59..6.59 rows=258 width=4)\n -> Seq Scan on sso_ssointegration u3 (cost=0.00..6.59 rows=258 width=4)\n Filter: ((provider)::text = 'allauth'::text)\n -> Index Only Scan using organizations_organization_pkey on organizations_organization u2 (cost=0.29..0.42 rows=1 width=4)\n Index Cond: (id = u1.organization_id)\n SubPlan 5\n -> Nested Loop (cost=10.10..451.03 rows=165 width=4)\n -> Hash Join (cost=9.81..167.34 rows=679 width=12)\n Hash Cond: (u1_1.organization_id = u3_1.organization_id)\n -> Seq Scan on organizations_organization_projects u1_1 (cost=0.00..134.73 rows=867 3 width=8)\n -> Hash (cost=6.59..6.59 rows=258 width=4)\n -> Seq Scan on sso_ssointegration u3_1 (cost=0.00..6.59 rows=258 width =4)\n Filter: ((provider)::text = 'allauth'::text)\n -> Index Only Scan using organizations_organization_pkey on organizations_orga nization u2_1 (cost=0.29..0.42 rows=1 width=4)\n Index Cond: (id = u1_1.organization_id)\n -> Index Scan using organizations_organization_projects_project_i d on organizations_organization_projects (cost=0.29..328.65 rows=8673 width=8)\n -> Memoize (cost=0.30..0.43 rows=1 width=4)\n Cache Key: organizations_organization_projects.organiz ation_id\n Cache Mode: logical\n -> Index Only Scan using organizations_organization_pkey on organizations_organization (cost=0.29..0.42 rows=1 width=4)\n Index Cond: (id = organizations_organization_projects.organization_id)\n -> Index Only Scan using organizations_organization_owners_organization_id_user_id_key on organizations_organizationowner (cost=0. 29..0.32 rows=1 width=8)\n Index Cond: (organization_id = organizations_organization.id)\n -> Hash (cost=1705.27..1705.27 rows=11278 width=15)\n -> Hash Right Join (cost=1293.80.. 1705.27 rows=11278 width=15)\n Hash Cond: (organizations_teammember.team_id = organizations_team.id)\n -> Seq Scan on organizations_teammember (cost=0.00..275.29 rows=17629 width=8)\n -> Hash (cost=1152.82..1152.82 rows=11278 width=15)\n -> Hash Left Join (cost=948.44..1152.82 rows=11278 width=15)\n Hash Cond: (organizations_team _projects.team_id = organizations_team.id)\n -> Seq Scan on organizations_team_projects (cost=0.00..174.78 rows=11278 width=8)\n -> Hash (cost=593.75..593.75 rows =28375 width=11)\n -> Seq Scan on organizations_team (cost=0.00..593.75 rows=28375 width=11)\n -> Hash (cost=5.87..5.87 rows=287 width=11)\n -> Seq Scan on sso_ssointegrati on (cost=0.00..5.87 rows=287 width=11)" ``` Fixed code ``` Out[25]: "Index Scan using projects_project_slug_uniq on projects_project (cost=2417.89..5720.59 rows=7612 width=715)\n Filter: ((hashed SubPlan 1) OR (hashed SubPlan 2) OR (hashed SubPlan 3))\n SubPlan 1\n -> Nested Loop Semi Join (cost=10.81..2307.67 rows=1 width=4)\n Join Filter: (v0.remote_repository_id = u0.remote_repository_id)\n -> Nested Loop (cost=10.38..551.09 rows=165 width=8)\n -> Nested Loop (cost=10.10..451.03 rows=16 5 width=4)\n -> Hash Join (cost=9.81..167.34 rows=679 width=12)\n Hash Cond: (v1.organization_id = v3.organization_id)\n -> Seq Scan on organizations_organization _projects v1 (cost=0.00..134.73 rows=8673 width=8)\n -> Hash (cost=6.59..6.59 rows=258 width=4)\n -> Seq Scan on sso_ssointegration v3 (cost=0.00..6.59 rows=258 width=4)\n Filter: ((provider)::text = 'allauth'::text)\n -> Index Only Scan using organizations_organization_pkey on organizations_organization v2 (cost=0.29..0.42 rows=1 width=4)\n Index Cond: (id = v1.organization_id)\n -> Index Scan using projects_project_pkey on projects_project v0 (cost=0.29..0.61 rows=1 width=8)\n Index Cond: (id = v1.project_id)\n -> Materialize (cost=0.43..935.71 rows=332 width=4)\n -> Index Scan using oauth_remoterepositoryrelation_user_id_3c2baf85 on oauth_remoterepositoryrelation u0 (cost=0.43..934.05 rows=332 width=4)\n In dex Cond: (user_id = 2939)\n SubPlan 2\n -> Nested Loop Anti Join (cost=1.86..58.55 rows=1 width=4)\n -> Nested Loop (cost=1.15..57.61 rows=1 width=4)\n -> Nested Loop (cost=0.86..57.25 rows=1 width=4)\n -> Nested Loop (cost=0.57..55.72 rows=4 width=8)\n -> Index Scan using organizations_team_members_user_id on organizations_teammember v3_1 (cost=0.29..14.17 rows=5 width=4)\n Index Cond: (member_id = 2939)\n -> Index Scan using organizations_team_pkey on organizations_team v2_1 (cost=0.29..8.31 rows=1 width=4)\n Index Cond: (id = v3_ 1.team_id)\n Filter: (((access)::text = 'admin'::text) OR ((access)::text = 'readonly'::text))\n -> Index Only Scan using organizations_team_projects_team_id_project_id_key on organizat ions_team_projects v1_1 (cost=0.29..0.35 rows=3 width=8)\n Index Cond: (team_id = v2_1.id)\n -> Index Only Scan using projects_project_pkey on projects_project v0_1 (cost=0.29..0.36 rows=1 width= 4)\n Index Cond: (id = v1_1.project_id)\n -> Nested Loop (cost=0.72..0.93 rows=1 width=4)\n -> Nested Loop (cost=0.57..0.76 rows=1 width=12)\n -> Index Scan using organi zations_organization_projects_project_id on organizations_organization_projects u1 (cost=0.29..0.34 rows=1 width=8)\n Index Cond: (project_id = v0_1.id)\n -> Index Only Scan using organizati ons_organization_pkey on organizations_organization u2 (cost=0.29..0.42 rows=1 width=4)\n Index Cond: (id = u1.organization_id)\n -> Index Scan using sso_ssointegration_organization_id_key on sso_ ssointegration u3 (cost=0.15..0.17 rows=1 width=4)\n Index Cond: (organization_id = u1.organization_id)\n Filter: ((provider)::text = 'allauth'::text)\n SubPlan 3\n -> Nested Loop Anti Join ( cost=1.86..51.38 rows=4 width=4)\n -> Nested Loop (cost=1.14..47.63 rows=4 width=4)\n -> Nested Loop (cost=0.86..46.14 rows=4 width=4)\n -> Nested Loop (cost=0.57..41.54 rows=11 width=12)\n -> Index Scan using organizations_organization_owners_user_id on organizations_organizationowner v3_2 (cost=0.29..10.97 rows=7 width=4)\n Index Cond: (owner_id = 2939)\n -> Index Only Scan using organizations_organization_proje_organization_id_project_id_key on organizations_organization_projects v1_2 (cost=0.29..4.34 rows=3 width=8)\n Index Cond: (orga nization_id = v3_2.organization_id)\n -> Index Only Scan using organizations_organization_pkey on organizations_organization v2_2 (cost=0.29..0.42 rows=1 width=4)\n Index Cond: (id = v1_2.or ganization_id)\n -> Index Only Scan using projects_project_pkey on projects_project v0_2 (cost=0.29..0.37 rows=1 width=4)\n Index Cond: (id = v1_2.project_id)\n -> Nested Loop (cost=0.72..0.9 3 rows=1 width=4)\n -> Nested Loop (cost=0.57..0.76 rows=1 width=12)\n -> Index Scan using organizations_organization_projects_project_id on organizations_organization_projects u1_1 (cost=0.29..0.34 r ows=1 width=8)\n Index Cond: (project_id = v0_2.id)\n -> Index Only Scan using organizations_organization_pkey on organizations_organization u2_1 (cost=0.29..0.42 rows=1 width=4)\n Index Cond: (id = u1_1.organization_id)\n -> Index Scan using sso_ssointegration_organization_id_key on sso_ssointegration u3_1 (cost=0.15..0.17 rows=1 width=4)\n Index Cond: (organiza tion_id = u1_1.organization_id)\n Filter: ((provider)::text = 'allauth'::text)" ``` The query generated before (slow) ``` Out[12]: 'SELECT "projects_project"."id", "projects_project"."pub_date", "projects_project"."modified_date", "projects_project"."name", "projects_project"."slug", "projects_project"."description", "projects_project"."git_checkout_command", "projects_project"."repo", "projects_project"."repo_type", "projects_project"."project_url", "projects_project"."canonical_url", "projects_project"."versioning_scheme", "projects_project"."single_version", "projects_project"."default_versio n", "projects_project"."default_branch", "projects_project"."custom_prefix", "projects_project"."custom_subproject_prefix", "projects_project"."external_builds_enabled", "projects_project"."external_builds_privacy_level", "projects_project" ."show_build_overview_in_comment", "projects_project"."cdn_enabled", "projects_project"."analytics_code", "projects_project"."analytics_disabled", "projects_project"."container_image", "projects_project"."container_mem_limit", "projects_pro ject"."container_time_limit", "projects_project"."build_queue", "projects_project"."max_concurrent_builds", "projects_project"."allow_promos", "projects_project"."ad_free", "projects_project"."is_spam", "projects_project"."show_version_warn ing", "projects_project"."readthedocs_yaml_path", "projects_project"."featured", "projects_project"."skip", "projects_project"."delisted", "projects_project"."privacy_level", "projects_project"."language", "projects_project"."programming_la nguage", "projects_project"."main_language_project_id", "projects_project"."has_valid_webhook", "projects_project"."has_valid_clone", "projects_project"."remote_repository_id", "projects_project"."documentation_type", "projects_project"."ha s_ssh_key_with_write_access", "projects_project"."latest_build_id" FROM "projects_project" LEFT OUTER JOIN "organizations_organization_projects" ON ("projects_project"."id" = "organizations_organization_projects"."project_id") LEFT OUTER JO IN "organizations_organization" ON ("organizations_organization_projects"."organization_id" = "organizations_organization"."id") LEFT OUTER JOIN "sso_ssointegration" ON ("organizations_organization"."id" = "sso_ssointegration"."organization _id") LEFT OUTER JOIN "organizations_team_projects" ON ("projects_project"."id" = "organizations_team_projects"."project_id") LEFT OUTER JOIN "organizations_team" ON ("organizations_team_projects"."team_id" = "organizations_team"."id") LEFT OUTER JOIN "organizations_teammember" ON ("organizations_team"."id" = "organizations_teammember"."team_id") LEFT OUTER JOIN "organizations_organizationowner" ON ("organizations_organization"."id" = "organizations_organizationowner"."organi zation_id") WHERE (("sso_ssointegration"."provider" = allauth AND "projects_project"."remote_repository_id" IN (SELECT U0."remote_repository_id" AS "remote_repository" FROM "oauth_remoterepositoryrelation" U0 WHERE U0."user_id" = 2939)) OR (("organizations_team"."access" = admin OR "organizations_team"."access" = readonly) AND "organizations_teammember"."member_id" = 2939 AND NOT (EXISTS(SELECT 1 AS "a" FROM "organizations_organization_projects" U1 INNER JOIN "organizations_o rganization" U2 ON (U1."organization_id" = U2."id") INNER JOIN "sso_ssointegration" U3 ON (U2."id" = U3."organization_id") WHERE (U3."provider" = allauth AND U1."project_id" = ("projects_project"."id")) LIMIT 1))) OR ("organizations_organiz ationowner"."owner_id" = 2939 AND NOT (EXISTS(SELECT 1 AS "a" FROM "organizations_organization_projects" U1 INNER JOIN "organizations_organization" U2 ON (U1."organization_id" = U2."id") INNER JOIN "sso_ssointegration" U3 ON (U2."id" = U3." organization_id") WHERE (U3."provider" = allauth AND U1."project_id" = ("projects_project"."id")) LIMIT 1)))) ORDER BY "projects_project"."slug" ASC' ``` The query now ``` Out[11]: 'SELECT "projects_project"."id", "projects_project"."pub_date", "projects_project"."modified_date", "projects_project"."name", "projects_project"."slug", "projects_project"."description", "projects_project"."git_checkout_command", "projects_project"."repo", "projects_project"."repo_type", "projects_project"."project_url", "projects_project"."canonical_url", "projects_project"."versioning_scheme", "projects_project"."single_version", "projects_project"."default_versio n", "projects_project"."default_branch", "projects_project"."custom_prefix", "projects_project"."custom_subproject_prefix", "projects_project"."external_builds_enabled", "projects_project"."external_builds_privacy_level", "projects_project" ."show_build_overview_in_comment", "projects_project"."cdn_enabled", "projects_project"."analytics_code", "projects_project"."analytics_disabled", "projects_project"."container_image", "projects_project"."container_mem_limit", "projects_pro ject"."container_time_limit", "projects_project"."build_queue", "projects_project"."max_concurrent_builds", "projects_project"."allow_promos", "projects_project"."ad_free", "projects_project"."is_spam", "projects_project"."show_version_warn ing", "projects_project"."readthedocs_yaml_path", "projects_project"."featured", "projects_project"."skip", "projects_project"."delisted", "projects_project"."privacy_level", "projects_project"."language", "projects_project"."programming_la nguage", "projects_project"."main_language_project_id", "projects_project"."has_valid_webhook", "projects_project"."has_valid_clone", "projects_project"."remote_repository_id", "projects_project"."documentation_type", "projects_project"."ha s_ssh_key_with_write_access", "projects_project"."latest_build_id" FROM "projects_project" WHERE ("projects_project"."id" IN (SELECT V0."id" FROM "projects_project" V0 INNER JOIN "organizations_organization_projects" V1 ON (V0."id" = V1."pr oject_id") INNER JOIN "organizations_organization" V2 ON (V1."organization_id" = V2."id") INNER JOIN "sso_ssointegration" V3 ON (V2."id" = V3."organization_id") WHERE (V3."provider" = allauth AND V0."remote_repository_id" IN (SELECT U0."rem ote_repository_id" AS "remote_repository" FROM "oauth_remoterepositoryrelation" U0 WHERE U0."user_id" = 2939))) OR "projects_project"."id" IN (SELECT V0."id" FROM "projects_project" V0 INNER JOIN "organizations_team_projects" V1 ON (V0."id" = V1."project_id") INNER JOIN "organizations_team" V2 ON (V1."team_id" = V2."id") INNER JOIN "organizations_teammember" V3 ON (V2."id" = V3."team_id") WHERE ((V2."access" = admin OR V2."access" = readonly) AND V3."member_id" = 2939 AND NOT (EXISTS(SELECT 1 AS "a" FROM "organizations_organization_projects" U1 INNER JOIN "organizations_organization" U2 ON (U1."organization_id" = U2."id") INNER JOIN "sso_ssointegration" U3 ON (U2."id" = U3."organization_id") WHERE (U3."provider " = allauth AND U1."project_id" = (V0."id")) LIMIT 1)))) OR "projects_project"."id" IN (SELECT V0."id" FROM "projects_project" V0 INNER JOIN "organizations_organization_projects" V1 ON (V0."id" = V1."project_id") INNER JOIN "organizations_o rganization" V2 ON (V1."organization_id" = V2."id") INNER JOIN "organizations_organizationowner" V3 ON (V2."id" = V3."organization_id") WHERE (V3."owner_id" = 2939 AND NOT (EXISTS(SELECT 1 AS "a" FROM "organizations_organization_projects" U 1 INNER JOIN "organizations_organization" U2 ON (U1."organization_id" = U2."id") INNER JOIN "sso_ssointegration" U3 ON (U2."id" = U3."organization_id") WHERE (U3."provider" = allauth AND U1."project_id" = (V0."id")) LIMIT 1))))) ORDER BY "p rojects_project"."slug" ASC' ```
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Add this suggestion to a batch that can be applied as a single commit.
This suggestion is invalid because no changes were made to the code.
Suggestions cannot be applied while the pull request is closed.
Suggestions cannot be applied while viewing a subset of changes.
Only one suggestion per line can be applied in a batch.
Add this suggestion to a batch that can be applied as a single commit.
Applying suggestions on deleted lines is not supported.
You must change the existing code in this line in order to create a valid suggestion.
Outdated suggestions cannot be applied.
This suggestion has been applied or marked resolved.
Suggestions cannot be applied from pending reviews.
Suggestions cannot be applied on multi-line comments.
Suggestions cannot be applied while the pull request is queued to merge.
Suggestion cannot be applied right now. Please check back later.
We were iterating over several objects like teams and user owners in order to get projects, and also checking if the organization attached to that object didn't have SSO with VCS enabled. So instead of doing that I'm just doing one query, which ignores all objects from organizations that have SSO with VCS enabled.
Also, instead of calling _get_projects_for_sso_user twice, I adapted that method so it can fetch projects from where the user is admin or member in just one call (similar to what the other methods do).
All these changes reduce the number of queries used in the dashboard on .com