explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8lYb

Settings
# exclusive inclusive rows x rows loops node
1. 1.825 1,156.104 ↑ 1.0 1 1

Aggregate (cost=3,983.09..3,983.10 rows=1 width=8) (actual time=1,156.104..1,156.104 rows=1 loops=1)

  • Buffers: shared hit=5,808 read=4,059
  • I/O Timings: read=1,101.882
2. 4.500 1,154.279 ↓ 84.3 3,794 1

Nested Loop (cost=1,428.57..3,982.98 rows=45 width=0) (actual time=22.194..1,154.279 rows=3,794 loops=1)

  • Buffers: shared hit=5,808 read=4,059
  • I/O Timings: read=1,101.882
3. 0.927 10.239 ↑ 2.7 1,004 1

Nested Loop (cost=1,428.00..2,020.06 rows=2,702 width=4) (actual time=2.217..10.239 rows=1,004 loops=1)

  • Buffers: shared hit=2,191
4. 0.390 2.497 ↑ 1.0 145 1

HashAggregate (cost=1,427.57..1,429.08 rows=151 width=4) (actual time=2.174..2.497 rows=145 loops=1)

  • Group Key: namespaces.id
  • Buffers: shared hit=728
5. 2.107 2.107 ↑ 1.0 145 1

CTE Scan on base_and_descendants namespaces (cost=1,422.66..1,425.68 rows=151 width=4) (actual time=0.051..2.107 rows=145 loops=1)

  • Buffers: shared hit=728
6.          

CTE base_and_descendants

7. 0.404 1.866 ↑ 1.0 145 1

Recursive Union (cost=0.43..1,422.66 rows=151 width=347) (actual time=0.047..1.866 rows=145 loops=1)

  • Buffers: shared hit=728
8. 0.037 0.037 ↑ 1.0 1 1

Index Scan using namespaces_pkey on public.namespaces namespaces_1 (cost=0.43..3.45 rows=1 width=347) (actual time=0.036..0.037 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
9. 0.100 1.425 ↓ 1.9 29 5

Nested Loop (cost=0.56..141.62 rows=15 width=347) (actual time=0.026..0.285 rows=29 loops=5)

  • Buffers: shared hit=724
10. 0.020 0.020 ↓ 2.9 29 5

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

11. 1.305 1.305 ↑ 1.0 1 145

Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_2 (cost=0.56..14.13 rows=1 width=347) (actual time=0.006..0.009 rows=1 loops=145)

  • 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=724
12. 6.815 6.815 ↑ 2.6 7 145

Index Only Scan using index_projects_on_namespace_id_and_id on public.projects (cost=0.43..3.73 rows=18 width=8) (actual time=0.014..0.047 rows=7 loops=145)

  • Index Cond: (projects.namespace_id = namespaces.id)
  • Heap Fetches: 156
  • Buffers: shared hit=1,463
13. 1,139.540 1,139.540 ↓ 4.0 4 1,004

Index Scan using test_agg on public.merge_request_metrics (cost=0.56..0.72 rows=1 width=4) (actual time=0.824..1.135 rows=4 loops=1,004)

  • Index Cond: ((date_trunc('month'::text, merge_request_metrics.merged_at) = '2020-04-01 00:00:00'::timestamp without time zone) AND (merge_request_metrics.target_project_id = projects.id))
  • Buffers: shared hit=3,617 read=4,059
  • I/O Timings: read=1,101.882