-
🔴 @smcgivernContributorQUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------- Aggregate (cost=32681412.74..32681412.75 rows=1 width=8) (actual time=46791.667..46791.667 rows=1 loops=1) -> Nested Loop (cost=29270698.47..32681403.35 rows=3756 width=0) (actual time=10489.631..46768.333 rows=50461 loops=1) -> Hash Join (cost=29270698.04..32665037.29 rows=4133 width=12) (actual time=10489.600..46301.645 rows=57465 loops=1) Hash Cond: (issues.project_id = projects.id) -> Bitmap Heap Scan on issues (cost=7621.74..3399047.05 rows=766028 width=4) (actual time=347.622..35880.923 rows=427985 loops=1) Recheck Cond: (((title)::text ~~* '%test%'::text) OR (description ~~* '%test%'::text)) Rows Removed by Index Recheck: 2415445 Filter: ((confidential IS NOT TRUE) OR (confidential AND ((author_id = 443319) OR (alternatives: SubPlan 1 or hashed SubPlan 2) OR (hashed SubPlan 3)))) Rows Removed by Filter: 7645 Heap Blocks: exact=126571 lossy=213800 -> BitmapOr (cost=7438.25..7438.25 rows=794098 width=0) (actual time=302.423..302.423 rows=0 loops=1) -> Bitmap Index Scan on index_issues_on_title_trigram (cost=0.00..2387.32 rows=280910 width=0) (actual time=71.485..71.485 rows=178162 loops=1) Index Cond: ((title)::text ~~* '%test%'::text) -> Bitmap Index Scan on index_issues_on_description_trigram (cost=0.00..4667.91 rows=513188 width=0) (actual time=230.934..230.934 rows=479202 loops=1) Index Cond: (description ~~* '%test%'::text) SubPlan 1 -> 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.id) AND (user_id = 443319)) Heap Fetches: 0 SubPlan 2 -> Index Scan using index_issue_assignees_on_user_id on issue_assignees issue_assignees_1 (cost=0.43..105.90 rows=78 width=4) (actual time=0.017..0.484 rows =242 loops=1) Index Cond: (user_id = 443319) SubPlan 3 -> Nested Loop (cost=0.86..183.27 rows=86 width=4) (actual time=0.027..6.035 rows=1121 loops=1) -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations (cost=0.43..10.43 rows=86 width=4) (actual time=0.010..0.574 rows=1121 loops=1) Index Cond: ((user_id = 443319) AND (access_level >= 20)) Heap Fetches: 80 -> Index Only Scan using projects_pkey on projects projects_1 (cost=0.43..2.00 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=1121) Index Cond: (id = project_authorizations.project_id) Heap Fetches: 83 -> Hash (cost=29262820.10..29262820.10 rows=20496 width=8) (actual time=10141.886..10141.886 rows=26833 loops=1) Buckets: 32768 Batches: 1 Memory Usage: 1305kB -> Nested Loop (cost=29036738.33..29262820.10 rows=20496 width=8) (actual time=7710.562..10126.961 rows=26833 loops=1) -> HashAggregate (cost=29036737.90..29037106.94 rows=36904 width=4) (actual time=7710.480..7889.395 rows=275620 loops=1) Group Key: issues_1.project_id -> Seq Scan on issues issues_1 (cost=183.49..29016784.03 rows=7981547 width=4) (actual time=0.024..4786.585 rows=5997277 loops=1) Filter: ((confidential IS NOT TRUE) OR (confidential AND ((author_id = 443319) OR (alternatives: SubPlan 8 or hashed SubPlan 9) OR (hashed Sub Plan 10)))) Rows Removed by Filter: 77867 SubPlan 8 -> Index Only Scan using index_issue_assignees_on_issue_id_and_user_id on issue_assignees issue_assignees_2 (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 9 -> Index Scan using index_issue_assignees_on_user_id on issue_assignees issue_assignees_3 (cost=0.43..105.90 rows=78 width=4) (actual time =0.017..0.421 rows=242 loops=1) Index Cond: (user_id = 443319) SubPlan 10 -> Nested Loop (cost=0.86..183.27 rows=86 width=4) (actual time=0.028..6.485 rows=1121 loops=1) -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizat ions_5 (cost=0.43..10.43 rows=86 width=4) (actual time=0.015..0.549 rows=1121 loops=1) Index Cond: ((user_id = 443319) AND (access_level >= 20)) Heap Fetches: 80 -> Index Only Scan using projects_pkey on projects projects_2 (cost=0.43..2.00 rows=1 width=4) (actual time=0.004..0.004 rows=1 loop s=1121) Index Cond: (id = project_authorizations_5.project_id) Heap Fetches: 83 -> Index Scan using projects_pkey on projects (cost=0.43..6.11 rows=1 width=4) (actual time=0.007..0.007 rows=0 loops=275620) Index Cond: (id = issues_1.project_id) Filter: ((alternatives: SubPlan 4 or hashed SubPlan 5) OR (visibility_level = ANY ('{10,20}'::integer[]))) Rows Removed by Filter: 1 SubPlan 4 -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_1 (cos t=0.43..3.45 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=275620) Index Cond: ((user_id = 443319) AND (project_id = projects.id)) Heap Fetches: 43 SubPlan 5 -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_2 (cos t=0.43..10.23 rows=88 width=4) (never executed) Index Cond: (user_id = 443319) Heap Fetches: 0 -> Index Scan using index_project_features_on_project_id on project_features (cost=0.43..3.95 rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=57465) Index Cond: (project_id = projects.id) Filter: ((issues_access_level = ANY ('{NULL,20}'::integer[])) OR ((issues_access_level = 10) AND (alternatives: SubPlan 6 or hashed SubPlan 7))) Rows Removed by Filter: 0 SubPlan 6 -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_3 (cost=0.43..3.45 rows= 1 width=0) (actual time=0.004..0.004 rows=0 loops=4901) Index Cond: ((user_id = 443319) AND (project_id = projects.id)) Heap Fetches: 1 SubPlan 7 -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_4 (cost=0.43..10.23 rows =88 width=4) (never executed) Index Cond: (user_id = 443319) Heap Fetches: 0 Planning time: 3.711 ms Execution time: 46792.241 ms (79 rows)
Please register or sign in to comment