explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gcnO

Settings
# exclusive inclusive rows x rows loops node
1. 16,401.254 520,443.491 ↓ 0.0 0 1

Insert on rep_attribution_data_2019_3 (cost=354,933,190.56..356,956,505.20 rows=1,995,646 width=3,483) (actual time=520,443.490..520,443.491 rows=0 loops=1)

  • Functions: 1,119
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 430.547 ms, Inlining 106.888 ms, Optimization 36814.519 ms, Emission 29428.997 ms, Total 66780.952 ms
2. 7,291.248 504,042.237 ↑ 1.4 1,416,150 1

Subquery Scan on etl_attribution_data_quarter_v (cost=354,933,190.56..356,956,505.20 rows=1,995,646 width=3,483) (actual time=463,402.147..504,042.237 rows=1,416,150 loops=1)

3. 29,122.616 496,750.989 ↑ 1.4 1,416,150 1

Hash Left Join (cost=354,933,190.56..356,896,635.82 rows=1,995,646 width=3,363) (actual time=463,402.124..496,750.989 rows=1,416,150 loops=1)

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

CTE user_doc_reject

5. 21.076 2,267.846 ↓ 952.0 952 1

GroupAggregate (cost=219,187.47..219,192.75 rows=1 width=16) (actual time=2,239.456..2,267.846 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.440 2,246.770 ↓ 243.3 36,732 1

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

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

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

  • Hash Cond: (e.user_id = u_1.user_id)
8. 1,873.947 1,873.947 ↓ 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=1.279..1,873.947 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.907 313.220 ↓ 3.0 17,345 1

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

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

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

11. 9.075 301.229 ↓ 3.0 17,345 1

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

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

CTE aff_goals

13. 3.454 12.880 ↓ 1.1 4,555 1

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

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

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

  • Group Key: g.user_id
15. 3.474 3.474 ↓ 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.032..3.474 rows=7,156 loops=1)

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

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

17. 6.705 276.969 ↓ 3.0 17,345 1

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

  • Buckets: 32,768 (originally 8192) Batches: 1 (originally 1) Memory Usage: 934kB
18. 270.264 270.264 ↓ 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.020..270.264 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. 640.038 429,192.322 ↑ 281.8 1,416,150 1

Append (cost=123,323.54..338,935,557.45 rows=399,129,260 width=2,940) (actual time=72,227.958..429,192.322 rows=1,416,150 loops=1)

21. 1,050.871 318,845.807 ↓ 8.9 1,173,025 1

Result (cost=123,323.54..7,892,317.96 rows=132,375 width=2,940) (actual time=72,227.956..318,845.807 rows=1,173,025 loops=1)

22. 561.225 317,794.936 ↓ 8.9 1,173,025 1

Append (cost=123,323.54..7,890,001.39 rows=132,375 width=2,880) (actual time=72,227.951..317,794.936 rows=1,173,025 loops=1)

23. 2,944.159 308,318.120 ↓ 8.9 1,172,933 1

Result (cost=123,323.54..7,486,650.04 rows=132,362 width=2,880) (actual time=72,227.949..308,318.120 rows=1,172,933 loops=1)

24. 532.564 305,373.961 ↓ 8.9 1,172,933 1

Append (cost=123,323.54..7,473,082.94 rows=132,362 width=1,868) (actual time=72,227.936..305,373.961 rows=1,172,933 loops=1)

25. 1,037.111 246,805.152 ↓ 8.7 897,516 1

Result (cost=123,323.54..6,514,553.39 rows=102,687 width=1,868) (actual time=72,227.935..246,805.152 rows=897,516 loops=1)

26. 412.322 245,768.041 ↓ 8.7 897,516 1

Append (cost=123,323.54..6,512,242.93 rows=102,687 width=1,908) (actual time=72,227.928..245,768.041 rows=897,516 loops=1)

27. 445.193 148,488.229 ↓ 21.8 482,174 1

Result (cost=123,323.54..4,205,283.31 rows=22,113 width=1,908) (actual time=72,227.927..148,488.229 rows=482,174 loops=1)

28. 227.603 148,043.036 ↓ 21.8 482,174 1

Append (cost=123,323.54..4,204,951.61 rows=22,113 width=1,928) (actual time=72,227.918..148,043.036 rows=482,174 loops=1)

29. 275.853 77,842.009 ↓ 24.5 321,958 1

Result (cost=123,323.54..833,483.39 rows=13,140 width=1,928) (actual time=72,227.917..77,842.009 rows=321,958 loops=1)

30. 140.259 77,566.156 ↓ 24.5 321,958 1

Append (cost=123,323.54..833,253.44 rows=13,140 width=1,928) (actual time=72,227.910..77,566.156 rows=321,958 loops=1)

31. 44.249 72,428.385 ↓ 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=72,227.909..72,428.385 rows=49,228 loops=1)

32. 118.438 72,384.136 ↓ 6.2 49,228 1

GroupAggregate (cost=123,323.54..124,347.63 rows=7,877 width=1,510) (actual time=72,227.902..72,384.136 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. 743.848 72,265.698 ↓ 14.8 116,221 1

Sort (cost=123,323.54..123,343.24 rows=7,879 width=238) (actual time=72,227.854..72,265.698 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. 71,521.850 71,521.850 ↓ 14.8 116,221 1

Seq Scan on tbl_installs (cost=0.00..122,813.62 rows=7,879 width=238) (actual time=70,685.530..71,521.850 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. 217.743 4,762.686 ↓ 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=3,726.880..4,762.686 rows=272,696 loops=1)

36. 736.226 4,544.943 ↓ 51.8 272,696 1

GroupAggregate (cost=66,063.98..66,783.95 rows=5,262 width=1,724) (actual time=3,726.874..4,544.943 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,217.953 3,808.717 ↓ 50.7 272,815 1

Sort (cost=66,063.98..66,077.44 rows=5,382 width=213) (actual time=3,726.819..3,808.717 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,590.764 1,590.764 ↓ 50.7 272,815 1

Seq Scan on fct_costs (cost=0.00..65,730.46 rows=5,382 width=213) (actual time=851.605..1,590.764 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.048 234.826 ↓ 34.0 34 1

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

40. 0.072 234.778 ↓ 34.0 34 1

GroupAggregate (cost=641,813.08..641,813.12 rows=1 width=1,705) (actual time=234.715..234.778 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 234.706 ↓ 35.0 35 1

Sort (cost=641,813.08..641,813.09 rows=1 width=73) (actual time=234.697..234.706 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.041 234.658 ↓ 35.0 35 1

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

43. 0.044 234.617 ↓ 35.0 35 1

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

44.          

CTE aff_price

45. 0.001 0.019 ↓ 0.0 0 1

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

46. 0.003 0.018 ↓ 0.0 0 1

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

47. 0.011 0.015 ↓ 0.0 0 1

Sort (cost=3,274.54..3,374.54 rows=40,000 width=176) (actual time=0.015..0.015 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.104 234.573 ↓ 35.0 35 1

Sort (cost=616,538.48..616,538.49 rows=1 width=119) (actual time=234.565..234.573 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.119 234.469 ↓ 35.0 35 1

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

52. 0.047 234.315 ↓ 35.0 35 1

Nested Loop Left Join (cost=179,550.42..616,535.95 rows=1 width=375) (actual time=20.179..234.315 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.044 234.268 ↓ 35.0 35 1

Nested Loop Left Join (cost=179,550.42..516,533.45 rows=1 width=311) (actual time=20.174..234.268 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.046 234.224 ↓ 35.0 35 1

Nested Loop Left Join (cost=179,550.42..406,530.20 rows=1 width=279) (actual time=20.170..234.224 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.074 234.178 ↓ 35.0 35 1

Nested Loop Left Join (cost=179,550.42..296,526.95 rows=1 width=215) (actual time=20.167..234.178 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.429 234.104 ↓ 35.0 35 1

Hash Left Join (cost=179,550.42..185,734.91 rows=1 width=151) (actual time=20.162..234.104 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.060 231.653 ↓ 35.0 35 1

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

58. 8.153 8.153 ↓ 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.520..8.153 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. 223.440 223.440 ↑ 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=6.384..6.384 rows=1 loops=35)

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

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

  • Buckets: 4,194,304 Batches: 1 Memory Usage: 32,768kB
61. 0.021 0.021 ↓ 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.021..0.021 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.035 0.035 ↓ 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.001..0.001 rows=0 loops=35)

  • Index Cond: ((a_1.event_dt = date) AND (currency = COALESCE(p.currency, r.currency, k.currency, j.currency)))
67. 182.594 41,195.520 ↓ 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=33,090.112..41,195.520 rows=158,442 loops=1)

68. 473.946 41,012.926 ↓ 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=33,090.104..41,012.926 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. 153.511 35,627.278 ↓ 17.9 158,442 1

Hash Left Join (cost=192,497.80..1,369,864.85 rows=8,829 width=446) (actual time=33,088.285..35,627.278 rows=158,442 loops=1)

  • Hash Cond: (u_3.apps_flyer_id = tbl_installs_1.apps_flyer_id)
70. 261.809 35,158.737 ↓ 17.9 158,442 1

Hash Join (cost=146,869.14..1,324,187.46 rows=8,829 width=415) (actual time=32,772.936..35,158.737 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,149.229 28,389.273 ↓ 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=26,264.932..28,389.273 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. 26,240.044 26,240.044 ↓ 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=26,240.043..26,240.044 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. 496.923 6,507.655 ↓ 2.2 520,847 1

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

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

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

75. 514.567 5,750.073 ↓ 2.2 520,847 1

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

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

CTE aff_goals

77. 3.297 11.788 ↓ 1.1 4,555 1

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

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

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

  • Group Key: g_1.user_id
79. 3.227 3.227 ↓ 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..3.227 rows=7,156 loops=1)

  • Filter: (goal_name <> 'INSTALL_EVENT'::text)
  • Rows Removed by Filter: 2,361
80. 14.490 14.490 ↓ 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.664..14.490 rows=4,555 loops=1)

81. 489.799 5,221.016 ↓ 2.2 520,847 1

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

  • Buckets: 524,288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 129,641kB
82. 4,731.217 4,731.217 ↓ 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=6.191..4,731.217 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. 126.150 315.030 ↓ 1.0 279,565 1

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

  • Buckets: 524,288 Batches: 1 Memory Usage: 21,792kB
84. 188.880 188.880 ↓ 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=9.155..188.880 rows=279,565 loops=1)

  • Index Cond: (app_name = 'Investmate'::text)
85. 4,911.702 4,911.702 ↑ 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.030..0.031 rows=2 loops=158,442)

  • Index Cond: (u_3.user_id = user_id)
86. 1.060 22,976.333 ↓ 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=22,321.884..22,976.333 rows=1,141 loops=1)

87. 583.973 22,975.273 ↓ 10.1 1,141 1

GroupAggregate (cost=1,735,630.16..1,738,119.92 rows=113 width=1,628) (actual time=22,321.877..22,975.273 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,592.423 22,391.300 ↓ 13.8 312,578 1

Sort (cost=1,735,630.16..1,735,686.74 rows=22,629 width=427) (actual time=22,285.256..22,391.300 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. 313.231 19,798.877 ↓ 13.8 312,578 1

Nested Loop Left Join (cost=229,324.80..1,733,993.42 rows=22,629 width=427) (actual time=11,681.423..19,798.877 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. 256.893 16,047.288 ↓ 13.8 312,578 1

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

  • Hash Cond: (u_5.apps_flyer_id = tbl_installs_2.apps_flyer_id)
91. 395.187 15,517.210 ↓ 13.8 312,578 1

Hash Join (cost=183,695.71..1,675,640.52 rows=22,629 width=303) (actual time=11,407.483..15,517.210 rows=312,578 loops=1)

  • Hash Cond: (e_2.user_id = u_5.user_id)
92. 3,752.189 13,019.284 ↓ 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,304.397..13,019.284 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,267.095 9,267.095 ↑ 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,267.095..9,267.095 rows=4,285,418 loops=1)

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

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

  • Buckets: 524,288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 119,935kB
95. 227.036 1,717.447 ↓ 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,047.398..1,717.447 rows=520,847 loops=1)

96. 439.854 1,490.411 ↓ 2.2 520,847 1

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

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

CTE aff_goals

98. 3.127 11.046 ↓ 1.1 4,555 1

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

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

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

  • Group Key: g_2.user_id
100. 3.015 3.015 ↓ 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.032..3.015 rows=7,156 loops=1)

  • Filter: (goal_name <> 'INSTALL_EVENT'::text)
  • Rows Removed by Filter: 2,361
101. 13.522 13.522 ↓ 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.030..13.522 rows=4,555 loops=1)

102. 419.959 1,037.035 ↓ 2.2 520,847 1

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

  • Buckets: 524,288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 115,079kB
103. 617.076 617.076 ↓ 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.025..617.076 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. 118.505 273.185 ↓ 1.0 279,565 1

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

  • Buckets: 524,288 Batches: 1 Memory Usage: 21,792kB
105. 154.680 154.680 ↓ 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.023..154.680 rows=279,565 loops=1)

  • Index Cond: (app_name = 'Investmate'::text)
106. 3,438.358 3,438.358 ↑ 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.010..0.011 rows=1 loops=312,578)

  • Index Cond: (u_5.user_id = user_id)
107. 0.795 5,801.571 ↓ 20.4 633 1

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

108. 1.794 5,800.776 ↓ 20.4 633 1

Nested Loop Left Join (cost=213,129.93..258,078.63 rows=31 width=1,453) (actual time=5,550.771..5,800.776 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. 98.498 5,791.386 ↓ 20.4 633 1

Hash Right Join (cost=213,129.51..258,060.67 rows=31 width=429) (actual time=5,550.740..5,791.386 rows=633 loops=1)

  • Hash Cond: (tbl_installs_3.apps_flyer_id = u_7.apps_flyer_id)
110. 153.478 153.478 ↓ 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.026..153.478 rows=279,565 loops=1)

  • Index Cond: (app_name = 'Investmate'::text)
111. 1.215 5,539.410 ↓ 20.4 633 1

Hash (cost=213,128.69..213,128.69 rows=31 width=398) (actual time=5,539.410..5,539.410 rows=633 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 194kB
112. 147.232 5,538.195 ↓ 20.4 633 1

Hash Join (cost=175,493.81..213,128.69 rows=31 width=398) (actual time=4,908.425..5,538.195 rows=633 loops=1)

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

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

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

CTE aff_goals

115. 3.978 11.898 ↓ 1.1 4,555 1

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

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

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

  • Group Key: g_3.user_id
117. 3.035 3.035 ↓ 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.033..3.035 rows=7,156 loops=1)

  • Filter: (goal_name <> 'INSTALL_EVENT'::text)
  • Rows Removed by Filter: 2,361
118. 15.952 15.952 ↓ 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=10.093..15.952 rows=4,555 loops=1)

119. 438.321 1,048.233 ↓ 2.2 520,847 1

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

  • Buckets: 524,288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 123,752kB
120. 609.912 609.912 ↓ 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.035..609.912 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.337 3,849.623 ↓ 20.7 641 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 64kB
122. 0.350 3,849.286 ↓ 20.7 641 1

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

123. 3,848.936 3,848.936 ↓ 20.7 641 1

CTE Scan on s (cost=91,362.42..91,582.67 rows=31 width=232) (actual time=3,841.309..3,848.936 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. 73.377 73.377 ↓ 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.030..73.377 rows=6,593 loops=1)

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

CTE s

129. 7.375 3,838.460 ↓ 1.0 6,593 1

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

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

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

131. 209.565 3,750.817 ↓ 2.2 523,614 1

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

  • Buckets: 524,288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 28,641kB
132. 3,541.252 3,541.252 ↓ 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=3.055..3,541.252 rows=523,614 loops=1)

  • Filter: (user_role = ANY ('{USER,PROFESSIONAL}'::text[]))
  • Rows Removed by Filter: 303,914
133. 7.596 7.596 ↑ 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.011..0.012 rows=1 loops=633)

  • Index Cond: (u_7.user_id = user_id)
134. 437.408 57,930.804 ↓ 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=49,153.415..57,930.804 rows=245,822 loops=1)

135. 8,001.890 57,493.396 ↓ 17.9 245,822 1

GroupAggregate (cost=617,423.49..623,510.97 rows=13,757 width=1,400) (actual time=49,153.404..57,493.396 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. 16,001.938 49,491.506 ↓ 83.1 1,143,046 1

Sort (cost=617,423.49..617,457.89 rows=13,757 width=492) (actual time=49,153.302..49,491.506 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: 646,244kB
137. 3,175.266 33,489.568 ↓ 83.1 1,143,046 1

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

138. 3,585.905 21,169.934 ↓ 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,610.064..21,169.934 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. 761.671 14,154.891 ↓ 83.1 1,143,046 1

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

  • Hash Cond: (u_8.apps_flyer_id = tbl_installs_4.apps_flyer_id)
140. 929.601 13,104.842 ↓ 83.1 1,143,046 1

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

  • Hash Cond: (fct_transactions.user_id = u_8.user_id)
141. 9,862.634 9,862.634 ↓ 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=6.029..9,862.634 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. 454.371 2,312.607 ↓ 2.2 520,847 1

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

  • Buckets: 524,288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 125,321kB
143. 252.451 1,858.236 ↓ 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,091.856..1,858.236 rows=520,847 loops=1)

144. 510.670 1,605.785 ↓ 2.2 520,847 1

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

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

CTE aff_goals

146. 3.233 11.101 ↓ 1.1 4,555 1

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

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

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

  • Group Key: g_4.user_id
148. 2.971 2.971 ↓ 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.034..2.971 rows=7,156 loops=1)

  • Filter: (goal_name <> 'INSTALL_EVENT'::text)
  • Rows Removed by Filter: 2,361
149. 13.656 13.656 ↓ 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.063..13.656 rows=4,555 loops=1)

150. 437.021 1,081.459 ↓ 2.2 520,847 1

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

  • Buckets: 524,288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 119,530kB
151. 644.438 644.438 ↓ 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.022..644.438 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. 125.792 288.378 ↓ 1.0 279,565 1

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

  • Buckets: 524,288 Batches: 1 Memory Usage: 21,792kB
153. 162.586 162.586 ↓ 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.030..162.586 rows=279,565 loops=1)

  • Index Cond: (app_name = 'Investmate'::text)
154. 3,429.138 3,429.138 ↓ 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.003 rows=4 loops=1,143,046)

  • Index Cond: (u_8.user_id = user_id)
155. 6,858.276 6,858.276 ↑ 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.006..0.006 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. 174.614 24,817.345 ↓ 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=23,269.051..24,817.345 rows=163,361 loops=1)

159. 1,121.446 24,642.731 ↓ 12.5 163,361 1

GroupAggregate (cost=551,470.44..553,204.73 rows=13,089 width=1,482) (actual time=23,269.040..24,642.731 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. 9,955.979 23,521.285 ↓ 76.8 1,005,601 1

Sort (cost=551,470.44..551,503.16 rows=13,089 width=436) (actual time=23,268.994..23,521.285 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,886.206 13,565.306 ↓ 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,236.904..13,565.306 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. 555.559 8,662.297 ↓ 76.8 1,005,601 1

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

  • Hash Cond: (u_10.apps_flyer_id = tbl_installs_5.apps_flyer_id)
163. 671.127 7,840.792 ↓ 76.8 1,005,601 1

Hash Join (cost=123,597.82..497,488.67 rows=13,089 width=312) (actual time=1,970.635..7,840.792 rows=1,005,601 loops=1)

  • Hash Cond: (fct_transactions_1.user_id = u_10.user_id)
164. 5,202.672 5,202.672 ↓ 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.365..5,202.672 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. 365.788 1,966.993 ↓ 2.2 520,847 1

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

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

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

167. 410.253 1,379.136 ↓ 2.2 520,847 1

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

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

CTE aff_goals

169. 3.141 10.991 ↓ 1.1 4,555 1

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

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

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

  • Group Key: g_5.user_id
171. 2.977 2.977 ↓ 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.032..2.977 rows=7,156 loops=1)

  • Filter: (goal_name <> 'INSTALL_EVENT'::text)
  • Rows Removed by Filter: 2,361
172. 13.429 13.429 ↓ 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.990..13.429 rows=4,555 loops=1)

173. 383.952 955.454 ↓ 2.2 520,847 1

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

  • Buckets: 524,288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 115,079kB
174. 571.502 571.502 ↓ 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.019..571.502 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. 115.124 265.946 ↓ 1.0 279,565 1

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

  • Buckets: 524,288 Batches: 1 Memory Usage: 21,792kB
176. 150.822 150.822 ↓ 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.023..150.822 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.439 5,594.769 ↑ 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,493.349..5,594.769 rows=2,234 loops=1)

179. 78.529 5,592.330 ↑ 22.4 2,234 1

GroupAggregate (cost=579,414.10..585,294.27 rows=50,044 width=1,482) (actual time=5,493.338..5,592.330 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. 650.350 5,513.801 ↓ 1.7 87,474 1

Sort (cost=579,414.10..579,539.21 rows=50,044 width=425) (actual time=5,493.272..5,513.801 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. 188.569 4,863.451 ↓ 1.7 87,474 1

Nested Loop Left Join (cost=169,226.91..575,507.94 rows=50,044 width=425) (actual time=2,376.064..4,863.451 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. 51.336 4,412.460 ↓ 1.7 87,474 1

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

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

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

  • Hash Cond: (fct_transactions_2.user_id = u_12.user_id)
184. 1,930.984 1,930.984 ↓ 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.274..1,930.984 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. 422.360 2,104.020 ↓ 2.2 520,847 1

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

  • Buckets: 524,288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 119,935kB
186. 224.511 1,681.660 ↓ 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,025.298..1,681.660 rows=520,847 loops=1)

187. 428.800 1,457.149 ↓ 2.2 520,847 1

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

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

CTE aff_goals

189. 3.172 10.913 ↓ 1.1 4,555 1

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

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

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

  • Group Key: g_6.user_id
191. 2.975 2.975 ↓ 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.029..2.975 rows=7,156 loops=1)

  • Filter: (goal_name <> 'INSTALL_EVENT'::text)
  • Rows Removed by Filter: 2,361
192. 13.249 13.249 ↓ 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=9.883..13.249 rows=4,555 loops=1)

193. 427.968 1,015.100 ↓ 2.2 520,847 1

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

  • Buckets: 524,288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 115,079kB
194. 587.132 587.132 ↓ 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.018..587.132 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. 118.079 268.181 ↓ 1.0 279,565 1

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

  • Buckets: 524,288 Batches: 1 Memory Usage: 21,792kB
196. 150.102 150.102 ↓ 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.024..150.102 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.642 8,524.572 ↓ 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,235.370..8,524.572 rows=3,925 loops=1)

199. 226.168 8,519.930 ↓ 1.1 3,925 1

GroupAggregate (cost=541,812.74..542,254.82 rows=3,684 width=1,490) (actual time=8,235.359..8,519.930 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. 1,951.527 8,293.762 ↓ 69.9 257,403 1

Sort (cost=541,812.74..541,821.95 rows=3,684 width=441) (actual time=8,235.228..8,293.762 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. 664.488 6,342.235 ↓ 69.9 257,403 1

Nested Loop Left Join (cost=169,226.91..541,594.52 rows=3,684 width=441) (actual time=2,360.964..6,342.235 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. 149.088 4,905.538 ↓ 69.9 257,403 1

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

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

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

  • Hash Cond: (fct_transactions_3.user_id = u_14.user_id)
204. 2,226.716 2,226.716 ↓ 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.096..2,226.716 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. 423.630 2,088.185 ↓ 2.2 520,847 1

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

  • Buckets: 524,288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 119,935kB
206. 221.778 1,664.555 ↓ 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,015.038..1,664.555 rows=520,847 loops=1)

207. 425.282 1,442.777 ↓ 2.2 520,847 1

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

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

CTE aff_goals

209. 3.161 10.938 ↓ 1.1 4,555 1

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

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

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

  • Group Key: g_7.user_id
211. 2.989 2.989 ↓ 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.029..2.989 rows=7,156 loops=1)

  • Filter: (goal_name <> 'INSTALL_EVENT'::text)
  • Rows Removed by Filter: 2,361
212. 13.252 13.252 ↓ 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=9.903..13.252 rows=4,555 loops=1)

213. 426.594 1,004.243 ↓ 2.2 520,847 1

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

  • Buckets: 524,288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 115,079kB
214. 577.649 577.649 ↓ 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..577.649 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. 117.743 268.128 ↓ 1.0 279,565 1

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

  • Buckets: 524,288 Batches: 1 Memory Usage: 21,792kB
216. 150.385 150.385 ↓ 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.025..150.385 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. 318.699 53,604.392 ↓ 9.2 274,465 1

Subquery Scan on *SELECT* 11 (cost=788,632.52..793,973.84 rows=29,674 width=1,450) (actual time=50,533.845..53,604.392 rows=274,465 loops=1)

219. 2,393.548 53,285.693 ↓ 9.2 274,465 1

GroupAggregate (cost=788,632.52..793,231.99 rows=29,674 width=1,406) (actual time=50,533.834..53,285.693 rows=274,465 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. 20,571.998 50,892.145 ↓ 42.3 1,255,744 1

Sort (cost=788,632.52..788,706.71 rows=29,674 width=482) (actual time=50,533.777..50,892.145 rows=1,255,744 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: 689,392kB
221. 3,325.742 30,320.147 ↓ 42.3 1,255,744 1

Nested Loop Left Join (cost=169,227.33..786,428.20 rows=29,674 width=482) (actual time=2,380.337..30,320.147 rows=1,255,744 loops=1)

222. 2,828.870 21,971.429 ↓ 42.3 1,255,744 1

Nested Loop Left Join (cost=169,226.91..729,132.53 rows=29,674 width=440) (actual time=2,380.293..21,971.429 rows=1,255,744 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,648,528
223. 852.003 14,119.583 ↓ 42.3 1,255,744 1

Hash Left Join (cost=169,226.48..712,759.06 rows=29,674 width=373) (actual time=2,380.262..14,119.583 rows=1,255,744 loops=1)

  • Hash Cond: (u_16.apps_flyer_id = tbl_installs_8.apps_flyer_id)
224. 1,175.468 13,001.032 ↓ 42.3 1,255,744 1

Hash Join (cost=123,597.82..666,966.61 rows=29,674 width=342) (actual time=2,111.419..13,001.032 rows=1,255,744 loops=1)

  • Hash Cond: (t.user_id = u_16.user_id)
225. 9,716.160 9,716.160 ↓ 42.4 1,259,105 1

Seq Scan on tbl_fct_position_history t (cost=0.00..542,960.78 rows=29,674 width=75) (actual time=0.764..9,716.160 rows=1,259,105 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,569
226. 424.089 2,109.404 ↓ 2.2 520,847 1

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

  • Buckets: 524,288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 120,114kB
227. 225.692 1,685.315 ↓ 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,016.797..1,685.315 rows=520,847 loops=1)

228. 440.441 1,459.623 ↓ 2.2 520,847 1

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

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

CTE aff_goals

230. 3.160 11.178 ↓ 1.1 4,555 1

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

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

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

  • Group Key: g_8.user_id
232. 3.100 3.100 ↓ 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.047..3.100 rows=7,156 loops=1)

  • Filter: (goal_name <> 'INSTALL_EVENT'::text)
  • Rows Removed by Filter: 2,361
233. 13.486 13.486 ↓ 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.180..13.486 rows=4,555 loops=1)

234. 427.505 1,005.696 ↓ 2.2 520,847 1

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

  • Buckets: 524,288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 115,181kB
235. 578.191 578.191 ↓ 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.023..578.191 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. 115.678 266.548 ↓ 1.0 279,565 1

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

  • Buckets: 524,288 Batches: 1 Memory Usage: 21,792kB
237. 150.870 150.870 ↓ 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.030..150.870 rows=279,565 loops=1)

  • Index Cond: (app_name = 'Investmate'::text)
238. 5,022.976 5,022.976 ↓ 2.0 4 1,255,744

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.004 rows=4 loops=1,255,744)

  • Index Cond: (u_16.user_id = user_id)
239. 2,511.488 2,511.488 ↑ 1.0 1 1,255,744

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,255,744)

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

SubPlan (for Nested Loop Left Join)

241. 2,511.488 2,511.488 ↑ 1.0 1 1,255,744

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

242. 1.524 4,431.853 ↓ 952.0 952 1

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

243. 2.317 4,430.329 ↓ 952.0 952 1

Nested Loop Left Join (cost=120,705.38..163,893.86 rows=1 width=1,470) (actual time=4,379.389..4,430.329 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. 92.174 4,424.204 ↓ 952.0 952 1

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

  • Hash Cond: (tbl_installs_9.apps_flyer_id = u_18.apps_flyer_id)
245. 142.007 142.007 ↓ 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.024..142.007 rows=279,565 loops=1)

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

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

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

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

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

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

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

CTE aff_goals

250. 3.145 11.110 ↓ 1.1 4,555 1

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

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

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

  • Group Key: g_9.user_id
252. 3.022 3.022 ↓ 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.039..3.022 rows=7,156 loops=1)

  • Filter: (goal_name <> 'INSTALL_EVENT'::text)
  • Rows Removed by Filter: 2,361
253. 13.424 13.424 ↓ 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=10.082..13.424 rows=4,555 loops=1)

254. 608.693 1,337.171 ↓ 2.2 523,614 1

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

  • Buckets: 524,288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 115,556kB
255. 728.478 728.478 ↓ 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.034..728.478 rows=523,614 loops=1)

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

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

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

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

258. 3.808 3.808 ↑ 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.003..0.004 rows=2 loops=952)

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

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

260. 0.924 8,915.393 ↓ 7.1 92 1

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

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

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

  • Group Key: date(src_finres_b2c_trades_daily.reportdate)
262. 265.668 265.668 ↑ 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=1.145..265.668 rows=326,625 loops=1)

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

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

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

Hash Full Join (cost=376,279.57..376,330.92 rows=10 width=480) (actual time=8,330.729..8,332.453 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.686 3.792 ↑ 2.4 880 1

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

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

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

267. 1.005 2.934 ↓ 1.3 879 1

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

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

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

269. 0.287 8,327.735 ↓ 4.1 815 1

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

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

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

  • Group Key: q1.reportdate
271.          

CTE q1

272. 4,042.743 7,089.125 ↑ 1.0 1,344,660 1

WindowAgg (cost=272,541.01..338,202.59 rows=1,382,349 width=20) (actual time=2,754.509..7,089.125 rows=1,344,660 loops=1)

273. 867.010 3,046.382 ↑ 1.0 1,344,660 1

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

  • Sort Key: (date(d.ts_utc))
  • Sort Method: quicksort Memory: 154,204kB
274. 2,179.372 2,179.372 ↑ 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=1.018..2,179.372 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,858.374 7,858.374 ↑ 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,754.512..7,858.374 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. 662.131 109,706.477 ↑ 1,641.1 243,125 1

Subquery Scan on *SELECT* 14 (cost=221,318,434.24..329,047,593.19 rows=398,996,885 width=2,550) (actual time=103,044.318..109,706.477 rows=243,125 loops=1)

279. 5,311.421 109,044.346 ↑ 1,641.1 243,125 1

GroupAggregate (cost=221,318,434.24..278,175,490.35 rows=398,996,885 width=1,454) (actual time=103,044.304..109,044.346 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. 21,266.135 103,732.925 ↑ 169.0 2,361,392 1

Sort (cost=221,318,434.24..222,315,926.45 rows=398,996,885 width=599) (actual time=103,044.232..103,732.925 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: quicksort Memory: 1,100,570kB
281. 1,273.448 82,466.790 ↑ 169.0 2,361,392 1

Hash Left Join (cost=58,986,457.38..102,013,834.72 rows=398,996,885 width=599) (actual time=73,248.678..82,466.790 rows=2,361,392 loops=1)

  • Hash Cond: (u_19.apps_flyer_id = tbl_installs_10.apps_flyer_id)
282. 7,746.303 80,913.887 ↑ 169.0 2,361,392 1

Hash Right Join (cost=58,940,828.71..94,778,414.96 rows=398,996,885 width=542) (actual time=72,968.905..80,913.887 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. 337.042 337.042 ↑ 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.030..337.042 rows=725,200 loops=1)

284. 2,555.787 72,830.542 ↑ 169.0 2,361,392 1

Hash (cost=29,405,707.65..29,405,707.65 rows=398,996,885 width=475) (actual time=72,830.542..72,830.542 rows=2,361,392 loops=1)

  • Buckets: 4,194,304 Batches: 128 Memory Usage: 35,480kB
285. 1,524.514 70,274.755 ↑ 169.0 2,361,392 1

Hash Join (cost=1,277,993.93..29,405,707.65 rows=398,996,885 width=475) (actual time=47,810.376..70,274.755 rows=2,361,392 loops=1)

  • Hash Cond: ("*SELECT* 1_1".user_id = u_19.user_id)
286. 955.774 66,509.208 ↑ 167.5 2,382,539 1

Append (cost=1,154,396.11..19,805,933.81 rows=398,996,885 width=536) (actual time=45,569.033..66,509.208 rows=2,382,539 loops=1)

287.          

CTE q1

288. 10,460.360 10,460.360 ↑ 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.941..10,460.360 rows=11,710,143 loops=1)

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

Subquery Scan on *SELECT* 1_1 (cost=710,904.49..16,544,361.29 rows=395,821,772 width=479) (actual time=45,569.031..53,510.311 rows=2,276,779 loops=1)

290. 5,000.736 52,252.532 ↑ 173.9 2,276,779 1

Merge Join (cost=710,904.49..11,596,589.14 rows=395,821,772 width=503) (actual time=45,569.025..52,252.532 rows=2,276,779 loops=1)

  • Merge Cond: (q1_1.account_id = ((a_4.id)::text))
291. 6,772.243 37,285.139 ↓ 38.9 2,276,779 1

Sort (cost=496,706.55..496,853.03 rows=58,592 width=407) (actual time=36,505.342..37,285.139 rows=2,276,779 loops=1)

  • Sort Key: q1_1.account_id
  • Sort Method: quicksort Memory: 692,707kB
292. 1,365.362 30,512.896 ↓ 38.9 2,276,779 1

Hash Join (cost=80,947.05..492,066.53 rows=58,592 width=407) (actual time=1,445.976..30,512.896 rows=2,276,779 loops=1)

  • Hash Cond: (q1_1.user_id = u_20.user_id)
293. 1,785.188 28,001.372 ↓ 38.9 2,280,377 1

Hash Join (cost=9,860.87..420,174.70 rows=58,592 width=395) (actual time=297.186..28,001.372 rows=2,280,377 loops=1)

  • Hash Cond: ((q1_1.instrument)::bigint = i.id)
294. 26,177.717 26,177.717 ↓ 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=258.657..26,177.717 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. 4.302 38.467 ↑ 1.7 7,557 1

Hash (cost=9,696.34..9,696.34 rows=13,162 width=35) (actual time=38.466..38.467 rows=7,557 loops=1)

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

Index Scan using ts_instrument__id__idx on ts_instrument i (cost=0.29..9,696.34 rows=13,162 width=35) (actual time=0.038..34.165 rows=7,557 loops=1)

297. 335.470 1,146.162 ↓ 2.2 827,528 1

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

  • Buckets: 1,048,576 (originally 524288) Batches: 1 (originally 1) Memory Usage: 53,448kB
298. 810.692 810.692 ↓ 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.024..810.692 rows=827,528 loops=1)

299. 8,363.496 9,966.657 ↓ 2.7 3,624,060 1

Sort (cost=214,197.94..217,575.72 rows=1,351,112 width=23) (actual time=9,062.334..9,966.657 rows=3,624,060 loops=1)

  • Sort Key: ((a_4.id)::text)
  • Sort Method: quicksort Memory: 175,191kB
300. 1,603.161 1,603.161 ↓ 1.0 1,355,965 1

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

301. 728.712 12,043.123 ↑ 30.0 105,760 1

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

  • Merge Cond: (((fct_transactions_4.user_account_id)::text) = ((a_5.id)::text))
302. 178.864 2,933.518 ↓ 225.0 105,760 1

Sort (cost=442,198.06..442,199.24 rows=470 width=84) (actual time=2,906.700..2,933.518 rows=105,760 loops=1)

  • Sort Key: ((fct_transactions_4.user_account_id)::text)
  • Sort Method: quicksort Memory: 29,644kB
303. 74.721 2,754.654 ↓ 225.0 105,760 1

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

  • Hash Cond: (fct_transactions_4.user_id = u_21.user_id)
304. 162.529 1,753.881 ↓ 225.0 105,760 1

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

305. 1,485.592 1,485.592 ↓ 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=962.713..1,485.592 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. 105.760 105.760 ↑ 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.001..0.001 rows=1 loops=105,760)

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

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

  • Buckets: 1,048,576 (originally 524288) Batches: 1 (originally 1) Memory Usage: 53,448kB
308. 612.601 612.601 ↓ 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.011..612.601 rows=827,528 loops=1)

309. 7,779.356 8,380.893 ↑ 1.0 1,350,871 1

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

  • Sort Key: ((a_5.id)::text)
  • Sort Method: quicksort Memory: 175,191kB
310. 601.537 601.537 ↓ 1.0 1,355,965 1

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

311. 448.753 2,241.033 ↓ 2.2 520,847 1

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

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

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

313. 468.890 1,560.101 ↓ 2.2 520,847 1

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

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

CTE aff_goals

315. 3.220 11.077 ↓ 1.1 4,555 1

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

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

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

  • Group Key: g_10.user_id
317. 2.982 2.982 ↓ 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.037..2.982 rows=7,156 loops=1)

  • Filter: (goal_name <> 'INSTALL_EVENT'::text)
  • Rows Removed by Filter: 2,361
318. 13.502 13.502 ↓ 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.983..13.502 rows=4,555 loops=1)

319. 456.619 1,077.709 ↓ 2.2 520,847 1

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

  • Buckets: 524,288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 115,079kB
320. 621.090 621.090 ↓ 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.038..621.090 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. 122.849 279.455 ↓ 1.0 279,565 1

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

  • Buckets: 524,288 Batches: 1 Memory Usage: 21,792kB
322. 156.606 156.606 ↓ 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..156.606 rows=279,565 loops=1)

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

CTE fx_recalc

324. 8,409.970 444,924.685 ↑ 1.4 1,416,150 1

Hash Left Join (cost=1,094.92..14,071,149.84 rows=1,995,646 width=2,988) (actual time=72,250.214..444,924.685 rows=1,416,150 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. 436,492.593 436,492.593 ↑ 1.4 1,416,150 1

CTE Scan on main_data (cost=0.00..13,969,524.10 rows=1,995,646 width=2,940) (actual time=72,227.977..436,492.593 rows=1,416,150 loops=1)

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

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

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,119kB
327. 9.821 9.821 ↓ 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.022..9.821 rows=33,858 loops=1)

328.          

CTE marketing_manager

329. 2.789 7.623 ↓ 1.3 2,529 1

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

  • Group Key: upper(m_1.media_source), upper(m_1.campaign), m_1.marketing_manager
330. 4.834 4.834 ↑ 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.728..4.834 rows=3,656 loops=1)

331. 3,541.388 467,627.471 ↑ 1.4 1,416,150 1

Merge Left Join (cost=1,707,128.95..1,739,448.44 rows=1,995,646 width=2,892) (actual time=463,400.374..467,627.471 rows=1,416,150 loops=1)

  • Merge Cond: (((upper(fx_recalc.media_source)) = (upper(m.media_source))) AND (fx_recalc.campaign_jn = (upper(m.campaign))))
332. 11,453.327 463,980.464 ↑ 1.4 1,416,150 1

Sort (cost=1,706,986.50..1,711,975.62 rows=1,995,646 width=2,892) (actual time=463,376.848..463,980.464 rows=1,416,150 loops=1)

  • Sort Key: (upper(fx_recalc.media_source)), fx_recalc.campaign_jn
  • Sort Method: quicksort Memory: 1,484,934kB
333. 452,527.137 452,527.137 ↑ 1.4 1,416,150 1

CTE Scan on fx_recalc (cost=0.00..39,912.92 rows=1,995,646 width=2,892) (actual time=72,250.232..452,527.137 rows=1,416,150 loops=1)

334. 93.758 105.619 ↓ 127.6 243,881 1

Sort (cost=142.45..147.23 rows=1,912 width=96) (actual time=23.483..105.619 rows=243,881 loops=1)

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

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

336. 0.116 0.902 ↑ 1.0 250 1

Hash (cost=4.50..4.50 rows=250 width=23) (actual time=0.901..0.902 rows=250 loops=1)

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

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

Execution time : 521,686.002 ms