explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zU5Dh

Settings
# exclusive inclusive rows x rows loops node
1. 45,953.277 147,129.155 ↓ 1.1 70,264 1

GroupAggregate (cost=773,487.20..843,414.86 rows=66,125 width=324) (actual time=87,766.557..147,129.155 rows=70,264 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: 2977679
  • Buffers: shared hit=25577458 read=125012 written=24, temp read=396455 written=396520
2. 54,758.192 101,175.878 ↓ 24.1 14,357,133 1

Sort (cost=773,487.20..774,975.02 rows=595,129 width=170) (actual time=87,698.968..101,175.878 rows=14,357,133 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: 2139264kB
  • Buffers: shared hit=25577458 read=125012 written=24, temp read=396455 written=396520
3. 15,195.892 46,417.686 ↓ 24.1 14,357,133 1

Hash Join (cost=126,026.52..665,550.87 rows=595,129 width=170) (actual time=1,558.662..46,417.686 rows=14,357,133 loops=1)

  • Hash Cond: (tp_1.player_id = g_1.id)
  • Buffers: shared hit=25577452 read=125012 written=24, temp read=129047 written=129047
4. 7,509.072 29,670.921 ↓ 24.1 14,357,133 1

Nested Loop (cost=1.98..503,456.10 rows=595,129 width=150) (actual time=0.691..29,670.921 rows=14,357,133 loops=1)

  • Buffers: shared hit=25577449 read=33660 written=24
5. 22.846 449.653 ↓ 5.6 29,027 1

Nested Loop (cost=1.42..188,566.66 rows=5,198 width=121) (actual time=0.658..449.653 rows=29,027 loops=1)

  • Buffers: shared hit=141397 read=13588 written=4
6. 20.449 136.537 ↓ 5.6 29,027 1

Nested Loop (cost=0.99..172,238.48 rows=5,198 width=101) (actual time=0.585..136.537 rows=29,027 loops=1)

  • Buffers: shared hit=31737 read=7060 written=1
7. 71.940 71.940 ↑ 1.2 849 1

Index Scan using tournament_finish_date_idx on tournament tt (cost=0.43..3,568.53 rows=1,049 width=24) (actual time=0.500..71.940 rows=849 loops=1)

  • Index Cond: ((COALESCE(finish_date, '9223372036854775807'::bigint) >= '1570613626491'::bigint) AND (COALESCE(finish_date, '9223372036854775807'::bigint) <= '1570700026491'::bigint))
  • Filter: ((status = ANY ('{5}'::integer[])) AND (tournament_type = ANY ('{2,1}'::integer[])))
  • Rows Removed by Filter: 7755
  • Buffers: shared hit=2708 read=5822 written=1
8. 44.148 44.148 ↑ 5.3 34 849

Index Scan using tournament_player_trnmt_fk_idx on tournament_player tp (cost=0.56..158.98 rows=181 width=77) (actual time=0.021..0.052 rows=34 loops=849)

  • Index Cond: (tournament_id = tt.id)
  • Filter: (status <> ALL ('{2}'::integer[]))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=29029 read=1238
9. 290.270 290.270 ↑ 1.0 1 29,027

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

  • Index Cond: (id = tp.player_id)
  • Buffers: shared hit=109660 read=6528 written=3
10. 21,712.196 21,712.196 ↓ 8.2 495 29,027

Index Scan using tournament_player_trnmt_fk_idx on tournament_player tp_1 (cost=0.56..59.98 rows=60 width=77) (actual time=0.012..0.748 rows=495 loops=29,027)

  • Index Cond: (tournament_id = tp.tournament_id)
  • Filter: ((status <> ALL ('{2}'::integer[])) AND (tp.player_id < player_id))
  • Rows Removed by Filter: 515
  • Buffers: shared hit=25436052 read=20072 written=20
11. 432.995 1,550.873 ↓ 1.0 1,152,744 1

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

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

  • Buffers: shared hit=3 read=91352