explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oJEc

Settings
# exclusive inclusive rows x rows loops node
1. 0.255 345.126 ↑ 1.0 1 1

Aggregate (cost=40,146.88..40,146.89 rows=1 width=8) (actual time=345.126..345.126 rows=1 loops=1)

  • Buffers: shared hit=46,219
2. 0.118 344.871 ↓ 112.0 112 1

Nested Loop Semi Join (cost=39,259.30..40,146.87 rows=1 width=16) (actual time=338.382..344.871 rows=112 loops=1)

  • Buffers: shared hit=46,208
3. 0.189 344.529 ↓ 112.0 112 1

Nested Loop Left Join (cost=38,158.37..39,039.52 rows=1 width=20) (actual time=338.342..344.529 rows=112 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)))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=46,188
4. 0.110 343.780 ↓ 112.0 112 1

Nested Loop (cost=38,157.93..39,035.42 rows=1 width=24) (actual time=338.273..343.780 rows=112 loops=1)

  • Buffers: shared hit=45,740
5. 3.152 342.326 ↓ 112.0 112 1

Nested Loop (cost=38,157.50..39,030.78 rows=1 width=20) (actual time=338.130..342.326 rows=112 loops=1)

  • Buffers: shared hit=44,810
6. 4.575 248.270 ↓ 4,132.0 4,132 1

Merge Join (cost=38,156.93..39,020.14 rows=1 width=24) (actual time=215.807..248.270 rows=4,132 loops=1)

  • Merge Cond: (issue_label_added_bb9dadb132.model_id = issue_label_added_fc400a2c30.model_id)
  • Buffers: shared hit=24,043
7. 1.744 111.796 ↓ 96.2 7,122 1

Subquery Scan on issue_label_added_bb9dadb132 (cost=21,184.31..21,663.13 rows=74 width=12) (actual time=99.323..111.796 rows=7,122 loops=1)

  • Filter: (issue_label_added_bb9dadb132.label_assignment_order = 1)
  • Rows Removed by Filter: 704
  • Buffers: shared hit=12,659
8. 9.004 110.052 ↑ 1.9 7,826 1

WindowAgg (cost=21,184.31..21,478.97 rows=14,733 width=20) (actual time=99.321..110.052 rows=7,826 loops=1)

  • Buffers: shared hit=12,659
9. 8.526 101.048 ↑ 1.9 7,827 1

Sort (cost=21,184.31..21,221.14 rows=14,733 width=12) (actual time=99.276..101.048 rows=7,827 loops=1)

  • Sort Key: resource_label_events.issue_id, resource_label_events.created_at
  • Sort Method: quicksort Memory: 973kB
  • Buffers: shared hit=12,659
10. 92.522 92.522 ↑ 1.2 12,549 1

Index Scan using index_resource_label_events_on_label_id_and_action on public.resource_label_events (cost=0.56..20,164.29 rows=14,733 width=12) (actual time=0.103..92.522 rows=12,549 loops=1)

  • Index Cond: ((resource_label_events.label_id = 2,526,320) AND (resource_label_events.action = 1))
  • Buffers: shared hit=12,656
11. 1.861 131.899 ↓ 112.7 6,649 1

Materialize (cost=16,972.62..17,356.66 rows=59 width=12) (actual time=116.463..131.899 rows=6,649 loops=1)

  • Buffers: shared hit=11,384
12. 1.778 130.038 ↓ 112.7 6,649 1

Subquery Scan on issue_label_added_fc400a2c30 (cost=16,972.62..17,356.51 rows=59 width=12) (actual time=116.460..130.038 rows=6,649 loops=1)

  • Filter: (issue_label_added_fc400a2c30.label_assignment_order = 1)
  • Rows Removed by Filter: 382
  • Buffers: shared hit=11,384
13. 9.840 128.260 ↑ 1.7 7,031 1

WindowAgg (cost=16,972.62..17,208.86 rows=11,812 width=20) (actual time=116.458..128.260 rows=7,031 loops=1)

  • Buffers: shared hit=11,384
14. 9.456 118.420 ↑ 1.7 7,032 1

Sort (cost=16,972.62..17,002.15 rows=11,812 width=12) (actual time=116.437..118.420 rows=7,032 loops=1)

  • Sort Key: resource_label_events_1.issue_id, resource_label_events_1.created_at
  • Sort Method: quicksort Memory: 914kB
  • Buffers: shared hit=11,384
15. 108.964 108.964 ↑ 1.0 11,297 1

Index Scan using index_resource_label_events_on_label_id_and_action on public.resource_label_events resource_label_events_1 (cost=0.56..16,173.66 rows=11,812 width=12) (actual time=0.054..108.964 rows=11,297 loops=1)

  • Index Cond: ((resource_label_events_1.label_id = 2,526,326) AND (resource_label_events_1.action = 1))
  • Buffers: shared hit=11,384
16. 90.585 90.904 ↓ 0.0 0 4,132

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

  • Index Cond: (issues.id = issue_label_added_bb9dadb132.model_id)
  • Filter: ((issues.created_at <= '2020-07-30 23:59:59.999999+00'::timestamp with time zone) AND (issues.created_at >= '2020-07-01 00:00:00+00'::timestamp with time zone) AND ((issues.confidential IS NOT TRUE) OR (issues.confidential AND ((issues.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
  • Buffers: shared hit=20,767
17.          

SubPlan (for Index Scan)

18. 0.264 0.264 ↓ 0.0 0 11

Index Only Scan using index_issue_assignees_on_issue_id_and_user_id on public.issue_assignees (cost=0.43..3.45 rows=1 width=0) (actual time=0.024..0.024 rows=0 loops=11)

  • Index Cond: ((issue_assignees.issue_id = issues.id) AND (issue_assignees.user_id = 4,156,052))
  • Heap Fetches: 0
  • Buffers: shared hit=33
19. 0.000 0.000 ↓ 0.0 0 0

Index Scan using index_issue_assignees_on_user_id on public.issue_assignees issue_assignees_1 (cost=0.43..83.87 rows=84 width=4) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: (issue_assignees_1.user_id = 4,156,052)
20. 0.055 0.055 ↑ 1.0 1 11

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

  • Index Cond: ((project_authorizations.user_id = 4,156,052) AND (project_authorizations.project_id = issues.project_id) AND (project_authorizations.access_level >= 20))
  • Heap Fetches: 4
  • Buffers: shared hit=52
21. 0.000 0.000 ↓ 0.0 0 0

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on public.project_authorizations project_authorizations_1 (cost=0.57..111.99 rows=1,248 width=4) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: ((project_authorizations_1.user_id = 4,156,052) AND (project_authorizations_1.access_level >= 20))
  • Heap Fetches: 0
22. 0.896 1.344 ↑ 1.0 1 112

Index Scan using projects_pkey on public.projects (cost=0.43..4.65 rows=1 width=8) (actual time=0.012..0.012 rows=1 loops=112)

  • Index Cond: (projects.id = issues.project_id)
  • Filter: ((alternatives: SubPlan 5 or hashed SubPlan 6) OR (projects.visibility_level = ANY ('{10,20}'::integer[])))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=930
23.          

SubPlan (for Index Scan)

24. 0.448 0.448 ↑ 1.0 1 112

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

  • Index Cond: ((project_authorizations_2.user_id = 4,156,052) AND (project_authorizations_2.project_id = projects.id) AND (project_authorizations_2.access_level >= 10))
  • Heap Fetches: 7
  • Buffers: shared hit=482
25. 0.000 0.000 ↓ 0.0 0 0

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on public.project_authorizations project_authorizations_3 (cost=0.57..127.57 rows=1,500 width=4) (actual time=0.000..0.000 rows=0 loops=0)

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

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

  • Index Cond: (projects.id = project_features.project_id)
  • Buffers: shared hit=448
27.          

SubPlan (for Nested Loop Left Join)

28. 0.000 0.000 ↓ 0.0 0 0

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

  • Index Cond: ((project_authorizations_4.user_id = 4,156,052) AND (project_authorizations_4.project_id = projects.id) AND (project_authorizations_4.access_level >= 10))
  • Heap Fetches: 0
29. 0.000 0.000 ↓ 0.0 0 0

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on public.project_authorizations project_authorizations_5 (cost=0.57..127.57 rows=1,500 width=4) (actual time=0.000..0.000 rows=0 loops=0)

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

CTE Scan on base_and_descendants namespaces (cost=1,100.93..1,103.95 rows=151 width=4) (actual time=0.001..0.002 rows=1 loops=112)

  • Buffers: shared hit=20
31.          

CTE base_and_descendants

32. 0.048 0.185 ↑ 11.6 13 1

Recursive Union (cost=0.43..1,100.93 rows=151 width=357) (actual time=0.029..0.185 rows=13 loops=1)

  • Buffers: shared hit=20
33. 0.019 0.019 ↑ 1.0 1 1

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

  • Index Cond: (namespaces_1.id = 9,970)
  • Filter: ((namespaces_1.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=4
34. 0.010 0.118 ↑ 1.2 12 1

Nested Loop (cost=0.56..109.45 rows=15 width=357) (actual time=0.064..0.118 rows=12 loops=1)

  • Buffers: shared hit=16
35. 0.004 0.004 ↑ 10.0 1 1

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

36. 0.104 0.104 ↓ 6.0 12 1

Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_2 (cost=0.56..10.90 rows=2 width=357) (actual time=0.056..0.104 rows=12 loops=1)

  • Index Cond: (namespaces_2.parent_id = base_and_descendants.id)
  • Filter: ((namespaces_2.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=16