explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JEW2

Settings
# exclusive inclusive rows x rows loops node
1. 728,258.782 746,540.422 ↓ 0.0 0 1

Insert on repo_metapackages (cost=248,956.15..2,241,521.27 rows=239,491 width=20) (actual time=746,540.422..746,540.422 rows=0 loops=1)

  • Buffers: shared hit=21919810 read=3833233 dirtied=3417088 written=3320654
2. 551.831 18,281.640 ↓ 7.3 1,737,527 1

Subquery Scan on *SELECT* (cost=248,956.15..2,241,521.27 rows=239,491 width=20) (actual time=5,456.525..18,281.640 rows=1,737,527 loops=1)

  • Buffers: shared hit=9876321 read=176791 dirtied=69 written=426
3. 5,213.103 17,729.809 ↓ 7.3 1,737,527 1

HashAggregate (cost=248,956.15..2,239,126.36 rows=239,491 width=31) (actual time=5,456.523..17,729.809 rows=1,737,527 loops=1)

  • Group Key: packages.effname, packages.repo
  • Buffers: shared hit=9876321 read=176791 dirtied=69 written=426
4. 1,491.202 3,829.071 ↓ 1.9 2,047,303 1

Hash Join (cost=80,767.31..213,788.09 rows=1,082,094 width=29) (actual time=888.680..3,829.071 rows=2,047,303 loops=1)

  • Hash Cond: (packages.effname = metapackages.effname)
  • Buffers: shared hit=4 read=176179
5. 1,449.332 1,449.332 ↑ 1.0 2,309,389 1

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

  • Buffers: shared hit=2 read=102783
6. 65.604 888.537 ↑ 1.0 181,620 1

Hash (cost=78,473.88..78,473.88 rows=183,475 width=19) (actual time=888.537..888.537 rows=181,620 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 10768kB
  • Buffers: shared hit=2 read=73396
7. 822.933 822.933 ↑ 1.0 181,620 1

Seq Scan on metapackages (cost=0.00..78,473.88 rows=183,475 width=19) (actual time=0.149..822.933 rows=181,620 loops=1)

  • Filter: (num_repos_nonshadow > 0)
  • Rows Removed by Filter: 226941
  • Buffers: shared hit=2 read=73396
8.          

SubPlan (for HashAggregate)

9. 8,687.635 8,687.635 ↑ 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.004..0.005 rows=1 loops=1,737,527)

  • Index Cond: (name = packages.repo)
  • Buffers: shared hit=9876317 read=612 dirtied=69 written=426
Planning time : 2.447 ms
Execution time : 746,541.506 ms