explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ScmI

Settings
# exclusive inclusive rows x rows loops node
1. 92,711.248 281,105.434 ↑ 1.4 168,544 1

GroupAggregate (cost=1,151,447.16..1,397,783.56 rows=232,942 width=324) (actual time=158,622.204..281,105.434 rows=168,544 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: 5981108
  • Buffers: shared hit=22820260 read=124863, temp read=789354 written=789485
2. 117,557.520 188,394.186 ↓ 13.7 28,751,478 1

Sort (cost=1,151,447.16..1,156,688.36 rows=2,096,480 width=170) (actual time=158,598.103..188,394.186 rows=28,751,478 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: 4287296kB
  • Buffers: shared hit=22820260 read=124863, temp read=789354 written=789485
3. 29,306.042 70,836.666 ↓ 13.7 28,751,478 1

Hash Join (cost=126,026.52..752,177.60 rows=2,096,480 width=170) (actual time=1,461.614..70,836.666 rows=28,751,478 loops=1)

  • Hash Cond: (tp_1.player_id = g_1.id)
  • Buffers: shared hit=22820254 read=124863, temp read=253442 written=253442
4. 15,311.412 40,076.015 ↓ 13.7 28,751,478 1

Nested Loop (cost=1.98..521,629.78 rows=2,096,480 width=150) (actual time=0.576..40,076.015 rows=28,751,478 loops=1)

  • Buffers: shared hit=22820251 read=33511
5. 22.803 2,035.853 ↓ 5.5 30,305 1

Nested Loop (cost=1.42..180,442.57 rows=5,483 width=121) (actual time=0.565..2,035.853 rows=30,305 loops=1)

  • Buffers: shared hit=146961 read=12273
6. 22.679 1,194.815 ↓ 5.5 30,305 1

Nested Loop (cost=0.99..163,224.45 rows=5,483 width=101) (actual time=0.479..1,194.815 rows=30,305 loops=1)

  • Buffers: shared hit=29505 read=8407
7. 958.296 958.296 ↑ 1.0 972 1

Index Scan using tournament_finish_date_idx on tournament tt (cost=0.43..3,381.38 rows=994 width=24) (actual time=0.360..958.296 rows=972 loops=1)

  • Index Cond: ((COALESCE(finish_date, '9223372036854775807'::bigint) >= '1570695605448'::bigint) AND (COALESCE(finish_date, '9223372036854775807'::bigint) <= '1570782005448'::bigint))
  • Filter: ((status = ANY ('{5}'::integer[])) AND (tournament_type = ANY ('{2,1}'::integer[])))
  • Rows Removed by Filter: 8230
  • Buffers: shared hit=2114 read=6998
8. 213.840 213.840 ↑ 6.5 31 972

Index Scan using tournament_player_trnmt_fk_idx on tournament_player tp (cost=0.56..158.79 rows=202 width=77) (actual time=0.197..0.220 rows=31 loops=972)

  • Index Cond: (tournament_id = tt.id)
  • Buffers: shared hit=27391 read=1409
9. 818.235 818.235 ↑ 1.0 1 30,305

Index Scan using pk_gamer on gamer g (cost=0.43..3.14 rows=1 width=36) (actual time=0.027..0.027 rows=1 loops=30,305)

  • Index Cond: (id = tp.player_id)
  • Buffers: shared hit=117456 read=3866
10. 22,728.750 22,728.750 ↓ 4.7 949 30,305

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.011..0.750 rows=949 loops=30,305)

  • 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=22673290 read=21238
11. 403.502 1,454.609 ↓ 1.0 1,152,744 1

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

  • Buckets: 1048576 Batches: 2 Memory Usage: 46429kB
  • Buffers: shared hit=3 read=91352, temp written=3944
12. 1,051.107 1,051.107 ↓ 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.007..1,051.107 rows=1,152,744 loops=1)

  • Buffers: shared hit=3 read=91352