explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kWuS : Optimization for: plan #vNBr

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.006 629.707 ↑ 1.0 1 1

Limit (cost=816.74..816.82 rows=1 width=158) (actual time=629.702..629.707 rows=1 loops=1)

  • Buffers: shared hit=24307
2. 0.004 629.701 ↑ 5,833,764.0 1 1

Nested Loop (cost=816.74..443,928.99 rows=5,833,764 width=158) (actual time=629.701..629.701 rows=1 loops=1)

  • Buffers: shared hit=24307
3. 0.002 0.015 ↑ 2,802.0 1 1

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

  • Buffers: shared hit=1
4. 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)

5. 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)

6. 0.010 0.010 ↑ 1,741.0 1 1

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

  • Buffers: shared hit=1
7. 0.000 0.000 ↓ 0.0 0 0

Seq Scan on teams tm_3 (cost=0..15.3 rows=530 width=122) (never executed)

8. 0.099 629.682 ↑ 2,082.0 1 1

Hash Join (cost=816.74..954.04 rows=2,082 width=102) (actual time=629.682..629.682 rows=1 loops=1)

  • Buffers: shared hit=24306
9. 0.071 0.218 ↑ 2.0 1,042 1

Append (cost=0..59.22 rows=2,082 width=8) (actual time=0.01..0.218 rows=1,042 loops=1)

  • Buffers: shared hit=8
10. 0.001 0.001 ↓ 0.0 0 1

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

11. 0.001 0.001 ↓ 0.0 0 1

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

12. 0.145 0.145 ↑ 1.0 1,042 1

Seq Scan on leagues le_2 (cost=0..18.61 rows=1,061 width=8) (actual time=0.007..0.145 rows=1,042 loops=1)

  • Buffers: shared hit=8
13. 0.000 0.000 ↓ 0.0 0 0

Seq Scan on leagues le_3 (cost=0..15.1 rows=510 width=8) (never executed)

14. 0.005 629.365 ↑ 20.0 10 1

Hash (cost=814.24..814.24 rows=200 width=98) (actual time=629.365..629.365 rows=10 loops=1)

  • Buffers: shared hit=24298
15. 628.160 629.360 ↑ 20.0 10 1

Aggregate (cost=810.24..812.24 rows=200 width=98) (actual time=629.355..629.36 rows=10 loops=1)

  • Buffers: shared hit=24298
16. 0.112 1.200 ↓ 1.9 396 1

Append (cost=0..266.15 rows=214 width=14) (actual time=0.14..1.2 rows=396 loops=1)

  • Buffers: shared hit=216
17. 0.001 0.001 ↓ 0.0 0 1

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

  • Filter: ((tm.teamid = mtc.homeid) OR (tm.teamid = mtc.awayid))
18. 0.001 0.001 ↓ 0.0 0 1

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

  • Filter: ((tm.teamid = mtc_1.homeid) OR (tm.teamid = mtc_1.awayid))
19. 0.983 1.084 ↓ 2.1 396 1

Bitmap Heap Scan on matches mtc_2 (cost=8.95..208.18 rows=189 width=14) (actual time=0.136..1.084 rows=396 loops=1)

  • Buffers: shared hit=216
20. 0.003 0.101 ↓ 0.0 0 1

BitmapOr (cost=8.95..8.95 rows=189 width=0) (actual time=0.101..0.101 rows=0 loops=1)

  • Buffers: shared hit=24
21. 0.050 0.050 ↓ 2.1 198 1

Bitmap Index Scan on inx_soccer_matches_home_date_cover (cost=0..4.43 rows=95 width=0) (actual time=0.05..0.05 rows=198 loops=1)

  • Index Cond: (tm.teamid = mtc_2.homeid)
  • Buffers: shared hit=12
22. 0.048 0.048 ↓ 2.1 198 1

Bitmap Index Scan on inx_soccer_away_home_date_cover (cost=0..4.42 rows=94 width=0) (actual time=0.048..0.048 rows=198 loops=1)

  • Index Cond: (tm.teamid = mtc_2.awayid)
  • Buffers: shared hit=12
23. 0.002 0.002 ↓ 0.0 0 1

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

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