explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Y9gj : constraint packages_raw_port_id_fk is now deferred

Settings
# exclusive inclusive rows x rows loops node
1. 6,176.690 12,221.244 ↓ 0.0 0 1

Update on packages_raw pr (cost=12,937.84..38,416.01 rows=20,524 width=115) (actual time=12,221.244..12,221.244 rows=0 loops=1)

2. 356.594 6,044.554 ↓ 1.9 38,905 1

Hash Join (cost=12,937.84..38,416.01 rows=20,524 width=115) (actual time=2,772.498..6,044.554 rows=38,905 loops=1)

  • Hash Cond: (p.package_origin = po.port_origin)
3. 12.347 5,468.500 ↓ 2.0 30,492 1

Nested Loop (cost=10,845.18..34,762.92 rows=14,998 width=113) (actual time=2,552.187..5,468.500 rows=30,492 loops=1)

4. 0.338 0.338 ↑ 1.0 1 1

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

  • Filter: (name = 'FreeBSD:13:i386'::text)
  • Rows Removed by Filter: 8
5. 2,383.453 5,455.815 ↓ 2.0 30,492 1

Hash Join (cost=10,845.18..34,611.91 rows=14,998 width=120) (actual time=2,551.846..5,455.815 rows=30,492 loops=1)

  • Hash Cond: (pr.id = p.id)
6. 2,901.118 2,901.118 ↓ 1.2 459,733 1

Seq Scan on packages_raw pr (cost=0.00..12,835.28 rows=384,928 width=77) (actual time=0.337..2,901.118 rows=459,733 loops=1)

7. 27.343 171.244 ↓ 2.0 30,492 1

Hash (cost=10,510.70..10,510.70 rows=14,998 width=51) (actual time=171.243..171.244 rows=30,492 loops=1)

  • Buckets: 16384 (originally 16384) Batches: 4 (originally 2) Memory Usage: 897kB
8. 43.728 143.901 ↓ 2.0 30,492 1

Bitmap Heap Scan on packages_raw p (cost=978.15..10,510.70 rows=14,998 width=51) (actual time=100.492..143.901 rows=30,492 loops=1)

  • Recheck Cond: ((abi = 'FreeBSD:13:i386'::text) AND (package_set = 'latest'::package_sets))
  • Heap Blocks: exact=416
9. 100.173 100.173 ↓ 4.1 60,966 1

Bitmap Index Scan on packages_raw_abi_package_set_idx (cost=0.00..974.40 rows=14,998 width=0) (actual time=100.172..100.173 rows=60,966 loops=1)

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

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

  • Buckets: 16384 Batches: 8 Memory Usage: 576kB
11. 172.605 172.605 ↑ 1.0 57,807 1

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

Planning time : 9.476 ms
Execution time : 12,589.008 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
for constraint packages_raw_abi_id_fk 363.506 ms 30474 0.012 ms