explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Zur

Settings
# exclusive inclusive rows x rows loops node
1. 0.772 155.946 ↓ 456.0 456 1

GroupAggregate (cost=114,127.33..114,127.36 rows=1 width=16) (actual time=154.838..155.946 rows=456 loops=1)

  • Group Key: label_links.label_id, (date(issues.created_at))
2. 2.357 155.174 ↓ 5,085.0 5,085 1

Sort (cost=114,127.33..114,127.34 rows=1 width=12) (actual time=154.829..155.174 rows=5,085 loops=1)

  • Sort Key: label_links.label_id, (date(issues.created_at))
  • Sort Method: quicksort Memory: 431kB
3. 2.893 152.817 ↓ 5,085.0 5,085 1

Nested Loop Left Join (cost=1,266.24..114,127.32 rows=1 width=12) (actual time=1.177..152.817 rows=5,085 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. 6.570 139.708 ↓ 5,085.0 5,085 1

Nested Loop (cost=1,265.80..114,123.23 rows=1 width=20) (actual time=1.163..139.708 rows=5,085 loops=1)

5. 1.324 42.058 ↓ 50.2 11,385 1

Nested Loop (cost=1,265.24..113,923.96 rows=227 width=16) (actual time=0.791..42.058 rows=11,385 loops=1)

6. 0.130 4.980 ↑ 2.0 606 1

Nested Loop (cost=1,264.68..12,146.75 rows=1,184 width=4) (actual time=0.770..4.980 rows=606 loops=1)

7. 0.050 0.755 ↑ 2.0 65 1

HashAggregate (cost=1,264.24..1,265.55 rows=131 width=4) (actual time=0.723..0.755 rows=65 loops=1)

  • Group Key: namespaces.id
8. 0.705 0.705 ↑ 2.0 65 1

CTE Scan on base_and_descendants namespaces (cost=1,259.99..1,262.61 rows=131 width=4) (actual time=0.025..0.705 rows=65 loops=1)

9.          

CTE base_and_descendants

10. 0.118 0.650 ↑ 2.0 65 1

Recursive Union (cost=0.43..1,259.99 rows=131 width=322) (actual time=0.023..0.650 rows=65 loops=1)

11. 0.017 0.017 ↑ 1.0 1 1

Index Scan using namespaces_pkey on namespaces namespaces_1 (cost=0.43..3.45 rows=1 width=322) (actual time=0.016..0.017 rows=1 loops=1)

  • Index Cond: (id = 9970)
  • Filter: ((type)::text = 'Group'::text)
12. 0.055 0.515 ↑ 1.0 13 5

Nested Loop (cost=0.43..125.39 rows=13 width=322) (actual time=0.020..0.103 rows=13 loops=5)

13. 0.005 0.005 ↓ 1.3 13 5

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

14. 0.455 0.455 ↑ 1.0 1 65

Index Scan using index_namespaces_on_parent_id_and_id on namespaces namespaces_2 (cost=0.43..12.51 rows=1 width=322) (actual time=0.005..0.007 rows=1 loops=65)

  • Index Cond: (parent_id = base_and_descendants.id)
  • Filter: ((type)::text = 'Group'::text)
15. 2.277 4.095 ↑ 1.0 9 65

Index Scan using index_projects_on_namespace_id on projects (cost=0.43..82.97 rows=9 width=8) (actual time=0.013..0.063 rows=9 loops=65)

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

SubPlan (forIndex Scan)

17. 1.818 1.818 ↑ 1.0 1 606

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.003..0.003 rows=1 loops=606)

  • Index Cond: ((user_id = 4156052) AND (project_id = projects.id) AND (access_level >= 10))
  • Heap Fetches: 249
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_3 (cost=0.56..645.41 rows=2,171 width=4) (never executed)

  • Index Cond: ((user_id = 4156052) AND (access_level >= 10))
  • Heap Fetches: 0
19. 31.110 35.754 ↓ 1.6 19 606

Index Scan using index_issues_on_project_id_and_created_at_and_id_and_state on issues (cost=0.56..85.84 rows=12 width=16) (actual time=0.008..0.059 rows=19 loops=606)

  • Index Cond: ((project_id = projects.id) AND (created_at <= '2019-08-01 00:00:00+00'::timestamp with time zone) AND (created_at >= '2019-05-01 00:00:00+00'::timestamp with time zone))
  • Filter: ((confidential IS NOT TRUE) OR (confidential AND ((author_id = 4156052) OR (alternatives: SubPlan 1 or hashed SubPlan 2) OR (alternatives: SubPlan 3 or hashed SubPlan 4))))
20.          

SubPlan (forIndex Scan)

21. 3.320 3.320 ↓ 0.0 0 664

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=664)

  • Index Cond: ((issue_id = issues.id) AND (user_id = 4156052))
  • Heap Fetches: 2
22. 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..69.33 rows=65 width=4) (never executed)

  • Index Cond: (user_id = 4156052)
23. 1.324 1.324 ↑ 1.0 1 662

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.002..0.002 rows=1 loops=662)

  • Index Cond: ((user_id = 4156052) AND (project_id = issues.project_id) AND (access_level >= 20))
  • Heap Fetches: 208
24. 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..608.06 rows=2,038 width=4) (never executed)

  • Index Cond: ((user_id = 4156052) AND (access_level >= 20))
  • Heap Fetches: 0
25. 91.080 91.080 ↓ 0.0 0 11,385

Index Scan using index_label_links_on_target_id_and_target_type on label_links (cost=0.56..0.87 rows=1 width=8) (actual time=0.008..0.008 rows=0 loops=11,385)

  • Index Cond: ((target_id = issues.id) AND ((target_type)::text = 'Issue'::text))
  • Filter: (label_id = ANY ('{2731248,10230929,4116705,2492649,2278648,2779806}'::integer[]))
  • Rows Removed by Filter: 3
26. 10.170 10.170 ↑ 1.0 1 5,085

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=5,085)

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

SubPlan (forNested Loop Left Join)

28. 0.046 0.046 ↑ 1.0 1 23

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) (actual time=0.002..0.002 rows=1 loops=23)

  • Index Cond: ((user_id = 4156052) AND (project_id = projects.id) AND (access_level >= 10))
  • Heap Fetches: 2
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_5 (cost=0.56..645.41 rows=2,171 width=4) (never executed)

  • Index Cond: ((user_id = 4156052) AND (access_level >= 10))
  • Heap Fetches: 0
Planning time : 3.003 ms
Execution time : 156.208 ms