explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wPoy : Optimization for: plan #dH2c

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.314 6,686.826 ↑ 1,907.3 441 1

Nested Loop (cost=816.74..64,764.24 rows=841,128 width=211) (actual time=169.875..6,686.826 rows=441 loops=1)

  • Buffers: shared hit=1113459
2. 0.033 0.187 ↑ 9.0 45 1

Append (cost=0..69.03 rows=404 width=109) (actual time=0.043..0.187 rows=45 loops=1)

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

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

  • Filter: (tm.teamid < 250)
4. 0.002 0.002 ↓ 0.0 0 1

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

  • Filter: (tm_1.teamid < 250)
5. 0.147 0.147 ↑ 1.1 45 1

Seq Scan on teams tm_2 (cost=0..33.76 rows=49 width=15) (actual time=0.018..0.147 rows=45 loops=1)

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

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

  • Filter: (tm_3.teamid < 250)
7. 3.465 6,686.325 ↑ 208.2 10 45

Hash Join (cost=816.74..954.04 rows=2,082 width=102) (actual time=148.567..148.585 rows=10 loops=45)

  • Buffers: shared hit=1113447
8. 2.430 6.930 ↑ 2.0 1,063 45

Append (cost=0..59.22 rows=2,082 width=8) (actual time=0.005..0.154 rows=1,063 loops=45)

  • Buffers: shared hit=360
9. 0.000 0.000 ↓ 0.0 0 45

Seq Scan on leagues le (cost=0..0 rows=1 width=8) (actual time=0..0 rows=0 loops=45)

10. 0.000 0.000 ↓ 0.0 0 45

Seq Scan on leagues le_1 (cost=0..15.1 rows=510 width=8) (actual time=0..0 rows=0 loops=45)

11. 4.500 4.500 ↓ 1.0 1,063 45

Seq Scan on leagues le_2 (cost=0..18.61 rows=1,061 width=8) (actual time=0.004..0.1 rows=1,063 loops=45)

  • Buffers: shared hit=360
12. 0.000 0.000 ↓ 0.0 0 45

Seq Scan on leagues le_3 (cost=0..15.1 rows=510 width=8) (actual time=0..0 rows=0 loops=45)

13. 0.315 6,675.930 ↑ 20.0 10 45

Hash (cost=814.24..814.24 rows=200 width=98) (actual time=148.354..148.354 rows=10 loops=45)

  • Buffers: shared hit=1113087
14. 6,651.495 6,675.615 ↑ 20.0 10 45

Aggregate (cost=810.24..812.24 rows=200 width=98) (actual time=148.344..148.347 rows=10 loops=45)

  • Buffers: shared hit=1113087
15. 2.565 24.120 ↓ 1.9 406 45

Append (cost=0..266.15 rows=214 width=14) (actual time=0.071..0.536 rows=406 loops=45)

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

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

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

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

  • Filter: ((tm.teamid = mtc_1.homeid) OR (tm.teamid = mtc_1.awayid))
18. 19.215 21.555 ↓ 2.1 406 45

Bitmap Heap Scan on matches mtc_2 (cost=8.95..208.18 rows=189 width=14) (actual time=0.067..0.479 rows=406 loops=45)

  • Buffers: shared hit=8076
19. 0.090 2.340 ↓ 0.0 0 45

BitmapOr (cost=8.95..8.95 rows=189 width=0) (actual time=0.052..0.052 rows=0 loops=45)

  • Buffers: shared hit=855
20. 1.170 1.170 ↓ 2.1 203 45

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

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

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

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

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

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