explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IOdg

Settings
# exclusive inclusive rows x rows loops node
1. 0.075 126.926 ↑ 1.0 1 1

Aggregate (cost=37,418.71..37,418.72 rows=1 width=8) (actual time=126.926..126.926 rows=1 loops=1)

2. 0.072 126.851 ↓ 101.0 101 1

Nested Loop Semi Join (cost=36,596.90..37,418.70 rows=1 width=16) (actual time=123.290..126.851 rows=101 loops=1)

  • Join Filter: (projects.namespace_id = namespaces.id)
  • Rows Removed by Join Filter: 22
3. 0.063 126.577 ↓ 101.0 101 1

Nested Loop Left Join (cost=35,321.28..36,136.67 rows=1 width=20) (actual time=123.260..126.577 rows=101 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.023 126.205 ↓ 101.0 101 1

Nested Loop (cost=35,320.85..36,132.57 rows=1 width=24) (actual time=123.243..126.205 rows=101 loops=1)

5. 1.424 125.172 ↓ 101.0 101 1

Nested Loop (cost=35,320.41..36,128.32 rows=1 width=20) (actual time=123.181..125.172 rows=101 loops=1)

6. 2.787 89.134 ↓ 3,846.0 3,846 1

Merge Join (cost=35,319.85..36,117.69 rows=1 width=24) (actual time=69.117..89.134 rows=3,846 loops=1)

  • Merge Cond: (issue_label_added_0885e167af.model_id = issue_label_added_9597a2bf2c.model_id)
  • Join Filter: (issue_label_added_9597a2bf2c.created_at >= issue_label_added_0885e167af.created_at)
  • Rows Removed by Join Filter: 120
7. 0.899 45.568 ↓ 96.3 6,647 1

Subquery Scan on issue_label_added_0885e167af (cost=20,015.79..20,467.50 rows=69 width=12) (actual time=37.269..45.568 rows=6,647 loops=1)

  • Filter: (issue_label_added_0885e167af.label_assignment_order = 1)
  • Rows Removed by Filter: 654
8. 5.714 44.669 ↑ 1.9 7,301 1

WindowAgg (cost=20,015.79..20,293.77 rows=13,899 width=20) (actual time=37.253..44.669 rows=7,301 loops=1)

9. 7.927 38.955 ↑ 1.9 7,302 1

Sort (cost=20,015.79..20,050.53 rows=13,899 width=12) (actual time=37.239..38.955 rows=7,302 loops=1)

  • Sort Key: resource_label_events.issue_id, resource_label_events.created_at
  • Sort Method: quicksort Memory: 930kB
10. 31.028 31.028 ↑ 1.2 11,628 1

Index Scan using index_resource_label_events_on_label_id_and_action on resource_label_events (cost=0.56..19,059.35 rows=13,899 width=12) (actual time=0.051..31.028 rows=11,628 loops=1)

  • Index Cond: ((label_id = 2,526,320) AND (action = 1))
11. 1.210 40.779 ↓ 116.9 6,198 1

Materialize (cost=15,304.06..15,649.87 rows=53 width=12) (actual time=31.822..40.779 rows=6,198 loops=1)

12. 0.835 39.569 ↓ 116.9 6,198 1

Subquery Scan on issue_label_added_9597a2bf2c (cost=15,304.06..15,649.73 rows=53 width=12) (actual time=31.818..39.569 rows=6,198 loops=1)

  • Filter: (issue_label_added_9597a2bf2c.label_assignment_order = 1)
  • Rows Removed by Filter: 361
13. 5.386 38.734 ↑ 1.6 6,559 1

WindowAgg (cost=15,304.06..15,516.78 rows=10,636 width=20) (actual time=31.816..38.734 rows=6,559 loops=1)

14. 7.626 33.348 ↑ 1.6 6,560 1

Sort (cost=15,304.06..15,330.65 rows=10,636 width=12) (actual time=31.801..33.348 rows=6,560 loops=1)

  • Sort Key: resource_label_events_1.issue_id, resource_label_events_1.created_at
  • Sort Method: quicksort Memory: 871kB
15. 25.722 25.722 ↑ 1.0 10,368 1

Index Scan using index_resource_label_events_on_label_id_and_action on resource_label_events resource_label_events_1 (cost=0.56..14,592.69 rows=10,636 width=12) (actual time=0.063..25.722 rows=10,368 loops=1)

  • Index Cond: ((label_id = 2,526,326) AND (action = 1))
16. 34.482 34.614 ↓ 0.0 0 3,846

Index Scan using issues_pkey on issues (cost=0.56..10.64 rows=1 width=8) (actual time=0.009..0.009 rows=0 loops=3,846)

  • Index Cond: (id = issue_label_added_0885e167af.model_id)
  • Filter: ((created_at <= '2020-07-06 23:59:59.999999+00'::timestamp with time zone) AND (created_at >= '2020-06-07 00:00:00+00'::timestamp with time zone) AND ((confidential IS NOT TRUE) OR (confidential AND ((author_id = 4,156,052) OR (alternatives: SubPlan 1 or hashed SubPlan 2) OR (alternatives: SubPlan 3 or hashed SubPlan 4)))))
  • Rows Removed by Filter: 1
17.          

SubPlan (for Index Scan)

18. 0.096 0.096 ↓ 0.0 0 12

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.008..0.008 rows=0 loops=12)

  • Index Cond: ((issue_id = issues.id) AND (user_id = 4,156,052))
  • Heap Fetches: 0
19. 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..82.90 rows=82 width=4) (never executed)

  • Index Cond: (user_id = 4,156,052)
20. 0.036 0.036 ↑ 1.0 1 12

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

  • Index Cond: ((user_id = 4,156,052) AND (project_id = issues.project_id) AND (access_level >= 20))
  • Heap Fetches: 0
21. 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.57..41.51 rows=1,042 width=4) (never executed)

  • Index Cond: ((user_id = 4,156,052) AND (access_level >= 20))
  • Heap Fetches: 0
22. 0.707 1.010 ↑ 1.0 1 101

Index Scan using projects_pkey on projects (cost=0.43..4.24 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=101)

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

SubPlan (for Index Scan)

24. 0.303 0.303 ↑ 1.0 1 101

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

  • Index Cond: ((user_id = 4,156,052) AND (project_id = projects.id) AND (access_level >= 10))
  • Heap Fetches: 1
25. 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.57..43.61 rows=1,252 width=4) (never executed)

  • Index Cond: ((user_id = 4,156,052) AND (access_level >= 10))
  • Heap Fetches: 0
26. 0.303 0.303 ↑ 1.0 1 101

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

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

SubPlan (for Nested Loop Left Join)

28. 0.006 0.006 ↑ 1.0 1 1

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_4 (cost=0.57..3.59 rows=1 width=0) (actual time=0.006..0.006 rows=1 loops=1)

  • Index Cond: ((user_id = 4,156,052) AND (project_id = projects.id) AND (access_level >= 10))
  • Heap Fetches: 1
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.57..43.61 rows=1,252 width=4) (never executed)

  • Index Cond: ((user_id = 4,156,052) AND (access_level >= 10))
  • Heap Fetches: 0
30. 0.202 0.202 ↑ 151.0 1 101

CTE Scan on base_and_descendants namespaces (cost=1,275.62..1,278.64 rows=151 width=4) (actual time=0.000..0.002 rows=1 loops=101)

31.          

CTE base_and_descendants

32. 0.060 0.176 ↑ 6.6 23 1

Recursive Union (cost=0.43..1,275.62 rows=151 width=329) (actual time=0.025..0.176 rows=23 loops=1)

33. 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=329) (actual time=0.016..0.017 rows=1 loops=1)

  • Index Cond: (id = 9,970)
  • Filter: ((type)::text = 'Group'::text)
34. 0.009 0.099 ↓ 1.5 22 1

Nested Loop (cost=0.56..126.91 rows=15 width=329) (actual time=0.031..0.099 rows=22 loops=1)

35. 0.005 0.005 ↑ 10.0 1 1

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

36. 0.085 0.085 ↓ 22.0 22 1

Index Scan using index_namespaces_on_parent_id_and_id on namespaces namespaces_2 (cost=0.56..12.66 rows=1 width=329) (actual time=0.022..0.085 rows=22 loops=1)

  • Index Cond: (parent_id = base_and_descendants.id)
  • Filter: ((type)::text = 'Group'::text)
Planning time : 5.699 ms
Execution time : 127.259 ms