explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XXnj : both packages_raw_port_id_fk and packages_raw_abi_id_fk constraints are now deferred

Settings
# exclusive inclusive rows x rows loops node
1. 3,838.327 8,798.389 ↓ 0.0 0 1

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

2. 768.485 4,960.062 ↓ 1.9 38,905 1

Hash Join (cost=12,937.84..38,416.01 rows=20,524 width=115) (actual time=2,841.848..4,960.062 rows=38,905 loops=1)

  • Hash Cond: (p.package_origin = po.port_origin)
3. 13.592 3,970.915 ↓ 2.0 30,492 1

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

4. 0.350 0.350 ↑ 1.0 1 1

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

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

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

  • Hash Cond: (pr.id = p.id)
6. 2,796.802 2,796.802 ↓ 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.340..2,796.802 rows=459,733 loops=1)

7. 26.282 167.196 ↓ 2.0 30,492 1

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

  • Buckets: 16384 (originally 16384) Batches: 4 (originally 2) Memory Usage: 897kB
8. 30.494 140.914 ↓ 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=110.897..140.914 rows=30,492 loops=1)

  • Recheck Cond: ((abi = 'FreeBSD:13:i386'::text) AND (package_set = 'latest'::package_sets))
  • Heap Blocks: exact=416
9. 110.420 110.420 ↓ 6.1 91,440 1

Bitmap Index Scan on packages_raw_abi_package_set_idx (cost=0.00..974.40 rows=14,998 width=0) (actual time=110.419..110.420 rows=91,440 loops=1)

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

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

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

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

Planning time : 4.560 ms
Execution time : 8,800.805 ms