explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 6,666.607 6,697.335 ↑ 174.7 441 1

Aggregate (cost=227,881.41..228,651.75 rows=77,034 width=102) (actual time=6,696.873..6,697.335 rows=441 loops=1)

  • Buffers: shared hit=1112498
2. 4.574 30.728 ↑ 4.2 18,250 1

Nested Loop (cost=0..31,829.88 rows=77,034 width=18) (actual time=0.207..30.728 rows=18,250 loops=1)

  • Buffers: shared hit=8088
3. 0.019 0.189 ↑ 9.0 45 1

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

  • Buffers: shared hit=12
4. 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)
5. 0.002 0.002 ↓ 0.0 0 1

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

  • Filter: (tm_1.teamid < 250)
6. 0.164 0.164 ↑ 1.1 45 1

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

  • Filter: (tm_2.teamid < 250)
  • Buffers: shared hit=12
7. 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)
8. 2.835 25.965 ↓ 1.9 406 45

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

  • Buffers: shared hit=8076
9. 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))
10. 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))
11. 20.700 23.130 ↓ 2.1 406 45

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

  • Buffers: shared hit=8076
12. 0.090 2.430 ↓ 0.0 0 45

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

  • Buffers: shared hit=855
13. 1.215 1.215 ↓ 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.027..0.027 rows=203 loops=45)

  • Index Cond: (tm.teamid = mtc_2.homeid)
  • Buffers: shared hit=427
14. 1.125 1.125 ↓ 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.025..0.025 rows=203 loops=45)

  • Index Cond: (tm.teamid = mtc_2.awayid)
  • Buffers: shared hit=428
15. 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))