explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mwfn

Settings
# exclusive inclusive rows x rows loops node
1. 0.014 6,229.952 ↑ 4.3 231 1

Limit (cost=227,852.78..227,855.28 rows=1,000 width=759) (actual time=6,229.839..6,229.952 rows=231 loops=1)

2. 0.647 6,229.938 ↑ 7.7 231 1

Sort (cost=227,852.78..227,857.25 rows=1,790 width=759) (actual time=6,229.839..6,229.938 rows=231 loops=1)

  • Sort Key: issues.updated_at DESC
  • Sort Method: quicksort Memory: 358kB
3. 2.878 6,229.291 ↑ 7.7 231 1

Nested Loop Left Join (cost=4,571.76..227,756.07 rows=1,790 width=759) (actual time=35.169..6,229.291 rows=231 loops=1)

  • Filter: (((projects.visibility_level > 0) AND ((project_features.issues_access_level IS NULL) OR (project_features.issues_access_level >= 20) OR ((project_features.issues_access_level = 10) AND (alternatives: SubPlan 6 or hashed SubPlan 7)))) OR ((projects.visibility_level = 0) AND ((project_features.issues_access_level IS NULL) OR (project_features.issues_access_level >= 10)) AND (alternatives: SubPlan 8 or hashed SubPlan 9)))
  • Rows Removed by Filter: 11
4. 3.374 6,223.216 ↑ 14.8 242 1

Nested Loop (cost=4,571.32..200,034.86 rows=3,590 width=767) (actual time=35.141..6,223.216 rows=242 loops=1)

5. 6,135.747 6,178.974 ↑ 5.2 2,404 1

Bitmap Heap Scan on issues (cost=4,570.89..70,898.32 rows=12,513 width=759) (actual time=35.103..6,178.974 rows=2,404 loops=1)

  • Recheck Cond: (((title)::text ~~* '%amex%'::text) OR (description ~~* '%amex%'::text))
  • Rows Removed by Index Recheck: 8322
  • Filter: ((confidential IS NOT TRUE) OR (confidential AND ((author_id = 64248) OR (alternatives: SubPlan 1 or hashed SubPlan 2) OR (hashed SubPlan 3))))
  • Rows Removed by Filter: 25
  • Heap Blocks: exact=10109
6. 0.002 32.102 ↓ 0.0 0 1

BitmapOr (cost=1,556.68..1,556.68 rows=12,723 width=0) (actual time=32.102..32.102 rows=0 loops=1)

7. 8.582 8.582 ↑ 2.2 696 1

Bitmap Index Scan on index_issues_on_title_trigram (cost=0.00..746.24 rows=1,499 width=0) (actual time=8.582..8.582 rows=696 loops=1)

  • Index Cond: ((title)::text ~~* '%amex%'::text)
8. 23.518 23.518 ↑ 1.1 10,444 1

Bitmap Index Scan on index_issues_on_description_trigram (cost=0.00..804.18 rows=11,223 width=0) (actual time=23.518..23.518 rows=10,444 loops=1)

  • Index Cond: (description ~~* '%amex%'::text)
9.          

SubPlan (forBitmap Heap Scan)

10. 0.000 0.000 ↓ 0.0 0

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 = 64248))
  • Heap Fetches: 0
11. 1.166 1.166 ↓ 8.5 465 1

Index Scan using index_issue_assignees_on_user_id on issue_assignees issue_assignees_1 (cost=0.43..60.52 rows=55 width=4) (actual time=0.019..1.166 rows=465 loops=1)

  • Index Cond: (user_id = 64248)
12. 0.000 9.959 ↓ 1.1 1,460 1

Nested Loop (cost=1.00..3,010.88 rows=1,334 width=4) (actual time=0.034..9.959 rows=1,460 loops=1)

13. 1.206 1.206 ↓ 1.1 1,460 1

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations (cost=0.56..350.90 rows=1,334 width=4) (actual time=0.020..1.206 rows=1,460 loops=1)

  • Index Cond: ((user_id = 64248) AND (access_level >= 20))
  • Heap Fetches: 333
14. 8.760 8.760 ↑ 1.0 1 1,460

Index Only Scan using projects_pkey on projects projects_1 (cost=0.43..1.98 rows=1 width=4) (actual time=0.005..0.006 rows=1 loops=1,460)

  • Index Cond: (id = project_authorizations.project_id)
  • Heap Fetches: 308
15. 27.555 40.868 ↓ 0.0 0 2,404

Index Scan using projects_pkey on projects (cost=0.43..10.31 rows=1 width=8) (actual time=0.017..0.017 rows=0 loops=2,404)

  • Index Cond: (id = issues.project_id)
  • Filter: (((alternatives: SubPlan 4 or hashed SubPlan 5) OR (visibility_level = 20)) AND ((visibility_level > 0) OR ((visibility_level = 0) AND (alternatives: SubPlan 8 or hashed SubPlan 9))))
  • Rows Removed by Filter: 1
16.          

SubPlan (forIndex Scan)

17. 12.020 12.020 ↓ 0.0 0 2,404

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_1 (cost=0.56..3.58 rows=1 width=0) (actual time=0.005..0.005 rows=0 loops=2,404)

  • Index Cond: ((user_id = 64248) AND (project_id = projects.id))
  • Heap Fetches: 0
18. 0.000 0.000 ↓ 0.0 0

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_2 (cost=0.56..355.11 rows=1,368 width=4) (never executed)

  • Index Cond: (user_id = 64248)
  • Heap Fetches: 0
19. 0.004 0.004 ↑ 1.0 1 1

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_5 (cost=0.56..3.58 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=1)

  • Index Cond: ((user_id = 64248) AND (project_id = projects.id) AND (access_level >= 10))
  • Heap Fetches: 0
20. 1.289 1.289 ↓ 1.1 1,460 1

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_6 (cost=0.56..358.53 rows=1,368 width=4) (actual time=0.016..1.289 rows=1,460 loops=1)

  • Index Cond: ((user_id = 64248) AND (access_level >= 10))
  • Heap Fetches: 333
21. 1.936 1.936 ↑ 1.0 1 242

Index Scan using index_project_features_on_project_id on project_features (cost=0.43..0.53 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=242)

  • Index Cond: (projects.id = project_id)
22.          

SubPlan (forNested Loop Left Join)

23. 0.000 0.000 ↓ 0.0 0

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_3 (cost=0.56..3.58 rows=1 width=0) (never executed)

  • Index Cond: ((user_id = 64248) AND (project_id = projects.id) AND (access_level >= 10))
  • Heap Fetches: 0
24. 1.261 1.261 ↓ 1.1 1,460 1

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_4 (cost=0.56..358.53 rows=1,368 width=4) (actual time=0.024..1.261 rows=1,460 loops=1)

  • Index Cond: ((user_id = 64248) AND (access_level >= 10))
  • Heap Fetches: 333
Planning time : 16.286 ms
Execution time : 6,230.162 ms