explain.depesz.com

PostgreSQL's explain analyze made readable

Result: imKN

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.162 72,525.999 ↓ 10.0 10 1

Limit (cost=34,066.26..34,066.26 rows=1 width=1,565) (actual time=72,525.838..72,525.999 rows=10 loops=1)

  • Buffers: shared hit=19652880 read=106, temp written=1198
2. 27.965 72,525.837 ↓ 10.0 10 1

Sort (cost=34,066.26..34,066.26 rows=1 width=1,565) (actual time=72,525.836..72,525.837 rows=10 loops=1)

  • Sort Key: tem.game_date DESC, tem.id DESC
  • Sort Method: top-N heapsort Memory: 30kB
  • Buffers: shared hit=19652880 read=106, temp written=1198
3. 110.665 72,497.872 ↓ 7,355.0 7,355 1

Nested Loop (cost=29,896.62..34,066.24 rows=1 width=1,565) (actual time=143.654..72,497.872 rows=7,355 loops=1)

  • Buffers: shared hit=19652880 read=106, temp written=1198
4.          

CTE temp_report1

5. 0.000 609.299 ↓ 767.9 39,165 1

Gather (cost=1,744.35..29,896.2 rows=51 width=326) (actual time=6.512..609.299 rows=39,165 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
  • Buffers: shared hit=4062503 read=106
6. 13.437 1,097.083 ↓ 611.9 9,791 4 / 4

Nested Loop (cost=744.35..28,891.1 rows=16 width=326) (actual time=5.954..1,097.083 rows=9,791 loops=4)

  • Buffers: shared hit=4062503 read=106
7. 24.994 1,064.063 ↓ 611.9 9,791 4 / 4

Hash Join (cost=744.08..28,875.84 rows=16 width=230) (actual time=5.922..1,064.063 rows=9,791 loops=4)

  • Buffers: shared hit=3944233 read=106
8. 87.896 1,039.063 ↓ 70.0 115,724 4 / 4

Nested Loop (cost=744..28,871.24 rows=1,653 width=230) (actual time=5.178..1,039.063 rows=115,724 loops=4)

  • Buffers: shared hit=3944095 read=106
9. 85.672 603.996 ↓ 70.0 115,724 4 / 4

Nested Loop (cost=743.58..27,006.74 rows=1,653 width=217) (actual time=5.136..603.996 rows=115,724 loops=4)

  • Buffers: shared hit=2092565 read=53
10. 44.597 171.153 ↓ 70.0 115,724 4 / 4

Hash Join (cost=743.15..25,086.63 rows=1,653 width=185) (actual time=5.096..171.153 rows=115,724 loops=4)

  • Buffers: shared hit=241019 read=1
11. 122.116 122.116 ↓ 2.1 115,724 4 / 4

Index Scan using ix_game_history_reports_game_date on game_history_reports gh (cost=0.57..23,624.85 rows=56,214 width=155) (actual time=0.639..122.116 rows=115,724 loops=4)

  • Index Cond: ((gh.game_date >= '2020-01-11 00:00:00+08'::timestamp with time zone) AND (gh.game_date < '2020-01-11 23:59:59.999+08'::timestamp with time zone))
  • Buffers: shared hit=238147 read=1
12. 0.079 4.440 ↓ 34.0 272 4 / 4

Hash (cost=742.48..742.48 rows=8 width=30) (actual time=4.44..4.44 rows=272 loops=4)

  • Buffers: shared hit=2872
13. 4.361 4.361 ↓ 34.0 272 4 / 4

Seq Scan on game_strings gs (cost=0..742.48 rows=8 width=30) (actual time=0.054..4.361 rows=272 loops=4)

  • Filter: (lower((gs.locale)::text) = 'zh-cn'::text)
  • Buffers: shared hit=2872
14. 347.171 347.171 ↑ 1.0 1 462,895 / 4

Index Scan using pk_casino_players on casino_players cp (cost=0.43..1.16 rows=1 width=48) (actual time=0.003..0.003 rows=1 loops=462,895)

  • Index Cond: (cp.id = gh.casino_player_id)
  • Buffers: shared hit=1851546 read=52
15. 347.171 347.171 ↑ 1.0 1 462,895 / 4

Index Scan using pk_players on players p (cost=0.43..1.13 rows=1 width=45) (actual time=0.003..0.003 rows=1 loops=462,895)

  • Index Cond: (p.id = cp.player_id)
  • Buffers: shared hit=1851530 read=53
16. 0.004 0.006 ↑ 1.0 3 4 / 4

Hash (cost=0.04..0.04 rows=3 width=16) (actual time=0.006..0.006 rows=3 loops=4)

17. 0.002 0.002 ↑ 1.0 3 4 / 4

Values Scan (cost=0..0.04 rows=3 width=16) (actual time=0.001..0.002 rows=3 loops=4)

18. 19.582 19.582 ↑ 1.0 1 39,165 / 4

Index Scan using pk_games on games g (cost=0.27..0.94 rows=1 width=28) (actual time=0.002..0.002 rows=1 loops=39,165)

  • Index Cond: (g.id = gh.game_id)
  • Buffers: shared hit=118270
19. 15.157 72,195.977 ↓ 7,355.0 7,355 1

Nested Loop (cost=0.42..4,169.32 rows=1 width=1,389) (actual time=143.628..72,195.977 rows=7,355 loops=1)

  • Buffers: shared hit=19652880 read=106, temp written=1198
20. 23.419 72,158.755 ↓ 7,355.0 7,355 1

Nested Loop (cost=0.42..4,169.24 rows=1 width=1,357) (actual time=143.622..72,158.755 rows=7,355 loops=1)

  • Buffers: shared hit=19652880 read=106, temp written=1198
21. 39.785 72,091.206 ↓ 7,355.0 7,355 1

Nested Loop (cost=0.42..4,169.12 rows=1 width=1,325) (actual time=143.612..72,091.206 rows=7,355 loops=1)

  • Buffers: shared hit=19652880 read=106, temp written=1198
22. 788.826 788.826 ↓ 7,355.0 7,355 1

CTE Scan on temp_report1 tem (cost=0..1.15 rows=1 width=1,312) (actual time=124.924..788.826 rows=7,355 loops=1)

  • Filter: ((tem.player_name)::text ~~* '%agcf157057%'::text)
  • Buffers: shared hit=4062503 read=106, temp written=1198
23. 71,262.595 71,262.595 ↓ 0.0 0 7,355

Index Scan using ux_side_bets_reports_ddc_casino_id_game_number_unique_ids on side_bets_reports sbr (cost=0.42..4,167.96 rows=1 width=53) (actual time=9.689..9.689 rows=0 loops=7,355)

  • Index Cond: (tem.game_number = sbr.game_number)
  • Filter: ((tem.casino_player_id = sbr.casino_player_id) AND ((tem.game_name_id)::uuid = sbr.game_id))
  • Buffers: shared hit=15590377
24. 44.130 44.130 ↑ 1.0 5 7,355

Values Scan (cost=0..0.06 rows=5 width=48) (actual time=0.003..0.006 rows=5 loops=7,355)

25. 22.065 22.065 ↑ 1.0 3 7,355

Values Scan (cost=0..0.04 rows=3 width=36) (actual time=0.002..0.003 rows=3 loops=7,355)

26. 191.230 191.230 ↑ 1.0 25 7,355

Values Scan (cost=0..0.31 rows=25 width=64) (actual time=0.003..0.026 rows=25 loops=7,355)

Planning time : 2.85 ms
Execution time : 72,527.718 ms