explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 6,621.372 6,651.624 ↑ 1,144.8 441 1

Aggregate (cost=79,653.05..1,370,806.93 rows=504,850 width=102) (actual time=47.335..6,651.624 rows=441 loops=1)

  • Buffers: shared hit=1113109
2. 11.056 30.252 ↑ 27.7 18,250 1

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

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

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

  • Buffers: shared hit=8088
4. 0.011 0.159 ↑ 9.0 45 1

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

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

Seq Scan on teams tm (cost=0..0 rows=1 width=4) (actual time=0.003..0.003 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.142 0.142 ↑ 1.1 45 1

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

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

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

  • Filter: (tm_3.teamid < 250)
9. 1.890 16.605 ↓ 1.9 406 45

Append (cost=0..76.48 rows=214 width=22) (actual time=0.081..0.369 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. 12.105 14.715 ↓ 2.1 406 45

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

  • Heap Blocks: exact=7221
  • Buffers: shared hit=8076
13. 0.045 2.610 ↓ 0.0 0 45

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

  • Buffers: shared hit=855
14. 1.305 1.305 ↓ 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.029..0.029 rows=203 loops=45)

  • Index Cond: (tm.teamid = mtc_2.homeid)
  • Buffers: shared hit=427
15. 1.260 1.260 ↓ 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.028..0.028 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))
Planning time : 2.713 ms
Execution time : 6,651.927 ms