explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7ogU : Optimization for: Optimization for: Optimization for: FreshPorts - packages : update pacakges_raw - match on pathname; plan #wIfx; plan #hsU5; plan #Wh1o

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 1,323.934 2,272.714 ↓ 0.0 0 1

Update on packages_raw pr (cost=39,832.64..46,032.38 rows=2,204 width=109) (actual time=2,272.714..2,272.714 rows=0 loops=1)

2. 44.823 948.780 ↓ 14.4 31,692 1

Hash Join (cost=39,832.64..46,032.38 rows=2,204 width=109) (actual time=839.469..948.780 rows=31,692 loops=1)

  • Hash Cond: (('/ports/head/'::text || pr.package_origin) = ep.pathname)
3. 64.855 64.855 ↑ 1.2 31,710 1

Index Scan using packages_raw_abi_idx on packages_raw pr (cost=0.42..5,984.86 rows=38,653 width=77) (actual time=0.186..64.855 rows=31,710 loops=1)

  • Index Cond: (abi = 'FreeBSD:11:amd64'::text)
  • Filter: (package_set = 'latest'::package_sets)
  • Rows Removed by Filter: 31572
4. 45.467 839.102 ↓ 1.0 58,238 1

Hash (cost=39,109.46..39,109.46 rows=57,821 width=66) (actual time=839.102..839.102 rows=58,238 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 5187kB
5. 325.361 793.635 ↓ 1.0 58,238 1

Hash Join (cost=13,890.97..39,109.46 rows=57,821 width=66) (actual time=72.433..793.635 rows=58,238 loops=1)

  • Hash Cond: (ep.element_id = p.element_id)
6. 396.077 396.077 ↑ 1.0 1,006,580 1

Seq Scan on element_pathname ep (cost=0.00..20,838.38 rows=1,013,838 width=60) (actual time=0.023..396.077 rows=1,006,580 loops=1)

7. 28.602 72.197 ↓ 1.0 58,238 1

Hash (cost=13,168.21..13,168.21 rows=57,821 width=14) (actual time=72.197..72.197 rows=58,238 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3242kB
8. 43.595 43.595 ↓ 1.0 58,238 1

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

Planning time : 1.336 ms
Execution time : 2,272.898 ms