explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ub0d

Settings
# exclusive inclusive rows x rows loops node
1. 0.101 16.000 ↑ 1.0 2 1

GroupAggregate (cost=756.13..756.17 rows=2 width=15) (actual time=15.949..16.000 rows=2 loops=1)

  • Group Key: issues.state
2. 0.218 15.899 ↓ 282.7 848 1

Sort (cost=756.13..756.14 rows=3 width=7) (actual time=15.842..15.899 rows=848 loops=1)

  • Sort Key: issues.state
  • Sort Method: quicksort Memory: 64kB
3. 0.368 15.681 ↓ 282.7 848 1

Nested Loop Left Join (cost=658.20..756.11 rows=3 width=7) (actual time=1.075..15.681 rows=848 loops=1)

  • Filter: ((project_features.issues_access_level IS NULL) OR (project_features.issues_access_level = ANY ('{20,30}'::integer[])) OR ((project_features.issues_access_level = 10) AND (alternatives: SubPlan 7 or hashed SubPlan 8)))
4. 0.000 13.617 ↓ 282.7 848 1

Nested Loop (cost=657.76..743.83 rows=3 width=11) (actual time=1.060..13.617 rows=848 loops=1)

5. 0.550 9.395 ↓ 141.3 848 1

Nested Loop (cost=657.33..719.31 rows=6 width=11) (actual time=1.024..9.395 rows=848 loops=1)

6. 0.428 1.213 ↓ 141.3 848 1

HashAggregate (cost=656.77..656.83 rows=6 width=4) (actual time=1.006..1.213 rows=848 loops=1)

  • Group Key: epic_issues.issue_id
7. 0.116 0.785 ↓ 141.3 848 1

Nested Loop (cost=650.93..656.75 rows=6 width=4) (actual time=0.053..0.785 rows=848 loops=1)

8. 0.002 0.038 ↑ 1.0 1 1

HashAggregate (cost=650.64..650.65 rows=1 width=4) (actual time=0.038..0.038 rows=1 loops=1)

  • Group Key: epics.id
9. 0.036 0.036 ↑ 1.0 1 1

CTE Scan on base_and_descendants epics (cost=644.53..650.63 rows=1 width=4) (actual time=0.024..0.036 rows=1 loops=1)

  • Filter: (group_id = 2573511)
10.          

CTE base_and_descendants

11. 0.006 0.032 ↑ 271.0 1 1

Recursive Union (cost=0.29..644.53 rows=271 width=775) (actual time=0.020..0.032 rows=1 loops=1)

12. 0.015 0.015 ↑ 1.0 1 1

Index Scan using epics_pkey on epics epics_1 (cost=0.29..3.30 rows=1 width=775) (actual time=0.014..0.015 rows=1 loops=1)

  • Index Cond: (id = 14355)
13. 0.001 0.011 ↓ 0.0 0 1

Nested Loop (cost=0.29..63.58 rows=27 width=775) (actual time=0.011..0.011 rows=0 loops=1)

14. 0.000 0.000 ↑ 10.0 1 1

WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.000..0.000 rows=1 loops=1)

15. 0.010 0.010 ↓ 0.0 0 1

Index Scan using index_epics_on_parent_id on epics epics_2 (cost=0.29..6.31 rows=3 width=775) (actual time=0.010..0.010 rows=0 loops=1)

  • Index Cond: (parent_id = base_and_descendants.id)
16. 0.631 0.631 ↓ 141.3 848 1

Index Scan using index_epic_issues_on_epic_id on epic_issues (cost=0.29..6.04 rows=6 width=8) (actual time=0.015..0.631 rows=848 loops=1)

  • Index Cond: (epic_id = epics.id)
17. 7.504 7.632 ↑ 1.0 1 848

Index Scan using issues_pkey on issues (cost=0.56..10.40 rows=1 width=15) (actual time=0.009..0.009 rows=1 loops=848)

  • Index Cond: (id = epic_issues.issue_id)
  • Filter: ((confidential IS NOT TRUE) OR (confidential AND ((author_id = 786864) OR (alternatives: SubPlan 1 or hashed SubPlan 2) OR (alternatives: SubPlan 3 or hashed SubPlan 4))))
18.          

SubPlan (forIndex Scan)

19. 0.080 0.080 ↓ 0.0 0 16

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) (actual time=0.005..0.005 rows=0 loops=16)

  • Index Cond: ((issue_id = issues.id) AND (user_id = 786864))
  • Heap Fetches: 0
20. 0.000 0.000 ↓ 0.0 0

Index Scan using index_issue_assignees_on_user_id on issue_assignees issue_assignees_1 (cost=0.43..72.13 rows=68 width=4) (never executed)

  • Index Cond: (user_id = 786864)
21. 0.048 0.048 ↑ 1.0 1 16

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations (cost=0.56..3.59 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=16)

  • Index Cond: ((user_id = 786864) AND (project_id = issues.project_id) AND (access_level >= 20))
  • Heap Fetches: 16
22. 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_1 (cost=0.56..38.12 rows=109 width=4) (never executed)

  • Index Cond: ((user_id = 786864) AND (access_level >= 20))
  • Heap Fetches: 0
23. 2.544 4.240 ↑ 1.0 1 848

Index Scan using projects_pkey on projects (cost=0.43..4.08 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=848)

  • Index Cond: (id = issues.project_id)
  • Filter: ((alternatives: SubPlan 5 or hashed SubPlan 6) OR (visibility_level = ANY ('{10,20}'::integer[])))
24.          

SubPlan (forIndex Scan)

25. 1.696 1.696 ↑ 1.0 1 848

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_2 (cost=0.56..3.59 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=848)

  • Index Cond: ((user_id = 786864) AND (project_id = projects.id) AND (access_level >= 10))
  • Heap Fetches: 848
26. 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..40.51 rows=117 width=4) (never executed)

  • Index Cond: ((user_id = 786864) AND (access_level >= 10))
  • Heap Fetches: 0
27. 1.696 1.696 ↑ 1.0 1 848

Index Scan using index_project_features_on_project_id on project_features (cost=0.43..0.49 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=848)

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

SubPlan (forNested Loop Left Join)

29. 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_4 (cost=0.56..3.59 rows=1 width=0) (never executed)

  • Index Cond: ((user_id = 786864) AND (project_id = projects.id) AND (access_level >= 10))
  • Heap Fetches: 0
30. 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_5 (cost=0.56..40.51 rows=117 width=4) (never executed)

  • Index Cond: ((user_id = 786864) AND (access_level >= 10))
  • Heap Fetches: 0
Planning time : 2.805 ms
Execution time : 16.231 ms