explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aE6K

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 760.629 ↑ 1.0 1 1

Limit (cost=75.69..2,825.24 rows=1 width=4) (actual time=760.626..760.629 rows=1 loops=1)

  • Buffers: shared hit=1211 read=447 dirtied=11
  • I/O Timings: read=751.737
2. 0.415 760.626 ↑ 1.0 1 1

Hash Left Join (cost=75.69..2,825.24 rows=1 width=4) (actual time=760.626..760.626 rows=1 loops=1)

  • Hash Cond: (projects.id = index_statuses.project_id)
  • Filter: ((NOT index_statuses.records_initially_indexed) OR (index_statuses.records_initially_indexed IS NULL))
  • Rows Removed by Filter: 219
  • Buffers: shared hit=1211 read=447 dirtied=11
  • I/O Timings: read=751.737
3. 0.583 759.738 ↑ 5.0 220 1

Nested Loop (cost=1.00..2,746.42 rows=1,095 width=4) (actual time=0.759..759.738 rows=220 loops=1)

  • Buffers: shared hit=678 read=447 dirtied=11
  • I/O Timings: read=751.737
4. 455.555 455.555 ↑ 5.0 220 1

Index Scan using index_routes_on_path_text_pattern_ops on public.routes rs (cost=0.56..4.59 rows=1,111 width=4) (actual time=0.572..455.555 rows=220 loops=1)

  • Index Cond: (((rs.path)::text ~>=~ 'gitlab-org/'::text) AND ((rs.path)::text ~<~ 'gitlab-org0'::text))
  • Filter: (((rs.path)::text ~~ 'gitlab-org/%'::text) AND ((rs.source_type)::text = 'Project'::text))
  • Rows Removed by Filter: 31
  • Buffers: shared hit=27 read=230 dirtied=6
  • I/O Timings: read=451.539
5. 303.600 303.600 ↑ 1.0 1 220

Index Only Scan using projects_pkey on public.projects (cost=0.43..2.46 rows=1 width=4) (actual time=1.380..1.380 rows=1 loops=220)

  • Index Cond: (projects.id = rs.source_id)
  • Heap Fetches: 21
  • Buffers: shared hit=651 read=217 dirtied=5
  • I/O Timings: read=300.198
6. 0.134 0.473 ↑ 1.0 644 1

Hash (cost=66.65..66.65 rows=644 width=5) (actual time=0.473..0.473 rows=644 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 32kB
  • Buffers: shared hit=530
7. 0.339 0.339 ↑ 1.0 644 1

Index Scan using index_index_statuses_on_project_id on public.index_statuses (cost=0.28..66.65 rows=644 width=5) (actual time=0.005..0.339 rows=644 loops=1)

  • Buffers: shared hit=530