explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Zmn2

Settings
# exclusive inclusive rows x rows loops node
1. 92,544.665 112,929.253 ↓ 0.0 0 1

Insert on repo_metapackages (cost=3,876,940.10..3,881,395.72 rows=356,450 width=20) (actual time=112,929.253..112,929.253 rows=0 loops=1)

  • Buffers: shared hit=18403936 read=1905478 dirtied=1127620 written=1086302, temp read=8924 written=8925
2. 870.168 20,384.588 ↓ 4.9 1,737,594 1

Subquery Scan on *SELECT* (cost=3,876,940.10..3,881,395.72 rows=356,450 width=20) (actual time=18,591.257..20,384.588 rows=1,737,594 loops=1)

  • Buffers: shared hit=5257601 read=777127 written=62, temp read=8924 written=8925
3. 4,103.557 19,514.420 ↓ 4.9 1,737,594 1

Sort (cost=3,876,940.10..3,877,831.22 rows=356,450 width=31) (actual time=18,591.254..19,514.420 rows=1,737,594 loops=1)

  • Sort Key: ((SubPlan 1)), packages.effname
  • Sort Method: external merge Disk: 71392kB
  • Buffers: shared hit=5257601 read=777127 written=62, temp read=8924 written=8925
4. 2,480.357 15,410.863 ↓ 4.9 1,737,594 1

HashAggregate (cost=881,969.96..3,844,069.46 rows=356,450 width=31) (actual time=10,789.970..15,410.863 rows=1,737,594 loops=1)

  • Group Key: packages.effname, packages.repo
  • Buffers: shared hit=5257601 read=777127 written=62
5. 1,471.741 9,455.318 ↓ 1.3 2,047,917 1

Hash Join (cost=96,731.60..829,298.54 rows=1,620,659 width=29) (actual time=1,392.960..9,455.318 rows=2,047,917 loops=1)

  • Hash Cond: (packages.effname = metapackages.effname)
  • Buffers: shared read=777077 written=62
6. 6,977.513 6,977.513 ↑ 1.5 2,310,219 1

Seq Scan on packages (cost=0.00..723,210.02 rows=3,564,502 width=27) (actual time=386.512..6,977.513 rows=2,310,219 loops=1)

  • Buffers: shared read=687565 written=51
7. 61.144 1,006.064 ↓ 1.0 181,813 1

Hash (cost=94,475.06..94,475.06 rows=180,523 width=19) (actual time=1,006.064..1,006.064 rows=181,813 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 10777kB
  • Buffers: shared read=89512 written=11
8. 944.920 944.920 ↓ 1.0 181,813 1

Seq Scan on metapackages (cost=0.00..94,475.06 rows=180,523 width=19) (actual time=0.224..944.920 rows=181,813 loops=1)

  • Filter: (num_repos_nonshadow > 0)
  • Rows Removed by Filter: 227171
  • Buffers: shared read=89512 written=11
9.          

SubPlan (for HashAggregate)

10. 3,475.188 3,475.188 ↑ 1.0 1 1,737,594

Index Scan using repositories_name_idx on repositories (cost=0.27..8.29 rows=1 width=2) (actual time=0.002..0.002 rows=1 loops=1,737,594)

  • Index Cond: (name = packages.repo)
  • Buffers: shared hit=5257601 read=50
Planning time : 0.417 ms
Execution time : 112,930.516 ms