explain.depesz.com

PostgreSQL's explain analyze made readable

Result: e1av : Optimization for: Optimization for: plan #imKN; plan #ZyxE

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.161 1,047.961 ↓ 10.0 10 1

Limit (cost=29,655.51..29,655.52 rows=1 width=1,565) (actual time=1,047.799..1,047.961 rows=10 loops=1)

  • Buffers: shared hit=4084157, temp written=1198
2. 4.933 1,047.800 ↓ 10.0 10 1

Sort (cost=29,655.51..29,655.52 rows=1 width=1,565) (actual time=1,047.798..1,047.8 rows=10 loops=1)

  • Sort Key: tem.game_date DESC, tem.id DESC
  • Sort Method: top-N heapsort Memory: 35kB
  • Buffers: shared hit=4084157, temp written=1198
3. 13.849 1,042.867 ↓ 7,355.0 7,355 1

Nested Loop (cost=29,652.57..29,655.49 rows=1 width=1,565) (actual time=984.922..1,042.867 rows=7,355 loops=1)

  • Buffers: shared hit=4084157, temp written=1198
4.          

CTE temp_report1

5. 0.000 893.717 ↓ 767.9 39,165 1

Gather (cost=1,744.35..29,650.99 rows=51 width=326) (actual time=3.994..893.717 rows=39,165 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
  • Buffers: shared hit=4062092
6. 11.672 943.007 ↓ 611.9 9,791 4 / 4

Nested Loop (cost=744.35..28,645.89 rows=16 width=326) (actual time=5.63..943.007 rows=9,791 loops=4)

  • Buffers: shared hit=4062092
7. 21.722 911.752 ↓ 611.9 9,791 4 / 4

Hash Join (cost=744.08..28,630.63 rows=16 width=230) (actual time=5.603..911.752 rows=9,791 loops=4)

  • Buffers: shared hit=3944594
8. 23.290 890.024 ↓ 70.6 115,724 4 / 4

Nested Loop (cost=744..28,626.08 rows=1,638 width=230) (actual time=4.497..890.024 rows=115,724 loops=4)

  • Buffers: shared hit=3944456
9. 15.576 519.563 ↓ 70.6 115,724 4 / 4

Nested Loop (cost=743.58..26,778.16 rows=1,638 width=217) (actual time=4.462..519.563 rows=115,724 loops=4)

  • Buffers: shared hit=2092873
10. 39.668 156.816 ↓ 70.6 115,724 4 / 4

Hash Join (cost=743.15..24,874.43 rows=1,638 width=185) (actual time=4.428..156.816 rows=115,724 loops=4)

  • Buffers: shared hit=241275
11. 112.771 112.771 ↓ 2.1 115,724 4 / 4

Index Scan using ix_game_history_reports_game_date on game_history_reports gh (cost=0.57..23,419.1 rows=55,709 width=155) (actual time=0.036..112.771 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=238403
12. 0.067 4.377 ↓ 34.0 272 4 / 4

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

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

Seq Scan on game_strings gs (cost=0..742.48 rows=8 width=30) (actual time=0.056..4.31 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=1851598
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=1851583
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=117498
19. 8.633 1,021.663 ↓ 7,355.0 7,355 1

Nested Loop (cost=1.58..4.39 rows=1 width=1,389) (actual time=984.888..1,021.663 rows=7,355 loops=1)

  • Buffers: shared hit=4084157, temp written=1198
20. 9.256 1,005.675 ↓ 7,355.0 7,355 1

Nested Loop (cost=1.58..4.26 rows=1 width=1,357) (actual time=984.869..1,005.675 rows=7,355 loops=1)

  • Buffers: shared hit=4084157, temp written=1198
21. 4.225 989.064 ↓ 7,355.0 7,355 1

Hash Join (cost=1.16..1.61 rows=1 width=1,344) (actual time=984.827..989.064 rows=7,355 loops=1)

  • Buffers: shared hit=4062092, temp written=1198
22. 0.051 0.051 ↑ 1.0 25 1

Values Scan (cost=0..0.31 rows=25 width=64) (actual time=0.014..0.051 rows=25 loops=1)

23. 7.356 984.788 ↓ 7,355.0 7,355 1

Hash (cost=1.15..1.15 rows=1 width=1,312) (actual time=984.788..984.788 rows=7,355 loops=1)

  • Buffers: shared hit=4062092, temp written=1198
24. 977.432 977.432 ↓ 7,355.0 7,355 1

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

  • Filter: ((tem.player_name)::text ~~* '%agcf157057%'::text)
  • Buffers: shared hit=4062092, temp written=1198
25. 7.355 7.355 ↓ 0.0 0 7,355

Index Scan using ix_side_bets_reports_playerid_gamenum on side_bets_reports sbr (cost=0.42..2.65 rows=1 width=53) (actual time=0.001..0.001 rows=0 loops=7,355)

  • Index Cond: ((tem.casino_player_id = sbr.casino_player_id) AND (tem.game_number = sbr.game_number) AND ((tem.game_name_id)::uuid = sbr.game_id))
  • Buffers: shared hit=22065
26. 7.355 7.355 ↑ 1.0 5 7,355

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

27. 7.355 7.355 ↑ 1.0 3 7,355

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

Planning time : 2.918 ms
Execution time : 1,049.933 ms