explain.depesz.com

PostgreSQL's explain analyze made readable

Result: B6kz

Settings
# exclusive inclusive rows x rows loops node
1. 47.128 16,307.924 ↓ 187.1 36,297 1

Nested Loop (cost=2,336.39..2,478.84 rows=194 width=168) (actual time=67.038..16,307.924 rows=36,297 loops=1)

  • Buffers: shared hit=162206 read=17922 dirtied=840
  • I/O Timings: read=15861.461
2. 17.864 15,534.856 ↓ 185.2 36,297 1

Nested Loop (cost=2,335.95..2,374.84 rows=196 width=168) (actual time=65.190..15,534.856 rows=36,297 loops=1)

  • Buffers: shared hit=17229 read=17399 dirtied=834
  • I/O Timings: read=15371.912
3. 0.409 60.699 ↓ 3.7 191 1

HashAggregate (cost=2,335.39..2,335.90 rows=51 width=4) (actual time=60.405..60.699 rows=191 loops=1)

  • Group Key: projects.id
  • Buffers: shared hit=162 read=34 dirtied=5
  • I/O Timings: read=59.887
4. 0.043 60.290 ↓ 3.7 191 1

Nested Loop (cost=1,678.31..2,335.26 rows=51 width=4) (actual time=16.390..60.290 rows=191 loops=1)

  • Buffers: shared hit=162 read=34 dirtied=5
  • I/O Timings: read=59.887
5. 0.003 11.080 ↑ 28.0 1 1

Nested Loop (cost=1,677.88..2,308.07 rows=28 width=8) (actual time=11.077..11.080 rows=1 loops=1)

  • Buffers: shared hit=5 read=7
  • I/O Timings: read=10.976
6. 0.006 11.067 ↑ 141.0 1 1

HashAggregate (cost=1,677.45..1,678.86 rows=141 width=4) (actual time=11.066..11.067 rows=1 loops=1)

  • Group Key: namespaces_1.id
  • Buffers: shared hit=1 read=7
  • I/O Timings: read=10.976
7. 11.061 11.061 ↑ 141.0 1 1

CTE Scan on base_and_descendants namespaces_1 (cost=1,672.86..1,675.68 rows=141 width=4) (actual time=6.221..11.061 rows=1 loops=1)

  • Buffers: shared hit=1 read=7
  • I/O Timings: read=10.976
8.          

CTE base_and_descendants

9. 0.032 11.057 ↑ 141.0 1 1

Recursive Union (cost=0.43..1,672.86 rows=141 width=334) (actual time=6.216..11.057 rows=1 loops=1)

  • Buffers: shared hit=1 read=7
  • I/O Timings: read=10.976
10. 6.187 6.187 ↑ 1.0 1 1

Index Scan using namespaces_pkey on public.namespaces namespaces_2 (cost=0.43..4.45 rows=1 width=334) (actual time=6.186..6.187 rows=1 loops=1)

  • Index Cond: (namespaces_2.id = 2735941)
  • Filter: ((namespaces_2.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=1 read=3
  • I/O Timings: read=6.161
11. 0.003 4.838 ↓ 0.0 0 1

Nested Loop (cost=0.56..166.56 rows=14 width=334) (actual time=4.838..4.838 rows=0 loops=1)

  • Buffers: shared read=4
  • I/O Timings: read=4.815
12. 0.002 0.002 ↑ 10.0 1 1

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

13. 4.833 4.833 ↓ 0.0 0 1

Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_3 (cost=0.56..16.63 rows=1 width=334) (actual time=4.833..4.833 rows=0 loops=1)

  • Index Cond: (namespaces_3.parent_id = base_and_descendants.id)
  • Filter: ((namespaces_3.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared read=4
  • I/O Timings: read=4.815
14. 0.010 0.010 ↑ 1.0 1 1

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

  • Index Cond: (namespaces.id = namespaces_1.id)
  • Filter: ((namespaces.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=4
15. 49.167 49.167 ↓ 11.2 191 1

Index Only Scan using index_projects_on_namespace_id_and_id on public.projects (cost=0.43..0.80 rows=17 width=8) (actual time=5.307..49.167 rows=191 loops=1)

  • Index Cond: (projects.namespace_id = namespaces.id)
  • Heap Fetches: 8
  • Buffers: shared hit=157 read=27 dirtied=5
  • I/O Timings: read=48.911
16. 15,456.293 15,456.293 ↓ 47.5 190 191

Index Scan using index_members_on_source_id_and_source_type on public.members (cost=0.56..0.72 rows=4 width=168) (actual time=2.341..80.923 rows=190 loops=191)

  • Index Cond: ((members.source_id = projects.id) AND ((members.source_type)::text = 'Project'::text))
  • Filter: ((members.requested_at IS NULL) AND ((members.type)::text = 'ProjectMember'::text))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=17067 read=17365 dirtied=829
  • I/O Timings: read=15312.025
17. 725.940 725.940 ↑ 1.0 1 36,297

Index Scan using users_pkey on public.users (cost=0.43..0.52 rows=1 width=4) (actual time=0.020..0.020 rows=1 loops=36,297)

  • Index Cond: (users.id = members.user_id)
  • Filter: (((users.user_type IS NULL) OR (users.user_type <> 6)) AND ((users.state)::text = 'active'::text))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=144977 read=523 dirtied=6
  • I/O Timings: read=489.549