explain.depesz.com

PostgreSQL's explain analyze made readable

Result: q2ek

Settings
# exclusive inclusive rows x rows loops node
1. 43,694.955 61,332.982 ↓ 0.0 0 1

Insert on repo_metapackages (cost=376,725.56..2,405,998.89 rows=239,491 width=20) (actual time=61,332.982..61,332.982 rows=0 loops=1)

  • Buffers: shared hit=20162683 read=1360471 dirtied=101274 written=96824, temp read=30652 written=30705
2. 497.504 17,638.027 ↓ 7.3 1,737,351 1

Subquery Scan on *SELECT* (cost=376,725.56..2,405,998.89 rows=239,491 width=20) (actual time=8,421.170..17,638.027 rows=1,737,351 loops=1)

  • Buffers: shared hit=5562611 read=135707 dirtied=31902 written=28245, temp read=30652 written=30705
3. 2,717.413 17,140.523 ↓ 7.3 1,737,351 1

GroupAggregate (cost=376,725.56..2,403,603.98 rows=239,491 width=31) (actual time=8,421.168..17,140.523 rows=1,737,351 loops=1)

  • Group Key: packages.effname, packages.repo
  • Buffers: shared hit=5562611 read=135707 dirtied=31902 written=28245, temp read=30652 written=30705
4. 3,473.097 9,211.057 ↓ 2.0 2,047,124 1

Sort (cost=376,725.56..379,347.57 rows=1,048,806 width=29) (actual time=8,420.541..9,211.057 rows=2,047,124 loops=1)

  • Sort Key: packages.effname, packages.repo
  • Sort Method: external merge Disk: 80344kB
  • Buffers: shared hit=307108 read=135278 dirtied=31771 written=28220, temp read=30652 written=30705
5. 1,932.755 5,737.960 ↓ 2.0 2,047,124 1

Hash Join (cost=79,949.52..246,748.30 rows=1,048,806 width=29) (actual time=1,797.835..5,737.960 rows=2,047,124 loops=1)

  • Hash Cond: (packages.effname = metapackages.effname)
  • Buffers: shared hit=307108 read=135278 dirtied=31771 written=28220, temp read=10622 written=10622
6. 2,007.630 2,007.630 ↑ 1.0 2,309,210 1

Seq Scan on packages (cost=0.00..126,734.08 rows=2,394,908 width=27) (actual time=0.011..2,007.630 rows=2,309,210 loops=1)

  • Buffers: shared hit=1 read=102784 dirtied=2250 written=2250
7. 74.513 1,797.575 ↓ 1.0 181,620 1

Hash (cost=76,710.15..76,710.15 rows=176,429 width=19) (actual time=1,797.574..1,797.575 rows=181,620 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 2706kB
  • Buffers: shared hit=307107 read=32494 dirtied=29521 written=25970, temp written=616
8. 1,723.062 1,723.062 ↓ 1.0 181,620 1

Index Scan using metapackages_active_idx on metapackages (cost=0.42..76,710.15 rows=176,429 width=19) (actual time=0.289..1,723.062 rows=181,620 loops=1)

  • Buffers: shared hit=307107 read=32494 dirtied=29521 written=25970
9.          

SubPlan (for GroupAggregate)

10. 5,212.053 5,212.053 ↑ 1.0 1 1,737,351

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

  • Index Cond: (name = packages.repo)
  • Buffers: shared hit=5255503 read=429 dirtied=131 written=25
Planning time : 1.106 ms
Execution time : 61,333.283 ms