explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wwhs

Settings
# exclusive inclusive rows x rows loops node
1. 0.348 13,934.358 ↑ 1.0 1 1

Aggregate (cost=44,593.92..44,593.93 rows=1 width=8) (actual time=13,934.358..13,934.358 rows=1 loops=1)

2. 0.011 13,934.010 ↓ 120.0 120 1

Nested Loop Semi Join (cost=43,613.86..44,593.91 rows=1 width=16) (actual time=13,928.352..13,934.010 rows=120 loops=1)

  • Join Filter: (projects.namespace_id = namespaces.id)
  • Rows Removed by Join Filter: 12
3. 0.073 13,933.519 ↓ 120.0 120 1

Nested Loop Left Join (cost=42,193.91..43,167.55 rows=1 width=20) (actual time=13,928.162..13,933.519 rows=120 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.116 13,933.086 ↓ 120.0 120 1

Nested Loop (cost=42,193.48..43,163.45 rows=1 width=24) (actual time=13,928.099..13,933.086 rows=120 loops=1)

5. 1.627 13,932.010 ↓ 120.0 120 1

Nested Loop (cost=42,193.04..43,158.80 rows=1 width=20) (actual time=13,927.889..13,932.010 rows=120 loops=1)

6. 2.344 13,839.215 ↓ 4,144.0 4,144 1

Merge Join (cost=42,192.48..43,148.16 rows=1 width=24) (actual time=13,820.333..13,839.215 rows=4,144 loops=1)

  • Merge Cond: (issue_label_added_bb9dadb132.model_id = issue_label_added_fc400a2c30.model_id)
  • Join Filter: (issue_label_added_fc400a2c30.created_at >= issue_label_added_bb9dadb132.created_at)
  • Rows Removed by Join Filter: 133
7. 0.811 7,375.729 ↓ 88.3 7,153 1

Subquery Scan on issue_label_added_bb9dadb132 (cost=23,386.43..23,915.70 rows=81 width=12) (actual time=7,367.706..7,375.729 rows=7,153 loops=1)

  • Filter: (issue_label_added_bb9dadb132.label_assignment_order = 1)
  • Rows Removed by Filter: 708
8. 5.226 7,374.918 ↑ 2.1 7,861 1

WindowAgg (cost=23,386.43..23,712.13 rows=16,285 width=20) (actual time=7,367.703..7,374.918 rows=7,861 loops=1)

9. 16.599 7,369.692 ↑ 2.1 7,862 1

Sort (cost=23,386.43..23,427.15 rows=16,285 width=12) (actual time=7,367.539..7,369.692 rows=7,862 loops=1)

  • Sort Key: resource_label_events.issue_id, resource_label_events.created_at
  • Sort Method: quicksort Memory: 976kB
10. 7,353.093 7,353.093 ↑ 1.3 12,610 1

Index Scan using index_resource_label_events_on_label_id_and_action on resource_label_events (cost=0.56..22,247.20 rows=16,285 width=12) (actual time=1.895..7,353.093 rows=12,610 loops=1)

  • Index Cond: ((label_id = 2,526,320) AND (action = 1))
11. 1.111 6,461.142 ↓ 101.0 6,666 1

Materialize (cost=18,806.04..19,232.09 rows=66 width=12) (actual time=6,452.582..6,461.142 rows=6,666 loops=1)

12. 0.780 6,460.031 ↓ 101.0 6,666 1

Subquery Scan on issue_label_added_fc400a2c30 (cost=18,806.04..19,231.92 rows=66 width=12) (actual time=6,452.575..6,460.031 rows=6,666 loops=1)

  • Filter: (issue_label_added_fc400a2c30.label_assignment_order = 1)
  • Rows Removed by Filter: 382
13. 4.906 6,459.251 ↑ 1.9 7,048 1

WindowAgg (cost=18,806.04..19,068.12 rows=13,104 width=20) (actual time=6,452.571..6,459.251 rows=7,048 loops=1)

14. 13.967 6,454.345 ↑ 1.9 7,049 1

Sort (cost=18,806.04..18,838.80 rows=13,104 width=12) (actual time=6,452.550..6,454.345 rows=7,049 loops=1)

  • Sort Key: resource_label_events_1.issue_id, resource_label_events_1.created_at
  • Sort Method: quicksort Memory: 916kB
15. 6,440.378 6,440.378 ↑ 1.2 11,340 1

Index Scan using index_resource_label_events_on_label_id_and_action on resource_label_events resource_label_events_1 (cost=0.56..17,909.88 rows=13,104 width=12) (actual time=0.069..6,440.378 rows=11,340 loops=1)

  • Index Cond: ((label_id = 2,526,326) AND (action = 1))
16. 90.928 91.168 ↓ 0.0 0 4,144

Index Scan using issues_pkey on issues (cost=0.56..10.64 rows=1 width=8) (actual time=0.022..0.022 rows=0 loops=4,144)

  • Index Cond: (id = issue_label_added_bb9dadb132.model_id)
  • Filter: ((created_at <= '2020-07-30 23:59:59.999999+00'::timestamp with time zone) AND (created_at >= '2020-07-01 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.192 0.192 ↓ 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.016..0.016 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..83.87 rows=84 width=4) (never executed)

  • Index Cond: (user_id = 4,156,052)
20. 0.048 0.048 ↑ 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.004..0.004 rows=1 loops=12)

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

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

Index Scan using projects_pkey on projects (cost=0.43..4.64 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=120)

  • 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.360 0.360 ↑ 1.0 1 120

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

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

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

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

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

SubPlan (for Nested Loop Left Join)

28. 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.57..3.59 rows=1 width=0) (never executed)

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

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

CTE Scan on base_and_descendants namespaces (cost=1,419.95..1,422.97 rows=151 width=4) (actual time=0.002..0.004 rows=1 loops=120)

31.          

CTE base_and_descendants

32. 0.167 0.387 ↑ 11.6 13 1

Recursive Union (cost=0.43..1,419.95 rows=151 width=347) (actual time=0.168..0.387 rows=13 loops=1)

33. 0.054 0.054 ↑ 1.0 1 1

Index Scan using namespaces_pkey on namespaces namespaces_1 (cost=0.43..3.45 rows=1 width=347) (actual time=0.052..0.054 rows=1 loops=1)

  • Index Cond: (id = 9,970)
  • Filter: ((type)::text = 'Group'::text)
34. 0.053 0.166 ↑ 1.2 12 1

Nested Loop (cost=0.56..141.35 rows=15 width=347) (actual time=0.126..0.166 rows=12 loops=1)

35. 0.020 0.020 ↑ 10.0 1 1

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

36. 0.093 0.093 ↓ 6.0 12 1

Index Scan using index_namespaces_on_parent_id_and_id on namespaces namespaces_2 (cost=0.56..14.09 rows=2 width=347) (actual time=0.056..0.093 rows=12 loops=1)

  • Index Cond: (parent_id = base_and_descendants.id)
  • Filter: ((type)::text = 'Group'::text)
Planning time : 7.547 ms
Execution time : 13,935.035 ms