explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aFBB

Settings
# exclusive inclusive rows x rows loops node
1. 10.188 13,308.766 ↓ 156.8 4,391 1

Nested Loop (cost=316.86..914,274.73 rows=28 width=158) (actual time=13,167.468..13,308.766 rows=4,391 loops=1)

2. 106.602 13,294.187 ↓ 156.8 4,391 1

Nested Loop (cost=316.58..914,040.51 rows=28 width=141) (actual time=13,167.367..13,294.187 rows=4,391 loops=1)

  • Join Filter: (match.competition_id = competition.competition_id)
  • Rows Removed by Join Filter: 509,356
3. 0.164 0.164 ↑ 1.0 117 1

Seq Scan on competition (cost=0.00..4.17 rows=117 width=29) (actual time=0.011..0.164 rows=117 loops=1)

4. 62.188 13,187.421 ↓ 156.8 4,391 117

Materialize (cost=316.58..913,987.27 rows=28 width=118) (actual time=0.039..112.713 rows=4,391 loops=117)

5. 18.278 13,125.233 ↓ 156.8 4,391 1

Nested Loop Left Join (cost=316.58..913,987.13 rows=28 width=118) (actual time=4.554..13,125.233 rows=4,391 loops=1)

  • Join Filter: (touch.event_type = 'ball_receipt'::event_type)
  • Rows Removed by Join Filter: 734
6. 275.897 7,363.527 ↓ 156.8 4,391 1

Nested Loop (cost=316.01..913,346.02 rows=28 width=109) (actual time=3.240..7,363.527 rows=4,391 loops=1)

  • Join Filter: (match.match_id = corner.match_id)
7. 862.503 2,311.314 ↓ 40.2 1,194,079 1

Nested Loop (cost=315.44..622,395.26 rows=29,667 width=100) (actual time=1.043..2,311.314 rows=1,194,079 loops=1)

8. 2.884 49.959 ↓ 18.2 546 1

Hash Join (cost=84.62..4,673.47 rows=30 width=75) (actual time=0.432..49.959 rows=546 loops=1)

  • Hash Cond: ((match.competition_id = license.competition_id) AND (match.season_id = season.season_id))
9. 30.730 46.754 ↓ 9.1 1,289 1

Nested Loop (cost=0.28..4,587.32 rows=141 width=59) (actual time=0.085..46.754 rows=1,289 loops=1)

  • Join Filter: ((team.team_id = match.match_home_team_id) OR (team.team_id = match.match_away_team_id))
  • Rows Removed by Join Filter: 115,032
10. 0.017 0.017 ↑ 1.0 1 1

Index Scan using team_pkey on team (cost=0.28..8.29 rows=1 width=33) (actual time=0.016..0.017 rows=1 loops=1)

  • Index Cond: (team_id = 667)
11. 16.007 16.007 ↑ 1.0 116,321 1

Seq Scan on match (cost=0.00..2,834.21 rows=116,321 width=26) (actual time=0.005..16.007 rows=116,321 loops=1)

12. 0.050 0.321 ↑ 1.0 172 1

Hash (cost=81.72..81.72 rows=175 width=24) (actual time=0.321..0.321 rows=172 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 19kB
13. 0.083 0.271 ↑ 1.0 172 1

Hash Join (cost=8.13..81.72 rows=175 width=24) (actual time=0.135..0.271 rows=172 loops=1)

  • Hash Cond: (license.season_id = season.season_id)
14. 0.084 0.138 ↑ 1.0 172 1

Bitmap Heap Scan on license (cost=5.64..76.83 rows=175 width=10) (actual time=0.063..0.138 rows=172 loops=1)

  • Recheck Cond: (account_id = 48)
  • Heap Blocks: exact=17
15. 0.054 0.054 ↑ 1.0 172 1

Bitmap Index Scan on license_pkey (cost=0.00..5.60 rows=175 width=0) (actual time=0.054..0.054 rows=172 loops=1)

  • Index Cond: (account_id = 48)
16. 0.027 0.050 ↑ 1.0 66 1

Hash (cost=1.66..1.66 rows=66 width=14) (actual time=0.050..0.050 rows=66 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
17. 0.023 0.023 ↑ 1.0 66 1

Seq Scan on season (cost=0.00..1.66 rows=66 width=14) (actual time=0.011..0.023 rows=66 loops=1)

18. 1,141.686 1,398.852 ↑ 2.3 2,187 546

Bitmap Heap Scan on event touch (cost=230.82..20,540.26 rows=5,047 width=25) (actual time=0.529..2.562 rows=2,187 loops=546)

  • Recheck Cond: (match_id = match.match_id)
  • Filter: (event_type = ANY ('{pass,take_on,tackle,interception,save,claim,clearance,miss,post,attempt_saved,goal,punch,good_skill,aerial,ball_recovery,keeper_pick_up,smother,keeper_sweeper,ball_touch,blocked_pass,other_ball_contact,ball_receipt,blocked_shot,attempt_blocked,attempt_saved_to_post,attempt_saved_off_target}'::event_type[]))
  • Rows Removed by Filter: 1,328
  • Heap Blocks: exact=206,313
19. 257.166 257.166 ↑ 1.9 3,514 546

Bitmap Index Scan on event_match_id_team_id_qualifier_related_team_index (cost=0.00..229.56 rows=6,734 width=0) (actual time=0.471..0.471 rows=3,514 loops=546)

  • Index Cond: (match_id = match.match_id)
20. 4,776.316 4,776.316 ↓ 0.0 0 1,194,079

Index Scan using event_match_id_event_match_index_idx on event corner (cost=0.57..9.79 rows=1 width=21) (actual time=0.004..0.004 rows=0 loops=1,194,079)

  • Index Cond: ((match_id = touch.match_id) AND (event_match_index = (touch.event_match_index - 1)))
  • Filter: (qualifier_corner_taken AND (touch.period_id = period_id))
  • Rows Removed by Filter: 1
21. 5,743.428 5,743.428 ↑ 1.0 1 4,391

Index Scan using event_match_id_team_id_qualifier_related_team_index on event second_touch (cost=0.57..22.88 rows=1 width=25) (actual time=0.999..1.308 rows=1 loops=4,391)

  • Index Cond: ((corner.match_id = match_id) AND (team_id = touch.team_id))
  • Filter: ((corner.period_id = period_id) AND (corner.event_match_index = (event_match_index - 2)))
  • Rows Removed by Filter: 2,080
22. 4.391 4.391 ↑ 1.0 1 4,391

Index Scan using team_pkey on team opposition (cost=0.28..8.30 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=4,391)

  • Index Cond: (team_id = CASE team.team_id WHEN match.match_home_team_id THEN match.match_away_team_id ELSE match.match_home_team_id END)
Planning time : 7.941 ms
Execution time : 13,310.009 ms