explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MWG5 : Get project members

Settings
# exclusive inclusive rows x rows loops node
1. 0.091 4,231.902 ↓ 1.9 309 1

Unique (cost=2,398.66..2,399.47 rows=163 width=4) (actual time=4,231.735..4,231.902 rows=309 loops=1)

  • Buffers: shared hit=5290 read=3270 dirtied=72 written=825
  • I/O Timings: read=3612.559 write=556.011
2. 1.502 4,231.811 ↓ 5.0 819 1

Sort (cost=2,398.66..2,399.06 rows=163 width=4) (actual time=4,231.734..4,231.811 rows=819 loops=1)

  • Sort Key: members.user_id
  • Sort Method: quicksort Memory: 63kB
  • Buffers: shared hit=5290 read=3270 dirtied=72 written=825
  • I/O Timings: read=3612.559 write=556.011
3. 1.933 4,230.309 ↓ 5.0 819 1

Nested Loop (cost=2,268.36..2,392.67 rows=163 width=4) (actual time=1,185.919..4,230.309 rows=819 loops=1)

  • Buffers: shared hit=5287 read=3270 dirtied=72 written=825
  • I/O Timings: read=3612.559 write=556.011
4. 1.533 3,481.356 ↓ 5.0 820 1

Nested Loop (cost=2,267.93..2,304.44 rows=165 width=4) (actual time=1,183.156..3,481.356 rows=820 loops=1)

  • Buffers: shared hit=2636 read=2634 dirtied=65 written=652
  • I/O Timings: read=2978.953 write=457.578
5. 2.951 1,167.887 ↓ 14.7 704 1

HashAggregate (cost=2,267.37..2,267.85 rows=48 width=4) (actual time=1,167.136..1,167.887 rows=704 loops=1)

  • Group Key: projects.id
  • Buffers: shared hit=601 read=1065 dirtied=29
  • I/O Timings: read=1146.364
6. 0.941 1,164.936 ↓ 14.7 704 1

Nested Loop (cost=1,639.60..2,267.25 rows=48 width=4) (actual time=202.649..1,164.936 rows=704 loops=1)

  • Buffers: shared hit=601 read=1065 dirtied=29
  • I/O Timings: read=1146.364
7. 0.334 326.699 ↓ 3.4 86 1

Nested Loop (cost=1,639.17..2,224.63 rows=25 width=8) (actual time=193.838..326.699 rows=86 loops=1)

  • Buffers: shared hit=425 read=267 dirtied=2
  • I/O Timings: read=319.690
8. 0.261 190.657 ↑ 1.5 86 1

HashAggregate (cost=1,638.73..1,640.04 rows=131 width=4) (actual time=190.562..190.657 rows=86 loops=1)

  • Group Key: namespaces_1.id
  • Buffers: shared hit=188 read=160 dirtied=2
  • I/O Timings: read=186.293
9. 190.396 190.396 ↑ 1.5 86 1

CTE Scan on base_and_descendants namespaces_1 (cost=1,634.48..1,637.10 rows=131 width=4) (actual time=1.248..190.396 rows=86 loops=1)

  • Buffers: shared hit=188 read=160 dirtied=2
  • I/O Timings: read=186.293
10.          

CTE base_and_descendants

11. 0.865 190.061 ↑ 1.5 86 1

Recursive Union (cost=0.43..1,634.48 rows=131 width=323) (actual time=1.244..190.061 rows=86 loops=1)

  • Buffers: shared hit=188 read=160 dirtied=2
  • I/O Timings: read=186.293
12. 1.186 1.186 ↑ 1.0 1 1

Index Scan using namespaces_pkey on public.namespaces namespaces_2 (cost=0.43..4.45 rows=1 width=323) (actual time=1.185..1.186 rows=1 loops=1)

  • Index Cond: (namespaces_2.id = 9970)
  • Filter: ((namespaces_2.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=3 read=1
  • I/O Timings: read=1.118
13. 0.288 188.010 ↓ 1.3 17 5

Nested Loop (cost=0.43..162.74 rows=13 width=323) (actual time=3.225..37.602 rows=17 loops=5)

  • Buffers: shared hit=185 read=159 dirtied=2
  • I/O Timings: read=185.175
14. 0.070 0.070 ↓ 1.7 17 5

WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.001..0.014 rows=17 loops=5)

15. 187.652 187.652 ↑ 1.0 1 86

Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_3 (cost=0.43..16.24 rows=1 width=323) (actual time=1.206..2.182 rows=1 loops=86)

  • Index Cond: (namespaces_3.parent_id = base_and_descendants.id)
  • Filter: ((namespaces_3.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=185 read=159 dirtied=2
  • I/O Timings: read=185.175
16. 135.708 135.708 ↑ 1.0 1 86

Index Scan using namespaces_pkey on public.namespaces (cost=0.43..4.45 rows=1 width=4) (actual time=1.577..1.578 rows=1 loops=86)

  • Index Cond: (namespaces.id = namespaces_1.id)
  • Filter: ((namespaces.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=237 read=107
  • I/O Timings: read=133.397
17. 837.296 837.296 ↑ 2.1 8 86

Index Scan using index_projects_on_namespace_id on public.projects (cost=0.43..1.53 rows=17 width=8) (actual time=2.253..9.736 rows=8 loops=86)

  • Index Cond: (projects.namespace_id = namespaces.id)
  • Buffers: shared hit=176 read=798 dirtied=27
  • I/O Timings: read=826.674
18. 2,311.936 2,311.936 ↑ 3.0 1 704

Index Scan using index_members_on_source_id_and_source_type on public.members (cost=0.56..0.73 rows=3 width=8) (actual time=2.045..3.284 rows=1 loops=704)

  • Index Cond: ((members.source_id = projects.id) AND ((members.source_type)::text = 'Project'::text))
  • Filter: ((members.requested_at IS NULL) AND (members.access_level > 10) AND ((members.type)::text = 'ProjectMember'::text))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=2035 read=1569 dirtied=36 written=652
  • I/O Timings: read=1832.589 write=457.578
19. 747.020 747.020 ↑ 1.0 1 820

Index Scan using users_pkey on public.users (cost=0.43..0.52 rows=1 width=4) (actual time=0.906..0.911 rows=1 loops=820)

  • Index Cond: (users.id = members.user_id)
  • Filter: ((users.state)::text = 'active'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=2651 read=636 dirtied=7 written=173
  • I/O Timings: read=633.606 write=98.433