explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PqjP : Optimization for: plan #qeUr

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.492 645.778 ↓ 0.0 0 1

Nested Loop Semi Join (cost=2,257.51..793,499.96 rows=350 width=183) (actual time=645.778..645.778 rows=0 loops=1)

  • Buffers: shared hit=4,765 read=286 dirtied=1
  • I/O Timings: read=627.072
2. 3.172 3.172 ↓ 1.5 517 1

Index Scan using merge_request_diffs_pkey on public.merge_request_diffs (cost=0.57..919.19 rows=350 width=183) (actual time=0.051..3.172 rows=517 loops=1)

  • Index Cond: ((merge_request_diffs.id >= 1) AND (merge_request_diffs.id <= 1,000))
  • Filter: (merge_request_diffs.stored_externally AND ((merge_request_diffs.state)::text <> ALL ('{without_files,empty}'::text[])))
  • Rows Removed by Filter: 382
  • Buffers: shared hit=385
3. 1.034 642.114 ↓ 0.0 0 517

Nested Loop Semi Join (cost=2,256.95..2,264.51 rows=1 width=4) (actual time=1.242..1.242 rows=0 loops=517)

  • Buffers: shared hit=4,380 read=286 dirtied=1
  • I/O Timings: read=627.072
4. 310.200 310.200 ↑ 1.0 1 517

Index Scan using merge_requests_pkey on public.merge_requests (cost=0.56..3.58 rows=1 width=8) (actual time=0.593..0.600 rows=1 loops=517)

  • Index Cond: (merge_requests.id = merge_request_diffs.merge_request_id)
  • Buffers: shared hit=2,424 read=165
  • I/O Timings: read=304.269
5. 1.551 330.880 ↓ 0.0 0 517

Nested Loop (cost=2,256.38..2,260.91 rows=1 width=4) (actual time=0.640..0.640 rows=0 loops=517)

  • Buffers: shared hit=1,956 read=121 dirtied=1
  • I/O Timings: read=322.803
6. 328.812 328.812 ↑ 1.0 1 517

Index Scan using projects_pkey on public.projects (cost=0.43..0.47 rows=1 width=8) (actual time=0.630..0.636 rows=1 loops=517)

  • Index Cond: (projects.id = merge_requests.target_project_id)
  • Buffers: shared hit=1,955 read=121 dirtied=1
  • I/O Timings: read=322.803
7. 0.473 0.517 ↓ 0.0 0 517

HashAggregate (cost=2,255.95..2,257.95 rows=200 width=4) (actual time=0.001..0.001 rows=0 loops=517)

  • Group Key: namespaces.id
  • Buffers: shared hit=1
8. 0.044 0.044 ↓ 0.0 0 1

CTE Scan on base_and_descendants namespaces (cost=2,074.04..2,185.98 rows=5,597 width=4) (actual time=0.043..0.044 rows=0 loops=1)

  • Buffers: shared hit=1
9.          

CTE base_and_descendants

10. 0.001 0.041 ↓ 0.0 0 1

Recursive Union (cost=0.15..2,074.04 rows=5,597 width=4) (actual time=0.040..0.041 rows=0 loops=1)

  • Buffers: shared hit=1
11. 0.036 0.036 ↓ 0.0 0 1

Index Only Scan using index_geo_node_namespace_links_on_geo_node_id_and_namespace_id on public.geo_node_namespace_links (cost=0.15..10.78 rows=7 width=4) (actual time=0.035..0.036 rows=0 loops=1)

  • Index Cond: (geo_node_namespace_links.geo_node_id = 100,109)
  • Heap Fetches: 0
  • Buffers: shared hit=1
12. 0.002 0.004 ↓ 0.0 0 1

Nested Loop (cost=0.56..195.13 rows=559 width=4) (actual time=0.004..0.004 rows=0 loops=1)

13. 0.002 0.002 ↓ 0.0 0 1

WorkTable Scan on base_and_descendants (cost=0.00..1.40 rows=70 width=4) (actual time=0.002..0.002 rows=0 loops=1)

14. 0.000 0.000 ↓ 0.0 0 0

Index Only Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_1 (cost=0.56..2.69 rows=8 width=8) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: (namespaces_1.parent_id = base_and_descendants.id)
  • Heap Fetches: 0