QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------
 Aggregate  (cost=4439644.15..4439644.16 rows=1 width=8) (actual time=70755.691..70755.691 rows=1 loops=1)
   ->  Hash Semi Join  (cost=1025745.88..4437729.08 rows=766028 width=0) (actual time=33868.134..70737.318 rows=50597 loops=1)
         Hash Cond: (issues.project_id = projects.id)
         ->  Bitmap Heap Scan on issues  (cost=7861.74..3399287.05 rows=766028 width=4) (actual time=362.105..36832.266 rows=428009 loops=1)
               Recheck Cond: (((title)::text ~~* '%test%'::text) OR (description ~~* '%test%'::text))
               Rows Removed by Index Recheck: 2415686
               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=126603 lossy=213800
               ->  BitmapOr  (cost=7678.25..7678.25 rows=794098 width=0) (actual time=310.597..310.597 rows=0 loops=1)
                     ->  Bitmap Index Scan on index_issues_on_title_trigram  (cost=0.00..2543.32 rows=280910 width=0) (actual time=75.809..75.809 rows=178174 loops=1)
                           Index Cond: ((title)::text ~~* '%test%'::text)
                     ->  Bitmap Index Scan on index_issues_on_description_trigram  (cost=0.00..4751.91 rows=513188 width=0) (actual time=234.785..234.785 rows=479299 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.76 rows=77 width=4) (actual time=0.018..0.459 rows=242 l
oops=1)
                       Index Cond: (user_id = 443319)
               SubPlan 3
                 ->  Nested Loop  (cost=0.86..183.27 rows=86 width=4) (actual time=0.040..6.525 rows=1121 loops=1)
                       ->  Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_1  (cost=0.43..10.4
3 rows=86 width=4) (actual time=0.016..0.574 rows=1121 loops=1)
                             Index Cond: ((user_id = 443319) AND (access_level >= 20))
                             Heap Fetches: 81
                       ->  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_1.project_id)
                             Heap Fetches: 57
         ->  Hash  (cost=1000922.37..1000922.37 rows=1033822 width=4) (actual time=33504.165..33504.165 rows=679562 loops=1)
               Buckets: 524288  Batches: 4  Memory Usage: 10069kB
               ->  Merge Left Join  (cost=1.29..1000922.37 rows=1033822 width=4) (actual time=0.645..33078.360 rows=679562 loops=1)
                     Merge Cond: (projects.id = project_authorizations.project_id)
                     Filter: (((project_authorizations.user_id = 443319) AND (project_authorizations.project_id = projects.id)) OR ((projects.visibility_level = ANY ('{10,20}'::inte
ger[])) AND (project_features.issues_access_level = ANY ('{NULL,20}'::integer[]))))
                     Rows Removed by Filter: 9382985
                     ->  Merge Left Join  (cost=0.86..487070.80 rows=3795740 width=12) (actual time=0.040..14329.053 rows=3687952 loops=1)
                           Merge Cond: (projects.id = project_features.project_id)
                           ->  Index Scan using projects_pkey on projects  (cost=0.43..298181.95 rows=3795740 width=8) (actual time=0.011..6427.912 rows=3687876 loops=1)
                           ->  Index Scan using index_project_features_on_project_id on project_features  (cost=0.43..133529.25 rows=3700248 width=8) (actual time=0.013..4188.258 ro
ws=3687953 loops=1)
                     ->  Index Scan using index_project_authorizations_on_project_id on project_authorizations  (cost=0.43..277622.31 rows=10077329 width=8) (actual time=0.014..1344
3.689 rows=10061872 loops=1)
 Planning time: 2.813 ms
 Execution time: 70755.965 ms
(42 rows)

``` QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------ Aggregate (cost=4439644.15..4439644.16 rows=1 width=8) (actual time=70755.691..70755.691 rows=1 loops=1) -> Hash Semi Join (cost=1025745.88..4437729.08 rows=766028 width=0) (actual time=33868.134..70737.318 rows=50597 loops=1) Hash Cond: (issues.project_id = projects.id) -> Bitmap Heap Scan on issues (cost=7861.74..3399287.05 rows=766028 width=4) (actual time=362.105..36832.266 rows=428009 loops=1) Recheck Cond: (((title)::text ~~* '%test%'::text) OR (description ~~* '%test%'::text)) Rows Removed by Index Recheck: 2415686 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=126603 lossy=213800 -> BitmapOr (cost=7678.25..7678.25 rows=794098 width=0) (actual time=310.597..310.597 rows=0 loops=1) -> Bitmap Index Scan on index_issues_on_title_trigram (cost=0.00..2543.32 rows=280910 width=0) (actual time=75.809..75.809 rows=178174 loops=1) Index Cond: ((title)::text ~~* '%test%'::text) -> Bitmap Index Scan on index_issues_on_description_trigram (cost=0.00..4751.91 rows=513188 width=0) (actual time=234.785..234.785 rows=479299 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.76 rows=77 width=4) (actual time=0.018..0.459 rows=242 l oops=1) Index Cond: (user_id = 443319) SubPlan 3 -> Nested Loop (cost=0.86..183.27 rows=86 width=4) (actual time=0.040..6.525 rows=1121 loops=1) -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_1 (cost=0.43..10.4 3 rows=86 width=4) (actual time=0.016..0.574 rows=1121 loops=1) Index Cond: ((user_id = 443319) AND (access_level >= 20)) Heap Fetches: 81 -> 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_1.project_id) Heap Fetches: 57 -> Hash (cost=1000922.37..1000922.37 rows=1033822 width=4) (actual time=33504.165..33504.165 rows=679562 loops=1) Buckets: 524288 Batches: 4 Memory Usage: 10069kB -> Merge Left Join (cost=1.29..1000922.37 rows=1033822 width=4) (actual time=0.645..33078.360 rows=679562 loops=1) Merge Cond: (projects.id = project_authorizations.project_id) Filter: (((project_authorizations.user_id = 443319) AND (project_authorizations.project_id = projects.id)) OR ((projects.visibility_level = ANY ('{10,20}'::inte ger[])) AND (project_features.issues_access_level = ANY ('{NULL,20}'::integer[])))) Rows Removed by Filter: 9382985 -> Merge Left Join (cost=0.86..487070.80 rows=3795740 width=12) (actual time=0.040..14329.053 rows=3687952 loops=1) Merge Cond: (projects.id = project_features.project_id) -> Index Scan using projects_pkey on projects (cost=0.43..298181.95 rows=3795740 width=8) (actual time=0.011..6427.912 rows=3687876 loops=1) -> Index Scan using index_project_features_on_project_id on project_features (cost=0.43..133529.25 rows=3700248 width=8) (actual time=0.013..4188.258 ro ws=3687953 loops=1) -> Index Scan using index_project_authorizations_on_project_id on project_authorizations (cost=0.43..277622.31 rows=10077329 width=8) (actual time=0.014..1344 3.689 rows=10061872 loops=1) Planning time: 2.813 ms Execution time: 70755.965 ms (42 rows) ```