explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2HoF

Settings
# exclusive inclusive rows x rows loops node
1. 15,769.425 498,127.361 ↓ 0.0 0 1

Insert on rep_attribution_data_2019_3 (cost=354,933,829.02..356,957,143.66 rows=1,995,646 width=3,483) (actual time=498,127.361..498,127.361 rows=0 loops=1)

  • Functions: 1,119
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 340.906 ms, Inlining 84.654 ms, Optimization 35367.527 ms, Emission 28368.920 ms, Total 64162.007 ms
2. 7,379.300 482,357.936 ↑ 1.4 1,416,026 1

Subquery Scan on etl_attribution_data_quarter_v (cost=354,933,829.02..356,957,143.66 rows=1,995,646 width=3,483) (actual time=437,696.418..482,357.936 rows=1,416,026 loops=1)

3. 28,919.824 474,978.636 ↑ 1.4 1,416,026 1

Hash Left Join (cost=354,933,829.02..356,897,274.28 rows=1,995,646 width=3,363) (actual time=437,696.389..474,978.636 rows=1,416,026 loops=1)

  • Hash Cond: (fx_recalc.country_code = c.country_code)
4.          

CTE user_doc_reject

5. 21.586 2,319.718 ↓ 952.0 952 1

GroupAggregate (cost=219,187.47..219,192.75 rows=1 width=16) (actual time=2,290.938..2,319.718 rows=952 loops=1)

  • Group Key: u_1.user_id
  • Filter: (date_trunc('quarter'::text, min(e.event_datetime)) = date_trunc('quarter'::text, (('now'::cstring)::date)::timestamp with time zone))
  • Rows Removed by Filter: 16,390
6. 27.488 2,298.132 ↓ 243.3 36,732 1

Sort (cost=219,187.47..219,187.85 rows=151 width=16) (actual time=2,289.361..2,298.132 rows=36,732 loops=1)

  • Sort Key: u_1.user_id
  • Sort Method: quicksort Memory: 3,258kB
7. 34.675 2,270.644 ↓ 243.3 36,732 1

Hash Join (cost=83,140.57..219,182.00 rows=151 width=16) (actual time=303.598..2,270.644 rows=36,732 loops=1)

  • Hash Cond: (e.user_id = u_1.user_id)
8. 1,932.475 1,932.475 ↓ 95.0 61,569 1

Index Scan using ix_etl_tbl_all_events_t_type on etl_tbl_all_events e (cost=0.56..136,038.06 rows=648 width=12) (actual time=0.073..1,932.475 rows=61,569 loops=1)

  • Index Cond: (event_type = 'User documents status'::text)
  • Filter: ((event_attributes ->> 'status'::text) = 'REJECTED'::text)
  • Rows Removed by Filter: 57,559
9. 4.944 303.494 ↓ 3.0 17,345 1

Hash (cost=83,066.81..83,066.81 rows=5,855 width=8) (actual time=303.494..303.494 rows=17,345 loops=1)

  • Buckets: 32,768 (originally 8192) Batches: 1 (originally 1) Memory Usage: 934kB
10. 7.166 298.550 ↓ 3.0 17,345 1

Subquery Scan on u_1 (cost=82,910.82..83,066.81 rows=5,855 width=8) (actual time=278.952..298.550 rows=17,345 loops=1)

11. 9.005 291.384 ↓ 3.0 17,345 1

Hash Right Join (cost=82,910.82..83,008.26 rows=5,855 width=3,541) (actual time=278.949..291.384 rows=17,345 loops=1)

  • Hash Cond: (aff_goals.user_id = u.user_id)
12.          

CTE aff_goals

13. 3.182 11.240 ↓ 1.1 4,555 1

Sort (cost=996.76..1,007.53 rows=4,307 width=28) (actual time=10.217..11.240 rows=4,555 loops=1)

  • Sort Key: g.user_id
  • Sort Method: quicksort Memory: 415kB
14. 4.967 8.058 ↓ 1.1 4,555 1

HashAggregate (cost=693.71..736.78 rows=4,307 width=28) (actual time=6.487..8.058 rows=4,555 loops=1)

  • Group Key: g.user_id
15. 3.091 3.091 ↓ 1.0 7,156 1

Seq Scan on tbl_affiliate_goals_for_export g (cost=0.00..496.95 rows=7,155 width=28) (actual time=0.031..3.091 rows=7,156 loops=1)

  • Filter: (goal_name <> 'INSTALL_EVENT'::text)
  • Rows Removed by Filter: 2,361
16. 13.701 13.701 ↓ 1.1 4,555 1

CTE Scan on aff_goals (cost=0.00..86.14 rows=4,307 width=8) (actual time=10.220..13.701 rows=4,555 loops=1)

17. 6.931 268.678 ↓ 3.0 17,345 1

Hash (cost=81,830.10..81,830.10 rows=5,855 width=8) (actual time=268.677..268.678 rows=17,345 loops=1)

  • Buckets: 32,768 (originally 8192) Batches: 1 (originally 1) Memory Usage: 934kB
18. 261.747 261.747 ↓ 3.0 17,345 1

Seq Scan on etl_dim_ums_users u (cost=0.00..81,830.10 rows=5,855 width=8) (actual time=0.015..261.747 rows=17,345 loops=1)

  • Filter: ((test_sign IS FALSE) AND (user_role = ANY ('{USER,PROFESSIONAL}'::text[])) AND (user_status <> 'VERIFIED'::text) AND ((poa_confirmed = 'POA rejected'::text) OR (poi_confirmed = 'POI rejected'::text)))
  • Rows Removed by Filter: 810,183
19.          

CTE main_data

20. 675.177 398,859.595 ↑ 281.9 1,416,026 1

Append (cost=123,323.54..338,936,196.18 rows=399,129,252 width=2,940) (actual time=66,954.730..398,859.595 rows=1,416,026 loops=1)

21. 1,144.366 285,260.872 ↓ 8.9 1,172,901 1

Result (cost=123,323.54..7,892,160.06 rows=132,367 width=2,940) (actual time=66,954.729..285,260.872 rows=1,172,901 loops=1)

22. 577.052 284,116.506 ↓ 8.9 1,172,901 1

Append (cost=123,323.54..7,889,843.64 rows=132,367 width=2,880) (actual time=66,954.723..284,116.506 rows=1,172,901 loops=1)

23. 2,999.745 274,845.827 ↓ 8.9 1,172,809 1

Result (cost=123,323.54..7,486,492.32 rows=132,354 width=2,880) (actual time=66,954.722..274,845.827 rows=1,172,809 loops=1)

24. 530.421 271,846.082 ↓ 8.9 1,172,809 1

Append (cost=123,323.54..7,472,926.04 rows=132,354 width=1,868) (actual time=66,954.709..271,846.082 rows=1,172,809 loops=1)

25. 1,024.363 216,688.220 ↓ 8.7 897,516 1

Result (cost=123,323.54..6,514,553.39 rows=102,687 width=1,868) (actual time=66,954.707..216,688.220 rows=897,516 loops=1)

26. 414.749 215,663.857 ↓ 8.7 897,516 1

Append (cost=123,323.54..6,512,242.93 rows=102,687 width=1,908) (actual time=66,954.701..215,663.857 rows=897,516 loops=1)

27. 466.012 109,762.786 ↓ 21.8 482,174 1

Result (cost=123,323.54..4,205,283.31 rows=22,113 width=1,908) (actual time=66,954.700..109,762.786 rows=482,174 loops=1)

28. 223.916 109,296.774 ↓ 21.8 482,174 1

Append (cost=123,323.54..4,204,951.61 rows=22,113 width=1,928) (actual time=66,954.693..109,296.774 rows=482,174 loops=1)

29. 291.238 72,718.295 ↓ 24.5 321,958 1

Result (cost=123,323.54..833,483.39 rows=13,140 width=1,928) (actual time=66,954.692..72,718.295 rows=321,958 loops=1)

30. 141.363 72,427.057 ↓ 24.5 321,958 1

Append (cost=123,323.54..833,253.44 rows=13,140 width=1,928) (actual time=66,954.686..72,427.057 rows=321,958 loops=1)

31. 45.583 67,163.941 ↓ 6.2 49,228 1

Subquery Scan on *SELECT* 1 (cost=123,323.54..124,524.87 rows=7,877 width=1,578) (actual time=66,954.684..67,163.941 rows=49,228 loops=1)

32. 124.841 67,118.358 ↓ 6.2 49,228 1

GroupAggregate (cost=123,323.54..124,347.63 rows=7,877 width=1,510) (actual time=66,954.678..67,118.358 rows=49,228 loops=1)

  • Group Key: tbl_installs.media_source, tbl_installs.adset, tbl_installs.ad, (((tbl_installs.install_ts)::timestamp without time zone)::date), tbl_installs.country_code, tbl_installs.app_name, tbl_installs.platform, tbl_installs.aff_id, tbl_installs.offer_id, tbl_installs.keywords, (CASE WHEN (upper(tbl_installs.media_source) ~~ '%DOUBLEC%'::text) THEN upper(replace("substring"(tbl_installs.campaign, '([^_]*)'::text), ' '::text, ''::text)) ELSE upper(COALESCE(tbl_installs.campaign, 'None'::text)) END), (CASE WHEN (upper(tbl_installs.ad_adjusted) ~~ 'CPN%'::text) THEN regexp_replace(upper(split_part(tbl_installs.ad_adjusted, '_'::text, 1)), 'CPN'::text, ''::text) ELSE 'None'::text END), (CASE WHEN (upper(tbl_installs.ad_adjusted) ~~ 'CPN%'::text) THEN regexp_replace(upper(split_part(tbl_installs.ad_adjusted, '_'::text, 2)), 'CRN'::text, ''::text) ELSE 'None'::text END), (CASE WHEN (upper(tbl_installs.ad_adjusted) ~~ 'CPN%'::text) THEN regexp_replace(upper(split_part(tbl_installs.ad_adjusted, '_'::text, 3)), 'CRT'::text, ''::text) ELSE 'None'::text END), tbl_installs.tenant
33. 733.656 66,993.517 ↓ 14.8 116,221 1

Sort (cost=123,323.54..123,343.24 rows=7,879 width=238) (actual time=66,954.641..66,993.517 rows=116,221 loops=1)

  • Sort Key: tbl_installs.media_source, tbl_installs.adset, tbl_installs.ad, (((tbl_installs.install_ts)::timestamp without time zone)::date), tbl_installs.country_code, tbl_installs.app_name, tbl_installs.platform, tbl_installs.aff_id, tbl_installs.offer_id, tbl_installs.keywords, (CASE WHEN (upper(tbl_installs.media_source) ~~ '%DOUBLEC%'::text) THEN upper(replace("substring"(tbl_installs.campaign, '([^_]*)'::text), ' '::text, ''::text)) ELSE upper(COALESCE(tbl_installs.campaign, 'None'::text)) END), (CASE WHEN (upper(tbl_installs.ad_adjusted) ~~ 'CPN%'::text) THEN regexp_replace(upper(split_part(tbl_installs.ad_adjusted, '_'::text, 1)), 'CPN'::text, ''::text) ELSE 'None'::text END), (CASE WHEN (upper(tbl_installs.ad_adjusted) ~~ 'CPN%'::text) THEN regexp_replace(upper(split_part(tbl_installs.ad_adjusted, '_'::text, 2)), 'CRN'::text, ''::text) ELSE 'None'::text END), (CASE WHEN (upper(tbl_installs.ad_adjusted) ~~ 'CPN%'::text) THEN regexp_replace(upper(split_part(tbl_installs.ad_adjusted, '_'::text, 3)), 'CRT'::text, ''::text) ELSE 'None'::text END), tbl_installs.tenant
  • Sort Method: quicksort Memory: 33,901kB
34. 66,259.861 66,259.861 ↓ 14.8 116,221 1

Seq Scan on tbl_installs (cost=0.00..122,813.62 rows=7,879 width=238) (actual time=65,781.940..66,259.861 rows=116,221 loops=1)

  • Filter: (date_trunc('quarter'::text, install_ts) = date_trunc('quarter'::text, (('now'::cstring)::date)::timestamp with time zone))
  • Rows Removed by Filter: 1,459,913
35. 215.826 5,109.780 ↓ 51.8 272,696 1

Subquery Scan on *SELECT* 2 (cost=66,063.98..66,849.73 rows=5,262 width=1,728) (actual time=4,002.188..5,109.780 rows=272,696 loops=1)

36. 807.354 4,893.954 ↓ 51.8 272,696 1

GroupAggregate (cost=66,063.98..66,783.95 rows=5,262 width=1,724) (actual time=4,002.183..4,893.954 rows=272,696 loops=1)

  • Group Key: fct_costs.media_source, (CASE WHEN (upper(fct_costs.media_source) ~~ '%DOUBLEC%'::text) THEN upper(replace("substring"(fct_costs.campaign, '([^_]*)'::text), ' '::text, ''::text)) ELSE upper(COALESCE(fct_costs.campaign, 'None'::text)) END), fct_costs.adset, fct_costs.ad, ((fct_costs.cost_date)::timestamp without time zone), fct_costs.country_code, fct_costs.app_name, fct_costs.platform, (COALESCE(fct_costs.keywords, 'None'::text)), fct_costs.tenant
37. 2,332.010 4,086.600 ↓ 50.7 272,815 1

Sort (cost=66,063.98..66,077.44 rows=5,382 width=213) (actual time=4,002.127..4,086.600 rows=272,815 loops=1)

  • Sort Key: fct_costs.media_source, (CASE WHEN (upper(fct_costs.media_source) ~~ '%DOUBLEC%'::text) THEN upper(replace("substring"(fct_costs.campaign, '([^_]*)'::text), ' '::text, ''::text)) ELSE upper(COALESCE(fct_costs.campaign, 'None'::text)) END), fct_costs.adset, fct_costs.ad, ((fct_costs.cost_date)::timestamp without time zone), fct_costs.country_code, fct_costs.app_name, fct_costs.platform, (COALESCE(fct_costs.keywords, 'None'::text)), fct_costs.tenant
  • Sort Method: quicksort Memory: 84,995kB
38. 1,754.590 1,754.590 ↓ 50.7 272,815 1

Seq Scan on fct_costs (cost=0.00..65,730.46 rows=5,382 width=213) (actual time=1,028.079..1,754.590 rows=272,815 loops=1)

  • Filter: (date_trunc('quarter'::text, ((cost_date)::date)::timestamp with time zone) = date_trunc('quarter'::text, (('now'::cstring)::date)::timestamp with time zone))
  • Rows Removed by Filter: 803,464
39. 0.050 11.973 ↓ 34.0 34 1

Subquery Scan on *SELECT* 3 (cost=641,813.08..641,813.14 rows=1 width=1,741) (actual time=11.859..11.973 rows=34 loops=1)

40. 0.079 11.923 ↓ 34.0 34 1

GroupAggregate (cost=641,813.08..641,813.12 rows=1 width=1,705) (actual time=11.851..11.923 rows=34 loops=1)

  • Group Key: ((etl_affiliate_costs_v.event_dt)::timestamp without time zone), etl_affiliate_costs_v.reg_country, etl_affiliate_costs_v.platform, etl_affiliate_costs_v.aff_id, etl_affiliate_costs_v.offer_id, etl_affiliate_costs_v.tenant
41. 0.048 11.844 ↓ 35.0 35 1

Sort (cost=641,813.08..641,813.09 rows=1 width=73) (actual time=11.835..11.844 rows=35 loops=1)

  • Sort Key: ((etl_affiliate_costs_v.event_dt)::timestamp without time zone), etl_affiliate_costs_v.reg_country, etl_affiliate_costs_v.platform, etl_affiliate_costs_v.aff_id, etl_affiliate_costs_v.offer_id, etl_affiliate_costs_v.tenant
  • Sort Method: quicksort Memory: 27kB
42. 0.026 11.796 ↓ 35.0 35 1

Subquery Scan on etl_affiliate_costs_v (cost=641,813.03..641,813.07 rows=1 width=73) (actual time=11.738..11.796 rows=35 loops=1)

43. 0.035 11.770 ↓ 35.0 35 1

Unique (cost=641,813.03..641,813.06 rows=1 width=119) (actual time=11.728..11.770 rows=35 loops=1)

44.          

CTE aff_price

45. 0.001 0.020 ↓ 0.0 0 1

ProjectSet (cost=3,274.54..25,274.54 rows=4,000,000 width=188) (actual time=0.020..0.020 rows=0 loops=1)

46. 0.002 0.019 ↓ 0.0 0 1

WindowAgg (cost=3,274.54..4,574.54 rows=40,000 width=184) (actual time=0.019..0.019 rows=0 loops=1)

47. 0.013 0.017 ↓ 0.0 0 1

Sort (cost=3,274.54..3,374.54 rows=40,000 width=176) (actual time=0.016..0.017 rows=0 loops=1)

  • Sort Key: svc_affiliate_price.user_ids, svc_affiliate_price.aff_id, svc_affiliate_price.offer_id, svc_affiliate_price.goal_name, (unnest(svc_affiliate_price.country_code_agg)), svc_affiliate_price.modify_timestamp
  • Sort Method: quicksort Memory: 25kB
48. 0.001 0.004 ↓ 0.0 0 1

ProjectSet (cost=0.00..217.00 rows=40,000 width=176) (actual time=0.004..0.004 rows=0 loops=1)

49. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on svc_affiliate_price (cost=0.00..14.00 rows=400 width=176) (actual time=0.003..0.003 rows=0 loops=1)

50. 0.088 11.735 ↓ 35.0 35 1

Sort (cost=616,538.48..616,538.49 rows=1 width=119) (actual time=11.727..11.735 rows=35 loops=1)

  • Sort Key: a_1.aff_id, a_1.offer_id, a_1.user_id, a_1.event_dt, a_1.goal_name, a_1.complete_dt, a_1.click_id, a_1.reg_country, (CASE WHEN (a_1.complete_dt IS NOT NULL) THEN round((COALESCE((NULLIF(split_part(a_1.goal_name, 'Payout'::text, 2), ''::text))::numeric, p.price, r.price, k.price, o.price, j.price, '0'::numeric) * COALESCE((ts_fx_daily.price)::numeric, '1'::numeric)), 3) ELSE '0'::numeric END), u_2.af_platform, u_2.tenant
  • Sort Method: quicksort Memory: 30kB
51. 0.074 11.647 ↓ 35.0 35 1

Nested Loop Left Join (cost=179,550.71..616,538.47 rows=1 width=119) (actual time=8.547..11.647 rows=35 loops=1)

52. 0.037 11.573 ↓ 35.0 35 1

Nested Loop Left Join (cost=179,550.42..616,535.95 rows=1 width=375) (actual time=8.533..11.573 rows=35 loops=1)

  • Join Filter: ((a_1.event_dt >= j.date_start) AND (a_1.event_dt <= j.date_end) AND (a_1.reg_country = j.country_code) AND (upper(a_1.goal_name) = upper(j.goal_name)))
53. 0.039 11.536 ↓ 35.0 35 1

Nested Loop Left Join (cost=179,550.42..516,533.45 rows=1 width=311) (actual time=8.529..11.536 rows=35 loops=1)

  • Join Filter: ((a_1.event_dt >= o.date_start) AND (a_1.event_dt <= o.date_end) AND (a_1.reg_country = o.country_code) AND (upper(a_1.goal_name) = upper(o.goal_name)) AND ((a_1.offer_id)::integer = o.offer_id))
54. 0.038 11.497 ↓ 35.0 35 1

Nested Loop Left Join (cost=179,550.42..406,530.20 rows=1 width=279) (actual time=8.525..11.497 rows=35 loops=1)

  • Join Filter: ((a_1.event_dt >= k.date_start) AND (a_1.event_dt <= k.date_end) AND (a_1.reg_country = k.country_code) AND (upper(a_1.goal_name) = upper(k.goal_name)) AND ((a_1.aff_id)::integer = k.aff_id))
55. 0.041 11.459 ↓ 35.0 35 1

Nested Loop Left Join (cost=179,550.42..296,526.95 rows=1 width=215) (actual time=8.521..11.459 rows=35 loops=1)

  • Join Filter: ((a_1.event_dt >= r.date_start) AND (a_1.event_dt <= r.date_end) AND (a_1.reg_country = r.country_code) AND (upper(a_1.goal_name) = upper(r.goal_name)) AND ((a_1.aff_id)::integer = r.aff_id) AND ((a_1.offer_id)::integer = r.offer_id))
56. 2.698 11.418 ↓ 35.0 35 1

Hash Left Join (cost=179,550.42..185,734.91 rows=1 width=151) (actual time=8.517..11.418 rows=35 loops=1)

  • Hash Cond: ((a_1.reg_country = p.country_code) AND (upper(a_1.goal_name) = upper(p.goal_name)) AND (a_1.user_id = p.user_id) AND ((a_1.aff_id)::integer = p.aff_id) AND ((a_1.offer_id)::integer = p.offer_id))
  • Join Filter: ((a_1.event_dt >= p.date_start) AND (a_1.event_dt <= p.date_end))
57. 0.053 8.697 ↓ 35.0 35 1

Nested Loop (cost=0.42..712.38 rows=1 width=87) (actual time=5.865..8.697 rows=35 loops=1)

58. 8.364 8.364 ↓ 7.0 35 1

Seq Scan on tbl_affiliate_goals_for_export a_1 (cost=0.00..699.16 rows=5 width=69) (actual time=5.826..8.364 rows=35 loops=1)

  • Filter: (((goal_name = ANY ('{Qualified_depositor,Qualified_introduced_trader,Qualified_introduced_trader_500}'::text[])) OR (goal_name ~~* '%Payout%'::text)) AND (date_trunc('quarter'::text, (event_dt)::timestamp with time zone) = date_trunc('quarter'::text, (('now'::cstring)::date)::timestamp with time zone)))
  • Rows Removed by Filter: 9,482
59. 0.280 0.280 ↑ 1.0 1 35

Index Scan using ix_unq_etl_dim_ums_users on etl_dim_ums_users u_2 (cost=0.42..2.64 rows=1 width=26) (actual time=0.008..0.008 rows=1 loops=35)

  • Index Cond: (user_id = a_1.user_id)
  • Filter: (aff_id <> 'None'::text)
60. 0.001 0.023 ↓ 0.0 0 1

Hash (cost=90,000.00..90,000.00 rows=3,980,000 width=156) (actual time=0.023..0.023 rows=0 loops=1)

  • Buckets: 4,194,304 Batches: 1 Memory Usage: 32,768kB
61. 0.022 0.022 ↓ 0.0 0 1

CTE Scan on aff_price p (cost=0.00..90,000.00 rows=3,980,000 width=156) (actual time=0.022..0.022 rows=0 loops=1)

  • Filter: (user_id <> 0)
62. 0.000 0.000 ↓ 0.0 0 35

CTE Scan on aff_price r (cost=0.00..110,000.00 rows=19,801 width=152) (actual time=0.000..0.000 rows=0 loops=35)

  • Filter: ((aff_id <> 0) AND (offer_id <> 0) AND (user_id = 0))
63. 0.000 0.000 ↓ 0.0 0 35

CTE Scan on aff_price k (cost=0.00..110,000.00 rows=100 width=148) (actual time=0.000..0.000 rows=0 loops=35)

  • Filter: ((aff_id <> 0) AND (user_id = 0) AND (offer_id = 0))
64. 0.000 0.000 ↓ 0.0 0 35

CTE Scan on aff_price o (cost=0.00..110,000.00 rows=100 width=116) (actual time=0.000..0.000 rows=0 loops=35)

  • Filter: ((offer_id <> 0) AND (aff_id = 0) AND (user_id = 0))
65. 0.000 0.000 ↓ 0.0 0 35

CTE Scan on aff_price j (cost=0.00..100,000.00 rows=100 width=144) (actual time=0.000..0.000 rows=0 loops=35)

  • Filter: ((aff_id = 0) AND (user_id = 0))
66. 0.000 0.000 ↓ 0.0 0 35

Index Scan using ts_fx_daily_pkey on ts_fx_daily (cost=0.29..2.51 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=35)

  • Index Cond: ((a_1.event_dt = date) AND (currency = COALESCE(p.currency, r.currency, k.currency, j.currency)))
67. 173.980 11,142.602 ↓ 17.9 158,442 1

Subquery Scan on *SELECT* 4 (cost=192,498.23..1,375,155.87 rows=8,829 width=1,460) (actual time=6,845.177..11,142.602 rows=158,442 loops=1)

68. 348.923 10,968.622 ↓ 17.9 158,442 1

Nested Loop Left Join (cost=192,498.23..1,374,979.29 rows=8,829 width=1,396) (actual time=6,845.170..10,968.622 rows=158,442 loops=1)

  • Join Filter: ((e_1.event_datetime >= h.valid_from) AND (e_1.event_datetime <= h.valid_to))
  • Rows Removed by Join Filter: 166,586
69. 139.793 9,352.163 ↓ 17.9 158,442 1

Hash Left Join (cost=192,497.80..1,369,864.85 rows=8,829 width=446) (actual time=6,844.704..9,352.163 rows=158,442 loops=1)

  • Hash Cond: (u_3.apps_flyer_id = tbl_installs_1.apps_flyer_id)
70. 248.808 8,930.919 ↓ 17.9 158,442 1

Hash Join (cost=146,869.14..1,324,187.46 rows=8,829 width=415) (actual time=6,562.921..8,930.919 rows=158,442 loops=1)

  • Hash Cond: (COALESCE(((e_1.event_attributes ->> 'referral_user_id'::text))::integer, e_1.user_id) = u_3.user_id)
71. 2,132.295 6,542.918 ↓ 18.1 159,564 1

Bitmap Heap Scan on etl_tbl_all_events e_1 (cost=23,271.32..1,200,435.13 rows=8,829 width=114) (actual time=4,423.373..6,542.918 rows=159,564 loops=1)

  • Recheck Cond: (event_type = ANY ('{"First deposit","Account created","Email confirmed","Reg form completed","User verification","Document upload skipped","Documents uploaded","Document upload started","Margin call","Registration status event","Terms and conditions accept","Margin closeout","Account created demo",full_registration_form_completed,"Referral program"}'::text[]))
  • Filter: (date_trunc('quarter'::text, event_datetime) = date_trunc('quarter'::text, (('now'::cstring)::date)::timestamp with time zone))
  • Rows Removed by Filter: 1,606,677
  • Heap Blocks: exact=38,771
72. 4,410.623 4,410.623 ↓ 1.0 1,766,241 1

Bitmap Index Scan on ix_etl_tbl_all_events_t_type (cost=0.00..23,269.11 rows=1,765,728 width=0) (actual time=4,410.623..4,410.623 rows=1,766,241 loops=1)

  • Index Cond: (event_type = ANY ('{"First deposit","Account created","Email confirmed","Reg form completed","User verification","Document upload skipped","Documents uploaded","Document upload started","Margin call","Registration status event","Terms and conditions accept","Margin closeout","Account created demo",full_registration_form_completed,"Referral program"}'::text[]))
73. 436.142 2,139.193 ↓ 2.2 520,847 1

Hash (cost=120,665.23..120,665.23 rows=234,607 width=305) (actual time=2,139.193..2,139.193 rows=520,847 loops=1)

  • Buckets: 524,288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 137,178kB
74. 223.867 1,703.051 ↓ 2.2 520,847 1

Subquery Scan on u_3 (cost=83,910.44..120,665.23 rows=234,607 width=305) (actual time=1,051.252..1,703.051 rows=520,847 loops=1)

75. 424.759 1,479.184 ↓ 2.2 520,847 1

Hash Right Join (cost=83,910.44..118,319.16 rows=234,607 width=3,113) (actual time=1,051.249..1,479.184 rows=520,847 loops=1)

  • Hash Cond: (aff_goals_1.user_id = u_4.user_id)
76.          

CTE aff_goals

77. 3.191 11.065 ↓ 1.1 4,555 1

Sort (cost=996.76..1,007.53 rows=4,307 width=28) (actual time=10.019..11.065 rows=4,555 loops=1)

  • Sort Key: g_1.user_id
  • Sort Method: quicksort Memory: 415kB
78. 4.942 7.874 ↓ 1.1 4,555 1

HashAggregate (cost=693.71..736.78 rows=4,307 width=28) (actual time=6.316..7.874 rows=4,555 loops=1)

  • Group Key: g_1.user_id
79. 2.932 2.932 ↓ 1.0 7,156 1

Seq Scan on tbl_affiliate_goals_for_export g_1 (cost=0.00..496.95 rows=7,155 width=28) (actual time=0.035..2.932 rows=7,156 loops=1)

  • Filter: (goal_name <> 'INSTALL_EVENT'::text)
  • Rows Removed by Filter: 2,361
80. 13.529 13.529 ↓ 1.1 4,555 1

CTE Scan on aff_goals aff_goals_1 (cost=0.00..86.14 rows=4,307 width=8) (actual time=10.022..13.529 rows=4,555 loops=1)

81. 448.669 1,040.896 ↓ 2.2 520,847 1

Hash (cost=79,970.32..79,970.32 rows=234,607 width=236) (actual time=1,040.895..1,040.896 rows=520,847 loops=1)

  • Buckets: 524,288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 129,641kB
82. 592.227 592.227 ↓ 2.2 520,847 1

Seq Scan on etl_dim_ums_users u_4 (cost=0.00..79,970.32 rows=234,607 width=236) (actual time=0.023..592.227 rows=520,847 loops=1)

  • Filter: ((test_sign IS FALSE) AND (user_role = ANY ('{USER,PROFESSIONAL}'::text[])) AND (user_id <> ALL ('{10789518,10788054}'::bigint[])))
  • Rows Removed by Filter: 306,681
83. 119.813 281.451 ↓ 1.0 279,565 1

Hash (cost=42,143.07..42,143.07 rows=278,847 width=31) (actual time=281.451..281.451 rows=279,565 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 21,792kB
84. 161.638 161.638 ↓ 1.0 279,565 1

Index Scan using ix_tbl_installs_1 on tbl_installs tbl_installs_1 (cost=0.43..42,143.07 rows=278,847 width=31) (actual time=0.047..161.638 rows=279,565 loops=1)

  • Index Cond: (app_name = 'Investmate'::text)
85. 1,267.536 1,267.536 ↑ 1.0 2 158,442

Index Scan using ix_etl_tbl_user_media_source_hist_1 on etl_tbl_user_media_source_hist h (cost=0.42..0.52 rows=2 width=82) (actual time=0.007..0.008 rows=2 loops=158,442)

  • Index Cond: (u_3.user_id = user_id)
86. 1.178 21,687.506 ↓ 10.1 1,141 1

Subquery Scan on *SELECT* 5 (cost=1,735,630.16..1,738,122.46 rows=113 width=1,692) (actual time=20,925.403..21,687.506 rows=1,141 loops=1)

87. 686.700 21,686.328 ↓ 10.1 1,141 1

GroupAggregate (cost=1,735,630.16..1,738,119.92 rows=113 width=1,628) (actual time=20,925.393..21,686.328 rows=1,141 loops=1)

  • Group Key: (COALESCE(u_5.reg_country, u_5.af_country_code)), u_5.apps_flyer_id, e_2.event_type, u_5.adset, u_5.ad, u_5.af_install_ts, e_2.app_name, u_5.af_platform, u_5.user_id, u_5.aff_id, u_5.offer_id, (CASE WHEN (tbl_installs_2.apps_flyer_id IS NOT NULL) THEN 'InvestmateApp'::text ELSE u_5.media_source END), (CASE WHEN (tbl_installs_2.apps_flyer_id IS NOT NULL) THEN ((u_5.media_source || ' '::text) || u_5.campaign) ELSE u_5.campaign END), u_5.demo_sign, u_5.deposit_bin, u_5.program_id, u_5.user_role, u_5.tenant, u_5.deposit_bin_total
  • Filter: (date_trunc('quarter'::text, min(e_2.event_datetime)) = date_trunc('quarter'::text, (('now'::cstring)::date)::timestamp with time zone))
  • Rows Removed by Filter: 245,689
88. 2,687.623 20,999.628 ↓ 13.8 312,578 1

Sort (cost=1,735,630.16..1,735,686.74 rows=22,629 width=427) (actual time=20,882.017..20,999.628 rows=312,578 loops=1)

  • Sort Key: (COALESCE(u_5.reg_country, u_5.af_country_code)), u_5.apps_flyer_id, u_5.adset, u_5.ad, u_5.af_install_ts, e_2.app_name, u_5.af_platform, u_5.user_id, u_5.aff_id, u_5.offer_id, (CASE WHEN (tbl_installs_2.apps_flyer_id IS NOT NULL) THEN 'InvestmateApp'::text ELSE u_5.media_source END), (CASE WHEN (tbl_installs_2.apps_flyer_id IS NOT NULL) THEN ((u_5.media_source || ' '::text) || u_5.campaign) ELSE u_5.campaign END), u_5.demo_sign, u_5.deposit_bin, u_5.program_id, u_5.user_role, u_5.tenant, u_5.deposit_bin_total
  • Sort Method: quicksort Memory: 160,525kB
89. 360.099 18,312.005 ↓ 13.8 312,578 1

Nested Loop Left Join (cost=229,324.80..1,733,993.42 rows=22,629 width=427) (actual time=12,372.358..18,312.005 rows=312,578 loops=1)

  • Join Filter: ((e_2.event_datetime >= h_1.valid_from) AND (e_2.event_datetime <= h_1.valid_to))
  • Rows Removed by Join Filter: 43,642
90. 258.531 16,701.594 ↓ 13.8 312,578 1

Hash Left Join (cost=229,324.37..1,721,394.09 rows=22,629 width=334) (actual time=12,372.328..16,701.594 rows=312,578 loops=1)

  • Hash Cond: (u_5.apps_flyer_id = tbl_installs_2.apps_flyer_id)
91. 385.936 16,141.476 ↓ 13.8 312,578 1

Hash Join (cost=183,695.71..1,675,640.52 rows=22,629 width=303) (actual time=12,070.421..16,141.476 rows=312,578 loops=1)

  • Hash Cond: (e_2.user_id = u_5.user_id)
92. 3,722.665 13,371.172 ↓ 13.9 313,695 1

Bitmap Heap Scan on etl_tbl_all_events e_2 (cost=60,097.89..1,551,731.55 rows=22,629 width=40) (actual time=9,685.551..13,371.172 rows=313,695 loops=1)

  • Recheck Cond: (event_type = 'Reg step opened'::text)
  • Filter: ((event_attributes ->> 'step'::text) = '0'::text)
  • Rows Removed by Filter: 3,971,723
  • Heap Blocks: exact=164,118
93. 9,648.507 9,648.507 ↑ 1.1 4,285,418 1

Bitmap Index Scan on ix_etl_tbl_all_events_t_type (cost=0.00..60,092.23 rows=4,525,809 width=0) (actual time=9,648.507..9,648.507 rows=4,285,418 loops=1)

  • Index Cond: (event_type = 'Reg step opened'::text)
94. 443.293 2,384.368 ↓ 2.2 520,847 1

Hash (cost=120,665.23..120,665.23 rows=234,607 width=267) (actual time=2,384.368..2,384.368 rows=520,847 loops=1)

  • Buckets: 524,288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 119,935kB
95. 257.701 1,941.075 ↓ 2.2 520,847 1

Subquery Scan on u_5 (cost=83,910.44..120,665.23 rows=234,607 width=267) (actual time=1,169.264..1,941.075 rows=520,847 loops=1)

96. 510.470 1,683.374 ↓ 2.2 520,847 1

Hash Right Join (cost=83,910.44..118,319.16 rows=234,607 width=3,183) (actual time=1,169.260..1,683.374 rows=520,847 loops=1)

  • Hash Cond: (aff_goals_2.user_id = u_6.user_id)
97.          

CTE aff_goals

98. 3.392 11.561 ↓ 1.1 4,555 1

Sort (cost=996.76..1,007.53 rows=4,307 width=28) (actual time=10.384..11.561 rows=4,555 loops=1)

  • Sort Key: g_2.user_id
  • Sort Method: quicksort Memory: 415kB
99. 5.026 8.169 ↓ 1.1 4,555 1

HashAggregate (cost=693.71..736.78 rows=4,307 width=28) (actual time=6.555..8.169 rows=4,555 loops=1)

  • Group Key: g_2.user_id
100. 3.143 3.143 ↓ 1.0 7,156 1

Seq Scan on tbl_affiliate_goals_for_export g_2 (cost=0.00..496.95 rows=7,155 width=28) (actual time=0.047..3.143 rows=7,156 loops=1)

  • Filter: (goal_name <> 'INSTALL_EVENT'::text)
  • Rows Removed by Filter: 2,361
101. 14.399 14.399 ↓ 1.1 4,555 1

CTE Scan on aff_goals aff_goals_2 (cost=0.00..86.14 rows=4,307 width=8) (actual time=10.389..14.399 rows=4,555 loops=1)

102. 464.231 1,158.505 ↓ 2.2 520,847 1

Hash (cost=79,970.32..79,970.32 rows=234,607 width=198) (actual time=1,158.505..1,158.505 rows=520,847 loops=1)

  • Buckets: 524,288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 115,079kB
103. 694.274 694.274 ↓ 2.2 520,847 1

Seq Scan on etl_dim_ums_users u_6 (cost=0.00..79,970.32 rows=234,607 width=198) (actual time=0.028..694.274 rows=520,847 loops=1)

  • Filter: ((test_sign IS FALSE) AND (user_role = ANY ('{USER,PROFESSIONAL}'::text[])) AND (user_id <> ALL ('{10789518,10788054}'::bigint[])))
  • Rows Removed by Filter: 306,681
104. 130.489 301.587 ↓ 1.0 279,565 1

Hash (cost=42,143.07..42,143.07 rows=278,847 width=31) (actual time=301.586..301.587 rows=279,565 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 21,792kB
105. 171.098 171.098 ↓ 1.0 279,565 1

Index Scan using ix_tbl_installs_1 on tbl_installs tbl_installs_2 (cost=0.43..42,143.07 rows=278,847 width=31) (actual time=0.029..171.098 rows=279,565 loops=1)

  • Index Cond: (app_name = 'Investmate'::text)
106. 1,250.312 1,250.312 ↑ 2.0 1 312,578

Index Scan using ix_etl_tbl_user_media_source_hist_1 on etl_tbl_user_media_source_hist h_1 (cost=0.42..0.52 rows=2 width=91) (actual time=0.004..0.004 rows=1 loops=312,578)

  • Index Cond: (u_5.user_id = user_id)
107. 0.840 3,524.455 ↓ 20.4 633 1

Subquery Scan on *SELECT* 6 (cost=213,129.93..258,079.33 rows=31 width=1,517) (actual time=3,270.238..3,524.455 rows=633 loops=1)

108. 1.703 3,523.615 ↓ 20.4 633 1

Nested Loop Left Join (cost=213,129.93..258,078.63 rows=31 width=1,453) (actual time=3,270.230..3,523.615 rows=633 loops=1)

  • Join Filter: ((e_3.event_datetime >= h_2.valid_from) AND (e_3.event_datetime <= h_2.valid_to))
  • Rows Removed by Join Filter: 280
109. 100.022 3,518.114 ↓ 20.4 633 1

Hash Right Join (cost=213,129.51..258,060.67 rows=31 width=429) (actual time=3,270.199..3,518.114 rows=633 loops=1)

  • Hash Cond: (tbl_installs_3.apps_flyer_id = u_7.apps_flyer_id)
110. 159.551 159.551 ↓ 1.0 279,565 1

Index Scan using ix_tbl_installs_1 on tbl_installs tbl_installs_3 (cost=0.43..42,143.07 rows=278,847 width=31) (actual time=0.024..159.551 rows=279,565 loops=1)

  • Index Cond: (app_name = 'Investmate'::text)
111. 1.342 3,258.541 ↓ 20.4 633 1

Hash (cost=213,128.69..213,128.69 rows=31 width=398) (actual time=3,258.540..3,258.541 rows=633 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 194kB
112. 149.499 3,257.199 ↓ 20.4 633 1

Hash Join (cost=175,493.81..213,128.69 rows=31 width=398) (actual time=2,602.156..3,257.199 rows=633 loops=1)

  • Hash Cond: (u_7.user_id = e_3.user_id)
113. 501.763 1,776.792 ↓ 2.2 520,847 1

Hash Right Join (cost=83,910.44..118,319.16 rows=234,607 width=3,134) (actual time=1,271.077..1,776.792 rows=520,847 loops=1)

  • Hash Cond: (aff_goals_3.user_id = u_7.user_id)
114.          

CTE aff_goals

115. 3.702 12.885 ↓ 1.1 4,555 1

Sort (cost=996.76..1,007.53 rows=4,307 width=28) (actual time=11.485..12.885 rows=4,555 loops=1)

  • Sort Key: g_3.user_id
  • Sort Method: quicksort Memory: 415kB
116. 5.844 9.183 ↓ 1.1 4,555 1

HashAggregate (cost=693.71..736.78 rows=4,307 width=28) (actual time=7.457..9.183 rows=4,555 loops=1)

  • Group Key: g_3.user_id
117. 3.339 3.339 ↓ 1.0 7,156 1

Seq Scan on tbl_affiliate_goals_for_export g_3 (cost=0.00..496.95 rows=7,155 width=28) (actual time=0.034..3.339 rows=7,156 loops=1)

  • Filter: (goal_name <> 'INSTALL_EVENT'::text)
  • Rows Removed by Filter: 2,361
118. 15.768 15.768 ↓ 1.1 4,555 1

CTE Scan on aff_goals aff_goals_3 (cost=0.00..86.14 rows=4,307 width=8) (actual time=11.489..15.768 rows=4,555 loops=1)

119. 526.624 1,259.261 ↓ 2.2 520,847 1

Hash (cost=79,970.32..79,970.32 rows=234,607 width=225) (actual time=1,259.260..1,259.261 rows=520,847 loops=1)

  • Buckets: 524,288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 123,752kB
120. 732.637 732.637 ↓ 2.2 520,847 1

Seq Scan on etl_dim_ums_users u_7 (cost=0.00..79,970.32 rows=234,607 width=225) (actual time=0.033..732.637 rows=520,847 loops=1)

  • Filter: ((test_sign IS FALSE) AND (user_role = ANY ('{USER,PROFESSIONAL}'::text[])) AND (user_id <> ALL ('{10789518,10788054}'::bigint[])))
  • Rows Removed by Filter: 306,681
121. 0.307 1,330.908 ↓ 20.7 641 1

Hash (cost=91,582.98..91,582.98 rows=31 width=112) (actual time=1,330.908..1,330.908 rows=641 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 64kB
122. 0.302 1,330.601 ↓ 20.7 641 1

Subquery Scan on e_3 (cost=91,362.42..91,582.98 rows=31 width=112) (actual time=1,299.428..1,330.601 rows=641 loops=1)

123. 1,330.299 1,330.299 ↓ 20.7 641 1

CTE Scan on s (cost=91,362.42..91,582.67 rows=31 width=232) (actual time=1,299.421..1,330.299 rows=641 loops=1)

  • Filter: (date_trunc('quarter'::text, event_datetime) = date_trunc('quarter'::text, (('now'::cstring)::date)::timestamp with time zone))
  • Rows Removed by Filter: 5,952
124.          

CTE client_events

125. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.00 rows=0 width=201) (never executed)

  • One-Time Filter: false
126.          

CTE server_events

127. 474.066 474.066 ↓ 1.0 6,593 1

Seq Scan on ums_user_status_audit_log t1 (cost=0.00..20,876.79 rows=6,293 width=248) (actual time=0.572..474.066 rows=6,593 loops=1)

  • Filter: (status = 'SUSPENDED'::text)
  • Rows Removed by Filter: 1,078,381
128.          

CTE s

129. 6.977 1,320.744 ↓ 1.0 6,593 1

Hash Left Join (cost=70,296.41..70,485.63 rows=6,293 width=224) (actual time=834.243..1,320.744 rows=6,593 loops=1)

  • Hash Cond: (server_events.user_id = a.user_id)
130. 480.424 480.424 ↓ 1.0 6,593 1

CTE Scan on server_events (cost=0.00..125.86 rows=6,293 width=216) (actual time=0.576..480.424 rows=6,593 loops=1)

131. 240.510 833.343 ↓ 2.2 523,614 1

Hash (cost=67,328.32..67,328.32 rows=237,447 width=16) (actual time=833.342..833.343 rows=523,614 loops=1)

  • Buckets: 524,288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 28,641kB
132. 592.833 592.833 ↓ 2.2 523,614 1

Seq Scan on etl_tbl_ums_users a (cost=0.00..67,328.32 rows=237,447 width=16) (actual time=0.016..592.833 rows=523,614 loops=1)

  • Filter: (user_role = ANY ('{USER,PROFESSIONAL}'::text[]))
  • Rows Removed by Filter: 303,914
133. 3.798 3.798 ↑ 2.0 1 633

Index Scan using ix_etl_tbl_user_media_source_hist_1 on etl_tbl_user_media_source_hist h_2 (cost=0.42..0.52 rows=2 width=91) (actual time=0.005..0.006 rows=1 loops=633)

  • Index Cond: (u_7.user_id = user_id)
134. 426.995 64,580.973 ↓ 17.9 245,822 1

Subquery Scan on *SELECT* 7 (cost=617,423.49..623,854.89 rows=13,757 width=1,440) (actual time=56,459.950..64,580.973 rows=245,822 loops=1)

135. 7,359.878 64,153.978 ↓ 17.9 245,822 1

GroupAggregate (cost=617,423.49..623,510.97 rows=13,757 width=1,400) (actual time=56,459.940..64,153.978 rows=245,822 loops=1)

  • Group Key: (COALESCE(u_8.reg_country, u_8.af_country_code)), u_8.apps_flyer_id, u_8.adset, u_8.ad, fct_transactions.transaction_type, (date(fct_transactions.ts_timestamp)), u_8.af_install_ts, fct_transactions.transaction_status, u_8.af_platform, fct_transactions.currency, u_8.user_id, u_8.aff_id, u_8.offer_id, (CASE WHEN (tbl_installs_4.apps_flyer_id IS NOT NULL) THEN 'InvestmateApp'::text ELSE u_8.media_source END), (CASE WHEN (tbl_installs_4.apps_flyer_id IS NOT NULL) THEN ((u_8.media_source || ' '::text) || u_8.campaign) ELSE u_8.campaign END), fct_transactions.first_deposit, fct_transactions.recreation_deposit, (((fct_transactions.transaction_type = 'EXCHANGE'::text) AND (fct_transactions.transaction_status = 'PROCESSED'::text) AND (fct_transactions.ts_timestamp = u_8.first_excom_ts))), u_8.demo_sign, u_8.deposit_bin, u_8.program_id, (CASE WHEN (tbl_installs_4.apps_flyer_id IS NOT NULL) THEN 'InvestmateApp'::text ELSE h_3.media_source END), (CASE WHEN (tbl_installs_4.apps_flyer_id IS NOT NULL) THEN ((h_3.media_source || ' '::text) || h_3.campaign) ELSE h_3.campaign END), h_3.adset, h_3.ad, h_3.aff_id, h_3.offer_id, u_8.keywords, u_8.ad_conceptname, u_8.ad_creativename, u_8.ad_creativetype, u_8.user_role, u_8.tenant, u_8.deposit_bin_total, ((row_to_json((SubPlan 10)))::jsonb)
136. 15,686.530 56,794.100 ↓ 83.1 1,143,046 1

Sort (cost=617,423.49..617,457.89 rows=13,757 width=492) (actual time=56,459.832..56,794.100 rows=1,143,046 loops=1)

  • Sort Key: (COALESCE(u_8.reg_country, u_8.af_country_code)), u_8.apps_flyer_id, u_8.adset, u_8.ad, fct_transactions.transaction_type, (date(fct_transactions.ts_timestamp)), u_8.af_install_ts, fct_transactions.transaction_status, u_8.af_platform, fct_transactions.currency, u_8.user_id, u_8.aff_id, u_8.offer_id, (CASE WHEN (tbl_installs_4.apps_flyer_id IS NOT NULL) THEN 'InvestmateApp'::text ELSE u_8.media_source END), (CASE WHEN (tbl_installs_4.apps_flyer_id IS NOT NULL) THEN ((u_8.media_source || ' '::text) || u_8.campaign) ELSE u_8.campaign END), fct_transactions.first_deposit, fct_transactions.recreation_deposit, (((fct_transactions.transaction_type = 'EXCHANGE'::text) AND (fct_transactions.transaction_status = 'PROCESSED'::text) AND (fct_transactions.ts_timestamp = u_8.first_excom_ts))), u_8.demo_sign, u_8.deposit_bin, u_8.program_id, (CASE WHEN (tbl_installs_4.apps_flyer_id IS NOT NULL) THEN 'InvestmateApp'::text ELSE h_3.media_source END), (CASE WHEN (tbl_installs_4.apps_flyer_id IS NOT NULL) THEN ((h_3.media_source || ' '::text) || h_3.campaign) ELSE h_3.campaign END), h_3.adset, h_3.ad, h_3.aff_id, h_3.offer_id, u_8.keywords, u_8.ad_conceptname, u_8.ad_creativename, u_8.ad_creativetype, u_8.user_role, u_8.tenant, u_8.deposit_bin_total, ((row_to_json((SubPlan 10)))::jsonb)
  • Sort Method: quicksort Memory: 642,066kB
137. 3,898.615 41,107.570 ↓ 83.1 1,143,046 1

Nested Loop (cost=169,227.33..616,477.85 rows=13,757 width=492) (actual time=2,675.818..41,107.570 rows=1,143,046 loops=1)

138. 3,094.264 30,350.679 ↓ 83.1 1,143,046 1

Nested Loop Left Join (cost=169,226.91..585,719.52 rows=13,757 width=441) (actual time=2,675.775..30,350.679 rows=1,143,046 loops=1)

  • Join Filter: ((fct_transactions.ts_timestamp >= h_3.valid_from) AND (fct_transactions.ts_timestamp <= h_3.valid_to))
  • Rows Removed by Join Filter: 3,273,275
139. 842.355 22,684.231 ↓ 83.1 1,143,046 1

Hash Left Join (cost=169,226.48..578,128.70 rows=13,757 width=374) (actual time=2,675.746..22,684.231 rows=1,143,046 loops=1)

  • Hash Cond: (u_8.apps_flyer_id = tbl_installs_4.apps_flyer_id)
140. 1,019.461 21,495.223 ↓ 83.1 1,143,046 1

Hash Join (cost=123,597.82..532,424.10 rows=13,757 width=343) (actual time=2,328.786..21,495.223 rows=1,143,046 loops=1)

  • Hash Cond: (fct_transactions.user_id = u_8.user_id)
141. 18,164.415 18,164.415 ↓ 83.4 1,147,088 1

Seq Scan on etl_fct_transactions fct_transactions (cost=0.00..408,637.12 rows=13,757 width=88) (actual time=12.833..18,164.415 rows=1,147,088 loops=1)

  • Filter: ((transaction_type <> 'DEMO_TRANSFER'::text) AND (date_trunc('quarter'::text, ts_timestamp) = date_trunc('quarter'::text, (('now'::cstring)::date)::timestamp with time zone)) AND ((transaction_type = ANY ('{TRANSFER,DEPOSIT,WITHDRAWAL,TRADE,REIMBURSEMENT,SWAP,EXCHANGE,INACTIVITY_FEE,EXCHANGE_COMMISSION,REFERRER_TRADE_COMMISSION,TRADE_COMMISSION}'::text[])) OR ((transaction_type = 'ADJUSTMENT'::text) AND (details_init_reason = 'BONUS'::text))))
  • Rows Removed by Filter: 4,818,933
142. 438.318 2,311.347 ↓ 2.2 520,847 1

Hash (cost=120,665.23..120,665.23 rows=234,607 width=291) (actual time=2,311.347..2,311.347 rows=520,847 loops=1)

  • Buckets: 524,288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 125,321kB
143. 248.167 1,873.029 ↓ 2.2 520,847 1

Subquery Scan on u_8 (cost=83,910.44..120,665.23 rows=234,607 width=291) (actual time=1,140.057..1,873.029 rows=520,847 loops=1)

144. 481.515 1,624.862 ↓ 2.2 520,847 1

Hash Right Join (cost=83,910.44..118,319.16 rows=234,607 width=3,183) (actual time=1,140.054..1,624.862 rows=520,847 loops=1)

  • Hash Cond: (aff_goals_4.user_id = u_9.user_id)
145.          

CTE aff_goals

146. 3.336 11.601 ↓ 1.1 4,555 1

Sort (cost=996.76..1,007.53 rows=4,307 width=28) (actual time=10.530..11.601 rows=4,555 loops=1)

  • Sort Key: g_4.user_id
  • Sort Method: quicksort Memory: 415kB
147. 5.189 8.265 ↓ 1.1 4,555 1

HashAggregate (cost=693.71..736.78 rows=4,307 width=28) (actual time=6.582..8.265 rows=4,555 loops=1)

  • Group Key: g_4.user_id
148. 3.076 3.076 ↓ 1.0 7,156 1

Seq Scan on tbl_affiliate_goals_for_export g_4 (cost=0.00..496.95 rows=7,155 width=28) (actual time=0.032..3.076 rows=7,156 loops=1)

  • Filter: (goal_name <> 'INSTALL_EVENT'::text)
  • Rows Removed by Filter: 2,361
149. 14.162 14.162 ↓ 1.1 4,555 1

CTE Scan on aff_goals aff_goals_4 (cost=0.00..86.14 rows=4,307 width=8) (actual time=10.534..14.162 rows=4,555 loops=1)

150. 454.126 1,129.185 ↓ 2.2 520,847 1

Hash (cost=79,970.32..79,970.32 rows=234,607 width=222) (actual time=1,129.185..1,129.185 rows=520,847 loops=1)

  • Buckets: 524,288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 119,530kB
151. 675.059 675.059 ↓ 2.2 520,847 1

Seq Scan on etl_dim_ums_users u_9 (cost=0.00..79,970.32 rows=234,607 width=222) (actual time=0.028..675.059 rows=520,847 loops=1)

  • Filter: ((test_sign IS FALSE) AND (user_role = ANY ('{USER,PROFESSIONAL}'::text[])) AND (user_id <> ALL ('{10789518,10788054}'::bigint[])))
  • Rows Removed by Filter: 306,681
152. 152.114 346.653 ↓ 1.0 279,565 1

Hash (cost=42,143.07..42,143.07 rows=278,847 width=31) (actual time=346.653..346.653 rows=279,565 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 21,792kB
153. 194.539 194.539 ↓ 1.0 279,565 1

Index Scan using ix_tbl_installs_1 on tbl_installs tbl_installs_4 (cost=0.43..42,143.07 rows=278,847 width=31) (actual time=0.020..194.539 rows=279,565 loops=1)

  • Index Cond: (app_name = 'Investmate'::text)
154. 4,572.184 4,572.184 ↓ 2.0 4 1,143,046

Index Scan using ix_etl_tbl_user_media_source_hist_1 on etl_tbl_user_media_source_hist h_3 (cost=0.42..0.52 rows=2 width=91) (actual time=0.002..0.004 rows=4 loops=1,143,046)

  • Index Cond: (u_8.user_id = user_id)
155. 4,572.184 4,572.184 ↑ 1.0 1 1,143,046

Index Scan using as_account_pkey on as_account a_2 (cost=0.43..2.19 rows=1 width=23) (actual time=0.004..0.004 rows=1 loops=1,143,046)

  • Index Cond: (id = fct_transactions.user_account_id)
156.          

SubPlan (for Nested Loop)

157. 2,286.092 2,286.092 ↑ 1.0 1 1,143,046

Result (cost=0.00..0.02 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=1,143,046)

158. 182.352 26,292.972 ↓ 12.5 163,361 1

Subquery Scan on *SELECT* 8 (cost=551,470.44..553,564.68 rows=13,089 width=1,518) (actual time=24,691.235..26,292.972 rows=163,361 loops=1)

159. 1,156.999 26,110.620 ↓ 12.5 163,361 1

GroupAggregate (cost=551,470.44..553,204.73 rows=13,089 width=1,482) (actual time=24,691.223..26,110.620 rows=163,361 loops=1)

  • Group Key: (COALESCE(u_10.reg_country, u_10.af_country_code)), u_10.apps_flyer_id, u_10.adset, u_10.ad, (date(fct_transactions_1.ts_timestamp)), u_10.af_install_ts, u_10.af_platform, u_10.user_id, u_10.aff_id, u_10.offer_id, fct_transactions_1.user_id, (CASE WHEN (tbl_installs_5.apps_flyer_id IS NOT NULL) THEN 'InvestmateApp'::text ELSE u_10.media_source END), (CASE WHEN (tbl_installs_5.apps_flyer_id IS NOT NULL) THEN ((u_10.media_source || ' '::text) || u_10.campaign) ELSE u_10.campaign END), u_10.demo_sign, u_10.deposit_bin, u_10.program_id, (CASE WHEN (tbl_installs_5.apps_flyer_id IS NOT NULL) THEN 'InvestmateApp'::text ELSE h_4.media_source END), (CASE WHEN (tbl_installs_5.apps_flyer_id IS NOT NULL) THEN ((h_4.media_source || ' '::text) || h_4.campaign) ELSE h_4.campaign END), h_4.adset, h_4.ad, h_4.aff_id, h_4.offer_id, u_10.keywords, u_10.ad_conceptname, u_10.ad_creativename, u_10.ad_creativetype, u_10.user_role, u_10.tenant, u_10.deposit_bin_total
160. 10,162.951 24,953.621 ↓ 76.8 1,005,601 1

Sort (cost=551,470.44..551,503.16 rows=13,089 width=436) (actual time=24,691.181..24,953.621 rows=1,005,601 loops=1)

  • Sort Key: (COALESCE(u_10.reg_country, u_10.af_country_code)), u_10.apps_flyer_id, u_10.adset, u_10.ad, (date(fct_transactions_1.ts_timestamp)), u_10.af_install_ts, u_10.af_platform, u_10.user_id, u_10.aff_id, u_10.offer_id, (CASE WHEN (tbl_installs_5.apps_flyer_id IS NOT NULL) THEN 'InvestmateApp'::text ELSE u_10.media_source END), (CASE WHEN (tbl_installs_5.apps_flyer_id IS NOT NULL) THEN ((u_10.media_source || ' '::text) || u_10.campaign) ELSE u_10.campaign END), u_10.demo_sign, u_10.deposit_bin, u_10.program_id, (CASE WHEN (tbl_installs_5.apps_flyer_id IS NOT NULL) THEN 'InvestmateApp'::text ELSE h_4.media_source END), (CASE WHEN (tbl_installs_5.apps_flyer_id IS NOT NULL) THEN ((h_4.media_source || ' '::text) || h_4.campaign) ELSE h_4.campaign END), h_4.adset, h_4.ad, h_4.aff_id, h_4.offer_id, u_10.keywords, u_10.ad_conceptname, u_10.ad_creativename, u_10.ad_creativetype, u_10.user_role, u_10.tenant, u_10.deposit_bin_total
  • Sort Method: quicksort Memory: 504,710kB
161. 1,940.631 14,790.670 ↓ 76.8 1,005,601 1

Nested Loop Left Join (cost=169,226.91..550,575.41 rows=13,089 width=436) (actual time=2,385.923..14,790.670 rows=1,005,601 loops=1)

  • Join Filter: ((fct_transactions_1.ts_timestamp >= h_4.valid_from) AND (fct_transactions_1.ts_timestamp <= h_4.valid_to))
  • Rows Removed by Join Filter: 2,765,366
162. 568.836 9,833.236 ↓ 76.8 1,005,601 1

Hash Left Join (cost=169,226.48..543,189.57 rows=13,089 width=343) (actual time=2,385.896..9,833.236 rows=1,005,601 loops=1)

  • Hash Cond: (u_10.apps_flyer_id = tbl_installs_5.apps_flyer_id)
163. 678.496 8,995.336 ↓ 76.8 1,005,601 1

Hash Join (cost=123,597.82..497,488.67 rows=13,089 width=312) (actual time=2,114.601..8,995.336 rows=1,005,601 loops=1)

  • Hash Cond: (fct_transactions_1.user_id = u_10.user_id)
164. 6,206.442 6,206.442 ↓ 77.0 1,008,228 1

Seq Scan on etl_fct_transactions fct_transactions_1 (cost=0.00..373,710.88 rows=13,089 width=73) (actual time=2.733..6,206.442 rows=1,008,228 loops=1)

  • Filter: ((transaction_type <> 'DEMO_TRANSFER'::text) AND (transaction_type = ANY ('{DEPOSIT,WITHDRAWAL,TRADE,REIMBURSEMENT,SWAP}'::text[])) AND (date_trunc('quarter'::text, ts_timestamp) = date_trunc('quarter'::text, (('now'::cstring)::date)::timestamp with time zone)))
  • Rows Removed by Filter: 4,957,793
165. 418.818 2,110.398 ↓ 2.2 520,847 1

Hash (cost=120,665.23..120,665.23 rows=234,607 width=267) (actual time=2,110.397..2,110.398 rows=520,847 loops=1)

  • Buckets: 524,288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 119,935kB
166. 226.359 1,691.580 ↓ 2.2 520,847 1

Subquery Scan on u_10 (cost=83,910.44..120,665.23 rows=234,607 width=267) (actual time=1,032.595..1,691.580 rows=520,847 loops=1)

167. 429.639 1,465.221 ↓ 2.2 520,847 1

Hash Right Join (cost=83,910.44..118,319.16 rows=234,607 width=3,183) (actual time=1,032.592..1,465.221 rows=520,847 loops=1)

  • Hash Cond: (aff_goals_5.user_id = u_11.user_id)
168.          

CTE aff_goals

169. 3.141 10.967 ↓ 1.1 4,555 1

Sort (cost=996.76..1,007.53 rows=4,307 width=28) (actual time=9.960..10.967 rows=4,555 loops=1)

  • Sort Key: g_5.user_id
  • Sort Method: quicksort Memory: 415kB
170. 4.843 7.826 ↓ 1.1 4,555 1

HashAggregate (cost=693.71..736.78 rows=4,307 width=28) (actual time=6.303..7.826 rows=4,555 loops=1)

  • Group Key: g_5.user_id
171. 2.983 2.983 ↓ 1.0 7,156 1

Seq Scan on tbl_affiliate_goals_for_export g_5 (cost=0.00..496.95 rows=7,155 width=28) (actual time=0.033..2.983 rows=7,156 loops=1)

  • Filter: (goal_name <> 'INSTALL_EVENT'::text)
  • Rows Removed by Filter: 2,361
172. 13.269 13.269 ↓ 1.1 4,555 1

CTE Scan on aff_goals aff_goals_5 (cost=0.00..86.14 rows=4,307 width=8) (actual time=9.963..13.269 rows=4,555 loops=1)

173. 425.322 1,022.313 ↓ 2.2 520,847 1

Hash (cost=79,970.32..79,970.32 rows=234,607 width=198) (actual time=1,022.313..1,022.313 rows=520,847 loops=1)

  • Buckets: 524,288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 115,079kB
174. 596.991 596.991 ↓ 2.2 520,847 1

Seq Scan on etl_dim_ums_users u_11 (cost=0.00..79,970.32 rows=234,607 width=198) (actual time=0.018..596.991 rows=520,847 loops=1)

  • Filter: ((test_sign IS FALSE) AND (user_role = ANY ('{USER,PROFESSIONAL}'::text[])) AND (user_id <> ALL ('{10789518,10788054}'::bigint[])))
  • Rows Removed by Filter: 306,681
175. 117.085 269.064 ↓ 1.0 279,565 1

Hash (cost=42,143.07..42,143.07 rows=278,847 width=31) (actual time=269.064..269.064 rows=279,565 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 21,792kB
176. 151.979 151.979 ↓ 1.0 279,565 1

Index Scan using ix_tbl_installs_1 on tbl_installs tbl_installs_5 (cost=0.43..42,143.07 rows=278,847 width=31) (actual time=0.025..151.979 rows=279,565 loops=1)

  • Index Cond: (app_name = 'Investmate'::text)
177. 3,016.803 3,016.803 ↓ 2.0 4 1,005,601

Index Scan using ix_etl_tbl_user_media_source_hist_1 on etl_tbl_user_media_source_hist h_4 (cost=0.42..0.52 rows=2 width=91) (actual time=0.002..0.003 rows=4 loops=1,005,601)

  • Index Cond: (u_10.user_id = user_id)
178. 2.538 5,969.891 ↑ 22.4 2,234 1

Subquery Scan on *SELECT* 9 (cost=579,414.10..586,670.48 rows=50,044 width=1,518) (actual time=5,866.174..5,969.891 rows=2,234 loops=1)

179. 80.311 5,967.353 ↑ 22.4 2,234 1

GroupAggregate (cost=579,414.10..585,294.27 rows=50,044 width=1,482) (actual time=5,866.163..5,967.353 rows=2,234 loops=1)

  • Group Key: (COALESCE(u_12.reg_country, u_12.af_country_code)), u_12.apps_flyer_id, u_12.adset, u_12.ad, u_12.af_install_ts, u_12.af_platform, u_12.user_id, u_12.aff_id, u_12.offer_id, fct_transactions_2.user_id, (CASE WHEN (tbl_installs_6.apps_flyer_id IS NOT NULL) THEN 'InvestmateApp'::text ELSE u_12.media_source END), (CASE WHEN (tbl_installs_6.apps_flyer_id IS NOT NULL) THEN ((u_12.media_source || ' '::text) || u_12.campaign) ELSE u_12.campaign END), u_12.demo_sign, u_12.deposit_bin, u_12.program_id, (CASE WHEN (tbl_installs_6.apps_flyer_id IS NOT NULL) THEN 'InvestmateApp'::text ELSE h_5.media_source END), (CASE WHEN (tbl_installs_6.apps_flyer_id IS NOT NULL) THEN ((h_5.media_source || ' '::text) || h_5.campaign) ELSE h_5.campaign END), h_5.adset, h_5.ad, h_5.aff_id, h_5.offer_id, u_12.keywords, u_12.ad_conceptname, u_12.ad_creativename, u_12.ad_creativetype, u_12.user_role, u_12.tenant, u_12.deposit_bin_total
180. 693.228 5,887.042 ↓ 1.7 87,474 1

Sort (cost=579,414.10..579,539.21 rows=50,044 width=425) (actual time=5,866.098..5,887.042 rows=87,474 loops=1)

  • Sort Key: (COALESCE(u_12.reg_country, u_12.af_country_code)), u_12.apps_flyer_id, u_12.adset, u_12.ad, u_12.af_install_ts, u_12.af_platform, u_12.user_id, u_12.aff_id, u_12.offer_id, (CASE WHEN (tbl_installs_6.apps_flyer_id IS NOT NULL) THEN 'InvestmateApp'::text ELSE u_12.media_source END), (CASE WHEN (tbl_installs_6.apps_flyer_id IS NOT NULL) THEN ((u_12.media_source || ' '::text) || u_12.campaign) ELSE u_12.campaign END), u_12.demo_sign, u_12.deposit_bin, u_12.program_id, (CASE WHEN (tbl_installs_6.apps_flyer_id IS NOT NULL) THEN 'InvestmateApp'::text ELSE h_5.media_source END), (CASE WHEN (tbl_installs_6.apps_flyer_id IS NOT NULL) THEN ((h_5.media_source || ' '::text) || h_5.campaign) ELSE h_5.campaign END), h_5.adset, h_5.ad, h_5.aff_id, h_5.offer_id, u_12.keywords, u_12.ad_conceptname, u_12.ad_creativename, u_12.ad_creativetype, u_12.user_role, u_12.tenant, u_12.deposit_bin_total
  • Sort Method: quicksort Memory: 37,745kB
181. 214.055 5,193.814 ↓ 1.7 87,474 1

Nested Loop Left Join (cost=169,226.91..575,507.94 rows=50,044 width=425) (actual time=2,567.371..5,193.814 rows=87,474 loops=1)

  • Join Filter: ((fct_transactions_2.ts_timestamp >= h_5.valid_from) AND (fct_transactions_2.ts_timestamp <= h_5.valid_to))
  • Rows Removed by Join Filter: 287,580
182. 53.773 4,717.337 ↓ 1.7 87,474 1

Hash Left Join (cost=169,226.48..547,394.31 rows=50,044 width=336) (actual time=2,567.346..4,717.337 rows=87,474 loops=1)

  • Hash Cond: (u_12.apps_flyer_id = tbl_installs_6.apps_flyer_id)
183. 61.440 4,381.141 ↓ 1.7 87,474 1

Hash Join (cost=123,597.82..501,489.43 rows=50,044 width=305) (actual time=2,282.227..4,381.141 rows=87,474 loops=1)

  • Hash Cond: (fct_transactions_2.user_id = u_12.user_id)
184. 2,039.111 2,039.111 ↓ 1.8 87,786 1

Seq Scan on etl_fct_transactions fct_transactions_2 (cost=0.00..377,203.50 rows=50,044 width=66) (actual time=1.282..2,039.111 rows=87,786 loops=1)

  • Filter: ((transaction_type <> 'DEMO_TRANSFER'::text) AND (transaction_type = 'TRADE'::text) AND (transaction_status = 'PROCESSED'::text) AND (ts_timestamp < ('now'::cstring)::date) AND (ts_timestamp >= (('now'::cstring)::date - 31)))
  • Rows Removed by Filter: 5,878,235
185. 438.962 2,280.590 ↓ 2.2 520,847 1

Hash (cost=120,665.23..120,665.23 rows=234,607 width=267) (actual time=2,280.590..2,280.590 rows=520,847 loops=1)

  • Buckets: 524,288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 119,935kB
186. 233.078 1,841.628 ↓ 2.2 520,847 1

Subquery Scan on u_12 (cost=83,910.44..120,665.23 rows=234,607 width=267) (actual time=1,158.970..1,841.628 rows=520,847 loops=1)

187. 446.478 1,608.550 ↓ 2.2 520,847 1

Hash Right Join (cost=83,910.44..118,319.16 rows=234,607 width=3,183) (actual time=1,158.966..1,608.550 rows=520,847 loops=1)

  • Hash Cond: (aff_goals_6.user_id = u_13.user_id)
188.          

CTE aff_goals

189. 3.216 11.488 ↓ 1.1 4,555 1

Sort (cost=996.76..1,007.53 rows=4,307 width=28) (actual time=10.447..11.488 rows=4,555 loops=1)

  • Sort Key: g_6.user_id
  • Sort Method: quicksort Memory: 415kB
190. 5.111 8.272 ↓ 1.1 4,555 1

HashAggregate (cost=693.71..736.78 rows=4,307 width=28) (actual time=6.742..8.272 rows=4,555 loops=1)

  • Group Key: g_6.user_id
191. 3.161 3.161 ↓ 1.0 7,156 1

Seq Scan on tbl_affiliate_goals_for_export g_6 (cost=0.00..496.95 rows=7,155 width=28) (actual time=0.046..3.161 rows=7,156 loops=1)

  • Filter: (goal_name <> 'INSTALL_EVENT'::text)
  • Rows Removed by Filter: 2,361
192. 13.876 13.876 ↓ 1.1 4,555 1

CTE Scan on aff_goals aff_goals_6 (cost=0.00..86.14 rows=4,307 width=8) (actual time=10.450..13.876 rows=4,555 loops=1)

193. 482.347 1,148.196 ↓ 2.2 520,847 1

Hash (cost=79,970.32..79,970.32 rows=234,607 width=198) (actual time=1,148.195..1,148.196 rows=520,847 loops=1)

  • Buckets: 524,288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 115,079kB
194. 665.849 665.849 ↓ 2.2 520,847 1

Seq Scan on etl_dim_ums_users u_13 (cost=0.00..79,970.32 rows=234,607 width=198) (actual time=0.020..665.849 rows=520,847 loops=1)

  • Filter: ((NOT test_sign) AND (user_role = ANY ('{USER,PROFESSIONAL}'::text[])) AND (user_id <> ALL ('{10789518,10788054}'::bigint[])))
  • Rows Removed by Filter: 306,681
195. 121.339 282.423 ↓ 1.0 279,565 1

Hash (cost=42,143.07..42,143.07 rows=278,847 width=31) (actual time=282.422..282.423 rows=279,565 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 21,792kB
196. 161.084 161.084 ↓ 1.0 279,565 1

Index Scan using ix_tbl_installs_1 on tbl_installs tbl_installs_6 (cost=0.43..42,143.07 rows=278,847 width=31) (actual time=0.391..161.084 rows=279,565 loops=1)

  • Index Cond: (app_name = 'Investmate'::text)
197. 262.422 262.422 ↓ 2.0 4 87,474

Index Scan using ix_etl_tbl_user_media_source_hist_1 on etl_tbl_user_media_source_hist h_5 (cost=0.42..0.52 rows=2 width=91) (actual time=0.002..0.003 rows=4 loops=87,474)

  • Index Cond: (u_12.user_id = user_id)
198. 4.647 8,642.486 ↓ 1.1 3,925 1

Subquery Scan on *SELECT* 10 (cost=541,812.74..542,356.13 rows=3,684 width=1,518) (actual time=8,351.776..8,642.486 rows=3,925 loops=1)

199. 227.043 8,637.839 ↓ 1.1 3,925 1

GroupAggregate (cost=541,812.74..542,254.82 rows=3,684 width=1,490) (actual time=8,351.764..8,637.839 rows=3,925 loops=1)

  • Group Key: (COALESCE(u_14.reg_country, u_14.af_country_code)), u_14.apps_flyer_id, u_14.adset, u_14.ad, (date_trunc('quarter'::text, fct_transactions_3.ts_timestamp)), u_14.af_install_ts, u_14.af_platform, u_14.user_id, u_14.aff_id, u_14.offer_id, fct_transactions_3.user_id, (CASE WHEN (tbl_installs_7.apps_flyer_id IS NOT NULL) THEN 'InvestmateApp'::text ELSE u_14.media_source END), (CASE WHEN (tbl_installs_7.apps_flyer_id IS NOT NULL) THEN ((u_14.media_source || ' '::text) || u_14.campaign) ELSE u_14.campaign END), u_14.demo_sign, u_14.deposit_bin, u_14.program_id, (CASE WHEN (tbl_installs_7.apps_flyer_id IS NOT NULL) THEN 'InvestmateApp'::text ELSE h_6.media_source END), (CASE WHEN (tbl_installs_7.apps_flyer_id IS NOT NULL) THEN ((h_6.media_source || ' '::text) || h_6.campaign) ELSE h_6.campaign END), h_6.adset, h_6.ad, h_6.aff_id, h_6.offer_id, u_14.keywords, u_14.ad_conceptname, u_14.ad_creativename, u_14.ad_creativetype, u_14.user_role, u_14.tenant, u_14.deposit_bin_total
200. 2,030.585 8,410.796 ↓ 69.9 257,403 1

Sort (cost=541,812.74..541,821.95 rows=3,684 width=441) (actual time=8,351.646..8,410.796 rows=257,403 loops=1)

  • Sort Key: (COALESCE(u_14.reg_country, u_14.af_country_code)), u_14.apps_flyer_id, u_14.adset, u_14.ad, u_14.af_install_ts, u_14.af_platform, u_14.user_id, u_14.aff_id, u_14.offer_id, (CASE WHEN (tbl_installs_7.apps_flyer_id IS NOT NULL) THEN 'InvestmateApp'::text ELSE u_14.media_source END), (CASE WHEN (tbl_installs_7.apps_flyer_id IS NOT NULL) THEN ((u_14.media_source || ' '::text) || u_14.campaign) ELSE u_14.campaign END), u_14.demo_sign, u_14.deposit_bin, u_14.program_id, (CASE WHEN (tbl_installs_7.apps_flyer_id IS NOT NULL) THEN 'InvestmateApp'::text ELSE h_6.media_source END), (CASE WHEN (tbl_installs_7.apps_flyer_id IS NOT NULL) THEN ((h_6.media_source || ' '::text) || h_6.campaign) ELSE h_6.campaign END), h_6.adset, h_6.ad, h_6.aff_id, h_6.offer_id, u_14.keywords, u_14.ad_conceptname, u_14.ad_creativename, u_14.ad_creativetype, u_14.user_role, u_14.tenant, u_14.deposit_bin_total
  • Sort Method: quicksort Memory: 123,465kB
201. 645.468 6,380.211 ↓ 69.9 257,403 1

Nested Loop Left Join (cost=169,226.91..541,594.52 rows=3,684 width=441) (actual time=2,411.784..6,380.211 rows=257,403 loops=1)

  • Join Filter: ((fct_transactions_3.ts_timestamp >= h_6.valid_from) AND (fct_transactions_3.ts_timestamp <= h_6.valid_to))
  • Rows Removed by Join Filter: 922,012
202. 146.071 4,962.534 ↓ 69.9 257,403 1

Hash Left Join (cost=169,226.48..539,515.72 rows=3,684 width=336) (actual time=2,411.757..4,962.534 rows=257,403 loops=1)

  • Hash Cond: (u_14.apps_flyer_id = tbl_installs_7.apps_flyer_id)
203. 172.485 4,552.257 ↓ 69.9 257,403 1

Hash Join (cost=123,597.82..493,866.73 rows=3,684 width=305) (actual time=2,145.236..4,552.257 rows=257,403 loops=1)

  • Hash Cond: (fct_transactions_3.user_id = u_14.user_id)
204. 2,236.805 2,236.805 ↓ 70.1 258,384 1

Seq Scan on etl_fct_transactions fct_transactions_3 (cost=0.00..370,218.25 rows=3,684 width=66) (actual time=1.062..2,236.805 rows=258,384 loops=1)

  • Filter: ((transaction_type <> 'DEMO_TRANSFER'::text) AND (transaction_type = 'TRADE'::text) AND (transaction_status = 'PROCESSED'::text) AND (date_trunc('quarter'::text, ts_timestamp) = date_trunc('quarter'::text, (('now'::cstring)::date)::timestamp with time zone)))
  • Rows Removed by Filter: 5,707,637
205. 427.057 2,142.967 ↓ 2.2 520,847 1

Hash (cost=120,665.23..120,665.23 rows=234,607 width=267) (actual time=2,142.967..2,142.967 rows=520,847 loops=1)

  • Buckets: 524,288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 119,935kB
206. 228.742 1,715.910 ↓ 2.2 520,847 1

Subquery Scan on u_14 (cost=83,910.44..120,665.23 rows=234,607 width=267) (actual time=1,042.149..1,715.910 rows=520,847 loops=1)

207. 442.531 1,487.168 ↓ 2.2 520,847 1

Hash Right Join (cost=83,910.44..118,319.16 rows=234,607 width=3,183) (actual time=1,042.146..1,487.168 rows=520,847 loops=1)

  • Hash Cond: (aff_goals_7.user_id = u_15.user_id)
208.          

CTE aff_goals

209. 3.206 11.130 ↓ 1.1 4,555 1

Sort (cost=996.76..1,007.53 rows=4,307 width=28) (actual time=10.096..11.130 rows=4,555 loops=1)

  • Sort Key: g_7.user_id
  • Sort Method: quicksort Memory: 415kB
210. 4.916 7.924 ↓ 1.1 4,555 1

HashAggregate (cost=693.71..736.78 rows=4,307 width=28) (actual time=6.339..7.924 rows=4,555 loops=1)

  • Group Key: g_7.user_id
211. 3.008 3.008 ↓ 1.0 7,156 1

Seq Scan on tbl_affiliate_goals_for_export g_7 (cost=0.00..496.95 rows=7,155 width=28) (actual time=0.030..3.008 rows=7,156 loops=1)

  • Filter: (goal_name <> 'INSTALL_EVENT'::text)
  • Rows Removed by Filter: 2,361
212. 13.478 13.478 ↓ 1.1 4,555 1

CTE Scan on aff_goals aff_goals_7 (cost=0.00..86.14 rows=4,307 width=8) (actual time=10.100..13.478 rows=4,555 loops=1)

213. 434.617 1,031.159 ↓ 2.2 520,847 1

Hash (cost=79,970.32..79,970.32 rows=234,607 width=198) (actual time=1,031.159..1,031.159 rows=520,847 loops=1)

  • Buckets: 524,288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 115,079kB
214. 596.542 596.542 ↓ 2.2 520,847 1

Seq Scan on etl_dim_ums_users u_15 (cost=0.00..79,970.32 rows=234,607 width=198) (actual time=0.018..596.542 rows=520,847 loops=1)

  • Filter: ((NOT test_sign) AND (user_role = ANY ('{USER,PROFESSIONAL}'::text[])) AND (user_id <> ALL ('{10789518,10788054}'::bigint[])))
  • Rows Removed by Filter: 306,681
215. 112.968 264.206 ↓ 1.0 279,565 1

Hash (cost=42,143.07..42,143.07 rows=278,847 width=31) (actual time=264.206..264.206 rows=279,565 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 21,792kB
216. 151.238 151.238 ↓ 1.0 279,565 1

Index Scan using ix_tbl_installs_1 on tbl_installs tbl_installs_7 (cost=0.43..42,143.07 rows=278,847 width=31) (actual time=0.022..151.238 rows=279,565 loops=1)

  • Index Cond: (app_name = 'Investmate'::text)
217. 772.209 772.209 ↓ 2.5 5 257,403

Index Scan using ix_etl_tbl_user_media_source_hist_1 on etl_tbl_user_media_source_hist h_6 (cost=0.42..0.52 rows=2 width=91) (actual time=0.002..0.003 rows=5 loops=257,403)

  • Index Cond: (u_14.user_id = user_id)
218. 333.826 50,214.291 ↓ 9.2 274,341 1

Subquery Scan on *SELECT* 11 (cost=788,477.11..793,816.99 rows=29,666 width=1,450) (actual time=47,095.634..50,214.291 rows=274,341 loops=1)

219. 2,410.567 49,880.465 ↓ 9.2 274,341 1

GroupAggregate (cost=788,477.11..793,075.34 rows=29,666 width=1,406) (actual time=47,095.624..49,880.465 rows=274,341 loops=1)

  • Group Key: ((row_to_json((SubPlan 15)))::jsonb), (COALESCE(u_16.reg_country, u_16.af_country_code)), u_16.apps_flyer_id, u_16.adset, u_16.ad, (date(t.created_timestamp)), u_16.af_install_ts, u_16.af_platform, t.currency, u_16.aff_id, u_16.offer_id, (CASE WHEN (tbl_installs_8.apps_flyer_id IS NOT NULL) THEN 'InvestmateApp'::text ELSE u_16.media_source END), (CASE WHEN (tbl_installs_8.apps_flyer_id IS NOT NULL) THEN ((u_16.media_source || ' '::text) || u_16.campaign) ELSE u_16.campaign END), (CASE WHEN (t.first_trade AND (t.created_timestamp = u_16.first_trade_ts)) THEN true ELSE false END), (CASE WHEN (t.status = ANY ('{OPENED,CLOSED}'::text[])) THEN u_16.user_id ELSE NULL::bigint END), u_16.demo_sign, u_16.deposit_bin, u_16.program_id, (CASE WHEN (tbl_installs_8.apps_flyer_id IS NOT NULL) THEN 'InvestmateApp'::text ELSE h_7.media_source END), (CASE WHEN (tbl_installs_8.apps_flyer_id IS NOT NULL) THEN ((h_7.media_source || ' '::text) || h_7.campaign) ELSE h_7.campaign END), h_7.adset, h_7.ad, h_7.aff_id, h_7.offer_id, u_16.keywords, u_16.ad_conceptname, u_16.ad_creativename, u_16.ad_creativetype, u_16.user_role, u_16.tenant, u_16.deposit_bin_total
220. 19,763.007 47,469.898 ↓ 42.3 1,254,329 1

Sort (cost=788,477.11..788,551.27 rows=29,666 width=482) (actual time=47,095.569..47,469.898 rows=1,254,329 loops=1)

  • Sort Key: ((row_to_json((SubPlan 15)))::jsonb), (COALESCE(u_16.reg_country, u_16.af_country_code)), u_16.apps_flyer_id, u_16.adset, u_16.ad, (date(t.created_timestamp)), u_16.af_install_ts, u_16.af_platform, t.currency, u_16.aff_id, u_16.offer_id, (CASE WHEN (tbl_installs_8.apps_flyer_id IS NOT NULL) THEN 'InvestmateApp'::text ELSE u_16.media_source END), (CASE WHEN (tbl_installs_8.apps_flyer_id IS NOT NULL) THEN ((u_16.media_source || ' '::text) || u_16.campaign) ELSE u_16.campaign END), (CASE WHEN (t.first_trade AND (t.created_timestamp = u_16.first_trade_ts)) THEN true ELSE false END), (CASE WHEN (t.status = ANY ('{OPENED,CLOSED}'::text[])) THEN u_16.user_id ELSE NULL::bigint END), u_16.demo_sign, u_16.deposit_bin, u_16.program_id, (CASE WHEN (tbl_installs_8.apps_flyer_id IS NOT NULL) THEN 'InvestmateApp'::text ELSE h_7.media_source END), (CASE WHEN (tbl_installs_8.apps_flyer_id IS NOT NULL) THEN ((h_7.media_source || ' '::text) || h_7.campaign) ELSE h_7.campaign END), h_7.adset, h_7.ad, h_7.aff_id, h_7.offer_id, u_16.keywords, u_16.ad_conceptname, u_16.ad_creativename, u_16.ad_creativetype, u_16.user_role, u_16.tenant, u_16.deposit_bin_total
  • Sort Method: quicksort Memory: 685,373kB
221. 2,963.763 27,706.891 ↓ 42.3 1,254,329 1

Nested Loop Left Join (cost=169,227.33..786,273.44 rows=29,666 width=482) (actual time=2,399.857..27,706.891 rows=1,254,329 loops=1)

222. 3,560.060 19,725.812 ↓ 42.3 1,254,329 1

Nested Loop Left Join (cost=169,226.91..728,989.36 rows=29,666 width=440) (actual time=2,399.813..19,725.812 rows=1,254,329 loops=1)

  • Join Filter: ((t.created_timestamp >= h_7.valid_from) AND (t.created_timestamp <= h_7.valid_to))
  • Rows Removed by Join Filter: 3,645,441
223. 811.068 12,402.765 ↓ 42.3 1,254,329 1

Hash Left Join (cost=169,226.48..712,620.31 rows=29,666 width=373) (actual time=2,399.790..12,402.765 rows=1,254,329 loops=1)

  • Hash Cond: (u_16.apps_flyer_id = tbl_installs_8.apps_flyer_id)
224. 1,098.097 11,323.308 ↓ 42.3 1,254,329 1

Hash Join (cost=123,597.82..666,827.90 rows=29,666 width=342) (actual time=2,129.124..11,323.308 rows=1,254,329 loops=1)

  • Hash Cond: (t.user_id = u_16.user_id)
225. 8,098.089 8,098.089 ↓ 42.4 1,257,672 1

Seq Scan on tbl_fct_position_history t (cost=0.00..542,822.18 rows=29,666 width=75) (actual time=0.755..8,098.089 rows=1,257,672 loops=1)

  • Filter: ((status = ANY ('{OPENED,CLOSED,SWAP}'::text[])) AND (date_trunc('quarter'::text, created_timestamp) = date_trunc('quarter'::text, (('now'::cstring)::date)::timestamp with time zone)))
  • Rows Removed by Filter: 6,912,076
226. 426.793 2,127.122 ↓ 2.2 520,847 1

Hash (cost=120,665.23..120,665.23 rows=234,607 width=275) (actual time=2,127.122..2,127.122 rows=520,847 loops=1)

  • Buckets: 524,288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 120,114kB
227. 227.674 1,700.329 ↓ 2.2 520,847 1

Subquery Scan on u_16 (cost=83,910.44..120,665.23 rows=234,607 width=275) (actual time=1,043.115..1,700.329 rows=520,847 loops=1)

228. 427.163 1,472.655 ↓ 2.2 520,847 1

Hash Right Join (cost=83,910.44..118,319.16 rows=234,607 width=3,183) (actual time=1,043.112..1,472.655 rows=520,847 loops=1)

  • Hash Cond: (aff_goals_8.user_id = u_17.user_id)
229.          

CTE aff_goals

230. 3.148 11.089 ↓ 1.1 4,555 1

Sort (cost=996.76..1,007.53 rows=4,307 width=28) (actual time=10.080..11.089 rows=4,555 loops=1)

  • Sort Key: g_8.user_id
  • Sort Method: quicksort Memory: 415kB
231. 4.865 7.941 ↓ 1.1 4,555 1

HashAggregate (cost=693.71..736.78 rows=4,307 width=28) (actual time=6.411..7.941 rows=4,555 loops=1)

  • Group Key: g_8.user_id
232. 3.076 3.076 ↓ 1.0 7,156 1

Seq Scan on tbl_affiliate_goals_for_export g_8 (cost=0.00..496.95 rows=7,155 width=28) (actual time=0.028..3.076 rows=7,156 loops=1)

  • Filter: (goal_name <> 'INSTALL_EVENT'::text)
  • Rows Removed by Filter: 2,361
233. 13.388 13.388 ↓ 1.1 4,555 1

CTE Scan on aff_goals aff_goals_8 (cost=0.00..86.14 rows=4,307 width=8) (actual time=10.083..13.388 rows=4,555 loops=1)

234. 440.804 1,032.104 ↓ 2.2 520,847 1

Hash (cost=79,970.32..79,970.32 rows=234,607 width=206) (actual time=1,032.104..1,032.104 rows=520,847 loops=1)

  • Buckets: 524,288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 115,181kB
235. 591.300 591.300 ↓ 2.2 520,847 1

Seq Scan on etl_dim_ums_users u_17 (cost=0.00..79,970.32 rows=234,607 width=206) (actual time=0.018..591.300 rows=520,847 loops=1)

  • Filter: ((test_sign IS FALSE) AND (user_role = ANY ('{USER,PROFESSIONAL}'::text[])) AND (user_id <> ALL ('{10789518,10788054}'::bigint[])))
  • Rows Removed by Filter: 306,681
236. 117.176 268.389 ↓ 1.0 279,565 1

Hash (cost=42,143.07..42,143.07 rows=278,847 width=31) (actual time=268.388..268.389 rows=279,565 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 21,792kB
237. 151.213 151.213 ↓ 1.0 279,565 1

Index Scan using ix_tbl_installs_1 on tbl_installs tbl_installs_8 (cost=0.43..42,143.07 rows=278,847 width=31) (actual time=0.026..151.213 rows=279,565 loops=1)

  • Index Cond: (app_name = 'Investmate'::text)
238. 3,762.987 3,762.987 ↓ 2.0 4 1,254,329

Index Scan using ix_etl_tbl_user_media_source_hist_1 on etl_tbl_user_media_source_hist h_7 (cost=0.42..0.52 rows=2 width=91) (actual time=0.002..0.003 rows=4 loops=1,254,329)

  • Index Cond: (u_16.user_id = user_id)
239. 2,508.658 2,508.658 ↑ 1.0 1 1,254,329

Index Scan using as_account_pkey on as_account a_3 (cost=0.43..1.89 rows=1 width=23) (actual time=0.002..0.002 rows=1 loops=1,254,329)

  • Index Cond: (id = t.account_id)
240.          

SubPlan (for Nested Loop Left Join)

241. 2,508.658 2,508.658 ↑ 1.0 1 1,254,329

Result (cost=0.00..0.02 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=1,254,329)

242. 1.710 4,413.150 ↓ 952.0 952 1

Subquery Scan on *SELECT* 12 (cost=120,705.38..163,893.90 rows=1 width=1,478) (actual time=4,350.875..4,413.150 rows=952 loops=1)

243. 2.107 4,411.440 ↓ 952.0 952 1

Nested Loop Left Join (cost=120,705.38..163,893.86 rows=1 width=1,470) (actual time=4,350.863..4,411.440 rows=952 loops=1)

  • Join Filter: ((r_1.event_datetime >= h_8.valid_from) AND (r_1.event_datetime <= h_8.valid_to))
  • Rows Removed by Join Filter: 576
244. 90.561 4,395.053 ↓ 952.0 952 1

Hash Right Join (cost=120,704.96..163,893.29 rows=1 width=306) (actual time=4,350.833..4,395.053 rows=952 loops=1)

  • Hash Cond: (tbl_installs_9.apps_flyer_id = u_18.apps_flyer_id)
245. 139.998 139.998 ↓ 1.0 279,565 1

Index Scan using ix_tbl_installs_1 on tbl_installs tbl_installs_9 (cost=0.43..42,143.07 rows=278,847 width=31) (actual time=0.020..139.998 rows=279,565 loops=1)

  • Index Cond: (app_name = 'Investmate'::text)
246. 1.039 4,164.494 ↓ 952.0 952 1

Hash (cost=120,704.52..120,704.52 rows=1 width=275) (actual time=4,164.494..4,164.494 rows=952 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 205kB
247. 152.299 4,163.455 ↓ 952.0 952 1

Hash Join (cost=82,987.06..120,704.52 rows=1 width=275) (actual time=3,601.005..4,163.455 rows=952 loops=1)

  • Hash Cond: (u_18.user_id = r_1.user_id)
248. 413.623 1,690.509 ↓ 2.2 523,614 1

Hash Right Join (cost=82,987.03..117,471.51 rows=235,125 width=3,183) (actual time=1,273.662..1,690.509 rows=523,614 loops=1)

  • Hash Cond: (aff_goals_9.user_id = u_18.user_id)
249.          

CTE aff_goals

250. 3.166 10.948 ↓ 1.1 4,555 1

Sort (cost=996.76..1,007.53 rows=4,307 width=28) (actual time=9.924..10.948 rows=4,555 loops=1)

  • Sort Key: g_9.user_id
  • Sort Method: quicksort Memory: 415kB
251. 4.789 7.782 ↓ 1.1 4,555 1

HashAggregate (cost=693.71..736.78 rows=4,307 width=28) (actual time=6.256..7.782 rows=4,555 loops=1)

  • Group Key: g_9.user_id
252. 2.993 2.993 ↓ 1.0 7,156 1

Seq Scan on tbl_affiliate_goals_for_export g_9 (cost=0.00..496.95 rows=7,155 width=28) (actual time=0.030..2.993 rows=7,156 loops=1)

  • Filter: (goal_name <> 'INSTALL_EVENT'::text)
  • Rows Removed by Filter: 2,361
253. 13.476 13.476 ↓ 1.1 4,555 1

CTE Scan on aff_goals aff_goals_9 (cost=0.00..86.14 rows=4,307 width=8) (actual time=9.928..13.476 rows=4,555 loops=1)

254. 556.058 1,263.410 ↓ 2.2 523,614 1

Hash (cost=79,040.44..79,040.44 rows=235,125 width=198) (actual time=1,263.409..1,263.410 rows=523,614 loops=1)

  • Buckets: 524,288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 115,556kB
255. 707.352 707.352 ↓ 2.2 523,614 1

Seq Scan on etl_dim_ums_users u_18 (cost=0.00..79,040.44 rows=235,125 width=198) (actual time=0.029..707.352 rows=523,614 loops=1)

  • Filter: (user_role = ANY ('{USER,PROFESSIONAL}'::text[]))
  • Rows Removed by Filter: 303,914
256. 0.349 2,320.647 ↓ 952.0 952 1

Hash (cost=0.02..0.02 rows=1 width=16) (actual time=2,320.647..2,320.647 rows=952 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 53kB
257. 2,320.298 2,320.298 ↓ 952.0 952 1

CTE Scan on user_doc_reject r_1 (cost=0.00..0.02 rows=1 width=16) (actual time=2,290.943..2,320.298 rows=952 loops=1)

258. 14.280 14.280 ↑ 1.0 2 952

Index Scan using ix_etl_tbl_user_media_source_hist_1 on etl_tbl_user_media_source_hist h_8 (cost=0.42..0.52 rows=2 width=82) (actual time=0.014..0.015 rows=2 loops=952)

  • Index Cond: (u_18.user_id = user_id)
259. 0.155 8,693.627 ↓ 7.1 92 1

Subquery Scan on *SELECT* 13 (cost=402,659.24..402,689.48 rows=13 width=1,950) (actual time=8,691.683..8,693.627 rows=92 loops=1)

260. 0.904 8,693.472 ↓ 7.1 92 1

Hash Right Join (cost=402,659.24..402,688.93 rows=13 width=1,962) (actual time=8,691.671..8,693.472 rows=92 loops=1)

  • Hash Cond: ((date(src_finres_b2c_trades_daily.reportdate)) = rep_capital_pl.reportdate)
261. 333.076 595.417 ↓ 1.0 878 1

HashAggregate (cost=26,328.19..26,345.57 rows=869 width=100) (actual time=594.520..595.417 rows=878 loops=1)

  • Group Key: date(src_finres_b2c_trades_daily.reportdate)
262. 262.341 262.341 ↑ 1.0 326,625 1

Seq Scan on src_finres_b2c_trades_daily (cost=0.00..19,781.51 rows=327,334 width=18) (actual time=0.497..262.341 rows=326,625 loops=1)

  • Filter: ((metric_type)::text = ANY ('{RPL,UPL}'::text[]))
  • Rows Removed by Filter: 395,899
263. 0.118 8,097.059 ↓ 9.2 92 1

Hash (cost=376,330.92..376,330.92 rows=10 width=480) (actual time=8,097.058..8,097.059 rows=92 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 42kB
264. 0.890 8,096.941 ↓ 9.2 92 1

Hash Full Join (cost=376,279.57..376,330.92 rows=10 width=480) (actual time=8,095.267..8,096.941 rows=92 loops=1)

  • Hash Cond: (COALESCE(rep_capital_pl.reportdate, rep_max_daily_position.reportdate) = q1.reportdate)
  • Filter: (date_trunc('quarter'::text, (COALESCE(COALESCE(rep_capital_pl.reportdate, rep_max_daily_position.reportdate), q1.reportdate))::timestamp with time zone) = date_trunc('quarter'::text, (('now'::cstring)::date)::timestamp with time zone))
  • Rows Removed by Filter: 788
265. 0.671 5.406 ↑ 2.4 880 1

Hash Full Join (cost=53.39..98.97 rows=2,090 width=412) (actual time=4.598..5.406 rows=880 loops=1)

  • Hash Cond: (rep_max_daily_position.reportdate = rep_capital_pl.reportdate)
266. 1.613 1.613 ↑ 3.4 623 1

Seq Scan on rep_max_daily_position (cost=0.00..30.90 rows=2,090 width=11) (actual time=1.451..1.613 rows=623 loops=1)

267. 1.162 3.122 ↓ 1.3 879 1

Hash (cost=44.84..44.84 rows=684 width=401) (actual time=3.122..3.122 rows=879 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 297kB
268. 1.960 1.960 ↓ 1.3 879 1

Seq Scan on rep_capital_pl (cost=0.00..44.84 rows=684 width=401) (actual time=0.395..1.960 rows=879 loops=1)

269. 0.268 8,090.645 ↓ 4.1 815 1

Hash (cost=376,223.68..376,223.68 rows=200 width=68) (actual time=8,090.645..8,090.645 rows=815 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 45kB
270. 480.966 8,090.377 ↓ 4.1 815 1

HashAggregate (cost=376,217.18..376,221.68 rows=200 width=68) (actual time=8,088.318..8,090.377 rows=815 loops=1)

  • Group Key: q1.reportdate
271.          

CTE q1

272. 4,105.377 6,835.285 ↑ 1.0 1,344,660 1

WindowAgg (cost=272,541.01..338,202.59 rows=1,382,349 width=20) (actual time=2,438.573..6,835.285 rows=1,344,660 loops=1)

273. 815.976 2,729.908 ↑ 1.0 1,344,660 1

Sort (cost=272,541.01..275,996.88 rows=1,382,349 width=28) (actual time=2,438.511..2,729.908 rows=1,344,660 loops=1)

  • Sort Key: (date(d.ts_utc))
  • Sort Method: quicksort Memory: 154,204kB
274. 1,913.932 1,913.932 ↑ 1.0 1,344,660 1

Seq Scan on src_ts_position_effective_leverage_to_dlk d (cost=0.00..131,550.46 rows=1,382,349 width=28) (actual time=0.675..1,913.932 rows=1,344,660 loops=1)

  • Filter: ((eqity_before_trade_usd <> '0'::numeric) AND (order_type = 'OPEN'::text))
  • Rows Removed by Filter: 1,383,675
275. 7,609.411 7,609.411 ↑ 1.0 1,344,660 1

CTE Scan on q1 (cost=0.00..27,646.98 rows=1,382,349 width=20) (actual time=2,438.575..7,609.411 rows=1,344,660 loops=1)

276.          

SubPlan (for Hash Right Join)

277. 0.092 0.092 ↑ 1.0 1 92

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=92)

278. 719.446 112,923.546 ↑ 1,641.1 243,125 1

Subquery Scan on *SELECT* 14 (cost=221,319,230.91..329,048,389.86 rows=398,996,885 width=2,550) (actual time=105,796.145..112,923.546 rows=243,125 loops=1)

279. 5,033.919 112,204.100 ↑ 1,641.1 243,125 1

GroupAggregate (cost=221,319,230.91..278,176,287.02 rows=398,996,885 width=1,454) (actual time=105,796.131..112,204.100 rows=243,125 loops=1)

  • Group Key: (COALESCE(u_19.reg_country, u_19.af_country_code)), u_19.apps_flyer_id, u_19.adset, u_19.ad, (date("*SELECT* 1_1".reportdate)), u_19.af_install_ts, u_19.af_platform, u_19.aff_id, u_19.offer_id, (CASE WHEN (tbl_installs_10.apps_flyer_id IS NOT NULL) THEN 'InvestmateApp'::text ELSE u_19.media_source END), (CASE WHEN (tbl_installs_10.apps_flyer_id IS NOT NULL) THEN ((u_19.media_source || ' '::text) || u_19.campaign) ELSE u_19.campaign END), u_19.user_id, u_19.demo_sign, u_19.deposit_bin, u_19.program_id, (CASE WHEN (tbl_installs_10.apps_flyer_id IS NOT NULL) THEN 'InvestmateApp'::text ELSE h_9.media_source END), (CASE WHEN (tbl_installs_10.apps_flyer_id IS NOT NULL) THEN ((h_9.media_source || ' '::text) || h_9.campaign) ELSE h_9.campaign END), h_9.adset, h_9.ad, h_9.aff_id, h_9.offer_id, u_19.keywords, u_19.ad_conceptname, u_19.ad_creativename, u_19.ad_creativetype, u_19.user_role, u_19.tenant, u_19.deposit_bin_total
280. 23,036.941 107,170.181 ↑ 169.0 2,361,392 1

Sort (cost=221,319,230.91..222,316,723.12 rows=398,996,885 width=599) (actual time=105,796.062..107,170.181 rows=2,361,392 loops=1)

  • Sort Key: (COALESCE(u_19.reg_country, u_19.af_country_code)), u_19.apps_flyer_id, u_19.adset, u_19.ad, (date("*SELECT* 1_1".reportdate)), u_19.af_install_ts, u_19.af_platform, u_19.aff_id, u_19.offer_id, (CASE WHEN (tbl_installs_10.apps_flyer_id IS NOT NULL) THEN 'InvestmateApp'::text ELSE u_19.media_source END), (CASE WHEN (tbl_installs_10.apps_flyer_id IS NOT NULL) THEN ((u_19.media_source || ' '::text) || u_19.campaign) ELSE u_19.campaign END), u_19.user_id, u_19.demo_sign, u_19.deposit_bin, u_19.program_id, (CASE WHEN (tbl_installs_10.apps_flyer_id IS NOT NULL) THEN 'InvestmateApp'::text ELSE h_9.media_source END), (CASE WHEN (tbl_installs_10.apps_flyer_id IS NOT NULL) THEN ((h_9.media_source || ' '::text) || h_9.campaign) ELSE h_9.campaign END), h_9.adset, h_9.ad, h_9.aff_id, h_9.offer_id, u_19.keywords, u_19.ad_conceptname, u_19.ad_creativename, u_19.ad_creativetype, u_19.user_role, u_19.tenant, u_19.deposit_bin_total
  • Sort Method: external merge Disk: 639,104kB
281. 1,306.141 84,133.240 ↑ 169.0 2,361,392 1

Hash Left Join (cost=58,987,254.05..102,014,631.39 rows=398,996,885 width=599) (actual time=76,303.432..84,133.240 rows=2,361,392 loops=1)

  • Hash Cond: (u_19.apps_flyer_id = tbl_installs_10.apps_flyer_id)
282. 6,263.901 82,494.498 ↑ 169.0 2,361,392 1

Hash Right Join (cost=58,941,625.39..94,779,211.63 rows=398,996,885 width=542) (actual time=75,970.520..82,494.498 rows=2,361,392 loops=1)

  • Hash Cond: (h_9.user_id = u_19.user_id)
  • Join Filter: (("*SELECT* 1_1".reportdate >= h_9.valid_from) AND ("*SELECT* 1_1".reportdate <= h_9.valid_to))
  • Rows Removed by Join Filter: 8,116,058
283. 455.984 455.984 ↑ 1.0 725,200 1

Seq Scan on etl_tbl_user_media_source_hist h_9 (cost=0.00..18,436.00 rows=725,200 width=91) (actual time=0.493..455.984 rows=725,200 loops=1)

284. 2,814.292 75,774.613 ↑ 169.0 2,361,392 1

Hash (cost=29,406,504.33..29,406,504.33 rows=398,996,885 width=475) (actual time=75,774.612..75,774.613 rows=2,361,392 loops=1)

  • Buckets: 2,097,152 Batches: 256 Memory Usage: 30,005kB
285. 1,689.536 72,960.321 ↑ 169.0 2,361,392 1

Hash Join (cost=1,278,790.60..29,406,504.33 rows=398,996,885 width=475) (actual time=49,159.626..72,960.321 rows=2,361,392 loops=1)

  • Hash Cond: ("*SELECT* 1_1".user_id = u_19.user_id)
286. 1,051.178 69,245.499 ↑ 167.5 2,382,539 1

Append (cost=1,155,192.78..19,806,730.49 rows=398,996,885 width=536) (actual time=47,134.042..69,245.499 rows=2,382,539 loops=1)

287.          

CTE q1

288. 10,810.558 10,810.558 ↑ 1.0 11,710,143 1

Seq Scan on src_spread_capture_to_dlk (cost=0.00..443,491.62 rows=11,718,438 width=214) (actual time=0.556..10,810.558 rows=11,710,143 loops=1)

  • Filter: (instrument ~ '[0-9]'::text)
  • Rows Removed by Filter: 7,697
289. 1,407.344 55,882.937 ↑ 173.9 2,276,779 1

Subquery Scan on *SELECT* 1_1 (cost=711,701.16..16,545,157.96 rows=395,821,772 width=479) (actual time=47,134.040..55,882.937 rows=2,276,779 loops=1)

290. 5,519.080 54,475.593 ↑ 173.9 2,276,779 1

Merge Join (cost=711,701.16..11,597,385.81 rows=395,821,772 width=503) (actual time=47,134.033..54,475.593 rows=2,276,779 loops=1)

  • Merge Cond: (q1_1.account_id = ((a_4.id)::text))
291. 6,804.914 39,170.729 ↓ 38.9 2,276,779 1

Sort (cost=497,503.22..497,649.70 rows=58,592 width=407) (actual time=38,334.248..39,170.729 rows=2,276,779 loops=1)

  • Sort Key: q1_1.account_id
  • Sort Method: quicksort Memory: 680,596kB
292. 1,352.889 32,365.815 ↓ 38.9 2,276,779 1

Hash Join (cost=81,743.73..492,863.20 rows=58,592 width=407) (actual time=1,360.515..32,365.815 rows=2,276,779 loops=1)

  • Hash Cond: (q1_1.user_id = u_20.user_id)
293. 1,788.302 29,929.587 ↓ 38.9 2,280,377 1

Hash Join (cost=10,657.54..420,971.38 rows=58,592 width=395) (actual time=276.876..29,929.587 rows=2,280,377 loops=1)

  • Hash Cond: ((q1_1.instrument)::bigint = i.id)
294. 28,128.861 28,128.861 ↓ 38.9 2,280,377 1

CTE Scan on q1 q1_1 (cost=0.00..410,145.33 rows=58,592 width=368) (actual time=264.397..28,128.861 rows=2,280,377 loops=1)

  • Filter: (date_trunc('quarter'::text, reportdate) = date_trunc('quarter'::text, (('now'::cstring)::date)::timestamp with time zone))
  • Rows Removed by Filter: 9,429,766
295. 3.245 12.424 ↑ 2.0 7,557 1

Hash (cost=10,470.77..10,470.77 rows=14,942 width=35) (actual time=12.424..12.424 rows=7,557 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 654kB
296. 9.179 9.179 ↑ 2.0 7,557 1

Index Scan using ts_instrument__id__idx on ts_instrument i (cost=0.29..10,470.77 rows=14,942 width=35) (actual time=0.023..9.179 rows=7,557 loops=1)

297. 312.888 1,083.339 ↓ 2.2 827,528 1

Hash (cost=66,388.86..66,388.86 rows=375,786 width=20) (actual time=1,083.339..1,083.339 rows=827,528 loops=1)

  • Buckets: 1,048,576 (originally 524288) Batches: 1 (originally 1) Memory Usage: 53,448kB
298. 770.451 770.451 ↓ 2.2 827,528 1

Seq Scan on etl_tbl_ums_users u_20 (cost=0.00..66,388.86 rows=375,786 width=20) (actual time=0.032..770.451 rows=827,528 loops=1)

299. 8,878.916 9,785.784 ↓ 2.7 3,624,019 1

Sort (cost=214,197.94..217,575.72 rows=1,351,112 width=23) (actual time=8,798.433..9,785.784 rows=3,624,019 loops=1)

  • Sort Key: ((a_4.id)::text)
  • Sort Method: quicksort Memory: 175,187kB
300. 906.868 906.868 ↓ 1.0 1,355,924 1

Seq Scan on as_account a_4 (cost=0.00..76,616.12 rows=1,351,112 width=23) (actual time=0.521..906.868 rows=1,355,924 loops=1)

301. 735.032 12,311.384 ↑ 30.0 105,760 1

Merge Join (cost=656,396.00..791,345.35 rows=3,175,113 width=452) (actual time=11,760.155..12,311.384 rows=105,760 loops=1)

  • Merge Cond: (((fct_transactions_4.user_account_id)::text) = ((a_5.id)::text))
302. 189.119 3,295.131 ↓ 225.0 105,760 1

Sort (cost=442,198.06..442,199.24 rows=470 width=84) (actual time=3,267.343..3,295.131 rows=105,760 loops=1)

  • Sort Key: ((fct_transactions_4.user_account_id)::text)
  • Sort Method: quicksort Memory: 29,644kB
303. 85.388 3,106.012 ↓ 225.0 105,760 1

Hash Join (cost=71,086.47..442,177.20 rows=470 width=84) (actual time=2,107.805..3,106.012 rows=105,760 loops=1)

  • Hash Cond: (fct_transactions_4.user_id = u_21.user_id)
304. 102.962 2,075.486 ↓ 225.0 105,760 1

Nested Loop (cost=0.29..371,084.56 rows=470 width=72) (actual time=1,162.357..2,075.486 rows=105,760 loops=1)

305. 1,761.004 1,761.004 ↓ 219.4 105,760 1

Seq Scan on etl_fct_transactions fct_transactions_4 (cost=0.00..370,218.25 rows=482 width=73) (actual time=1,162.321..1,761.004 rows=105,760 loops=1)

  • Filter: ((transaction_type <> 'DEMO_TRANSFER'::text) AND (transaction_type = ANY ('{TRADE_COMMISSION,EXCHANGE_COMMISSION}'::text[])) AND (transaction_status = 'PROCESSED'::text) AND (date_trunc('quarter'::text, ts_timestamp) = date_trunc('quarter'::text, (('now'::cstring)::date)::timestamp with time zone)))
  • Rows Removed by Filter: 5,860,261
306. 211.520 211.520 ↑ 1.0 1 105,760

Index Scan using ts_instrument__id__idx on ts_instrument i_1 (cost=0.29..1.80 rows=1 width=35) (actual time=0.002..0.002 rows=1 loops=105,760)

  • Index Cond: (id = fct_transactions_4.details_instrument_id)
307. 314.104 945.138 ↓ 2.2 827,528 1

Hash (cost=66,388.86..66,388.86 rows=375,786 width=20) (actual time=945.138..945.138 rows=827,528 loops=1)

  • Buckets: 1,048,576 (originally 524288) Batches: 1 (originally 1) Memory Usage: 53,448kB
308. 631.034 631.034 ↓ 2.2 827,528 1

Seq Scan on etl_tbl_ums_users u_21 (cost=0.00..66,388.86 rows=375,786 width=20) (actual time=0.012..631.034 rows=827,528 loops=1)

309. 7,617.168 8,281.221 ↑ 1.0 1,350,871 1

Sort (cost=214,197.94..217,575.72 rows=1,351,112 width=23) (actual time=7,913.042..8,281.221 rows=1,350,871 loops=1)

  • Sort Key: ((a_5.id)::text)
  • Sort Method: quicksort Memory: 175,187kB
310. 664.053 664.053 ↓ 1.0 1,355,924 1

Seq Scan on as_account a_5 (cost=0.00..76,616.12 rows=1,351,112 width=23) (actual time=0.015..664.053 rows=1,355,924 loops=1)

311. 382.128 2,025.286 ↓ 2.2 520,847 1

Hash (cost=120,665.23..120,665.23 rows=234,607 width=267) (actual time=2,025.285..2,025.286 rows=520,847 loops=1)

  • Buckets: 524,288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 119,935kB
312. 223.463 1,643.158 ↓ 2.2 520,847 1

Subquery Scan on u_19 (cost=83,910.44..120,665.23 rows=234,607 width=267) (actual time=999.056..1,643.158 rows=520,847 loops=1)

313. 417.506 1,419.695 ↓ 2.2 520,847 1

Hash Right Join (cost=83,910.44..118,319.16 rows=234,607 width=3,183) (actual time=999.054..1,419.695 rows=520,847 loops=1)

  • Hash Cond: (aff_goals_10.user_id = u_22.user_id)
314.          

CTE aff_goals

315. 3.126 10.868 ↓ 1.1 4,555 1

Sort (cost=996.76..1,007.53 rows=4,307 width=28) (actual time=9.849..10.868 rows=4,555 loops=1)

  • Sort Key: g_10.user_id
  • Sort Method: quicksort Memory: 415kB
316. 4.792 7.742 ↓ 1.1 4,555 1

HashAggregate (cost=693.71..736.78 rows=4,307 width=28) (actual time=6.232..7.742 rows=4,555 loops=1)

  • Group Key: g_10.user_id
317. 2.950 2.950 ↓ 1.0 7,156 1

Seq Scan on tbl_affiliate_goals_for_export g_10 (cost=0.00..496.95 rows=7,155 width=28) (actual time=0.029..2.950 rows=7,156 loops=1)

  • Filter: (goal_name <> 'INSTALL_EVENT'::text)
  • Rows Removed by Filter: 2,361
318. 13.303 13.303 ↓ 1.1 4,555 1

CTE Scan on aff_goals aff_goals_10 (cost=0.00..86.14 rows=4,307 width=8) (actual time=9.852..13.303 rows=4,555 loops=1)

319. 401.104 988.886 ↓ 2.2 520,847 1

Hash (cost=79,970.32..79,970.32 rows=234,607 width=198) (actual time=988.886..988.886 rows=520,847 loops=1)

  • Buckets: 524,288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 115,079kB
320. 587.782 587.782 ↓ 2.2 520,847 1

Seq Scan on etl_dim_ums_users u_22 (cost=0.00..79,970.32 rows=234,607 width=198) (actual time=0.025..587.782 rows=520,847 loops=1)

  • Filter: ((test_sign IS FALSE) AND (user_role = ANY ('{USER,PROFESSIONAL}'::text[])) AND (user_id <> ALL ('{10789518,10788054}'::bigint[])))
  • Rows Removed by Filter: 306,681
321. 148.200 332.601 ↓ 1.0 279,565 1

Hash (cost=42,143.07..42,143.07 rows=278,847 width=31) (actual time=332.601..332.601 rows=279,565 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 21,792kB
322. 184.401 184.401 ↓ 1.0 279,565 1

Index Scan using ix_tbl_installs_1 on tbl_installs tbl_installs_10 (cost=0.43..42,143.07 rows=278,847 width=31) (actual time=0.022..184.401 rows=279,565 loops=1)

  • Index Cond: (app_name = 'Investmate'::text)
323.          

CTE fx_recalc

324. 8,477.265 415,683.541 ↑ 1.4 1,416,026 1

Hash Left Join (cost=1,094.92..14,071,149.56 rows=1,995,646 width=2,988) (actual time=66,979.849..415,683.541 rows=1,416,026 loops=1)

  • Hash Cond: ((date(main_data.event_datetime) = ts_fx_daily_1.date) AND (main_data.ts_currency = ts_fx_daily_1.currency))
325. 407,181.291 407,181.291 ↑ 1.4 1,416,026 1

CTE Scan on main_data (cost=0.00..13,969,523.82 rows=1,995,646 width=2,940) (actual time=66,954.748..407,181.291 rows=1,416,026 loops=1)

  • Filter: (date_trunc('quarter'::text, event_datetime) = date_trunc('quarter'::text, (('now'::cstring)::date)::timestamp with time zone))
326. 12.862 24.985 ↓ 1.0 33,858 1

Hash (cost=587.97..587.97 rows=33,797 width=16) (actual time=24.984..24.985 rows=33,858 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,119kB
327. 12.123 12.123 ↓ 1.0 33,858 1

Seq Scan on ts_fx_daily ts_fx_daily_1 (cost=0.00..587.97 rows=33,797 width=16) (actual time=0.514..12.123 rows=33,858 loops=1)

328.          

CTE marketing_manager

329. 2.766 8.857 ↓ 1.3 2,529 1

HashAggregate (cost=125.26..153.94 rows=1,912 width=79) (actual time=8.056..8.857 rows=2,529 loops=1)

  • Group Key: upper(m_1.media_source), upper(m_1.campaign), m_1.marketing_manager
330. 6.091 6.091 ↑ 1.0 3,656 1

Seq Scan on scv_campaigns_managers m_1 (cost=0.00..97.84 rows=3,656 width=79) (actual time=0.709..6.091 rows=3,656 loops=1)

331. 3,182.337 446,058.636 ↑ 1.4 1,416,026 1

Merge Left Join (cost=1,707,128.95..1,739,448.44 rows=1,995,646 width=2,892) (actual time=437,690.611..446,058.636 rows=1,416,026 loops=1)

  • Merge Cond: (((upper(fx_recalc.media_source)) = (upper(m.media_source))) AND (fx_recalc.campaign_jn = (upper(m.campaign))))
332. 18,314.951 442,769.449 ↑ 1.4 1,416,026 1

Sort (cost=1,706,986.50..1,711,975.62 rows=1,995,646 width=2,892) (actual time=437,666.262..442,769.449 rows=1,416,026 loops=1)

  • Sort Key: (upper(fx_recalc.media_source)), fx_recalc.campaign_jn
  • Sort Method: external merge Disk: 858,968kB
333. 424,454.498 424,454.498 ↑ 1.4 1,416,026 1

CTE Scan on fx_recalc (cost=0.00..39,912.92 rows=1,995,646 width=2,892) (actual time=66,979.868..424,454.498 rows=1,416,026 loops=1)

334. 94.004 106.850 ↓ 127.5 243,853 1

Sort (cost=142.45..147.23 rows=1,912 width=96) (actual time=24.308..106.850 rows=243,853 loops=1)

  • Sort Key: (upper(m.media_source)), (upper(m.campaign))
  • Sort Method: quicksort Memory: 467kB
335. 12.846 12.846 ↓ 1.3 2,529 1

CTE Scan on marketing_manager m (cost=0.00..38.24 rows=1,912 width=96) (actual time=8.063..12.846 rows=2,529 loops=1)

336. 0.099 0.176 ↑ 1.0 250 1

Hash (cost=4.50..4.50 rows=250 width=23) (actual time=0.175..0.176 rows=250 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 22kB
337. 0.077 0.077 ↑ 1.0 250 1

Seq Scan on scv_geo_country_codes c (cost=0.00..4.50 rows=250 width=23) (actual time=0.009..0.077 rows=250 loops=1)

Execution time : 498,984.278 ms