explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Oi4Y

Settings
# exclusive inclusive rows x rows loops node
1. 13.211 362,356.820 ↑ 652.7 8,938 1

Nested Loop (cost=816.74..443,928.99 rows=5,833,764 width=158) (actual time=580.943..362,356.82 rows=8,938 loops=1)

  • Buffers: shared hit=14710942
2. 0.563 1.833 ↑ 1.6 1,747 1

Append (cost=0..74.02 rows=2,802 width=56) (actual time=0.014..1.833 rows=1,747 loops=1)

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

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

4. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on teams tm_1 (cost=0..15.3 rows=530 width=122) (actual time=0.001..0.001 rows=0 loops=1)

5. 1.265 1.265 ↓ 1.0 1,747 1

Seq Scan on teams tm_2 (cost=0..29.41 rows=1,741 width=15) (actual time=0.01..1.265 rows=1,747 loops=1)

  • Buffers: shared hit=12
6. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on teams tm_3 (cost=0..15.3 rows=530 width=122) (actual time=0.002..0.002 rows=0 loops=1)

7. 139.516 362,341.776 ↑ 416.4 5 1,747

Hash Join (cost=816.74..954.04 rows=2,082 width=102) (actual time=207.32..207.408 rows=5 loops=1,747)

  • Buffers: shared hit=14710930
8. 100.528 286.752 ↑ 2.0 1,063 1,648

Append (cost=0..59.22 rows=2,082 width=8) (actual time=0.007..0.174 rows=1,063 loops=1,648)

  • Buffers: shared hit=13184
9. 0.000 0.000 ↓ 0.0 0 1,648

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

10. 0.000 0.000 ↓ 0.0 0 1,648

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

11. 186.224 186.224 ↓ 1.0 1,063 1,648

Seq Scan on leagues le_2 (cost=0..18.61 rows=1,061 width=8) (actual time=0.005..0.113 rows=1,063 loops=1,648)

  • Buffers: shared hit=13184
12. 0.000 0.000 ↓ 0.0 0 1,648

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

13. 10.482 361,915.508 ↑ 40.0 5 1,747

Hash (cost=814.24..814.24 rows=200 width=98) (actual time=207.164..207.164 rows=5 loops=1,747)

  • Buffers: shared hit=14697746
14. 361,349.480 361,905.026 ↑ 40.0 5 1,747

Aggregate (cost=810.24..812.24 rows=200 width=98) (actual time=207.155..207.158 rows=5 loops=1,747)

  • Buffers: shared hit=14697746
15. 82.109 555.546 ↑ 1.5 141 1,747

Append (cost=0..266.15 rows=214 width=14) (actual time=0.042..0.318 rows=141 loops=1,747)

  • Buffers: shared hit=89065
16. 0.000 0.000 ↓ 0.0 0 1,747

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

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

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

  • Filter: ((tm.teamid = mtc_1.homeid) OR (tm.teamid = mtc_1.awayid))
18. 419.280 473.437 ↑ 1.3 141 1,747

Bitmap Heap Scan on matches mtc_2 (cost=8.95..208.18 rows=189 width=14) (actual time=0.037..0.271 rows=141 loops=1,747)

  • Buffers: shared hit=89065
19. 3.494 54.157 ↓ 0.0 0 1,747

BitmapOr (cost=8.95..8.95 rows=189 width=0) (actual time=0.031..0.031 rows=0 loops=1,747)

  • Buffers: shared hit=17768
20. 27.952 27.952 ↑ 1.4 70 1,747

Bitmap Index Scan on inx_soccer_matches_home_date_cover (cost=0..4.43 rows=95 width=0) (actual time=0.016..0.016 rows=70 loops=1,747)

  • Index Cond: (tm.teamid = mtc_2.homeid)
  • Buffers: shared hit=8871
21. 22.711 22.711 ↑ 1.3 70 1,747

Bitmap Index Scan on inx_soccer_away_home_date_cover (cost=0..4.42 rows=94 width=0) (actual time=0.013..0.013 rows=70 loops=1,747)

  • Index Cond: (tm.teamid = mtc_2.awayid)
  • Buffers: shared hit=8897
22. 0.000 0.000 ↓ 0.0 0 1,747

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

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