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=20,162,683 read=1,360,471 dirtied=101,274 written=96,824, temp read=30,652 written=30,705
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=5,562,611 read=135,707 dirtied=31,902 written=28,245, temp read=30,652 written=30,705
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=5,562,611 read=135,707 dirtied=31,902 written=28,245, temp read=30,652 written=30,705
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: 80,344kB
  • Buffers: shared hit=307,108 read=135,278 dirtied=31,771 written=28,220, temp read=30,652 written=30,705
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=307,108 read=135,278 dirtied=31,771 written=28,220, temp read=10,622 written=10,622
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=102,784 dirtied=2,250 written=2,250
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: 65,536 Batches: 4 Memory Usage: 2,706kB
  • Buffers: shared hit=307,107 read=32,494 dirtied=29,521 written=25,970, 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=307,107 read=32,494 dirtied=29,521 written=25,970
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=5,255,503 read=429 dirtied=131 written=25
Planning time : 1.106 ms
Execution time : 61,333.283 ms