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: 2,977,679
  • Buffers: shared hit=25,577,458 read=125,012 written=24, temp read=396,455 written=396,520
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: 2,139,264kB
  • Buffers: shared hit=25,577,458 read=125,012 written=24, temp read=396,455 written=396,520
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=25,577,452 read=125,012 written=24, temp read=129,047 written=129,047
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=25,577,449 read=33,660 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=141,397 read=13,588 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=31,737 read=7,060 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: 7,755
  • Buffers: shared hit=2,708 read=5,822 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=29,029 read=1,238
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=109,660 read=6,528 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=25,436,052 read=20,072 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: 1,048,576 Batches: 2 Memory Usage: 46,429kB
  • Buffers: shared hit=3 read=91,352, temp written=3,944
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=91,352