explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Dm3X

Settings
# exclusive inclusive rows x rows loops node
1. 0.848 16,269.190 ↑ 1.0 1 1

Aggregate (cost=47,469.70..47,469.72 rows=1 width=8) (actual time=16,269.189..16,269.190 rows=1 loops=1)

2. 0.064 16,268.342 ↓ 127.0 127 1

Nested Loop Semi Join (cost=46,427.87..47,469.70 rows=1 width=16) (actual time=16,259.917..16,268.342 rows=127 loops=1)

  • Join Filter: (projects.namespace_id = namespaces.id)
  • Rows Removed by Join Filter: 12
3. 0.428 16,267.262 ↓ 127.0 127 1

Nested Loop Left Join (cost=45,008.00..46,043.42 rows=1 width=20) (actual time=16,259.481..16,267.262 rows=127 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)))l time=16,259.261..16266.470 rows=127 loops=1)7..46039.32 rows=1 width=24) (actua--More--
4. 3.146 16,264.802 ↓ 127.0 127 1

Nested Loop (cost=45,007.13..46,034.68 rows=1 width=20) (actual time=16,258.813..16,264.802 rows=127 loops=1)

5. 2.772 16,140.958 ↓ 4,162.0 4,162 1

Merge Join (cost=45,006.57..46,024.04 rows=1 width=24) (actual time=16,119.325..16,140.958 rows=4,162 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
6. 0.906 8,617.344 ↓ 84.5 7,184 1

Subquery Scan on issue_label_added_bb9dadb132 (cost=24,537.78..25,092.07 rows=85 width=12) (actual time=8,607.885..8,617.344 rows=7,184 loops=1)

  • Filter: (issue_label_added_bb9dadb132.label_assignment_order = 1)
  • Rows Removed by Filter: 711
7. 5.582 8,616.438 ↑ 2.2 7,895 1

WindowAgg (cost=24,537.78..24,878.88 rows=17,055 width=20) (actual time=8,607.884..8,616.438 rows=7,895 loops=1)

8. 20.359 8,610.856 ↑ 2.2 7,896 1

Sort (cost=24,537.78..24,580.42 rows=17,055 width=12) (actual time=8,607.870..8,610.856 rows=7,896 loops=1)

  • Sort Key: resource_label_events.issue_id, resource_label_events.created_aty: 979kB Sort Method: quicksort Memor--More--
9. 8,590.497 8,590.497 ↑ 1.3 12,672 1

Index Scan using index_resource_label_events_on_label_id_and_action on resource_label_events (cost=0.56..23,338.99 rows=17,055 width=12) (actual time=2.748..8,590.497 rows=12,672 loops=1)

  • Index Cond: ((label_id = 2,526,320) AND (action = 1))
10. 1.264 7,520.842 ↓ 94.3 6,695 1

Materialize (cost=20,468.79..20,931.57 rows=71 width=12) (actual time=7,511.180..7,520.842 rows=6,695 loops=1)

11. 0.848 7,519.578 ↓ 94.3 6,695 1

Subquery Scan on issue_label_added_fc400a2c30 (cost=20,468.79..20,931.39 rows=71 width=12) (actual time=7,511.142..7,519.578 rows=6,695 loops=1)

  • Filter: (issue_label_added_fc400a2c30.label_assignment_order = 1)
  • Rows Removed by Filter: 384
12. 5.802 7,518.730 ↑ 2.0 7,079 1

WindowAgg (cost=20,468.79..20,753.47 rows=14,234 width=20) (actual time=7,511.131..7,518.730 rows=7,079 loops=1)

13. 16.990 7,512.928 ↑ 2.0 7,080 1

Sort (cost=20,468.79..20,504.37 rows=14,234 width=12) (actual time=7,511.025..7,512.928 rows=7,080 loops=1)

  • Sort Key: resource_label_events_1.issue_id, resource_label_events_1.created_at
14. 7,495.938 7,495.938 ↑ 1.2 11,407 1

Sort Method: quicksort Memory: 919kBdex_resource_label_events_on_label_id_and_action on resource_label_events resource_label_events_1 (cost=0.56..19,486.85 rows=14,234 width=12) (actual time=0.088..7,495.938 rows=11,407 loops=1)

  • Index Cond: ((label_id = 2,526,326) AND (action = 1))
15. 120.218 120.698 ↓ 0.0 0 4,162

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

  • 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
16.          

SubPlan (for Index Scan)

17. 0.360 0.360 ↓ 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.030..0.030 rows=0 loops=12)

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

  • Index Cond: (user_id = 4,156,052)
19. 0.120 0.120 ↑ 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.010..0.010 rows=1 loops=12)

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

  • Index Cond: ((user_id = 4,156,052) AND (access_level >= 20))
  • Heap Fetches: 0
21. 0.889 1.397 ↑ 1.0 1 127

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

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

SubPlan (for Index Scan)

23. 0.508 0.508 ↑ 1.0 1 127

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.004..0.004 rows=1 loops=127)

  • Index Cond: ((user_id = 4,156,052) AND (project_id = projects.id) AND (access_level >= 10))
  • Heap Fetches: 6
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_3 (cost=0.57..364.85 rows=1,537 width=4) (never executed)

  • Index Cond: ((user_id = 4,156,052) AND (access_level >= 10))
  • Heap Fetches: 0
25. 0.635 0.635 ↑ 1.0 1 127

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

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

SubPlan (for Nested Loop Left Join)

27. 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
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_5 (cost=0.57..364.85 rows=1,537 width=4) (never executed)

  • Index Cond: ((user_id = 4,156,052) AND (access_level >= 10))
  • Heap Fetches: 0
29. 1.016 1.016 ↑ 151.0 1 127

CTE Scan on base_and_descendants namespaces (cost=1,419.86..1,422.88 rows=151 width=4) (actual time=0.003..0.008 rows=1 loops=127)

30.          

CTE base_and_descendants

31. 0.728 0.897 ↑ 11.6 13 1

Recursive Union (cost=0.43..1,419.86 rows=151 width=347) (actual time=0.359..0.897 rows=13 loops=1)

  • -> Nested Loop (cost=0.56..141.34 rows=15 width=347) (actual time=0.303..0.458 rows=12 loops=1).00..0.20 rows=10 width=4) (actual time=0.067..0.067 rows=1 loops=1)nts (cost=0--More--
32. 0.000 0.169 ↑ 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.167..0.169 rows=1 loops=1)

  • Index Cond: (id = 9,970)
  • Filter: ((type)::text = 'Group'::text)
33. 0.302 0.302 ↓ 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.152..0.302 rows=12 loops=1)

  • Index Cond: (parent_id = base_and_descendants.id)
  • Filter: ((type)::text = 'Group'::text)
Planning time : 21.669 ms
Execution time : 16,270.556 ms