explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xGqs

Settings
# exclusive inclusive rows x rows loops node
1. 0.108 96.084 ↑ 1.0 1 1

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

  • Buffers: shared hit=46,205
2. 0.085 95.976 ↓ 112.0 112 1

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

  • Buffers: shared hit=46,205
3. 0.150 95.667 ↓ 112.0 112 1

Nested Loop Left Join (cost=38,158.37..39,039.52 rows=1 width=20) (actual time=92.693..95.667 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,185
4. 0.114 95.293 ↓ 112.0 112 1

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

  • Buffers: shared hit=45,737
5. 2.841 94.507 ↓ 112.0 112 1

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

  • Buffers: shared hit=44,807
6. 3.512 71.006 ↓ 4,132.0 4,132 1

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

  • Merge Cond: (issue_label_added_bb9dadb132.model_id = issue_label_added_fc400a2c30.model_id)
  • Buffers: shared hit=24,040
7. 1.635 35.185 ↓ 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=24.351..35.185 rows=7,122 loops=1)

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

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

  • Buffers: shared hit=12,656
9. 7.785 25.704 ↑ 1.9 7,827 1

Sort (cost=21,184.31..21,221.14 rows=14,733 width=12) (actual time=24.336..25.704 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,656
10. 17.919 17.919 ↑ 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.027..17.919 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.501 32.309 ↓ 112.7 6,649 1

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

  • Buffers: shared hit=11,384
12. 1.521 30.808 ↓ 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=20.545..30.808 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. 7.699 29.287 ↑ 1.7 7,031 1

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

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

Sort (cost=16,972.62..17,002.15 rows=11,812 width=12) (actual time=20.533..21.588 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. 15.033 15.033 ↑ 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.026..15.033 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. 20.550 20.660 ↓ 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.005..0.005 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.077 0.077 ↓ 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.007..0.007 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.033 0.033 ↑ 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.003..0.003 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.336 0.672 ↑ 1.0 1 112

Index Scan using projects_pkey on public.projects (cost=0.43..4.65 rows=1 width=8) (actual time=0.006..0.006 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.336 0.336 ↑ 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.003..0.003 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.224 0.224 ↑ 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.002..0.002 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.118 0.190 ↑ 11.6 13 1

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

  • Buffers: shared hit=20
33. 0.016 0.016 ↑ 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.016..0.016 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.056 ↑ 1.2 12 1

Nested Loop (cost=0.56..109.45 rows=15 width=357) (actual time=0.024..0.056 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.042 0.042 ↓ 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.015..0.042 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