explain.depesz.com

PostgreSQL's explain analyze made readable

Result: h9JK : Optimization for: Optimization for: Optimization for: Optimization for: plan #dH2c; plan #wPoy; plan #fbBs; plan #xTSh

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 7,119.773 7,150.357 ↑ 1,144.8 441 1

Aggregate (cost=79,653.05..1,370,806.93 rows=504,850 width=102) (actual time=50.493..7,150.357 rows=441 loops=1)

  • Buffers: shared hit=1113103
2. 12.520 30.584 ↑ 27.7 18,250 1

Sort (cost=79,653.05..80,915.18 rows=504,850 width=18) (actual time=26.458..30.584 rows=18,250 loops=1)

  • Sort Key: mtc.leagueid, mtc.roundid, mtc.seasonid, tm.teamid
  • Sort Method: quicksort Memory: 2041kB
  • Buffers: shared hit=8088
3. 1.824 18.064 ↑ 27.7 18,250 1

Nested Loop (cost=0..31,829.88 rows=504,850 width=18) (actual time=0.204..18.064 rows=18,250 loops=1)

  • Buffers: shared hit=8088
4. 0.012 0.175 ↑ 9.0 45 1

Append (cost=0..69.03 rows=404 width=4) (actual time=0.023..0.175 rows=45 loops=1)

  • Buffers: shared hit=12
5. 0.004 0.004 ↓ 0.0 0 1

Seq Scan on teams tm (cost=0..0 rows=1 width=4) (actual time=0.003..0.004 rows=0 loops=1)

  • Filter: (tm.teamid < 250)
6. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on teams tm_1 (cost=0..16.62 rows=177 width=4) (actual time=0.001..0.001 rows=0 loops=1)

  • Filter: (tm_1.teamid < 250)
7. 0.157 0.157 ↑ 1.1 45 1

Seq Scan on teams tm_2 (cost=0..33.76 rows=49 width=4) (actual time=0.017..0.157 rows=45 loops=1)

  • Filter: (tm_2.teamid < 250)
  • Buffers: shared hit=12
8. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on teams tm_3 (cost=0..16.62 rows=177 width=4) (actual time=0.001..0.001 rows=0 loops=1)

  • Filter: (tm_3.teamid < 250)
9. 1.575 16.065 ↓ 1.9 406 45

Append (cost=0..76.48 rows=214 width=22) (actual time=0.087..0.357 rows=406 loops=45)

  • Buffers: shared hit=8076
10. 0.000 0.000 ↓ 0.0 0 45

Seq Scan on matches mtc (cost=0..0 rows=1 width=22) (actual time=0..0 rows=0 loops=45)

  • Filter: ((tm.teamid = mtc.homeid) OR (tm.teamid = mtc.awayid))
11. 0.000 0.000 ↓ 0.0 0 45

Seq Scan on matches mtc_1 (cost=0..28.45 rows=12 width=22) (actual time=0..0 rows=0 loops=45)

  • Filter: ((tm.teamid = mtc_1.homeid) OR (tm.teamid = mtc_1.awayid))
12. 11.475 14.490 ↓ 2.1 406 45

Bitmap Heap Scan on matches mtc_2 (cost=8.01..18.51 rows=189 width=22) (actual time=0.084..0.322 rows=406 loops=45)

  • Buffers: shared hit=8076
13. 0.045 3.015 ↓ 0.0 0 45

BitmapOr (cost=8.01..8.01 rows=189 width=0) (actual time=0.067..0.067 rows=0 loops=45)

  • Buffers: shared hit=855
14. 1.530 1.530 ↓ 2.1 203 45

Bitmap Index Scan on inx_soccer_matches_home_date_cover (cost=0..3.96 rows=95 width=0) (actual time=0.034..0.034 rows=203 loops=45)

  • Index Cond: (tm.teamid = mtc_2.homeid)
  • Buffers: shared hit=427
15. 1.440 1.440 ↓ 2.2 203 45

Bitmap Index Scan on inx_soccer_away_home_date_cover (cost=0..3.95 rows=94 width=0) (actual time=0.032..0.032 rows=203 loops=45)

  • Index Cond: (tm.teamid = mtc_2.awayid)
  • Buffers: shared hit=428
16. 0.000 0.000 ↓ 0.0 0 45

Seq Scan on matches mtc_3 (cost=0..28.45 rows=12 width=22) (actual time=0..0 rows=0 loops=45)

  • Filter: ((tm.teamid = mtc_3.homeid) OR (tm.teamid = mtc_3.awayid))