explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1,408.948 1,889.905 ↓ 0.0 0 1

Update on packages_raw pr (cost=1.26..41,692.69 rows=1,078 width=115) (actual time=1,889.905..1,889.905 rows=0 loops=1)

2. 13.509 480.957 ↓ 27.2 29,355 1

Nested Loop (cost=1.26..41,692.69 rows=1,078 width=115) (actual time=0.222..480.957 rows=29,355 loops=1)

3. 0.028 0.028 ↑ 1.0 1 1

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

  • Filter: (name = 'FreeBSD:13:aarch64'::text)
  • Rows Removed by Filter: 8
4. 39.602 467.420 ↓ 27.2 29,355 1

Nested Loop (cost=1.26..41,680.87 rows=1,078 width=93) (actual time=0.192..467.420 rows=29,355 loops=1)

5. 54.516 310.398 ↓ 1.6 29,355 1

Nested Loop (cost=0.97..35,422.99 rows=18,885 width=87) (actual time=0.174..310.398 rows=29,355 loops=1)

6. 50.271 50.271 ↓ 1.6 29,373 1

Index Scan using packages_raw_abi_idx on packages_raw pr (cost=0.42..3,079.84 rows=18,885 width=77) (actual time=0.143..50.271 rows=29,373 loops=1)

  • Index Cond: (abi = 'FreeBSD:13:aarch64'::text)
  • Filter: (package_set = 'latest'::package_sets)
7. 205.611 205.611 ↑ 1.0 1 29,373

Index Scan using element_pathname_pathname on element_pathname ep (cost=0.55..1.71 rows=1 width=60) (actual time=0.007..0.007 rows=1 loops=29,373)

  • Index Cond: (pathname = ('/ports/head/'::text || pr.package_origin))
8. 117.420 117.420 ↑ 1.0 1 29,355

Index Scan using ports_element_id on ports p (cost=0.29..0.32 rows=1 width=14) (actual time=0.004..0.004 rows=1 loops=29,355)

  • Index Cond: (element_id = ep.element_id)
Planning time : 1.141 ms
Execution time : 1,890.774 ms