explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xHVE

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

Sort (cost=1,349.26..1,349.29 rows=11 width=248) (actual rows= loops=)

  • Sort Key: p.month
2.          

CTE withdraw

3. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=10.64..10.68 rows=1 width=40) (actual rows= loops=)

  • Group Key: (date_trunc('month'::text, ((timezone('Europe/Minsk'::text, wt.transaction_ts))::date)::timestamp with time zone))
4. 0.000 0.000 ↓ 0.0

Sort (cost=10.64..10.65 rows=1 width=40) (actual rows= loops=)

  • Sort Key: (date_trunc('month'::text, ((timezone('Europe/Minsk'::text, wt.transaction_ts))::date)::timestamp with time zone))
5. 0.000 0.000 ↓ 0.0

Seq Scan on withdraw_transactions wt (cost=0.00..10.63 rows=1 width=40) (actual rows= loops=)

  • Filter: ((status)::text = 'PROCESSED'::text)
6.          

CTE purchase

7. 0.000 0.000 ↓ 0.0

HashAggregate (cost=2.98..3.27 rows=13 width=40) (actual rows= loops=)

  • Group Key: date_trunc('month'::text, ((timezone('Europe/Minsk'::text, pt.transaction_ts))::date)::timestamp with time zone)
8. 0.000 0.000 ↓ 0.0

Seq Scan on purchase_transactions pt (cost=0.00..2.91 rows=13 width=13) (actual rows= loops=)

  • Filter: (((platform)::text <> 'promo games'::text) AND ((status)::text = 'COMPLETED'::text))
9.          

CTE active_months

10. 0.000 0.000 ↓ 0.0

HashAggregate (cost=0.63..0.77 rows=14 width=8) (actual rows= loops=)

  • Group Key: w.month
11. 0.000 0.000 ↓ 0.0

Append (cost=0.02..0.59 rows=14 width=8) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

HashAggregate (cost=0.02..0.03 rows=1 width=8) (actual rows= loops=)

  • Group Key: w.month
13. 0.000 0.000 ↓ 0.0

CTE Scan on withdraw w (cost=0.00..0.02 rows=1 width=8) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

HashAggregate (cost=0.29..0.42 rows=13 width=8) (actual rows= loops=)

  • Group Key: p_1.month
15. 0.000 0.000 ↓ 0.0

CTE Scan on purchase p_1 (cost=0.00..0.26 rows=13 width=8) (actual rows= loops=)

16.          

CTE payments

17. 0.000 0.000 ↓ 0.0

WindowAgg (cost=1.25..1.53 rows=14 width=144) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Sort (cost=1.25..1.28 rows=14 width=80) (actual rows= loops=)

  • Sort Key: (COALESCE(am.month))
19. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=0.46..0.98 rows=14 width=80) (actual rows= loops=)

  • Hash Cond: (am.month = w_1.month)
20. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=0.42..0.89 rows=14 width=40) (actual rows= loops=)

  • Hash Cond: (am.month = p_2.month)
21. 0.000 0.000 ↓ 0.0

CTE Scan on active_months am (cost=0.00..0.28 rows=14 width=8) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Hash (cost=0.26..0.26 rows=13 width=40) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

CTE Scan on purchase p_2 (cost=0.00..0.26 rows=13 width=40) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Hash (cost=0.02..0.02 rows=1 width=40) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

CTE Scan on withdraw w_1 (cost=0.00..0.02 rows=1 width=40) (actual rows= loops=)

26.          

CTE months

27. 0.000 0.000 ↓ 0.0

Result (cost=0.00..5.03 rows=1,000 width=8) (actual rows= loops=)

28.          

CTE user_balance

29. 0.000 0.000 ↓ 0.0

HashAggregate (cost=437.15..437.26 rows=11 width=48) (actual rows= loops=)

  • Group Key: ""*SELECT* 1"".user_id, ""*SELECT* 1"".date, ""*SELECT* 1"".balance
30. 0.000 0.000 ↓ 0.0

Append (cost=27.31..437.07 rows=11 width=48) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 1 (cost=27.31..27.35 rows=1 width=48) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

WindowAgg (cost=27.31..27.34 rows=1 width=64) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Sort (cost=27.31..27.31 rows=1 width=64) (actual rows= loops=)

  • Sort Key: wt2.user_id, (date_trunc('month'::text, wt_max.date)), wt_max.date
34. 0.000 0.000 ↓ 0.0

Hash Join (cost=16.41..27.30 rows=1 width=64) (actual rows= loops=)

  • Hash Cond: ((wt2.user_id = wt_max.user_id) AND (wt2.transaction_ts = wt_max.date))
35. 0.000 0.000 ↓ 0.0

Seq Scan on withdraw_transactions wt2 (cost=0.00..10.50 rows=50 width=48) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Hash (cost=15.66..15.66 rows=50 width=16) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Subquery Scan on wt_max (cost=14.16..15.66 rows=50 width=16) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

HashAggregate (cost=14.16..15.16 rows=50 width=24) (actual rows= loops=)

  • Group Key: wt_1.user_id, max(wt_1.transaction_ts) OVER (?)
39. 0.000 0.000 ↓ 0.0

WindowAgg (cost=12.41..13.91 rows=50 width=24) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Sort (cost=12.41..12.54 rows=50 width=24) (actual rows= loops=)

  • Sort Key: wt_1.user_id, (date_trunc('month'::text, ((timezone('Europe/Minsk'::text, wt_1.transaction_ts))::date)::timestamp with time zone))
41. 0.000 0.000 ↓ 0.0

Seq Scan on withdraw_transactions wt_1 (cost=0.00..11.00 rows=50 width=24) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2 (cost=7.12..7.16 rows=1 width=48) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

WindowAgg (cost=7.12..7.15 rows=1 width=64) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Sort (cost=7.12..7.13 rows=1 width=38) (actual rows= loops=)

  • Sort Key: pt2.user_id, (date_trunc('month'::text, pt_max.date)), pt_max.date
45. 0.000 0.000 ↓ 0.0

Hash Join (cost=4.19..7.11 rows=1 width=38) (actual rows= loops=)

  • Hash Cond: ((pt2.user_id = pt_max.user_id) AND (pt2.transaction_ts = pt_max.date))
46. 0.000 0.000 ↓ 0.0

Seq Scan on purchase_transactions pt2 (cost=0.00..2.52 rows=52 width=22) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

Hash (cost=4.00..4.00 rows=13 width=16) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Subquery Scan on pt_max (cost=3.61..4.00 rows=13 width=16) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

HashAggregate (cost=3.61..3.87 rows=13 width=24) (actual rows= loops=)

  • Group Key: pt_1.user_id, max(pt_1.transaction_ts) OVER (?)
50. 0.000 0.000 ↓ 0.0

WindowAgg (cost=3.15..3.54 rows=13 width=24) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Sort (cost=3.15..3.18 rows=13 width=24) (actual rows= loops=)

  • Sort Key: pt_1.user_id, (date_trunc('month'::text, ((timezone('Europe/Minsk'::text, pt_1.transaction_ts))::date)::timestamp with time zone))
52. 0.000 0.000 ↓ 0.0

Seq Scan on purchase_transactions pt_1 (cost=0.00..2.91 rows=13 width=24) (actual rows= loops=)

  • Filter: (((platform)::text <> 'promo games'::text) AND ((status)::text = 'COMPLETED'::text))
53. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 3 (cost=87.87..87.91 rows=2 width=48) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Unique (cost=87.87..87.89 rows=2 width=56) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

Sort (cost=87.87..87.87 rows=2 width=56) (actual rows= loops=)

  • Sort Key: gp.user_id, (max(gp.game_ts) OVER (?)), (last_value(gp2.after_game_balance) OVER (?))
56. 0.000 0.000 ↓ 0.0

WindowAgg (cost=87.81..87.86 rows=2 width=56) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

Sort (cost=87.81..87.81 rows=2 width=31) (actual rows= loops=)

  • Sort Key: gp.user_id, (date_trunc('month'::text, (max(gp.game_ts) OVER (?)))), (max(gp.game_ts) OVER (?))
58. 0.000 0.000 ↓ 0.0

Hash Join (cost=76.91..87.80 rows=2 width=31) (actual rows= loops=)

  • Hash Cond: ((gp.user_id = gp2.user_id) AND ((max(gp.game_ts) OVER (?)) = gp2.game_ts))
59. 0.000 0.000 ↓ 0.0

HashAggregate (cost=48.86..53.39 rows=362 width=24) (actual rows= loops=)

  • Group Key: gp.user_id, max(gp.game_ts) OVER (?)
60. 0.000 0.000 ↓ 0.0

WindowAgg (cost=38.91..47.05 rows=362 width=24) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

Sort (cost=38.91..39.81 rows=362 width=24) (actual rows= loops=)

  • Sort Key: gp.user_id, (date_trunc('month'::text, gp.game_ts))
62. 0.000 0.000 ↓ 0.0

Seq Scan on game_play gp (cost=0.00..23.53 rows=362 width=24) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

Hash (cost=22.62..22.62 rows=362 width=23) (actual rows= loops=)

64. 0.000 0.000 ↓ 0.0

Seq Scan on game_play gp2 (cost=0.00..22.62 rows=362 width=23) (actual rows= loops=)

65. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 4 (cost=176.47..176.55 rows=4 width=48) (actual rows= loops=)

66. 0.000 0.000 ↓ 0.0

Unique (cost=176.47..176.51 rows=4 width=56) (actual rows= loops=)

67. 0.000 0.000 ↓ 0.0

Sort (cost=176.47..176.48 rows=4 width=56) (actual rows= loops=)

  • Sort Key: gpb.user_id, (max(gpb.game_ts) OVER (?)), (last_value(gp2_1.after_game_balance) OVER (?))
68. 0.000 0.000 ↓ 0.0

WindowAgg (cost=176.33..176.43 rows=4 width=56) (actual rows= loops=)

69. 0.000 0.000 ↓ 0.0

Sort (cost=176.33..176.34 rows=4 width=31) (actual rows= loops=)

  • Sort Key: gpb.user_id, (date_trunc('month'::text, (max(gpb.game_ts) OVER (?)))), (max(gpb.game_ts) OVER (?))
70. 0.000 0.000 ↓ 0.0

Hash Join (cost=153.77..176.29 rows=4 width=31) (actual rows= loops=)

  • Hash Cond: ((gpb.user_id = gp2_1.user_id) AND ((max(gpb.game_ts) OVER (?)) = gp2_1.game_ts))
71. 0.000 0.000 ↓ 0.0

HashAggregate (cost=125.72..135.08 rows=749 width=24) (actual rows= loops=)

  • Group Key: gpb.user_id, max(gpb.game_ts) OVER (?)
72. 0.000 0.000 ↓ 0.0

WindowAgg (cost=105.12..121.98 rows=749 width=24) (actual rows= loops=)

73. 0.000 0.000 ↓ 0.0

Sort (cost=105.12..107.00 rows=749 width=24) (actual rows= loops=)

  • Sort Key: gpb.user_id, (date_trunc('month'::text, gpb.game_ts))
74. 0.000 0.000 ↓ 0.0

Seq Scan on game_play_bonus gpb (cost=0.00..69.36 rows=749 width=24) (actual rows= loops=)

75. 0.000 0.000 ↓ 0.0

Hash (cost=22.62..22.62 rows=362 width=23) (actual rows= loops=)

76. 0.000 0.000 ↓ 0.0

Seq Scan on game_play gp2_1 (cost=0.00..22.62 rows=362 width=23) (actual rows= loops=)

77. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 5 (cost=138.04..138.10 rows=3 width=48) (actual rows= loops=)

78. 0.000 0.000 ↓ 0.0

Unique (cost=138.04..138.07 rows=3 width=56) (actual rows= loops=)

79. 0.000 0.000 ↓ 0.0

Sort (cost=138.04..138.05 rows=3 width=56) (actual rows= loops=)

  • Sort Key: gc.user_id, (max(gc.game_collect_ts) OVER (?)), (last_value(gc2.after_game_balance) OVER (?))
80. 0.000 0.000 ↓ 0.0

WindowAgg (cost=137.94..138.02 rows=3 width=56) (actual rows= loops=)

81. 0.000 0.000 ↓ 0.0

Sort (cost=137.94..137.95 rows=3 width=30) (actual rows= loops=)

  • Sort Key: gc.user_id, (date_trunc('month'::text, (max(gc.game_collect_ts) OVER (?)))), (max(gc.game_collect_ts) OVER (?))
82. 0.000 0.000 ↓ 0.0

Hash Join (cost=118.53..137.92 rows=3 width=30) (actual rows= loops=)

  • Hash Cond: ((gc.user_id = gc2.user_id) AND ((max(gc.game_collect_ts) OVER (?)) = gc2.game_collect_ts))
83. 0.000 0.000 ↓ 0.0

HashAggregate (cost=78.63..88.95 rows=516 width=24) (actual rows= loops=)

  • Group Key: gc.user_id, max(gc.game_collect_ts) OVER (?)
84. 0.000 0.000 ↓ 0.0

WindowAgg (cost=60.57..76.05 rows=516 width=24) (actual rows= loops=)

85. 0.000 0.000 ↓ 0.0

Sort (cost=60.57..61.86 rows=516 width=24) (actual rows= loops=)

  • Sort Key: gc.user_id, (date_trunc('month'::text, ((timezone('Europe/Minsk'::text, gc.game_collect_ts))::date)::timestamp with time zone))
86. 0.000 0.000 ↓ 0.0

Seq Scan on game_collect_result gc (cost=0.00..37.32 rows=516 width=24) (actual rows= loops=)

87. 0.000 0.000 ↓ 0.0

Hash (cost=32.16..32.16 rows=516 width=22) (actual rows= loops=)

88. 0.000 0.000 ↓ 0.0

Seq Scan on game_collect_result gc2 (cost=0.00..32.16 rows=516 width=22) (actual rows= loops=)

89.          

CTE user_last_balance

90. 0.000 0.000 ↓ 0.0

HashAggregate (cost=0.80..0.93 rows=11 width=56) (actual rows= loops=)

  • Group Key: ub.user_id, (date_trunc('month'::text, ub.date)), last_value(ub.balance) OVER (?)
91. 0.000 0.000 ↓ 0.0

WindowAgg (cost=0.44..0.71 rows=11 width=56) (actual rows= loops=)

92. 0.000 0.000 ↓ 0.0

Sort (cost=0.44..0.47 rows=11 width=56) (actual rows= loops=)

  • Sort Key: ub.user_id, (date_trunc('month'::text, ub.date)), ub.date
93. 0.000 0.000 ↓ 0.0

CTE Scan on user_balance ub (cost=0.00..0.25 rows=11 width=56) (actual rows= loops=)

94.          

CTE user_month

95. 0.000 0.000 ↓ 0.0

HashAggregate (cost=384.62..406.62 rows=2,200 width=48) (actual rows= loops=)

  • Group Key: t.user_id, t.month, ulb_1.last
96.          

CTE user_join_month

97. 0.000 0.000 ↓ 0.0

HashAggregate (cost=285.22..307.22 rows=2,200 width=16) (actual rows= loops=)

  • Group Key: ulb.user_id, m.val
98. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..230.22 rows=11,000 width=16) (actual rows= loops=)

99. 0.000 0.000 ↓ 0.0

CTE Scan on user_last_balance ulb (cost=0.00..0.22 rows=11 width=8) (actual rows= loops=)

100. 0.000 0.000 ↓ 0.0

CTE Scan on months m (cost=0.00..20.00 rows=1,000 width=8) (actual rows= loops=)

101. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=0.39..60.89 rows=2,200 width=48) (actual rows= loops=)

  • Hash Cond: ((t.month = ulb_1.month) AND (t.user_id = ulb_1.user_id))
102. 0.000 0.000 ↓ 0.0

CTE Scan on user_join_month t (cost=0.00..44.00 rows=2,200 width=16) (actual rows= loops=)

103. 0.000 0.000 ↓ 0.0

Hash (cost=0.22..0.22 rows=11 width=48) (actual rows= loops=)

104. 0.000 0.000 ↓ 0.0

CTE Scan on user_last_balance ulb_1 (cost=0.00..0.22 rows=11 width=48) (actual rows= loops=)

105.          

CTE user_last_month_balance

106. 0.000 0.000 ↓ 0.0

WindowAgg (cost=354.27..403.77 rows=2,200 width=56) (actual rows= loops=)

107. 0.000 0.000 ↓ 0.0

Sort (cost=354.27..359.77 rows=2,200 width=56) (actual rows= loops=)

  • Sort Key: grouped_user_balance.user_id, grouped_user_balance.count
108. 0.000 0.000 ↓ 0.0

Subquery Scan on grouped_user_balance (cost=166.14..232.14 rows=2,200 width=56) (actual rows= loops=)

109. 0.000 0.000 ↓ 0.0

WindowAgg (cost=166.14..210.14 rows=2,200 width=56) (actual rows= loops=)

110. 0.000 0.000 ↓ 0.0

Sort (cost=166.14..171.64 rows=2,200 width=48) (actual rows= loops=)

  • Sort Key: um.user_id, um.month
111. 0.000 0.000 ↓ 0.0

CTE Scan on user_month um (cost=0.00..44.00 rows=2,200 width=48) (actual rows= loops=)

112.          

CTE month_balance

113. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=77.19..77.41 rows=11 width=40) (actual rows= loops=)

  • Group Key: ulmb.month
114. 0.000 0.000 ↓ 0.0

Sort (cost=77.19..77.22 rows=11 width=40) (actual rows= loops=)

  • Sort Key: ulmb.month
115. 0.000 0.000 ↓ 0.0

CTE Scan on user_last_month_balance ulmb (cost=0.00..77.00 rows=11 width=40) (actual rows= loops=)

  • Filter: ((month >= date_trunc('year'::text, now())) AND (month <= date_trunc('month'::text, now())))
116.          

CTE balance

117. 0.000 0.000 ↓ 0.0

WindowAgg (cost=0.41..0.60 rows=11 width=72) (actual rows= loops=)

118. 0.000 0.000 ↓ 0.0

Sort (cost=0.41..0.44 rows=11 width=40) (actual rows= loops=)

  • Sort Key: mb.month
119. 0.000 0.000 ↓ 0.0

CTE Scan on month_balance mb (cost=0.00..0.22 rows=11 width=40) (actual rows= loops=)

120. 0.000 0.000 ↓ 0.0

WindowAgg (cost=0.99..1.21 rows=11 width=248) (actual rows= loops=)

121. 0.000 0.000 ↓ 0.0

Sort (cost=0.99..1.02 rows=11 width=208) (actual rows= loops=)

  • Sort Key: (COALESCE(p.month))
122. 0.000 0.000 ↓ 0.0

Hash Join (cost=0.36..0.80 rows=11 width=208) (actual rows= loops=)

  • Hash Cond: (p.month = b.month)
123. 0.000 0.000 ↓ 0.0

CTE Scan on payments p (cost=0.00..0.28 rows=14 width=136) (actual rows= loops=)

124. 0.000 0.000 ↓ 0.0

Hash (cost=0.22..0.22 rows=11 width=72) (actual rows= loops=)

125. 0.000 0.000 ↓ 0.0

CTE Scan on balance b (cost=0.00..0.22 rows=11 width=72) (actual rows= loops=)