explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hsU5 : Optimization for: FreshPorts - packages : update pacakges_raw - this grabs stuff from only head; plan #wIfx

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1,343.237 1,907.037 ↓ 0.0 0 1

Update on packages_raw pr (cost=5,596.59..37,984.19 rows=11,644 width=121) (actual time=1,907.037..1,907.037 rows=0 loops=1)

2. 13.007 563.800 ↓ 2.5 28,577 1

Nested Loop (cost=5,596.59..37,984.19 rows=11,644 width=121) (actual time=159.542..563.800 rows=28,577 loops=1)

3. 0.027 0.027 ↑ 1.0 1 1

Seq Scan on abi (cost=0.00..1.04 rows=1 width=42) (actual time=0.024..0.027 rows=1 loops=1)

  • Filter: (name = 'FreeBSD:13:aarch64'::text)
  • Rows Removed by Filter: 8
4. 25.503 550.766 ↓ 2.5 28,577 1

Nested Loop (cost=5,596.59..37,866.71 rows=11,644 width=99) (actual time=159.511..550.766 rows=28,577 loops=1)

5. 60.653 267.390 ↓ 1.4 36,839 1

Hash Join (cost=5,596.17..19,243.19 rows=26,198 width=97) (actual time=159.441..267.390 rows=36,839 loops=1)

  • Hash Cond: (p.id = po.port_id)
6. 47.418 47.418 ↓ 1.0 58,138 1

Seq Scan on ports p (cost=0.00..13,168.21 rows=57,821 width=14) (actual time=0.010..47.418 rows=58,138 loops=1)

7. 35.290 159.319 ↓ 1.4 36,839 1

Hash (cost=5,268.69..5,268.69 rows=26,198 width=87) (actual time=159.319..159.319 rows=36,839 loops=1)

  • Buckets: 65536 (originally 32768) Batches: 1 (originally 1) Memory Usage: 5032kB
8. 31.388 124.029 ↓ 1.4 36,839 1

Hash Join (cost=1,428.92..5,268.69 rows=26,198 width=87) (actual time=42.306..124.029 rows=36,839 loops=1)

  • Hash Cond: (pr.package_origin = po.port_origin)
9. 50.567 50.567 ↓ 1.6 29,373 1

Index Scan using packages_raw_abi_idx on packages_raw pr (cost=0.42..3,068.36 rows=18,540 width=77) (actual time=0.039..50.567 rows=29,373 loops=1)

  • Index Cond: (abi = 'FreeBSD:13:aarch64'::text)
  • Filter: (package_set = 'latest'::package_sets)
10. 20.648 42.074 ↓ 1.0 39,404 1

Hash (cost=936.00..936.00 rows=39,400 width=30) (actual time=42.073..42.074 rows=39,404 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2913kB
11. 21.426 21.426 ↓ 1.0 39,404 1

Seq Scan on ports_origin po (cost=0.00..936.00 rows=39,400 width=30) (actual time=0.021..21.426 rows=39,404 loops=1)

12. 257.873 257.873 ↑ 1.0 1 36,839

Index Scan using element_pathname_element_id on element_pathname ep (cost=0.42..0.70 rows=1 width=10) (actual time=0.006..0.007 rows=1 loops=36,839)

  • Index Cond: (element_id = p.element_id)
  • Filter: (pathname ~~ '/ports/head/%'::text)
  • Rows Removed by Filter: 0
Planning time : 3.083 ms
Execution time : 1,908.185 ms