explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6d9Z : FreshPorts updating packages raw before inserting in packages

Settings
# exclusive inclusive rows x rows loops node
1. 6,401.278 11,081.844 ↓ 0.0 0 1

Update on packages_raw pr (cost=11,144.47..40,827.87 rows=30,175 width=115) (actual time=11,081.844..11,081.844 rows=0 loops=1)

2. 601.562 4,680.566 ↓ 1.3 38,905 1

Hash Join (cost=11,144.47..40,827.87 rows=30,175 width=115) (actual time=2,880.504..4,680.566 rows=38,905 loops=1)

  • Hash Cond: (p.package_origin = po.port_origin)
3. 14.274 3,858.975 ↓ 1.4 30,492 1

Nested Loop (cost=9,051.81..36,627.53 rows=22,050 width=113) (actual time=2,659.534..3,858.975 rows=30,492 loops=1)

4. 0.351 0.351 ↑ 1.0 1 1

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

  • Filter: (name = 'FreeBSD:13:i386'::text)
  • Rows Removed by Filter: 8
5. 1,335.561 3,844.350 ↓ 1.4 30,492 1

Hash Join (cost=9,051.81..36,405.99 rows=22,050 width=120) (actual time=2,659.181..3,844.350 rows=30,492 loops=1)

  • Hash Cond: (pr.id = p.id)
6. 2,365.531 2,365.531 ↑ 1.1 459,733 1

Seq Scan on packages_raw pr (cost=0.00..12,452.32 rows=524,132 width=77) (actual time=0.370..2,365.531 rows=459,733 loops=1)

7. 25.373 143.258 ↓ 1.4 30,492 1

Hash (cost=8,560.18..8,560.18 rows=22,050 width=51) (actual time=143.257..143.258 rows=30,492 loops=1)

  • Buckets: 16,384 Batches: 4 Memory Usage: 767kB
8. 31.967 117.885 ↓ 1.4 30,492 1

Bitmap Heap Scan on packages_raw p (cost=1,018.44..8,560.18 rows=22,050 width=51) (actual time=86.264..117.885 rows=30,492 loops=1)

  • Recheck Cond: ((abi = 'FreeBSD:13:i386'::text) AND (package_set = 'latest'::package_sets))
  • Heap Blocks: exact=416
9. 85.918 85.918 ↓ 3.0 66,670 1

Bitmap Index Scan on packages_raw_abi_package_set_idx (cost=0.00..1,012.92 rows=22,050 width=0) (actual time=85.918..85.918 rows=66,670 loops=1)

  • Index Cond: ((abi = 'FreeBSD:13:i386'::text) AND (package_set = 'latest'::package_sets))
10. 47.547 220.029 ↑ 1.0 57,807 1

Hash (cost=974.07..974.07 rows=57,807 width=30) (actual time=220.029..220.029 rows=57,807 loops=1)

  • Buckets: 16,384 Batches: 8 Memory Usage: 576kB
11. 172.482 172.482 ↑ 1.0 57,807 1

Seq Scan on ports_origin po (cost=0.00..974.07 rows=57,807 width=30) (actual time=0.458..172.482 rows=57,807 loops=1)

Planning time : 7.125 ms
Execution time : 18,782.602 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
for constraint packages_raw_abi_id_fk 1,151.252 ms 30474 0.038 ms
for constraint packages_raw_port_id_fk 6,532.501 ms 30474 0.214 ms