explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XAjN : aggregate is off

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

GroupAggregate (cost=2,321,767.83..2,337,763.00 rows=40,000 width=219) (actual rows= loops=)

  • Group Key: (sum(CASE WHEN (mt5_deal_y2018.action_type = 'deposit'::text) THEN mt5_deal_y2018.profit ELSE '0'::numeric END)), ((mt5_deal_y2018.login_id)::text), u."group", u.country, u.email, u.registration_ts, (sum(CASE WHEN (mt5_deal_y2018.action_type = 'deposit'::text) THEN mt5_deal_y2018.count_of_operations ELSE '0'::numeric END)), (sum(CASE WHEN (mt5_deal_y2018.action_type = 'withdrawal'::text) THEN mt5_deal_y2018.count_of_operations ELSE '0'::numeric END)), (max(mt5_deal_y2019_m07_1.transaction_date))
  • Functions: 226
2. 0.000 0.000 ↓ 0.0

Sort (cost=2,321,767.83..2,323,158.30 rows=556,188 width=196) (actual rows= loops=)

  • Sort Key: (sum(CASE WHEN (mt5_deal_y2018.action_type = 'deposit'::text) THEN mt5_deal_y2018.profit ELSE '0'::numeric END)) DESC, ((mt5_deal_y2018.login_id)::text), u."group", u.country, u.email, u.registration_ts, (sum(CASE WHEN (mt5_deal_y2018.action_type = 'deposit'::text) THEN mt5_deal_y2018.count_of_operations ELSE '0'::numeric END)), (sum(CASE WHEN (mt5_deal_y2018.action_type = 'withdrawal'::text) THEN mt5_deal_y2018.count_of_operations ELSE '0'::numeric END)), (max(mt5_deal_y2019_m07_1.transaction_date))
3. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=1,858,350.51..2,268,693.00 rows=556,188 width=196) (actual rows= loops=)

  • Merge Cond: (mt5_deal_y2018.login_id = mt5_deal_y2019_m07_1.login_id)
4. 0.000 0.000 ↓ 0.0

Nested Loop (cost=744,186.99..1,103,233.55 rows=1,689 width=164) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Finalize GroupAggregate (cost=744,186.56..1,099,668.09 rows=950 width=187) (actual rows= loops=)

  • Group Key: mt5_deal_y2018.login_id, u."group", u.registration_ts, u.country, u.email
  • Filter: ((sum(CASE WHEN (mt5_deal_y2018.action_type = 'deposit'::text) THEN mt5_deal_y2018.count_of_operations ELSE '0'::numeric END) >= '1'::numeric) AND (sum(CASE WHEN (mt5_deal_y2018.action_type = 'deposit'::text) THEN mt5_deal_y2018.profit ELSE '0'::numeric END) >= '900'::numeric) AND (sum(CASE WHEN (mt5_deal_y2018.volume <> '0'::numeric) THEN mt5_deal_y2018.profit ELSE '0'::numeric END) = '0'::numeric))
6. 0.000 0.000 ↓ 0.0

Gather Merge (cost=744,186.56..971,403.12 rows=1,710,200 width=187) (actual rows= loops=)

  • Workers Planned: 4
7. 0.000 0.000 ↓ 0.0

Partial GroupAggregate (cost=743,186.51..766,701.76 rows=427,550 width=187) (actual rows= loops=)

  • Group Key: mt5_deal_y2018.login_id, u."group", u.registration_ts, u.country, u.email
8. 0.000 0.000 ↓ 0.0

Sort (cost=743,186.51..744,255.38 rows=427,550 width=79) (actual rows= loops=)

  • Sort Key: mt5_deal_y2018.login_id, u."group", u.registration_ts, u.country, u.email
9. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.43..703,198.32 rows=427,550 width=79) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on "user" u (cost=0.00..52,070.83 rows=4,146 width=63) (actual rows= loops=)

  • Filter: ((registration_ts >= '2017-01-01 00:00:00+00'::timestamp with time zone) AND (registration_ts <= (now() - '7 days'::interval)) AND ("group" <> ALL ('{"real\\svg","real\\svg_standard","real\\svg_financial","real\\inactive_accounts_gaming","real\\feed_for_demo_server","real\\inactive_accounts_financial","real\\svg_financial_Bbook","real\\svg_stsandard_Bbook"}'::text[])))
11. 0.000 0.000 ↓ 0.0

Append (cost=0.43..134.29 rows=2,276 width=24) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Index Scan using mt5_deal_y2018_login_id_idx on mt5_deal_y2018 (cost=0.43..9.58 rows=293 width=25) (actual rows= loops=)

  • Index Cond: (login_id = u.login)
13. 0.000 0.000 ↓ 0.0

Index Scan using mt5_deal_y2019_m01_login_id_idx on mt5_deal_y2019_m01 (cost=0.42..1.70 rows=53 width=24) (actual rows= loops=)

  • Index Cond: (login_id = u.login)
14. 0.000 0.000 ↓ 0.0

Index Scan using mt5_deal_y2019_m02_login_id_idx on mt5_deal_y2019_m02 (cost=0.42..1.64 rows=51 width=24) (actual rows= loops=)

  • Index Cond: (login_id = u.login)
15. 0.000 0.000 ↓ 0.0

Index Scan using mt5_deal_y2019_m03_login_id_idx on mt5_deal_y2019_m03 (cost=0.42..1.73 rows=53 width=24) (actual rows= loops=)

  • Index Cond: (login_id = u.login)
16. 0.000 0.000 ↓ 0.0

Index Scan using mt5_deal_y2019_m04_login_id_idx on mt5_deal_y2019_m04 (cost=0.42..1.81 rows=54 width=24) (actual rows= loops=)

  • Index Cond: (login_id = u.login)
17. 0.000 0.000 ↓ 0.0

Index Scan using mt5_deal_y2019_m05_login_id_idx on mt5_deal_y2019_m05 (cost=0.42..2.22 rows=64 width=25) (actual rows= loops=)

  • Index Cond: (login_id = u.login)
18. 0.000 0.000 ↓ 0.0

Index Scan using mt5_deal_y2019_m06_login_id_idx on mt5_deal_y2019_m06 (cost=0.42..2.36 rows=65 width=25) (actual rows= loops=)

  • Index Cond: (login_id = u.login)
19. 0.000 0.000 ↓ 0.0

Index Scan using mt5_deal_y2019_m07_login_id_idx on mt5_deal_y2019_m07 (cost=0.42..2.92 rows=73 width=25) (actual rows= loops=)

  • Index Cond: (login_id = u.login)
20. 0.000 0.000 ↓ 0.0

Index Scan using mt5_deal_y2019_m08_login_id_idx on mt5_deal_y2019_m08 (cost=0.42..3.00 rows=79 width=25) (actual rows= loops=)

  • Index Cond: (login_id = u.login)
21. 0.000 0.000 ↓ 0.0

Index Scan using mt5_deal_y2019_m09_login_id_idx on mt5_deal_y2019_m09 (cost=0.42..3.10 rows=79 width=25) (actual rows= loops=)

  • Index Cond: (login_id = u.login)
22. 0.000 0.000 ↓ 0.0

Index Scan using mt5_deal_y2019_m10_login_id_idx on mt5_deal_y2019_m10 (cost=0.43..3.65 rows=84 width=25) (actual rows= loops=)

  • Index Cond: (login_id = u.login)
23. 0.000 0.000 ↓ 0.0

Index Scan using mt5_deal_y2019_m11_login_id_idx on mt5_deal_y2019_m11 (cost=0.43..4.51 rows=93 width=25) (actual rows= loops=)

  • Index Cond: (login_id = u.login)
24. 0.000 0.000 ↓ 0.0

Index Scan using mt5_deal_y2019_m12_login_id_idx on mt5_deal_y2019_m12 (cost=0.43..5.25 rows=101 width=24) (actual rows= loops=)

  • Index Cond: (login_id = u.login)
25. 0.000 0.000 ↓ 0.0

Index Scan using mt5_deal_y2020_m01_login_id_idx on mt5_deal_y2020_m01 (cost=0.43..6.15 rows=109 width=24) (actual rows= loops=)

  • Index Cond: (login_id = u.login)
26. 0.000 0.000 ↓ 0.0

Index Scan using mt5_deal_y2020_m02_login_id_idx on mt5_deal_y2020_m02 (cost=0.43..6.95 rows=120 width=24) (actual rows= loops=)

  • Index Cond: (login_id = u.login)
27. 0.000 0.000 ↓ 0.0

Index Scan using mt5_deal_y2020_m03_login_id_idx on mt5_deal_y2020_m03 (cost=0.43..8.04 rows=133 width=24) (actual rows= loops=)

  • Index Cond: (login_id = u.login)
28. 0.000 0.000 ↓ 0.0

Index Scan using mt5_deal_y2020_m04_login_id_idx on mt5_deal_y2020_m04 (cost=0.43..10.12 rows=148 width=24) (actual rows= loops=)

  • Index Cond: (login_id = u.login)
29. 0.000 0.000 ↓ 0.0

Index Scan using mt5_deal_y2020_m05_login_id_idx on mt5_deal_y2020_m05 (cost=0.43..11.12 rows=152 width=24) (actual rows= loops=)

  • Index Cond: (login_id = u.login)
30. 0.000 0.000 ↓ 0.0

Index Scan using mt5_deal_y2020_m06_login_id_idx on mt5_deal_y2020_m06 (cost=0.43..11.33 rows=145 width=24) (actual rows= loops=)

  • Index Cond: (login_id = u.login)
31. 0.000 0.000 ↓ 0.0

Index Scan using mt5_deal_y2020_m07_login_id_idx on mt5_deal_y2020_m07 (cost=0.43..12.92 rows=164 width=24) (actual rows= loops=)

  • Index Cond: (login_id = u.login)
32. 0.000 0.000 ↓ 0.0

Index Scan using mt5_deal_y2020_m08_login_id_idx on mt5_deal_y2020_m08 (cost=0.43..11.82 rows=155 width=24) (actual rows= loops=)

  • Index Cond: (login_id = u.login)
33. 0.000 0.000 ↓ 0.0

Index Scan using mt5_deal_y2020_m09_login_id_idx on mt5_deal_y2020_m09 (cost=0.29..0.48 rows=5 width=24) (actual rows= loops=)

  • Index Cond: (login_id = u.login)
34. 0.000 0.000 ↓ 0.0

Index Scan using mt5_deal_y2020_m10_login_id_idx on mt5_deal_y2020_m10 (cost=0.14..0.16 rows=1 width=132) (actual rows= loops=)

  • Index Cond: (login_id = u.login)
35. 0.000 0.000 ↓ 0.0

Index Scan using mt5_deal_y2020_m11_login_id_idx on mt5_deal_y2020_m11 (cost=0.14..0.16 rows=1 width=132) (actual rows= loops=)

  • Index Cond: (login_id = u.login)
36. 0.000 0.000 ↓ 0.0

Index Scan using mt5_deal_y2020_m12_login_id_idx on mt5_deal_y2020_m12 (cost=0.14..0.16 rows=1 width=132) (actual rows= loops=)

  • Index Cond: (login_id = u.login)
37. 0.000 0.000 ↓ 0.0

Index Scan using client_email_idx on client bo (cost=0.43..3.72 rows=2 width=32) (actual rows= loops=)

  • Index Cond: ((email)::text = u.email)
38. 0.000 0.000 ↓ 0.0

Finalize GroupAggregate (cost=1,114,163.51..1,156,289.16 rows=65,860 width=8) (actual rows= loops=)

  • Group Key: mt5_deal_y2019_m07_1.login_id
39. 0.000 0.000 ↓ 0.0

Gather Merge (cost=1,114,163.51..1,153,984.06 rows=329,300 width=8) (actual rows= loops=)

  • Workers Planned: 5
40. 0.000 0.000 ↓ 0.0

Sort (cost=1,113,163.44..1,113,328.09 rows=65,860 width=8) (actual rows= loops=)

  • Sort Key: mt5_deal_y2019_m07_1.login_id
41. 0.000 0.000 ↓ 0.0

Partial HashAggregate (cost=1,107,233.69..1,107,892.29 rows=65,860 width=8) (actual rows= loops=)

  • Group Key: mt5_deal_y2019_m07_1.login_id
42. 0.000 0.000 ↓ 0.0

Parallel Append (cost=0.00..1,103,603.14 rows=726,110 width=8) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Parallel Index Only Scan using mt5_deal_y2019_m07_pkey on mt5_deal_y2019_m07 mt5_deal_y2019_m07_1 (cost=0.42..11,712.17 rows=9,915 width=8) (actual rows= loops=)

  • Index Cond: (action = 2)
44. 0.000 0.000 ↓ 0.0

Parallel Index Only Scan using mt5_deal_y2020_m10_pkey on mt5_deal_y2020_m10 mt5_deal_y2020_m10_1 (cost=0.14..4.00 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (action = 2)
45. 0.000 0.000 ↓ 0.0

Parallel Index Only Scan using mt5_deal_y2020_m11_pkey on mt5_deal_y2020_m11 mt5_deal_y2020_m11_1 (cost=0.14..4.00 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (action = 2)
46. 0.000 0.000 ↓ 0.0

Parallel Index Only Scan using mt5_deal_y2020_m12_pkey on mt5_deal_y2020_m12 mt5_deal_y2020_m12_1 (cost=0.14..4.00 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (action = 2)
47. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on mt5_deal_y2020_m07 mt5_deal_y2020_m07_1 (cost=0.00..149,792.71 rows=91,593 width=8) (actual rows= loops=)

  • Filter: (action = 2)
48. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on mt5_deal_y2020_m08 mt5_deal_y2020_m08_1 (cost=0.00..136,792.64 rows=94,237 width=8) (actual rows= loops=)

  • Filter: (action = 2)
49. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on mt5_deal_y2020_m06 mt5_deal_y2020_m06_1 (cost=0.00..128,221.70 rows=79,297 width=8) (actual rows= loops=)

  • Filter: (action = 2)
50. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on mt5_deal_y2020_m05 mt5_deal_y2020_m05_1 (cost=0.00..122,746.17 rows=72,845 width=8) (actual rows= loops=)

  • Filter: (action = 2)
51. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on mt5_deal_y2020_m04 mt5_deal_y2020_m04_1 (cost=0.00..106,876.81 rows=60,734 width=8) (actual rows= loops=)

  • Filter: (action = 2)
52. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on mt5_deal_y2020_m03 mt5_deal_y2020_m03_1 (cost=0.00..80,888.51 rows=63,129 width=8) (actual rows= loops=)

  • Filter: (action = 2)
53. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on mt5_deal_y2020_m02 mt5_deal_y2020_m02_1 (cost=0.00..66,572.07 rows=52,064 width=8) (actual rows= loops=)

  • Filter: (action = 2)
54. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on mt5_deal_y2020_m01 mt5_deal_y2020_m01_1 (cost=0.00..56,319.76 rows=52,281 width=8) (actual rows= loops=)

  • Filter: (action = 2)
55. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on mt5_deal_y2018 mt5_deal_y2018_1 (cost=0.00..52,381.69 rows=56,892 width=8) (actual rows= loops=)

  • Filter: (action = 2)
56. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on mt5_deal_y2019_m12 mt5_deal_y2019_m12_1 (cost=0.00..43,954.66 rows=41,240 width=8) (actual rows= loops=)

  • Filter: (action = 2)
57. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on mt5_deal_y2019_m11 mt5_deal_y2019_m11_1 (cost=0.00..34,642.31 rows=32,882 width=8) (actual rows= loops=)

  • Filter: (action = 2)
58. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on mt5_deal_y2019_m10 mt5_deal_y2019_m10_1 (cost=0.00..25,887.18 rows=31,225 width=8) (actual rows= loops=)

  • Filter: (action = 2)
59. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on mt5_deal_y2019_m09 mt5_deal_y2019_m09_1 (cost=0.00..19,024.40 rows=21,195 width=8) (actual rows= loops=)

  • Filter: (action = 2)
60. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on mt5_deal_y2019_m08 mt5_deal_y2019_m08_1 (cost=0.00..17,252.40 rows=18,488 width=8) (actual rows= loops=)

  • Filter: (action = 2)
61. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on mt5_deal_y2019_m06 mt5_deal_y2019_m06_1 (cost=0.00..11,757.47 rows=14,042 width=8) (actual rows= loops=)

  • Filter: (action = 2)
62. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on mt5_deal_y2019_m05 mt5_deal_y2019_m05_1 (cost=0.00..10,381.43 rows=15,036 width=8) (actual rows= loops=)

  • Filter: (action = 2)
63. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on mt5_deal_y2019_m04 mt5_deal_y2019_m04_1 (cost=0.00..6,790.14 rows=11,138 width=8) (actual rows= loops=)

  • Filter: (action = 2)
64. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on mt5_deal_y2019_m03 mt5_deal_y2019_m03_1 (cost=0.00..5,891.35 rows=9,382 width=8) (actual rows= loops=)

  • Filter: (action = 2)
65. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on mt5_deal_y2019_m01 mt5_deal_y2019_m01_1 (cost=0.00..5,193.48 rows=8,517 width=8) (actual rows= loops=)

  • Filter: (action = 2)
66. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on mt5_deal_y2019_m02 mt5_deal_y2019_m02_1 (cost=0.00..5,092.94 rows=8,351 width=8) (actual rows= loops=)

  • Filter: (action = 2)
67. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on mt5_deal_y2020_m09 mt5_deal_y2020_m09_1 (cost=0.00..1,788.60 rows=2,547 width=8) (actual rows= loops=)

  • Filter: (action = 2)