explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GmH7

Settings
# exclusive inclusive rows x rows loops node
1. 27,617.734 45,979.662 ↓ 0.0 0 1

Insert on repo_metapackages (cost=2,490,949.97..2,493,863.11 rows=233,051 width=20) (actual time=45,979.662..45,979.662 rows=0 loops=1)

  • Buffers: shared hit=20981071 read=539034 dirtied=68964 written=46373, temp read=8926 written=8931
2. 532.411 18,361.928 ↓ 7.5 1,737,527 1

Subquery Scan on *SELECT* (cost=2,490,949.97..2,493,863.11 rows=233,051 width=20) (actual time=16,524.990..18,361.928 rows=1,737,527 loops=1)

  • Buffers: shared hit=5255127 read=460984, temp read=8926 written=8931
3. 4,092.096 17,829.517 ↓ 7.5 1,737,527 1

Sort (cost=2,490,949.97..2,491,532.60 rows=233,051 width=31) (actual time=16,524.986..17,829.517 rows=1,737,527 loops=1)

  • Sort Key: packages.effname, packages.repo
  • Sort Method: external merge Disk: 71408kB
  • Buffers: shared hit=5255127 read=460984, temp read=8926 written=8931
4. 3,430.931 13,737.421 ↓ 7.5 1,737,527 1

HashAggregate (cost=533,519.33..2,470,173.14 rows=233,051 width=31) (actual time=8,456.496..13,737.421 rows=1,737,527 loops=1)

  • Group Key: packages.effname, packages.repo
  • Buffers: shared hit=5255127 read=460984
5. 1,518.898 6,831.436 ↓ 2.0 2,047,303 1

Hash Join (cost=77,698.27..499,663.01 rows=1,041,733 width=29) (actual time=845.252..6,831.436 rows=2,047,303 loops=1)

  • Hash Cond: (packages.effname = metapackages.effname)
  • Buffers: shared hit=2 read=460984
6. 4,468.016 4,468.016 ↑ 1.0 2,309,389 1

Seq Scan on packages (cost=0.00..415,847.09 rows=2,330,509 width=27) (actual time=0.612..4,468.016 rows=2,309,389 loops=1)

  • Buffers: shared hit=2 read=392540
7. 106.163 844.522 ↑ 1.3 181,620 1

Hash (cost=74,839.50..74,839.50 rows=228,702 width=19) (actual time=844.522..844.522 rows=181,620 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 10768kB
  • Buffers: shared read=68444
8. 738.359 738.359 ↑ 1.3 181,620 1

Seq Scan on metapackages (cost=0.00..74,839.50 rows=228,702 width=19) (actual time=1.306..738.359 rows=181,620 loops=1)

  • Filter: (num_repos_nonshadow > 0)
  • Rows Removed by Filter: 226938
  • Buffers: shared read=68444
9.          

SubPlan (for HashAggregate)

10. 3,475.054 3,475.054 ↑ 1.0 1 1,737,527

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,527)

  • Index Cond: (name = packages.repo)
  • Buffers: shared hit=5255125
Planning time : 9.910 ms
Execution time : 45,981.051 ms