explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZyxE : Optimization for: plan #imKN

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.000 848.575 ↓ 0.0 0 1

Limit (cost=65,326.29..65,326.3 rows=1 width=1,565) (actual time=848.575..848.575 rows=0 loops=1)

  • Buffers: shared hit=3419930, temp written=2138
2. 0.027 848.575 ↓ 0.0 0 1

Sort (cost=65,326.29..65,326.3 rows=1 width=1,565) (actual time=848.574..848.575 rows=0 loops=1)

  • Sort Key: tem.game_date DESC, tem.id DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=3419930, temp written=2138
3. 0.000 848.548 ↓ 0.0 0 1

Nested Loop (cost=61,153.92..65,326.27 rows=1 width=1,565) (actual time=848.548..848.548 rows=0 loops=1)

  • Buffers: shared hit=3419924, temp written=2138
4.          

CTE temp_report1

5. 76.739 708.735 ↓ 626.7 70,186 1

Nested Loop (cost=1,744.35..61,153.5 rows=112 width=326) (actual time=2.528..708.735 rows=70,186 loops=1)

  • Buffers: shared hit=3419924
6. 0.000 561.810 ↓ 626.7 70,186 1

Gather (cost=1,744.08..61,046.83 rows=112 width=230) (actual time=2.515..561.81 rows=70,186 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
  • Buffers: shared hit=3209366
7. 20.209 743.682 ↓ 487.4 17,546 4 / 4

Hash Join (cost=744.08..60,035.63 rows=36 width=230) (actual time=4.565..743.682 rows=17,546 loops=4)

  • Buffers: shared hit=3209366
8. 18.505 723.466 ↓ 26.6 95,524 4 / 4

Nested Loop (cost=744..60,025.71 rows=3,597 width=230) (actual time=4.242..723.466 rows=95,524 loops=4)

  • Buffers: shared hit=3209225
9. 11.295 418.390 ↓ 26.6 95,524 4 / 4

Nested Loop (cost=743.58..55,968.49 rows=3,597 width=217) (actual time=4.206..418.39 rows=95,524 loops=4)

  • Buffers: shared hit=1680780
10. 33.327 120.524 ↓ 26.6 95,524 4 / 4

Hash Join (cost=743.15..51,904.65 rows=3,597 width=185) (actual time=4.17..120.524 rows=95,524 loops=4)

  • Buffers: shared hit=152397
11. 83.072 83.072 ↑ 1.3 95,524 4 / 4

Index Scan using ix_game_history_reports_game_date on game_history_reports gh (cost=0.57..49,597.18 rows=122,314 width=155) (actual time=0.029..83.072 rows=95,524 loops=4)

  • Index Cond: ((gh.game_date >= '2019-12-04 00:00:00+08'::timestamp with time zone) AND (gh.game_date < '2019-12-04 23:59:59.999+08'::timestamp with time zone))
  • Buffers: shared hit=149525
12. 0.089 4.125 ↓ 34.0 272 4 / 4

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

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

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

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

Index Scan using pk_casino_players on casino_players cp (cost=0.43..1.13 rows=1 width=48) (actual time=0.003..0.003 rows=1 loops=382,095)

  • Index Cond: (cp.id = gh.casino_player_id)
  • Buffers: shared hit=1528383
15. 286.571 286.571 ↑ 1.0 1 382,095 / 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=382,095)

  • Index Cond: (p.id = cp.player_id)
  • Buffers: shared hit=1528445
16. 0.005 0.007 ↑ 1.0 3 4 / 4

Hash (cost=0.04..0.04 rows=3 width=16) (actual time=0.007..0.007 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. 70.186 70.186 ↑ 1.0 1 70,186

Index Scan using pk_games on games g (cost=0.27..0.94 rows=1 width=28) (actual time=0.001..0.001 rows=1 loops=70,186)

  • Index Cond: (g.id = gh.game_id)
  • Buffers: shared hit=210558
19. 0.001 848.548 ↓ 0.0 0 1

Nested Loop (cost=0.42..4,172.05 rows=1 width=1,389) (actual time=848.548..848.548 rows=0 loops=1)

  • Buffers: shared hit=3419924, temp written=2138
20. 0.000 848.547 ↓ 0.0 0 1

Nested Loop (cost=0.42..4,171.98 rows=1 width=1,357) (actual time=848.547..848.547 rows=0 loops=1)

  • Buffers: shared hit=3419924, temp written=2138
21. 0.001 848.547 ↓ 0.0 0 1

Nested Loop (cost=0..2.65 rows=1 width=1,344) (actual time=848.547..848.547 rows=0 loops=1)

  • Buffers: shared hit=3419924, temp written=2138
22. 848.546 848.546 ↓ 0.0 0 1

CTE Scan on temp_report1 tem (cost=0..2.52 rows=1 width=1,312) (actual time=848.546..848.546 rows=0 loops=1)

  • Filter: ((tem.player_name)::text ~~* '%agcf157057%'::text)
  • Buffers: shared hit=3419924, temp written=2138
23. 0.000 0.000 ↓ 0.0 0 0

Values Scan (cost=0..0.06 rows=5 width=48) (never executed)

24. 0.000 0.000 ↓ 0.0 0 0

Index Scan using ux_side_bets_reports_ddc_casino_id_game_number_unique_ids on side_bets_reports sbr (cost=0.42..4,169.32 rows=1 width=53) (never executed)

  • 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))
25. 0.000 0.000 ↓ 0.0 0 0

Values Scan (cost=0..0.04 rows=3 width=36) (never executed)

26. 0.000 0.000 ↓ 0.0 0 0

Values Scan (cost=0..0.31 rows=25 width=64) (never executed)

Planning time : 3.757 ms
Execution time : 874.398 ms