explain.depesz.com

PostgreSQL's explain analyze made readable

Result: adP1 : Get invited group members to project

Settings
# exclusive inclusive rows x rows loops node
1. 7.107 6,348.151 ↑ 243.5 1,188 1

HashAggregate (cost=578,929.81..581,822.51 rows=289,270 width=4) (actual time=6,346.635..6,348.151 rows=1,188 loops=1)

  • Group Key: members.user_id
  • Buffers: shared hit=12677 read=5411 dirtied=215
  • I/O Timings: read=6196.391
2. 6.864 6,341.044 ↑ 118.9 2,432 1

Nested Loop (cost=123,940.52..578,206.64 rows=289,270 width=4) (actual time=2,189.781..6,341.044 rows=2,432 loops=1)

  • Buffers: shared hit=12677 read=5411 dirtied=215
  • I/O Timings: read=6196.391
3. 1.661 4,510.120 ↑ 113.6 2,580 1

Nested Loop (cost=123,940.09..124,860.53 rows=293,077 width=4) (actual time=2,185.234..4,510.120 rows=2,580 loops=1)

  • Buffers: shared hit=4465 read=3871 dirtied=195
  • I/O Timings: read=4423.700
4. 0.062 2,180.351 ↑ 16.7 12 1

HashAggregate (cost=123,939.53..123,941.53 rows=200 width=4) (actual time=2,180.330..2,180.351 rows=12 loops=1)

  • Group Key: namespaces.id
  • Buffers: shared hit=4147 read=1782 dirtied=52
  • I/O Timings: read=2139.717
5. 2,180.289 2,180.289 ↑ 456.8 12 1

CTE Scan on base_and_ancestors namespaces (cost=123,761.36..123,871.00 rows=5,482 width=4) (actual time=1,860.320..2,180.289 rows=12 loops=1)

  • Buffers: shared hit=4147 read=1782 dirtied=52
  • I/O Timings: read=2139.717
6.          

CTE base_and_ancestors

7. 0.241 2,180.229 ↑ 456.8 12 1

Recursive Union (cost=3,153.24..123,761.36 rows=5,482 width=323) (actual time=1,860.317..2,180.229 rows=12 loops=1)

  • Buffers: shared hit=4147 read=1782 dirtied=52
  • I/O Timings: read=2139.717
8. 0.118 2,179.886 ↑ 11.3 24 1

Nested Loop (cost=3,153.24..6,327.72 rows=272 width=323) (actual time=1,860.290..2,179.886 rows=24 loops=1)

  • Buffers: shared hit=4111 read=1782 dirtied=52
  • I/O Timings: read=2139.717
9. 0.983 2,165.680 ↑ 59.0 24 1

Nested Loop (cost=3,152.81..3,695.56 rows=1,417 width=4) (actual time=1,855.488..2,165.680 rows=24 loops=1)

  • Buffers: shared hit=4022 read=1775 dirtied=52
  • I/O Timings: read=2126.296
10. 2.415 1,852.825 ↑ 1.7 704 1

HashAggregate (cost=3,152.39..3,164.19 rows=1,180 width=8) (actual time=1,852.232..1,852.825 rows=704 loops=1)

  • Group Key: projects.id
  • Buffers: shared hit=2214 read=1444 dirtied=52
  • I/O Timings: read=1820.086
11. 2.803 1,850.410 ↑ 1.7 704 1

Nested Loop (cost=1.00..3,149.44 rows=1,180 width=8) (actual time=17.971..1,850.410 rows=704 loops=1)

  • Buffers: shared hit=2214 read=1444 dirtied=52
  • I/O Timings: read=1820.086
12. 1,028.151 1,028.151 ↑ 1.7 704 1

Index Scan using index_routes_on_path_text_pattern_ops on public.routes rs (cost=0.56..4.59 rows=1,204 width=4) (actual time=11.763..1,028.151 rows=704 loops=1)

  • Index Cond: (((rs.path)::text ~>=~ 'gitlab-org/'::text) AND ((rs.path)::text ~<~ 'gitlab-org0'::text))
  • Filter: (((rs.path)::text ~~ 'gitlab-org/%'::text) AND ((rs.source_type)::text = 'Project'::text))
  • Rows Removed by Filter: 85
  • Buffers: shared hit=69 read=724 dirtied=35
  • I/O Timings: read=1017.366
13. 819.456 819.456 ↑ 1.0 1 704

Index Only Scan using projects_pkey on public.projects (cost=0.43..2.60 rows=1 width=4) (actual time=1.133..1.164 rows=1 loops=704)

  • Index Cond: (projects.id = rs.source_id)
  • Heap Fetches: 140
  • Buffers: shared hit=2145 read=720 dirtied=17
  • I/O Timings: read=802.720
14. 311.872 311.872 ↓ 0.0 0 704

Index Scan using index_project_group_links_on_project_id on public.project_group_links (cost=0.42..0.44 rows=1 width=8) (actual time=0.436..0.443 rows=0 loops=704)

  • Index Cond: (project_group_links.project_id = projects.id)
  • Buffers: shared hit=1808 read=331
  • I/O Timings: read=306.210
15. 14.088 14.088 ↑ 1.0 1 24

Index Scan using namespaces_pkey on public.namespaces namespaces_1 (cost=0.43..1.85 rows=1 width=323) (actual time=0.587..0.587 rows=1 loops=24)

  • Index Cond: (namespaces_1.id = project_group_links.group_id)
  • Filter: ((namespaces_1.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=89 read=7
  • I/O Timings: read=13.421
16. 0.012 0.102 ↑ 130.2 4 2

Nested Loop (cost=0.43..11,732.40 rows=521 width=323) (actual time=0.029..0.051 rows=4 loops=2)

  • Buffers: shared hit=36
17. 0.006 0.006 ↑ 453.3 6 2

WorkTable Scan on base_and_ancestors (cost=0.00..54.40 rows=2,720 width=4) (actual time=0.003..0.003 rows=6 loops=2)

18. 0.084 0.084 ↑ 1.0 1 12

Index Scan using namespaces_pkey on public.namespaces namespaces_2 (cost=0.43..4.28 rows=1 width=323) (actual time=0.007..0.007 rows=1 loops=12)

  • Index Cond: (namespaces_2.id = base_and_ancestors.parent_id)
  • Filter: ((namespaces_2.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=36
19. 2,328.108 2,328.108 ↓ 215.0 215 12

Index Scan using index_members_on_source_id_and_source_type on public.members (cost=0.56..4.58 rows=1 width=8) (actual time=3.219..194.009 rows=215 loops=12)

  • Index Cond: ((members.source_id = namespaces.id) AND ((members.source_type)::text = 'Namespace'::text))
  • Filter: ((members.requested_at IS NULL) AND (members.access_level > 10) AND ((members.type)::text = 'GroupMember'::text))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=318 read=2089 dirtied=143
  • I/O Timings: read=2283.983
20. 1,824.060 1,824.060 ↑ 1.0 1 2,580

Index Scan using users_pkey on public.users (cost=0.43..1.54 rows=1 width=4) (actual time=0.701..0.707 rows=1 loops=2,580)

  • Index Cond: (users.id = members.user_id)
  • Filter: ((users.state)::text = 'active'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=8212 read=1540 dirtied=20
  • I/O Timings: read=1772.691