explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TEn7

Settings
# exclusive inclusive rows x rows loops node
1. 0.386 14.889 ↑ 1.0 1 1

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

  • Buffers: shared hit=9,867
2. 0.803 14.503 ↓ 84.3 3,794 1

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

  • Buffers: shared hit=9,867
3. 0.209 3.660 ↑ 2.7 1,004 1

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

  • Buffers: shared hit=2,191
4. 0.117 1.856 ↑ 1.0 145 1

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

  • Group Key: namespaces.id
  • Buffers: shared hit=728
5. 1.739 1.739 ↑ 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.033..1.739 rows=145 loops=1)

  • Buffers: shared hit=728
6.          

CTE base_and_descendants

7. 0.373 1.544 ↑ 1.0 145 1

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

  • Buffers: shared hit=728
8. 0.021 0.021 ↑ 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.020..0.021 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.120 1.150 ↓ 1.9 29 5

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

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

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

11. 1.015 1.015 ↑ 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.005..0.007 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. 1.595 1.595 ↑ 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.006..0.011 rows=7 loops=145)

  • Index Cond: (projects.namespace_id = namespaces.id)
  • Heap Fetches: 156
  • Buffers: shared hit=1,463
13. 10.040 10.040 ↓ 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.005..0.010 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=7,676