explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7BsZ : https://gitlab.com/gitlab-org/gitlab/merge_requests/22284

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 234.754 ↓ 2.0 2 1

Aggregate (cost=929.51..929.53 rows=1 width=10) (actual time=234.749..234.754 rows=2 loops=1)

  • Group Key: issues.state_id
  • Buffers: shared hit=381 read=123 dirtied=1
  • I/O Timings: read=231.642
2. 0.122 234.745 ↓ 24.0 24 1

Sort (cost=929.51..929.51 rows=1 width=6) (actual time=234.742..234.745 rows=24 loops=1)

  • Sort Key: issues.state_id
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=381 read=123 dirtied=1
  • I/O Timings: read=231.642
3. 0.080 234.623 ↓ 24.0 24 1

Nested Loop Left Join (cost=838.67..929.50 rows=1 width=6) (actual time=102.257..234.623 rows=24 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=378 read=123 dirtied=1
  • I/O Timings: read=231.642
4. 0.099 217.551 ↓ 24.0 24 1

Nested Loop (cost=838.24..923.37 rows=1 width=10) (actual time=90.363..217.551 rows=24 loops=1)

  • Buffers: shared hit=287 read=118 dirtied=1
  • I/O Timings: read=214.887
5. 0.096 181.836 ↓ 24.0 24 1

Nested Loop (cost=837.80..915.19 rows=1 width=10) (actual time=65.100..181.836 rows=24 loops=1)

  • Buffers: shared hit=94 read=107 dirtied=1
  • I/O Timings: read=179.919
6. 0.098 61.088 ↓ 5.2 31 1

HashAggregate (cost=837.37..837.43 rows=6 width=4) (actual time=61.054..61.088 rows=31 loops=1)

  • Group Key: epic_issues.issue_id
  • Buffers: shared hit=32 read=45
  • I/O Timings: read=60.071
7. 0.041 60.990 ↓ 5.2 31 1

Nested Loop (cost=829.51..837.35 rows=6 width=4) (actual time=25.249..60.990 rows=31 loops=1)

  • Buffers: shared hit=32 read=45
  • I/O Timings: read=60.071
8. 0.019 21.765 ↓ 4.0 4 1

HashAggregate (cost=829.22..829.23 rows=1 width=4) (actual time=21.761..21.765 rows=4 loops=1)

  • Group Key: epics.id
  • Buffers: shared hit=24 read=18
  • I/O Timings: read=21.164
9. 21.746 21.746 ↓ 4.0 4 1

CTE Scan on base_and_descendants epics (cost=823.11..829.21 rows=1 width=4) (actual time=10.434..21.746 rows=4 loops=1)

  • Filter: (epics.group_id = 9,970)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=24 read=18
  • I/O Timings: read=21.164
10.          

CTE base_and_descendants

11. 9.279 21.704 ↑ 67.8 4 1

Recursive Union (cost=0.29..823.11 rows=271 width=778) (actual time=10.421..21.704 rows=4 loops=1)

  • Buffers: shared hit=24 read=18
  • I/O Timings: read=21.164
12. 4.905 4.905 ↑ 1.0 1 1

Index Scan using epics_pkey on public.epics epics_1 (cost=0.29..4.30 rows=1 width=778) (actual time=4.904..4.905 rows=1 loops=1)

  • Index Cond: (epics_1.id = 7,651)
  • Buffers: shared read=3
  • I/O Timings: read=4.860
13. 0.014 7.520 ↑ 13.5 2 2

Nested Loop (cost=0.29..81.34 rows=27 width=778) (actual time=2.554..3.760 rows=2 loops=2)

  • Buffers: shared hit=4 read=7
  • I/O Timings: read=7.411
14. 0.006 0.006 ↑ 5.0 2 2

WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.002..0.003 rows=2 loops=2)

15. 7.500 7.500 ↑ 3.0 1 4

Index Scan using index_epics_on_parent_id on public.epics epics_2 (cost=0.29..8.08 rows=3 width=778) (actual time=1.274..1.875 rows=1 loops=4)

  • Index Cond: (epics_2.parent_id = base_and_descendants.id)
  • Buffers: shared hit=4 read=7
  • I/O Timings: read=7.411
16. 39.184 39.184 ↓ 1.3 8 4

Index Scan using index_epic_issues_on_epic_id on public.epic_issues (cost=0.29..8.06 rows=6 width=8) (actual time=1.706..9.796 rows=8 loops=4)

  • Index Cond: (epic_issues.epic_id = epics.id)
  • Buffers: shared hit=8 read=27
  • I/O Timings: read=38.907
17. 120.652 120.652 ↑ 1.0 1 31

Index Scan using issues_pkey on public.issues (cost=0.44..12.95 rows=1 width=14) (actual time=3.891..3.892 rows=1 loops=31)

  • Index Cond: (issues.id = epic_issues.issue_id)
  • Filter: ((issues.weight IS NOT NULL) AND (issues.weight > 0) AND ((issues.confidential IS NOT TRUE) OR (issues.confidential AND ((issues.author_id = 64,248) OR (alternatives: SubPlan 1 or hashed SubPlan 2) OR (alternatives: SubPlan 3 or hashed SubPlan 4)))))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=62 read=62 dirtied=1
  • I/O Timings: read=119.848
18.          

SubPlan (for Index Scan)

19. 0.000 0.000 ↓ 0.0 0 0

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

  • Index Cond: ((issue_assignees.issue_id = issues.id) AND (issue_assignees.user_id = 64,248))
  • Heap Fetches: 0
20. 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..99.01 rows=71 width=4) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: (issue_assignees_1.user_id = 64,248)
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 (cost=0.56..4.59 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: ((project_authorizations.user_id = 64,248) AND (project_authorizations.project_id = issues.project_id) AND (project_authorizations.access_level >= 20))
  • Heap Fetches: 0
22. 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.56..46.21 rows=144 width=4) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: ((project_authorizations_1.user_id = 64,248) AND (project_authorizations_1.access_level >= 20))
  • Heap Fetches: 0
23. 13.200 35.616 ↑ 1.0 1 24

Index Scan using projects_pkey on public.projects (cost=0.43..8.17 rows=1 width=4) (actual time=1.484..1.484 rows=1 loops=24)

  • 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=193 read=11
  • I/O Timings: read=34.968
24.          

SubPlan (for Index Scan)

25. 22.416 22.416 ↑ 1.0 1 24

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on public.project_authorizations project_authorizations_2 (cost=0.56..4.59 rows=1 width=0) (actual time=0.934..0.934 rows=1 loops=24)

  • Index Cond: ((project_authorizations_2.user_id = 64,248) AND (project_authorizations_2.project_id = projects.id) AND (project_authorizations_2.access_level >= 10))
  • Heap Fetches: 11
  • Buffers: shared hit=103 read=5
  • I/O Timings: read=22.148
26. 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.56..54.10 rows=174 width=4) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: ((project_authorizations_3.user_id = 64,248) AND (project_authorizations_3.access_level >= 10))
  • Heap Fetches: 0
27. 16.992 16.992 ↑ 1.0 1 24

Index Scan using index_project_features_on_project_id on public.project_features (cost=0.43..1.53 rows=1 width=8) (actual time=0.707..0.708 rows=1 loops=24)

  • Index Cond: (projects.id = project_features.project_id)
  • Buffers: shared hit=91 read=5
  • I/O Timings: read=16.755
28.          

SubPlan (for Nested Loop Left Join)

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_4 (cost=0.56..4.59 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: ((project_authorizations_4.user_id = 64,248) AND (project_authorizations_4.project_id = projects.id) AND (project_authorizations_4.access_level >= 10))
  • Heap Fetches: 0
30. 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.56..54.10 rows=174 width=4) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: ((project_authorizations_5.user_id = 64,248) AND (project_authorizations_5.access_level >= 10))
  • Heap Fetches: 0