explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2tAB

Settings
# exclusive inclusive rows x rows loops node
1. 94,917.967 283,606.840 ↑ 1.4 174,166 1

GroupAggregate (cost=1,209,692.53..1,469,956.44 rows=246,112 width=324) (actual time=158,025.244..283,606.840 rows=174,166 loops=1)

  • Group Key: tt.tournament_type, tp.player_id, g.user_id, g.user_type, tp_1.player_id, g_1.user_id, g_1.user_type
  • Filter: ((count(*) >= '20'::bigint) AND (count(*) <= '9223372036854775807'::bigint))
  • Rows Removed by Filter: 5981838
  • Buffers: shared hit=26071537 read=115038 written=3, temp read=822640 written=822775
2. 117,491.022 188,688.873 ↓ 13.5 29,807,496 1

Sort (cost=1,209,692.53..1,215,230.06 rows=2,215,012 width=170) (actual time=158,016.727..188,688.873 rows=29,807,496 loops=1)

  • Sort Key: tt.tournament_type, tp.player_id, g.user_id, g.user_type, tp_1.player_id, g_1.user_id, g_1.user_type
  • Sort Method: external merge Disk: 4441160kB
  • Buffers: shared hit=26071537 read=115038 written=3, temp read=822640 written=822775
3. 30,709.794 71,197.851 ↓ 13.5 29,807,496 1

Hash Join (cost=126,026.52..786,969.63 rows=2,215,012 width=170) (actual time=1,621.237..71,197.851 rows=29,807,496 loops=1)

  • Hash Cond: (tp_1.player_id = g_1.id)
  • Buffers: shared hit=26071531 read=115038 written=3, temp read=267495 written=267495
4. 16,079.579 38,872.652 ↓ 13.5 29,807,496 1

Nested Loop (cost=1.98..551,016.66 rows=2,215,012 width=150) (actual time=0.568..38,872.652 rows=29,807,496 loops=1)

  • Buffers: shared hit=26066418 read=28796 written=3
5. 36.032 377.001 ↓ 5.2 29,928 1

Nested Loop (cost=1.42..190,541.83 rows=5,793 width=121) (actual time=0.525..377.001 rows=29,928 loops=1)

  • Buffers: shared hit=149020 read=9843 written=1
6. 21.909 131.473 ↓ 5.2 29,928 1

Nested Loop (cost=0.99..172,350.22 rows=5,793 width=101) (actual time=0.484..131.473 rows=29,928 loops=1)

  • Buffers: shared hit=32216 read=6852 written=1
7. 69.285 69.285 ↑ 1.2 857 1

Index Scan using tournament_finish_date_idx on tournament tt (cost=0.43..3,571.21 rows=1,050 width=24) (actual time=0.368..69.285 rows=857 loops=1)

  • Index Cond: ((COALESCE(finish_date, '9223372036854775807'::bigint) >= '1570612518715'::bigint) AND (COALESCE(finish_date, '9223372036854775807'::bigint) <= '1570698918715'::bigint))
  • Filter: ((status = ANY ('{5}'::integer[])) AND (tournament_type = ANY ('{2,1}'::integer[])))
  • Rows Removed by Filter: 7812
  • Buffers: shared hit=2964 read=5624
8. 40.279 40.279 ↑ 5.8 35 857

Index Scan using tournament_player_trnmt_fk_idx on tournament_player tp (cost=0.56..158.72 rows=202 width=77) (actual time=0.021..0.047 rows=35 loops=857)

  • Index Cond: (tournament_id = tt.id)
  • Buffers: shared hit=29252 read=1228 written=1
9. 209.496 209.496 ↑ 1.0 1 29,928

Index Scan using pk_gamer on gamer g (cost=0.43..3.14 rows=1 width=36) (actual time=0.007..0.007 rows=1 loops=29,928)

  • Index Cond: (id = tp.player_id)
  • Buffers: shared hit=116804 read=2991
10. 22,416.072 22,416.072 ↓ 4.9 996 29,928

Index Scan using tournament_player_trnmt_fk_idx on tournament_player tp_1 (cost=0.56..60.21 rows=202 width=77) (actual time=0.007..0.749 rows=996 loops=29,928)

  • Index Cond: (tournament_id = tp.tournament_id)
  • Filter: ((tp.player_id < player_id) OR (tp.player_id <> player_id))
  • Rows Removed by Filter: 2
  • Buffers: shared hit=25917398 read=18953 written=2
11. 406.813 1,615.405 ↓ 1.0 1,152,744 1

Hash (cost=102,792.13..102,792.13 rows=1,143,713 width=36) (actual time=1,614.961..1,615.405 rows=1,152,744 loops=1)

  • Buckets: 1048576 Batches: 2 Memory Usage: 46429kB
  • Buffers: shared hit=5113 read=86242, temp written=3944
12. 1,208.592 1,208.592 ↓ 1.0 1,152,744 1

Seq Scan on gamer g_1 (cost=0.00..102,792.13 rows=1,143,713 width=36) (actual time=0.008..1,208.592 rows=1,152,744 loops=1)

  • Buffers: shared hit=5113 read=86242