explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 6,731.997 6,758.891 ↑ 1,144.8 441 1

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

  • Buffers: shared hit=1113109
2. 9.189 26.894 ↑ 27.7 18,250 1

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

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

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

  • Buffers: shared hit=8088
4. 0.010 0.141 ↑ 9.0 45 1

Append (cost=0..69.03 rows=404 width=4) (actual time=0.019..0.141 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.126 0.126 ↑ 1.1 45 1

Seq Scan on teams tm_2 (cost=0..33.76 rows=49 width=4) (actual time=0.013..0.126 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.755 15.345 ↓ 1.9 406 45

Append (cost=0..76.48 rows=214 width=22) (actual time=0.079..0.341 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.025 13.590 ↓ 2.1 406 45

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

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

BitmapOr (cost=8.01..8.01 rows=189 width=0) (actual time=0.057..0.057 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.215 1.215 ↓ 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.027..0.027 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))