-
🔴 @smcgivernContributorQUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------- Aggregate (cost=29570864.13..29570864.14 rows=1 width=8) (actual time=11042.700..11042.700 rows=1 loops=1) -> Limit (cost=29208507.80..29570837.13 rows=2160 width=4) (actual time=8545.589..11038.944 rows=10001 loops=1) -> Nested Loop (cost=29208507.80..29570837.13 rows=2160 width=4) (actual time=8545.588..11034.144 rows=10001 loops=1) -> Nested Loop (cost=29208507.37..29561564.14 rows=2341 width=12) (actual time=8545.530..10961.554 rows=11058 loops=1) -> Nested Loop (cost=29208506.94..29433461.58 rows=20357 width=8) (actual time=8545.427..9338.591 rows=6713 loops=1) -> HashAggregate (cost=29208506.51..29208873.50 rows=36699 width=4) (actual time=8545.345..8602.431 rows=68714 loops=1) Group Key: issues_1.project_id -> Seq Scan on issues issues_1 (cost=181.52..29188684.18 rows=7928933 width=4) (actual time=0.014..5473.209 rows=5944254 loops=1) Filter: ((deleted_at IS NULL) AND ((confidential IS NOT TRUE) OR (confidential AND ((author_id = 443319) OR (alternatives: SubPlan 5 or hashed SubPlan 6) OR (hashed SubPlan 7))))) Rows Removed by Filter: 148969 SubPlan 5 -> Index Only Scan using index_issue_assignees_on_issue_id_and_user_id on issue_assignees (cost=0.43..3.45 rows=1 width=0) (never executed ) Index Cond: ((issue_id = issues_1.id) AND (user_id = 443319)) Heap Fetches: 0 SubPlan 6 -> Index Scan using index_issue_assignees_on_user_id on issue_assignees issue_assignees_1 (cost=0.43..107.20 rows=78 width=4) (actual time =0.055..1.991 rows=244 loops=1) Index Cond: (user_id = 443319) SubPlan 7 -> Nested Loop (cost=0.86..181.30 rows=87 width=4) (actual time=0.055..11.514 rows=1125 loops=1) -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizat ions_4 (cost=0.43..6.50 rows=87 width=4) (actual time=0.021..0.791 rows=1125 loops=1) Index Cond: ((user_id = 443319) AND (access_level >= 20)) Heap Fetches: 30 -> Index Only Scan using projects_pkey on projects projects_1 (cost=0.43..2.00 rows=1 width=4) (actual time=0.008..0.009 rows=1 loop s=1125) Index Cond: (id = project_authorizations_4.project_id) Heap Fetches: 55 -> Index Scan using projects_pkey on projects (cost=0.43..6.11 rows=1 width=4) (actual time=0.010..0.010 rows=0 loops=68714) Index Cond: (id = issues_1.project_id) Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (visibility_level = ANY ('{10,20}'::integer[]))) Rows Removed by Filter: 1 SubPlan 1 -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations (cost=0.43..3.45 rows=1 width =0) (actual time=0.002..0.002 rows=0 loops=68714) Index Cond: ((user_id = 443319) AND (project_id = projects.id)) Heap Fetches: 1 SubPlan 2 -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_1 (cos t=0.43..6.29 rows=88 width=4) (never executed) Index Cond: (user_id = 443319) Heap Fetches: 0 -> Index Scan using index_issues_on_project_id_and_iid on issues (cost=0.43..6.17 rows=12 width=4) (actual time=0.070..0.240 rows=2 loops=6713) Index Cond: (project_id = projects.id) Filter: ((deleted_at IS NULL) AND (confidential IS NOT TRUE) AND (((title)::text ~~* '%test%'::text) OR (description ~~* '%test%'::text))) Rows Removed by Filter: 14 -> Index Scan using index_project_features_on_project_id on project_features (cost=0.43..3.95 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=11058) Index Cond: (project_id = projects.id) Filter: ((issues_access_level = ANY ('{NULL,20}'::integer[])) OR ((issues_access_level = 10) AND (alternatives: SubPlan 3 or hashed SubPlan 4))) Rows Removed by Filter: 0 SubPlan 3 -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_2 (cost=0.43..3.45 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=727) Index Cond: ((user_id = 443319) AND (project_id = projects.id)) Heap Fetches: 0 SubPlan 4 -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_3 (cost=0.43..6.29 rows=88 width=4) (never executed) Index Cond: (user_id = 443319) Heap Fetches: 0 Planning time: 3.620 ms Execution time: 11043.084 ms (55 rows) -
🔴 @smcgivernContributorgitlabhq_production=> EXPLAIN ANALYZE SELECT COUNT(*) FROM gitlabhq_production-> (SELECT 1 FROM "issues" gitlabhq_production(> INNER JOIN "projects" ON "projects"."id" = "issues"."project_id" gitlabhq_production(> LEFT JOIN project_features ON projects.id = project_features.project_id gitlabhq_production(> WHERE "issues"."deleted_at" IS NULL gitlabhq_production(> AND issues.confidential IS NOT TRUE gitlabhq_production(> AND (EXISTS gitlabhq_production(> (SELECT 1 gitlabhq_production(> FROM "project_authorizations" gitlabhq_production(> WHERE "project_authorizations"."user_id" = 443319 gitlabhq_production(> AND (project_authorizations.project_id = projects.id)) gitlabhq_production(> OR projects.visibility_level IN (10, gitlabhq_production(> 20)) gitlabhq_production(> AND "projects"."id" IN gitlabhq_production(> (SELECT "issues"."project_id" gitlabhq_production(> FROM "issues" gitlabhq_production(> WHERE "issues"."deleted_at" IS NULL gitlabhq_production(> AND ( issues.confidential IS NOT TRUE gitlabhq_production(> OR (issues.confidential = TRUE gitlabhq_production(> AND (issues.author_id = 443319 gitlabhq_production(> OR EXISTS gitlabhq_production(> (SELECT TRUE gitlabhq_production(> FROM issue_assignees gitlabhq_production(> WHERE user_id = 443319 gitlabhq_production(> AND issue_id = issues.id) gitlabhq_production(> OR issues.project_id IN gitlabhq_production(> (SELECT "projects"."id" gitlabhq_production(> FROM "projects" gitlabhq_production(> INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" gitlabhq_production(> WHERE "project_authorizations"."user_id" = 443319 gitlabhq_production(> AND (project_authorizations.access_level >= 20)))))) gitlabhq_production(> AND ("project_features"."issues_access_level" IN (NULL, gitlabhq_production(> 20) gitlabhq_production(> OR ("project_features"."issues_access_level" = 10 gitlabhq_production(> AND EXISTS gitlabhq_production(> (SELECT 1 gitlabhq_production(> FROM "project_authorizations" gitlabhq_production(> WHERE "project_authorizations"."user_id" = 443319 gitlabhq_production(> AND (project_authorizations.project_id = projects.id)))) gitlabhq_production(> AND ("issues"."title" ILIKE '%test%' gitlabhq_production(> OR "issues"."description" ILIKE '%test%') gitlabhq_production(> LIMIT 1001) AS foo; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------- Aggregate (cost=14011366.64..14011366.65 rows=1 width=8) (actual time=523.680..523.681 rows=1 loops=1) -> Limit (cost=559.26..14011354.12 rows=1001 width=4) (actual time=31.032..522.404 rows=1001 loops=1) -> Nested Loop (cost=559.26..33494897.03 rows=2393 width=4) (actual time=31.031..521.861 rows=1001 loops=1) -> Merge Semi Join (cost=558.83..33484293.26 rows=2678 width=12) (actual time=30.984..516.513 rows=1002 loops=1) Merge Cond: (projects.id = issues_1.project_id) -> Nested Loop (cost=0.86..3858271.42 rows=273649 width=8) (actual time=28.147..475.853 rows=1002 loops=1) -> Index Scan using index_issues_on_project_id_and_iid on issues (cost=0.43..1689887.20 rows=491468 width=4) (actual time=0.058..454.824 rows=1867 loops =1) Filter: ((deleted_at IS NULL) AND (confidential IS NOT TRUE) AND (((title)::text ~~* '%test%'::text) OR (description ~~* '%test%'::text))) Rows Removed by Filter: 19938 -> Index Scan using projects_pkey on projects (cost=0.43..4.40 rows=1 width=4) (actual time=0.009..0.010 rows=1 loops=1867) Index Cond: (id = issues.project_id) Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (visibility_level = ANY ('{10,20}'::integer[]))) Rows Removed by Filter: 0 SubPlan 1 -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations (cost=0.43..3.45 rows=1 width =0) (actual time=0.003..0.003 rows=1 loops=1867) Index Cond: ((user_id = 443319) AND (project_id = projects.id)) Heap Fetches: 0 SubPlan 2 -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_1 (cos t=0.43..11.55 rows=91 width=4) (never executed) Index Cond: (user_id = 443319) Heap Fetches: 0 -> Index Scan using index_issues_on_project_id_and_iid on issues issues_1 (cost=191.32..29600426.29 rows=7891037 width=4) (actual time=0.009..35.589 rows=1559 4 loops=1) Filter: ((deleted_at IS NULL) AND ((confidential IS NOT TRUE) OR (confidential AND ((author_id = 443319) OR (alternatives: SubPlan 5 or hashed SubPlan 6) OR (hashed SubPlan 7))))) Rows Removed by Filter: 30 SubPlan 5 -> Index Only Scan using index_issue_assignees_on_issue_id_and_user_id on issue_assignees (cost=0.43..3.45 rows=1 width=0) (never executed) Index Cond: ((issue_id = issues_1.id) AND (user_id = 443319)) Heap Fetches: 0 SubPlan 6 -> Index Scan using index_issue_assignees_on_user_id on issue_assignees issue_assignees_1 (cost=0.43..105.60 rows=78 width=4) (actual time=0.062..2.15 4 rows=244 loops=1) Index Cond: (user_id = 443319) SubPlan 7 -> Nested Loop (cost=0.86..190.67 rows=89 width=4) (actual time=0.073..14.501 rows=1126 loops=1) -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_4 (cos t=0.43..10.45 rows=89 width=4) (actual time=0.035..1.402 rows=1126 loops=1) Index Cond: ((user_id = 443319) AND (access_level >= 20)) Heap Fetches: 68 -> Index Only Scan using projects_pkey on projects projects_1 (cost=0.43..2.01 rows=1 width=4) (actual time=0.010..0.011 rows=1 loops=1126) Index Cond: (id = project_authorizations_4.project_id) Heap Fetches: 61 -> Index Scan using index_project_features_on_project_id on project_features (cost=0.43..3.95 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=1002) Index Cond: (project_id = projects.id) Filter: ((issues_access_level = ANY ('{NULL,20}'::integer[])) OR ((issues_access_level = 10) AND (alternatives: SubPlan 3 or hashed SubPlan 4))) Rows Removed by Filter: 0 SubPlan 3 -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_2 (cost=0.43..3.45 rows=1 width=0) (actual time=0.005..0.005 rows=0 loops=1) Index Cond: ((user_id = 443319) AND (project_id = projects.id)) Heap Fetches: 0 SubPlan 4 -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_3 (cost=0.43..11.5 5 rows=91 width=4) (never executed) Index Cond: (user_id = 443319) Heap Fetches: 0 Planning time: 8.223 ms Execution time: 524.018 ms (53 rows)
Please register or sign in to comment