explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TYUg

Settings
# exclusive inclusive rows x rows loops node
1. 0.565 10,544.733 ↑ 1.0 1 1

Aggregate (cost=180,683.53..180,683.54 rows=1 width=8) (actual time=10,544.733..10,544.733 rows=1 loops=1)

  • Buffers: shared hit=36,894 read=10,134 dirtied=772
  • I/O Timings: read=10,193.249
2. 0.532 10,544.168 ↓ 2.2 111 1

Nested Loop (cost=1,114.47..180,683.27 rows=51 width=16) (actual time=2,213.515..10,544.168 rows=111 loops=1)

  • Buffers: shared hit=36,883 read=10,134 dirtied=772
  • I/O Timings: read=10,193.249
3. 10.313 10,538.556 ↓ 1.6 254 1

Nested Loop (cost=1,110.87..180,125.02 rows=154 width=12) (actual time=527.968..10,538.556 rows=254 loops=1)

  • Buffers: shared hit=35,745 read=10,134 dirtied=772
  • I/O Timings: read=10,193.249
4. 7.887 10,227.112 ↓ 34.3 5,283 1

Nested Loop (cost=1,107.28..179,567.15 rows=154 width=4) (actual time=20.952..10,227.112 rows=5,283 loops=1)

  • Buffers: shared hit=17,549 read=6,908 dirtied=772
  • I/O Timings: read=10,000.336
5. 4.628 283.753 ↑ 1.6 922 1

Nested Loop Left Join (cost=1,106.71..21,436.60 rows=1,470 width=4) (actual time=4.810..283.753 rows=922 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: 97
  • Buffers: shared hit=12,781 read=84
  • I/O Timings: read=178.558
6. 0.971 54.597 ↑ 1.5 1,019 1

Nested Loop (cost=1,106.28..15,149.12 rows=1,534 width=4) (actual time=4.752..54.597 rows=1,019 loops=1)

  • Buffers: shared hit=7,069
7. 0.358 4.819 ↓ 1.0 153 1

HashAggregate (cost=1,105.84..1,107.35 rows=151 width=4) (actual time=4.547..4.819 rows=153 loops=1)

  • Group Key: namespaces.id
  • Buffers: shared hit=761
8. 4.461 4.461 ↓ 1.0 153 1

CTE Scan on base_and_descendants namespaces (cost=1,100.93..1,103.95 rows=151 width=4) (actual time=0.058..4.461 rows=153 loops=1)

  • Buffers: shared hit=761
9.          

CTE base_and_descendants

10. 0.505 4.177 ↓ 1.0 153 1

Recursive Union (cost=0.43..1,100.93 rows=151 width=357) (actual time=0.053..4.177 rows=153 loops=1)

  • Buffers: shared hit=761
11. 0.042 0.042 ↑ 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.041..0.042 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
12. 0.234 3.630 ↓ 2.0 30 5

Nested Loop (cost=0.56..109.45 rows=15 width=357) (actual time=0.085..0.726 rows=30 loops=5)

  • Buffers: shared hit=757
13. 0.030 0.030 ↓ 3.1 31 5

WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.000..0.006 rows=31 loops=5)

14. 3.366 3.366 ↑ 2.0 1 153

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.022 rows=1 loops=153)

  • 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=757
15. 37.598 48.807 ↑ 1.4 7 153

Index Scan using index_projects_on_namespace_id_and_id on public.projects (cost=0.43..92.89 rows=10 width=8) (actual time=0.059..0.319 rows=7 loops=153)

  • Index Cond: (projects.namespace_id = namespaces.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=6,308
16.          

SubPlan (for Index Scan)

17. 11.209 11.209 ↑ 1.0 1 1,019

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.011..0.011 rows=1 loops=1,019)

  • 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: 361
  • Buffers: shared hit=4,826
18. 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
19. 211.952 211.952 ↑ 1.0 1 1,019

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.208..0.208 rows=1 loops=1,019)

  • Index Cond: (projects.id = project_features.project_id)
  • Buffers: shared hit=3,994 read=83
  • I/O Timings: read=171.985
20.          

SubPlan (for Nested Loop Left Join)

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_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
22. 12.576 12.576 ↓ 2.2 3,311 1

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.044..12.576 rows=3,311 loops=1)

  • Index Cond: ((project_authorizations_5.user_id = 4,156,052) AND (project_authorizations_5.access_level >= 10))
  • Heap Fetches: 862
  • Buffers: shared hit=1,718 read=1
  • I/O Timings: read=6.573
23. 9,632.961 9,935.472 ↑ 2.5 6 922

Index Scan using idx_issues_on_project_id_and_created_at_and_id_and_state_id on public.issues (cost=0.56..107.42 rows=15 width=8) (actual time=2.517..10.776 rows=6 loops=922)

  • Index Cond: ((issues.project_id = projects.id) AND (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))
  • Filter: ((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: 0
  • Buffers: shared hit=4,768 read=6,824 dirtied=772
  • I/O Timings: read=9,821.778
24.          

SubPlan (for Index Scan)

25. 298.185 298.185 ↓ 0.0 0 309

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.965..0.965 rows=0 loops=309)

  • Index Cond: ((issue_assignees.issue_id = issues.id) AND (issue_assignees.user_id = 4,156,052))
  • Heap Fetches: 0
  • Buffers: shared hit=670 read=257
  • I/O Timings: read=292.505
26. 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)
27. 4.326 4.326 ↑ 1.0 1 309

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.014..0.014 rows=1 loops=309)

  • 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: 71
  • Buffers: shared hit=1,325
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_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
29. 10.566 301.131 ↓ 0.0 0 5,283

Limit (cost=3.60..3.60 rows=1 width=8) (actual time=0.057..0.057 rows=0 loops=5,283)

  • Buffers: shared hit=18,196 read=3,226
  • I/O Timings: read=192.913
30. 26.415 290.565 ↓ 0.0 0 5,283

Sort (cost=3.60..3.60 rows=1 width=8) (actual time=0.055..0.055 rows=0 loops=5,283)

  • Sort Key: resource_label_events.created_at DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=18,196 read=3,226
  • I/O Timings: read=192.913
31. 264.150 264.150 ↓ 0.0 0 5,283

Index Scan using resource_label_events_issue_id_label_id_action on public.resource_label_events (cost=0.56..3.59 rows=1 width=8) (actual time=0.050..0.050 rows=0 loops=5,283)

  • Index Cond: ((resource_label_events.issue_id = issues.id) AND (resource_label_events.label_id = 2,526,320) AND (resource_label_events.action = 1))
  • Buffers: shared hit=18,193 read=3,226
  • I/O Timings: read=192.913
32. 0.508 5.080 ↓ 0.0 0 254

Limit (cost=3.60..3.60 rows=1 width=8) (actual time=0.020..0.020 rows=0 loops=254)

  • Buffers: shared hit=1,138
33. 1.016 4.572 ↓ 0.0 0 254

Sort (cost=3.60..3.60 rows=1 width=8) (actual time=0.018..0.018 rows=0 loops=254)

  • Sort Key: resource_label_events_1.created_at
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=1,138
34. 3.556 3.556 ↓ 0.0 0 254

Index Scan using resource_label_events_issue_id_label_id_action on public.resource_label_events resource_label_events_1 (cost=0.56..3.59 rows=1 width=8) (actual time=0.013..0.014 rows=0 loops=254)

  • Index Cond: ((resource_label_events_1.issue_id = issues.id) AND (resource_label_events_1.label_id = 2,526,326) AND (resource_label_events_1.action = 1))
  • Buffers: shared hit=1,138