explain.depesz.com

PostgreSQL's explain analyze made readable

Result: O74C

Settings
# exclusive inclusive rows x rows loops node
1. 87,303.560 371,667.911 ↓ 0.0 0 1

Insert on repo_metapackages (cost=802,166.85..805,186.16 rows=241,545 width=20) (actual time=371,667.904..371,667.911 rows=0 loops=1)

  • Buffers: shared hit=19008923 read=431307 dirtied=296654 written=149791, temp read=8990 written=8991
2. 37,908.518 284,364.351 ↓ 7.2 1,750,012 1

Subquery Scan on *SELECT* (cost=802,166.85..805,186.16 rows=241,545 width=20) (actual time=226,688.695..284,364.351 rows=1,750,012 loops=1)

  • Buffers: shared hit=5275291 read=131477 dirtied=6030 written=2390, temp read=8990 written=8991
3. 41,063.738 246,455.833 ↓ 7.2 1,750,012 1

Sort (cost=802,166.85..802,770.71 rows=241,545 width=31) (actual time=226,688.657..246,455.833 rows=1,750,012 loops=1)

  • Sort Key: ((SubPlan 1)), packages.effname
  • Sort Method: external merge Disk: 71920kB
  • Buffers: shared hit=5275291 read=131477 dirtied=6030 written=2390, temp read=8990 written=8991
4. 82,916.598 205,392.095 ↓ 7.2 1,750,012 1

HashAggregate (cost=222,601.44..780,570.39 rows=241,545 width=31) (actual time=107,415.805..205,392.095 rows=1,750,012 loops=1)

  • Group Key: packages.effname, packages.repo
  • Buffers: shared hit=5275291 read=131477 dirtied=6030 written=2390
5. 46,755.567 82,225.221 ↓ 2.4 2,060,539 1

Hash Join (cost=47,730.53..194,204.67 rows=873,747 width=29) (actual time=4,438.158..82,225.221 rows=2,060,539 loops=1)

  • Hash Cond: (packages.effname = metapackages.effname)
  • Buffers: shared hit=25262 read=131470 dirtied=6030 written=2390
6. 31,035.079 31,035.079 ↑ 1.0 2,322,880 1

Seq Scan on packages (cost=0.00..140,133.52 rows=2,415,452 width=27) (actual time=0.875..31,035.079 rows=2,322,880 loops=1)

  • Buffers: shared hit=3930 read=112049 dirtied=6030 written=2380
7. 1,959.864 4,434.575 ↓ 1.0 181,942 1

Hash (cost=45,481.13..45,481.13 rows=179,952 width=19) (actual time=4,434.566..4,434.575 rows=181,942 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 10784kB
  • Buffers: shared hit=21332 read=19421 written=10
8. 2,474.711 2,474.711 ↓ 1.0 181,942 1

Index Scan using metapackages_active_idx on metapackages (cost=0.42..45,481.13 rows=179,952 width=19) (actual time=0.141..2,474.711 rows=181,942 loops=1)

  • Buffers: shared hit=21332 read=19421 written=10
9.          

SubPlan (for HashAggregate)

10. 40,250.276 40,250.276 ↑ 1.0 1 1,750,012

Index Scan using repositories_name_idx on repositories (cost=0.27..2.29 rows=1 width=2) (actual time=0.014..0.023 rows=1 loops=1,750,012)

  • Index Cond: (name = packages.repo)
  • Buffers: shared hit=5250029 read=7
Planning time : 1.477 ms
Execution time : 371,712.208 ms