explain.depesz.com

PostgreSQL's explain analyze made readable

Result: abMi

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.000 5,063.836 ↑ 4.8 2,381 1

WindowAgg (cost=97,909.24..101,746.67 rows=11,455 width=663) (actual time=5,003.013..5,063.836 rows=2,381 loops=1)

2. 5,003.783 5,003.783 ↑ 4.8 2,381 1

Sort (cost=97,909.24..97,937.88 rows=11,455 width=2,311) (actual time=5,002.980..5,003.783 rows=2,381 loops=1)

3. 0.000 4,999.864 ↑ 4.8 2,381 1

GroupAggregate (cost=91,266.28..97,136.96 rows=11,455 width=2,311) (actual time=4,772.619..4,999.864 rows=2,381 loops=1)

  • Group Key: event.id, play_pattern_lookup.play_pattern_name
4. 67.197 4,776.504 ↑ 1.4 8,322 1

Sort (cost=91,266.28..91,294.91 rows=11,455 width=224) (actual time=4,772.586..4,776.504 rows=8,322 loops=1)

  • Sort Key: event.id, play_pattern_lookup.play_pattern_name
  • Sort Method: quicksort Memory: 2692kB
5. 4,709.307 4,709.307 ↑ 1.4 8,322 1

Gather (cost=1,118.04..90,494.00 rows=11,455 width=224) (actual time=91.898..4,709.307 rows=8,322 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
6. 9,020.264 9,074.676 ↑ 1.6 4,161 2

Nested Loop Left Join (cost=118.04..88,348.50 rows=6,738 width=224) (actual time=22.300..4,537.338 rows=4,161 loops=2)

7. 54.412 54.412 ↑ 1.5 1,190 2

Hash Left Join (cost=114.29..11,472.67 rows=1,748 width=199) (actual time=17.450..27.206 rows=1,190 loops=2)

  • Hash Cond: (event.play_pattern_id = play_pattern_lookup.play_pattern_id)
8. 50.066 50.066 ↑ 1.5 1,190 2

Parallel Bitmap Heap Scan on event (cost=83.59..11,437.36 rows=1,748 width=151) (actual time=17.369..25.033 rows=1,190 loops=2)

  • Recheck Cond: (match_id = 15316)
  • Heap Blocks: exact=29
9. 33.942 34.014 ↑ 1.2 2,381 1

Bitmap Index Scan on statsbomb_event__match_id (cost=0.00..82.84 rows=2,971 width=0) (actual time=34.014..34.014 rows=2,381 loops=1)

  • Index Cond: (match_id = 15316)
10. 0.044 0.044 ↑ 102.2 9 2

Hash (cost=19.20..19.20 rows=920 width=52) (actual time=0.022..0.022 rows=9 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
11. 0.000 0.028 ↑ 102.2 9 2

Seq Scan on play_pattern_lkup play_pattern_lookup (cost=0.00..19.20 rows=920 width=52) (actual time=0.011..0.014 rows=9 loops=2)

12. 8,995.418 8,995.418 ↑ 3.3 3 2,381

Hash Left Join (cost=3.75..47.63 rows=10 width=62) (actual time=3.647..3.778 rows=3 loops=2,381)

  • Hash Cond: ((qualifier.value)::text = (qualifier_lookup.id)::text)
13. 8,980.862 8,981.132 ↑ 3.3 3 2,381

Index Scan using event_attribute_ix_id on event_attribute qualifier (cost=0.57..44.37 rows=10 width=52) (actual time=3.644..3.772 rows=3 loops=2,381)

  • Index Cond: (event.id = id)
14. 0.188 0.188 ↑ 1.0 97 2

Hash (cost=1.97..1.97 rows=97 width=12) (actual time=0.094..0.094 rows=97 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
15. 0.082 0.082 ↑ 1.0 97 2

Seq Scan on event_attribute_lkup qualifier_lookup (cost=0.00..1.97 rows=97 width=12) (actual time=0.015..0.041 rows=97 loops=2)

Planning time : 2.438 ms
Execution time : 5,099.676 ms