explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4Y8c : sdsdf

Settings
# exclusive inclusive rows x rows loops node
1. 15,084.119 2,683,015.801 ↓ 0.0 0 1

Insert on rep_attribution_data (cost=502,626,397.01..507,050,539.64 rows=1,995,646 width=3,483) (actual time=2,683,015.801..2,683,015.801 rows=0 loops=1)

  • Buffers: shared hit=889,437,984 read=752,321 dirtied=163,710, temp read=917,051 written=1,308,447
2. 8,504.071 2,667,931.682 ↑ 1.4 1,415,028 1

Subquery Scan on etl_attribution_data_quarter_v (cost=502,626,397.01..507,050,539.64 rows=1,995,646 width=3,483) (actual time=71,109.043..2,667,931.682 rows=1,415,028 loops=1)

  • Buffers: shared hit=887,672,568 read=588,634 dirtied=15, temp read=917,051 written=1,308,447
3. 34,559.921 2,659,427.611 ↑ 1.4 1,415,028 1

Hash Join (cost=502,626,397.01..506,990,670.26 rows=1,995,646 width=3,363) (actual time=71,109.022..2,659,427.611 rows=1,415,028 loops=1)

  • Buffers: shared hit=887,672,568 read=588,634 dirtied=15, temp read=917,051 written=1,308,447
4.          

CTE main_data

5. 807.514 2,588,216.774 ↑ 282.1 1,415,028 1

Append (cost=123,298.55..488,555,020.36 rows=399,129,212 width=2,940) (actual time=71,071.287..2,588,216.774 rows=1,415,028 loops=1)

  • Buffers: shared hit=886,257,537 read=588,342 dirtied=6, temp read=917,051 written=1,101,542
6. 1,326.211 2,468,481.290 ↓ 8.9 1,171,934 1

Result (cost=123,298.55..8,529,068.43 rows=132,327 width=2,940) (actual time=71,071.285..2,468,481.29 rows=1,171,934 loops=1)

  • Buffers: shared hit=884,268,399 read=466,095 dirtied=6, temp read=490,407 written=496,285
7. 685.892 2,467,155.079 ↓ 8.9 1,171,934 1

Append (cost=123,298.55..8,526,752.71 rows=132,327 width=2,880) (actual time=71,071.279..2,467,155.079 rows=1,171,934 loops=1)

  • Buffers: shared hit=884,268,399 read=466,095 dirtied=6, temp read=490,407 written=496,285
8. 3,696.002 2,456,788.816 ↓ 8.9 1,171,842 1

Result (cost=123,298.55..8,104,029.09 rows=132,314 width=2,880) (actual time=71,071.277..2,456,788.816 rows=1,171,842 loops=1)

  • Buffers: shared hit=884,268,399 read=370,520 dirtied=6, temp read=484,063 written=484,344
9. 662.050 2,453,092.814 ↓ 8.9 1,171,842 1

Append (cost=123,298.55..8,090,466.91 rows=132,314 width=1,868) (actual time=71,071.265..2,453,092.814 rows=1,171,842 loops=1)

  • Buffers: shared hit=884,268,399 read=370,520 dirtied=6, temp read=484,063 written=484,344
10. 1,277.383 211,454.306 ↓ 8.7 896,679 1

Result (cost=123,298.55..6,859,278.19 rows=102,652 width=1,868) (actual time=71,071.263..211,454.306 rows=896,679 loops=1)

  • Buffers: shared hit=24,199,653 read=370,332 dirtied=6, temp read=349,937 written=350,133
11. 501.623 210,176.923 ↓ 8.7 896,679 1

Append (cost=123,298.55..6,856,968.52 rows=102,652 width=1,908) (actual time=71,071.23..210,176.923 rows=896,679 loops=1)

  • Buffers: shared hit=24,199,653 read=370,332 dirtied=6, temp read=349,937 written=350,133
12. 608.833 104,567.852 ↓ 21.8 481,690 1

Result (cost=123,298.55..4,409,324.68 rows=22,092 width=1,908) (actual time=71,071.229..104,567.852 rows=481,690 loops=1)

  • Buffers: shared hit=4,691,149 read=370,319, temp read=75,143 written=75,175
13. 252.901 103,959.019 ↓ 21.8 481,690 1

Append (cost=123,298.55..4,408,993.3 rows=22,092 width=1,928) (actual time=71,071.222..103,959.019 rows=481,690 loops=1)

  • Buffers: shared hit=4,691,149 read=370,319, temp read=75,143 written=75,175
14. 393.827 77,971.349 ↓ 24.5 321,872 1

Result (cost=123,298.55..842,910.75 rows=13,139 width=1,928) (actual time=71,071.219..77,971.349 rows=321,872 loops=1)

  • Buffers: shared hit=518 read=113,593, temp read=8,500 written=8,513
15. 164.744 77,577.522 ↓ 24.5 321,872 1

Append (cost=123,298.55..842,680.82 rows=13,139 width=1,928) (actual time=71,071.213..77,577.522 rows=321,872 loops=1)

  • Buffers: shared hit=518 read=113,593, temp read=8,500 written=8,513
16. 60.142 71,530.952 ↓ 6.2 49,142 1

Subquery Scan on *SELECT* 1 (cost=123,298.55..124,499.69 rows=7,876 width=1,578) (actual time=71,071.212..71,530.952 rows=49,142 loops=1)

  • Buffers: shared hit=2 read=82,949, temp read=2,048 written=2,052
17. 170.595 71,470.810 ↓ 6.2 49,142 1

GroupAggregate (cost=123,298.55..124,322.48 rows=7,876 width=1,510) (actual time=71,071.205..71,470.81 rows=49,142 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
  • Buffers: shared hit=2 read=82,949, temp read=2,048 written=2,052
18. 876.016 71,300.215 ↓ 14.7 115,878 1

Sort (cost=123,298.55..123,318.25 rows=7,877 width=238) (actual time=71,071.169..71,300.215 rows=115,878 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: external merge Disk: 16,384kB
  • Buffers: shared hit=2 read=82,949, temp read=2,048 written=2,052
19. 70,424.199 70,424.199 ↓ 14.7 115,878 1

Seq Scan on tbl_installs tbl_installs (cost=0..122,788.78 rows=7,877 width=238) (actual time=69,987.181..70,424.199 rows=115,878 loops=1)

  • Filter: (date_trunc('quarter'::text, tbl_installs.install_ts) = date_trunc('quarter'::text, (('now'::cstring)::date)::timestamp with time zone))
  • Buffers: shared hit=2 read=82,949
20. 278.586 5,872.779 ↓ 51.8 272,696 1

Subquery Scan on *SELECT* 2 (cost=66,063.95..66,849.7 rows=5,262 width=1,728) (actual time=3,942.461..5,872.779 rows=272,696 loops=1)

  • Buffers: shared read=30,642, temp read=6,452 written=6,461
21. 1,163.491 5,594.193 ↓ 51.8 272,696 1

GroupAggregate (cost=66,063.95..66,783.92 rows=5,262 width=1,724) (actual time=3,942.452..5,594.193 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
  • Buffers: shared read=30,642, temp read=6,452 written=6,461
22. 2,673.297 4,430.702 ↓ 50.7 272,815 1

Sort (cost=66,063.95..66,077.41 rows=5,382 width=213) (actual time=3,942.387..4,430.702 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: external merge Disk: 51,616kB
  • Buffers: shared read=30,642, temp read=6,452 written=6,461
23. 1,757.405 1,757.405 ↓ 50.7 272,815 1

Seq Scan on fct_costs fct_costs (cost=0..65,730.43 rows=5,382 width=213) (actual time=1,042.148..1,757.405 rows=272,815 loops=1)

  • Filter: (date_trunc('quarter'::text, ((fct_costs.cost_date)::date)::timestamp with time zone) = date_trunc('quarter'::text, (('now'::cstring)::date)::timestamp with time zone))
  • Buffers: shared read=30,642
24. 0.064 9.047 ↓ 34.0 34 1

Subquery Scan on *SELECT* 3 (cost=651,265.68..651,265.74 rows=1 width=1,741) (actual time=8.889..9.047 rows=34 loops=1)

  • Buffers: shared hit=516 read=2
25. 0.106 8.983 ↓ 34.0 34 1

GroupAggregate (cost=651,265.68..651,265.72 rows=1 width=1,705) (actual time=8.881..8.983 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
  • Buffers: shared hit=516 read=2
26. 0.042 8.877 ↓ 35.0 35 1

Sort (cost=651,265.68..651,265.69 rows=1 width=73) (actual time=8.865..8.877 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
  • Buffers: shared hit=516 read=2
27. 0.022 8.835 ↓ 35.0 35 1

Subquery Scan on etl_affiliate_costs_v (cost=651,265.63..651,265.67 rows=1 width=73) (actual time=8.783..8.835 rows=35 loops=1)

  • Buffers: shared hit=516 read=2
28. 0.029 8.813 ↓ 35.0 35 1

Unique (cost=651,265.63..651,265.66 rows=1 width=119) (actual time=8.778..8.813 rows=35 loops=1)

  • Buffers: shared hit=516 read=2
29.          

CTE aff_price

30. 0.001 0.017 ↓ 0.0 0 1

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

31. 0.003 0.016 ↓ 0.0 0 1

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

32. 0.009 0.013 ↓ 0.0 0 1

Sort (cost=3,274.54..3,374.54 rows=40,000 width=176) (actual time=0.012..0.013 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
33. 0.001 0.004 ↓ 0.0 0 1

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

34. 0.003 0.003 ↓ 0.0 0 1

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

35. 0.074 8.784 ↓ 35.0 35 1

Sort (cost=625,991.08..625,991.09 rows=1 width=119) (actual time=8.777..8.784 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.af_platform, u.tenant
  • Sort Method: quicksort Memory: 30kB
  • Buffers: shared hit=516 read=2
36. 0.067 8.710 ↓ 35.0 35 1

Nested Loop (cost=699.99..625,991.07 rows=1 width=119) (actual time=8.353..8.71 rows=35 loops=1)

  • Buffers: shared hit=516 read=2
37. 0.035 8.643 ↓ 35.0 35 1

Nested Loop (cost=699.7..625,988.55 rows=1 width=375) (actual time=8.341..8.643 rows=35 loops=1)

  • Buffers: shared hit=516 read=2
38. 0.036 8.608 ↓ 35.0 35 1

Nested Loop (cost=699.7..525,986.05 rows=1 width=311) (actual time=8.337..8.608 rows=35 loops=1)

  • Buffers: shared hit=516 read=2
39. 0.035 8.572 ↓ 35.0 35 1

Nested Loop (cost=699.7..415,982.8 rows=1 width=279) (actual time=8.332..8.572 rows=35 loops=1)

  • Buffers: shared hit=516 read=2
40. 0.039 8.537 ↓ 35.0 35 1

Nested Loop (cost=699.7..305,979.55 rows=1 width=215) (actual time=8.329..8.537 rows=35 loops=1)

  • Buffers: shared hit=516 read=2
41. 0.021 8.498 ↓ 35.0 35 1

Nested Loop (cost=699.7..195,187.51 rows=1 width=151) (actual time=8.323..8.498 rows=35 loops=1)

  • Buffers: shared hit=516 read=2
42. 0.031 8.302 ↓ 7.0 35 1

Hash Join (cost=699.28..195,174.29 rows=5 width=133) (actual time=8.288..8.302 rows=35 loops=1)

  • Buffers: shared hit=378
43. 0.018 0.018 ↓ 0.0 0 1

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

  • Filter: (p.user_id <> 0)
44. 0.064 8.253 ↓ 7.0 35 1

Hash (cost=699.16..699.16 rows=5 width=69) (actual time=8.253..8.253 rows=35 loops=1)

  • Buffers: shared hit=378
45. 8.189 8.189 ↓ 7.0 35 1

Seq Scan on tbl_affiliate_goals_for_export a_1 (cost=0..699.16 rows=5 width=69) (actual time=5.63..8.189 rows=35 loops=1)

  • Filter: (((a_1.goal_name = ANY ('{Qualified_depositor,Qualified_introduced_trader,Qualified_introduced_trader_500}'::text[])) OR (a_1.goal_name ~~* '%Payout%'::text)) AND (date_trunc('quarter'::text, (a_1.event_dt)::timestamp with time zone) = date_trunc('quarter'::text, (('now'::cstring)::date)::timestamp with time zone)))
  • Buffers: shared hit=378
46. 0.175 0.175 ↑ 1.0 1 35

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

  • Index Cond: (u.user_id = a_1.user_id)
  • Filter: (u.aff_id <> 'None'::text)
  • Buffers: shared hit=138 read=2
47. 0.000 0.000 ↓ 0.0 0 35

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

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

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

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

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

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

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

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

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

  • Index Cond: ((a_1.event_dt = ts_fx_daily.date) AND (ts_fx_daily.currency = COALESCE(p.currency, r.currency, k.currency, j.currency)))
52. 194.924 11,181.468 ↓ 17.9 158,046 1

Subquery Scan on *SELECT* 4 (cost=191,313.57..1,435,868.05 rows=8,809 width=1,459) (actual time=3,439.977..11,181.468 rows=158,046 loops=1)

  • Buffers: shared hit=1,551,302 read=48,597, temp read=29,367 written=29,367
53. 470.832 10,986.544 ↓ 17.9 158,046 1

Nested Loop (cost=191,313.57..1,435,691.87 rows=8,809 width=1,395) (actual time=3,439.967..10,986.544 rows=158,046 loops=1)

  • Buffers: shared hit=1,551,302 read=48,597, temp read=29,367 written=29,367
54. 393.509 9,725.482 ↓ 17.9 158,046 1

Hash Join (cost=191,313.15..1,430,588.9 rows=8,809 width=446) (actual time=3,439.916..9,725.482 rows=158,046 loops=1)

  • Buffers: shared hit=923,430 read=48,391, temp read=29,367 written=29,367
55. 653.733 9,015.660 ↓ 17.9 158,046 1

Hash Join (cost=143,607.83..1,379,978.94 rows=8,809 width=415) (actual time=3,123.406..9,015.66 rows=158,046 loops=1)

  • Buffers: shared hit=856,658 read=48,391, temp read=24,483 written=24,483
56. 5,259.766 5,259.766 ↓ 18.1 159,168 1

Index Scan using ix_etl_tbl_all_events_t_type on etl_tbl_all_events e (cost=0.56..1,226,286.53 rows=8,809 width=114) (actual time=1.089..5,259.766 rows=159,168 loops=1)

  • Index Cond: (e.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, e.event_datetime) = date_trunc('quarter'::text, (('now'::cstring)::date)::timestamp with time zone))
  • Buffers: shared hit=26,028 read=48,391
57. 714.869 3,102.161 ↓ 2.2 520,743 1

Hash (cost=131,054.27..131,054.27 rows=234,559 width=305) (actual time=3,102.161..3,102.161 rows=520,743 loops=1)

  • Buffers: shared hit=830,630, temp written=15,368
58. 280.313 2,387.292 ↓ 2.2 520,743 1

Subquery Scan on u_1 (cost=1,354.07..131,054.27 rows=234,559 width=305) (actual time=22.649..2,387.292 rows=520,743 loops=1)

  • Buffers: shared hit=830,630
59. 664.259 2,106.979 ↓ 2.2 520,743 1

Merge Join (cost=1,354.07..128,708.68 rows=234,559 width=3,113) (actual time=22.644..2,106.979 rows=520,743 loops=1)

  • Buffers: shared hit=830,630
60.          

CTE aff_goals

61. 5.138 16.695 ↓ 1.1 4,555 1

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

  • Sort Key: g.user_id
  • Sort Method: quicksort Memory: 415kB
  • Buffers: shared hit=378
62. 7.809 11.557 ↓ 1.1 4,555 1

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

  • Group Key: g.user_id
  • Buffers: shared hit=378
63. 3.748 3.748 ↓ 1.0 7,156 1

Seq Scan on tbl_affiliate_goals_for_export g (cost=0..496.95 rows=7,155 width=28) (actual time=0.017..3.748 rows=7,156 loops=1)

  • Filter: (g.goal_name <> 'INSTALL_EVENT'::text)
  • Buffers: shared hit=378
64. 1,418.714 1,418.714 ↓ 2.2 520,743 1

Index Scan using ix_unq_etl_dim_ums_users on etl_dim_ums_users u_2 (cost=0.42..92,415.68 rows=234,559 width=236) (actual time=0.118..1,418.714 rows=520,743 loops=1)

  • Filter: ((u_2.test_sign IS FALSE) AND (u_2.user_role = ANY ('{USER,PROFESSIONAL}'::text[])) AND (u_2.user_id <> ALL ('{10789518,10788054}'::bigint[])))
  • Buffers: shared hit=830,252
65. 3.601 24.006 ↓ 1.1 4,555 1

Sort (cost=346.12..356.89 rows=4,307 width=8) (actual time=22.475..24.006 rows=4,555 loops=1)

  • Sort Key: aff_goals.user_id
  • Sort Method: quicksort Memory: 406kB
  • Buffers: shared hit=378
66. 20.405 20.405 ↓ 1.1 4,555 1

CTE Scan on aff_goals aff_goals (cost=0..86.14 rows=4,307 width=8) (actual time=15.191..20.405 rows=4,555 loops=1)

  • Buffers: shared hit=378
67. 141.569 316.313 ↓ 1.0 279,503 1

Hash (cost=42,309.8..42,309.8 rows=279,002 width=31) (actual time=316.313..316.313 rows=279,503 loops=1)

  • Buffers: shared hit=66,772, temp written=1,352
68. 174.744 174.744 ↓ 1.0 279,503 1

Index Scan using ix_tbl_installs_1 on tbl_installs tbl_installs_1 (cost=0.43..42,309.8 rows=279,002 width=31) (actual time=0.03..174.744 rows=279,503 loops=1)

  • Index Cond: (tbl_installs_1.app_name = 'Investmate'::text)
  • Buffers: shared hit=66,772
69. 790.230 790.230 ↑ 1.0 2 158,046

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=81) (actual time=0.004..0.005 rows=2 loops=158,046)

  • Index Cond: (u_1.user_id = h.user_id)
  • Buffers: shared hit=627,872 read=206
70. 1.390 11,506.938 ↓ 10.1 1,140 1

Subquery Scan on *SELECT* 5 (cost=1,856,324.44..1,858,811.24 rows=113 width=1,692) (actual time=10,265.976..11,506.938 rows=1,140 loops=1)

  • Buffers: shared hit=2,232,104 read=145,511, temp read=34,883 written=34,902
71. 545.593 11,505.548 ↓ 10.1 1,140 1

GroupAggregate (cost=1,856,324.44..1,858,808.69 rows=113 width=1,628) (actual time=10,265.968..11,505.548 rows=1,140 loops=1)

  • Group Key: (COALESCE(u_3.reg_country, u_3.af_country_code)), u_3.apps_flyer_id, e_1.event_type, u_3.adset, u_3.ad, u_3.af_install_ts, e_1.app_name, u_3.af_platform, u_3.user_id, u_3.aff_id, u_3.offer_id, (CASE WHEN (tbl_installs_2.apps_flyer_id IS NOT NULL) THEN 'InvestmateApp'::text ELSE u_3.media_source END), (CASE WHEN (tbl_installs_2.apps_flyer_id IS NOT NULL) THEN ((u_3.media_source || ' '::text) || u_3.campaign) ELSE u_3.campaign END), u_3.demo_sign, u_3.deposit_bin, u_3.program_id, u_3.user_role, u_3.tenant, u_3.deposit_bin_total
  • Filter: (date_trunc('quarter'::text, min(e_1.event_datetime)) = date_trunc('quarter'::text, (('now'::cstring)::date)::timestamp with time zone))
  • Buffers: shared hit=2,232,104 read=145,511, temp read=34,883 written=34,902
72. 2,653.971 10,959.955 ↓ 13.8 312,576 1

Sort (cost=1,856,324.44..1,856,380.89 rows=22,579 width=426) (actual time=10,199.694..10,959.955 rows=312,576 loops=1)

  • Sort Key: (COALESCE(u_3.reg_country, u_3.af_country_code)), u_3.apps_flyer_id, u_3.adset, u_3.ad, u_3.af_install_ts, e_1.app_name, u_3.af_platform, u_3.user_id, u_3.aff_id, u_3.offer_id, (CASE WHEN (tbl_installs_2.apps_flyer_id IS NOT NULL) THEN 'InvestmateApp'::text ELSE u_3.media_source END), (CASE WHEN (tbl_installs_2.apps_flyer_id IS NOT NULL) THEN ((u_3.media_source || ' '::text) || u_3.campaign) ELSE u_3.campaign END), u_3.demo_sign, u_3.deposit_bin, u_3.program_id, u_3.user_role, u_3.tenant, u_3.deposit_bin_total
  • Sort Method: external merge Disk: 94,088kB
  • Buffers: shared hit=2,232,104 read=145,511, temp read=34,883 written=34,902
73. 425.609 8,305.984 ↓ 13.8 312,576 1

Nested Loop (cost=190,168.57..1,852,114.82 rows=22,579 width=426) (actual time=2,950.429..8,305.984 rows=312,576 loops=1)

  • Buffers: shared hit=2,232,104 read=145,511, temp read=23,122 written=23,122
74. 586.235 6,942.647 ↓ 13.8 312,576 1

Hash Join (cost=190,168.15..1,839,543.04 rows=22,579 width=334) (actual time=2,950.404..6,942.647 rows=312,576 loops=1)

  • Buffers: shared hit=1,004,642 read=145,444, temp read=23,122 written=23,122
75. 624.822 6,076.319 ↓ 13.8 312,576 1

Hash Join (cost=142,462.83..1,787,995.06 rows=22,579 width=303) (actual time=2,663.975..6,076.319 rows=312,576 loops=1)

  • Buffers: shared hit=937,870 read=145,444, temp read=15,040 written=15,040
76. 2,791.089 2,791.089 ↓ 13.9 313,693 1

Index Scan using ix_etl_tbl_all_events_t_type on etl_tbl_all_events e_1 (cost=0.56..1,636,392.34 rows=22,579 width=40) (actual time=0.213..2,791.089 rows=313,693 loops=1)

  • Index Cond: (e_1.event_type = 'Reg step opened'::text)
  • Filter: ((e_1.event_attributes ->> 'step'::text) = '0'::text)
  • Buffers: shared hit=107,240 read=145,444
77. 545.247 2,660.408 ↓ 2.2 520,743 1

Hash (cost=131,054.27..131,054.27 rows=234,559 width=267) (actual time=2,660.408..2,660.408 rows=520,743 loops=1)

  • Buffers: shared hit=830,630, temp written=12,860
78. 254.326 2,115.161 ↓ 2.2 520,743 1

Subquery Scan on u_3 (cost=1,354.07..131,054.27 rows=234,559 width=267) (actual time=14.671..2,115.161 rows=520,743 loops=1)

  • Buffers: shared hit=830,630
79. 600.509 1,860.835 ↓ 2.2 520,743 1

Merge Join (cost=1,354.07..128,708.68 rows=234,559 width=3,183) (actual time=14.668..1,860.835 rows=520,743 loops=1)

  • Buffers: shared hit=830,630
80.          

CTE aff_goals

81. 3.085 10.891 ↓ 1.1 4,555 1

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

  • Sort Key: g_1.user_id
  • Sort Method: quicksort Memory: 415kB
  • Buffers: shared hit=378
82. 4.896 7.806 ↓ 1.1 4,555 1

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

  • Group Key: g_1.user_id
  • Buffers: shared hit=378
83. 2.910 2.910 ↓ 1.0 7,156 1

Seq Scan on tbl_affiliate_goals_for_export g_1 (cost=0..496.95 rows=7,155 width=28) (actual time=0.013..2.91 rows=7,156 loops=1)

  • Filter: (g_1.goal_name <> 'INSTALL_EVENT'::text)
  • Buffers: shared hit=378
84. 1,244.306 1,244.306 ↓ 2.2 520,743 1

Index Scan using ix_unq_etl_dim_ums_users on etl_dim_ums_users u_4 (cost=0.42..92,415.68 rows=234,559 width=198) (actual time=0.08..1,244.306 rows=520,743 loops=1)

  • Filter: ((u_4.test_sign IS FALSE) AND (u_4.user_role = ANY ('{USER,PROFESSIONAL}'::text[])) AND (u_4.user_id <> ALL ('{10789518,10788054}'::bigint[])))
  • Buffers: shared hit=830,252
85. 2.758 16.020 ↓ 1.1 4,555 1

Sort (cost=346.12..356.89 rows=4,307 width=8) (actual time=14.563..16.02 rows=4,555 loops=1)

  • Sort Key: aff_goals_1.user_id
  • Sort Method: quicksort Memory: 406kB
  • Buffers: shared hit=378
86. 13.262 13.262 ↓ 1.1 4,555 1

CTE Scan on aff_goals aff_goals_1 (cost=0..86.14 rows=4,307 width=8) (actual time=9.909..13.262 rows=4,555 loops=1)

  • Buffers: shared hit=378
87. 123.423 280.093 ↓ 1.0 279,503 1

Hash (cost=42,309.8..42,309.8 rows=279,002 width=31) (actual time=280.092..280.093 rows=279,503 loops=1)

  • Buffers: shared hit=66,772, temp written=1,352
88. 156.670 156.670 ↓ 1.0 279,503 1

Index Scan using ix_tbl_installs_1 on tbl_installs tbl_installs_2 (cost=0.43..42,309.8 rows=279,002 width=31) (actual time=0.018..156.67 rows=279,503 loops=1)

  • Index Cond: (tbl_installs_2.app_name = 'Investmate'::text)
  • Buffers: shared hit=66,772
89. 937.728 937.728 ↑ 2.0 1 312,576

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=90) (actual time=0.003..0.003 rows=1 loops=312,576)

  • Index Cond: (u_3.user_id = h_1.user_id)
  • Buffers: shared hit=1,227,462 read=67
90. 0.850 3,046.363 ↓ 20.4 632 1

Subquery Scan on *SELECT* 6 (cost=226,175.13..271,292.8 rows=31 width=1,516) (actual time=2,813.133..3,046.363 rows=632 loops=1)

  • Buffers: shared hit=907,225 read=62,618, temp read=2,393 written=2,393
91. 2.238 3,045.513 ↓ 20.4 632 1

Nested Loop (cost=226,175.13..271,292.1 rows=31 width=1,452) (actual time=2,813.125..3,045.513 rows=632 loops=1)

  • Buffers: shared hit=907,225 read=62,618, temp read=2,393 written=2,393
92. 93.142 3,040.115 ↓ 20.4 632 1

Hash Join (cost=226,174.7..271,274.14 rows=31 width=429) (actual time=2,813.098..3,040.115 rows=632 loops=1)

  • Buffers: shared hit=904,715 read=62,608, temp read=2,393 written=2,393
93. 144.950 144.950 ↓ 1.0 279,503 1

Index Scan using ix_tbl_installs_1 on tbl_installs tbl_installs_3 (cost=0.43..42,309.8 rows=279,002 width=31) (actual time=0.047..144.95 rows=279,503 loops=1)

  • Index Cond: (tbl_installs_3.app_name = 'Investmate'::text)
  • Buffers: shared hit=66,772
94. 1.215 2,802.023 ↓ 20.4 632 1

Hash (cost=226,173.89..226,173.89 rows=31 width=398) (actual time=2,802.022..2,802.023 rows=632 loops=1)

  • Buffers: shared hit=837,943 read=62,608, temp read=2,393 written=2,393
95. 145.664 2,800.808 ↓ 20.4 632 1

Hash Join (cost=95,593.78..226,173.89 rows=31 width=398) (actual time=884.014..2,800.808 rows=632 loops=1)

  • Buffers: shared hit=837,943 read=62,608, temp read=2,393 written=2,393
96. 547.444 1,791.729 ↓ 2.2 520,743 1

Merge Join (cost=1,354.07..128,708.68 rows=234,559 width=3,134) (actual time=15.416..1,791.729 rows=520,743 loops=1)

  • Buffers: shared hit=830,630
97.          

CTE aff_goals

98. 3.176 11.618 ↓ 1.1 4,555 1

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

  • Sort Key: g_2.user_id
  • Sort Method: quicksort Memory: 415kB
  • Buffers: shared hit=378
99. 5.157 8.442 ↓ 1.1 4,555 1

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

  • Group Key: g_2.user_id
  • Buffers: shared hit=378
100. 3.285 3.285 ↓ 1.0 7,156 1

Seq Scan on tbl_affiliate_goals_for_export g_2 (cost=0..496.95 rows=7,155 width=28) (actual time=0.014..3.285 rows=7,156 loops=1)

  • Filter: (g_2.goal_name <> 'INSTALL_EVENT'::text)
  • Buffers: shared hit=378
101. 1,227.485 1,227.485 ↓ 2.2 520,743 1

Index Scan using ix_unq_etl_dim_ums_users on etl_dim_ums_users u_5 (cost=0.42..92,415.68 rows=234,559 width=225) (actual time=0.094..1,227.485 rows=520,743 loops=1)

  • Filter: ((u_5.test_sign IS FALSE) AND (u_5.user_role = ANY ('{USER,PROFESSIONAL}'::text[])) AND (u_5.user_id <> ALL ('{10789518,10788054}'::bigint[])))
  • Buffers: shared hit=830,252
102. 2.815 16.800 ↓ 1.1 4,555 1

Sort (cost=346.12..356.89 rows=4,307 width=8) (actual time=15.296..16.8 rows=4,555 loops=1)

  • Sort Key: aff_goals_2.user_id
  • Sort Method: quicksort Memory: 406kB
  • Buffers: shared hit=378
103. 13.985 13.985 ↓ 1.1 4,555 1

CTE Scan on aff_goals aff_goals_2 (cost=0..86.14 rows=4,307 width=8) (actual time=10.622..13.985 rows=4,555 loops=1)

  • Buffers: shared hit=378
104. 0.295 863.415 ↓ 20.6 640 1

Hash (cost=94,239.32..94,239.32 rows=31 width=112) (actual time=863.415..863.415 rows=640 loops=1)

  • Buffers: shared hit=7,313 read=62,608, temp read=2,393 written=2,393
105. 0.291 863.120 ↓ 20.6 640 1

Subquery Scan on e_2 (cost=94,018.82..94,239.32 rows=31 width=112) (actual time=788.895..863.12 rows=640 loops=1)

  • Buffers: shared hit=7,313 read=62,608, temp read=2,393 written=2,393
106. 862.829 862.829 ↓ 20.6 640 1

CTE Scan on s s (cost=94,018.82..94,239.01 rows=31 width=232) (actual time=788.887..862.829 rows=640 loops=1)

  • Filter: (date_trunc('quarter'::text, s.event_datetime) = date_trunc('quarter'::text, (('now'::cstring)::date)::timestamp with time zone))
  • Buffers: shared hit=7,313 read=62,608, temp read=2,393 written=2,393
107.          

CTE client_events

108. 0.000 0.000 ↓ 0.0 0 0

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

109.          

CTE server_events

110. 75.087 75.087 ↓ 1.0 6,592 1

Seq Scan on ums_user_status_audit_log t1 (cost=0..20,871.08 rows=6,291 width=248) (actual time=0.036..75.087 rows=6,592 loops=1)

  • Filter: (t1.status = 'SUSPENDED'::text)
  • Buffers: shared hit=7,313
111.          

CTE s

112. 67.215 852.499 ↓ 1.0 6,592 1

Hash Join (cost=71,429.59..73,147.75 rows=6,291 width=224) (actual time=703.394..852.499 rows=6,592 loops=1)

  • Buffers: shared hit=7,313 read=62,608, temp read=2,393 written=2,393
113. 82.891 82.891 ↓ 1.0 6,592 1

CTE Scan on server_events server_events (cost=0..125.82 rows=6,291 width=216) (actual time=0.041..82.891 rows=6,592 loops=1)

  • Buffers: shared hit=7,313
114. 191.695 702.393 ↓ 2.2 523,510 1

Hash (cost=67,303.6..67,303.6 rows=237,359 width=16) (actual time=702.393..702.393 rows=523,510 loops=1)

  • Buffers: shared read=62,608, temp written=1,725
115. 510.698 510.698 ↓ 2.2 523,510 1

Seq Scan on etl_tbl_ums_users a (cost=0..67,303.6 rows=237,359 width=16) (actual time=0.017..510.698 rows=523,510 loops=1)

  • Filter: (a.user_role = ANY ('{USER,PROFESSIONAL}'::text[]))
  • Buffers: shared read=62,608
116. 3.160 3.160 ↑ 2.0 1 632

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=90) (actual time=0.005..0.005 rows=1 loops=632)

  • Index Cond: (u_5.user_id = h_2.user_id)
  • Buffers: shared hit=2,510 read=10
117. 739.363 53,858.223 ↓ 17.9 245,594 1

Subquery Scan on *SELECT* 7 (cost=651,635.7..658,065.69 rows=13,754 width=1,439) (actual time=39,890.046..53,858.223 rows=245,594 loops=1)

  • Buffers: shared hit=10,436,439 read=13 dirtied=6, temp read=128,135 written=128,213
118. 11,276.182 53,118.860 ↓ 17.9 245,594 1

GroupAggregate (cost=651,635.7..657,721.84 rows=13,754 width=1,399) (actual time=39,890.036..53,118.86 rows=245,594 loops=1)

  • Group Key: (COALESCE(u_6.reg_country, u_6.af_country_code)), u_6.apps_flyer_id, u_6.adset, u_6.ad, fct_transactions.transaction_type, (date(fct_transactions.ts_timestamp)), u_6.af_install_ts, fct_transactions.transaction_status, u_6.af_platform, fct_transactions.currency, u_6.user_id, u_6.aff_id, u_6.offer_id, (CASE WHEN (tbl_installs_4.apps_flyer_id IS NOT NULL) THEN 'InvestmateApp'::text ELSE u_6.media_source END), (CASE WHEN (tbl_installs_4.apps_flyer_id IS NOT NULL) THEN ((u_6.media_source || ' '::text) || u_6.campaign) ELSE u_6.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_6.first_excom_ts))), u_6.demo_sign, u_6.deposit_bin, u_6.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_6.keywords, u_6.ad_conceptname, u_6.ad_creativename, u_6.ad_creativetype, u_6.user_role, u_6.tenant, u_6.deposit_bin_total, ((row_to_json((SubPlan 8)))::jsonb)
  • Buffers: shared hit=10,436,439 read=13 dirtied=6, temp read=128,135 written=128,213
119. 17,063.733 41,842.678 ↓ 83.0 1,142,095 1

Sort (cost=651,635.7..651,670.08 rows=13,754 width=491) (actual time=39,889.922..41,842.678 rows=1,142,095 loops=1)

  • Sort Key: (COALESCE(u_6.reg_country, u_6.af_country_code)), u_6.apps_flyer_id, u_6.adset, u_6.ad, fct_transactions.transaction_type, (date(fct_transactions.ts_timestamp)), u_6.af_install_ts, fct_transactions.transaction_status, u_6.af_platform, fct_transactions.currency, u_6.user_id, u_6.aff_id, u_6.offer_id, (CASE WHEN (tbl_installs_4.apps_flyer_id IS NOT NULL) THEN 'InvestmateApp'::text ELSE u_6.media_source END), (CASE WHEN (tbl_installs_4.apps_flyer_id IS NOT NULL) THEN ((u_6.media_source || ' '::text) || u_6.campaign) ELSE u_6.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_6.first_excom_ts))), u_6.demo_sign, u_6.deposit_bin, u_6.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_6.keywords, u_6.ad_conceptname, u_6.ad_creativename, u_6.ad_creativetype, u_6.user_role, u_6.tenant, u_6.deposit_bin_total, ((row_to_json((SubPlan 8)))::jsonb)
  • Sort Method: external merge Disk: 374,592kB
  • Buffers: shared hit=10,436,439 read=13 dirtied=6, temp read=128,135 written=128,213
120. 4,812.475 24,778.945 ↓ 83.0 1,142,095 1

Nested Loop (cost=190,855.44..650,690.28 rows=13,754 width=491) (actual time=3,691.732..24,778.945 rows=1,142,095 loops=1)

  • Buffers: shared hit=10,436,439 read=13 dirtied=6, temp read=52,126 written=52,126
121. 1,507.679 15,398.090 ↓ 83.0 1,142,095 1

Hash Join (cost=190,855.01..642,482.02 rows=13,754 width=381) (actual time=3,691.686..15,398.09 rows=1,142,095 loops=1)

  • Buffers: shared hit=5,761,395 read=13 dirtied=6, temp read=52,126 written=52,126
122. 1,418.505 13,614.694 ↓ 83.0 1,142,095 1

Hash Join (cost=143,149.69..591,528.76 rows=13,754 width=350) (actual time=3,262.959..13,614.694 rows=1,142,095 loops=1)

  • Buffers: shared hit=5,694,623 read=13 dirtied=6, temp read=25,400 written=25,400
123. 684.996 8,964.112 ↓ 83.3 1,146,081 1

Nested Loop (cost=0.43..438,703.38 rows=13,754 width=67) (actual time=2.377..8,964.112 rows=1,146,081 loops=1)

  • Buffers: shared hit=4,863,993 read=13 dirtied=6
124. 5,986.954 5,986.954 ↓ 83.3 1,146,081 1

Seq Scan on etl_fct_transactions fct_transactions (cost=0..408,569.85 rows=13,754 width=88) (actual time=2.36..5,986.954 rows=1,146,081 loops=1)

  • Filter: ((fct_transactions.transaction_type <> 'DEMO_TRANSFER'::text) AND (date_trunc('quarter'::text, fct_transactions.ts_timestamp) = date_trunc('quarter'::text, (('now'::cstring)::date)::timestamp with time zone)) AND ((fct_transactions.transaction_type = ANY ('{TRANSFER,DEPOSIT,WITHDRAWAL,TRADE,REIMBURSEMENT,SWAP,EXCHANGE,INACTIVITY_FEE,EXCHANGE_COMMISSION,REFERRER_TRADE_COMMISSION,TRADE_COMMISSION}'::text[])) OR ((fct_transactions.transaction_type = 'ADJUSTMENT'::text) AND (fct_transactions.details_init_reason = 'BONUS'::text))))
  • Buffers: shared hit=279,364
125. 2,292.162 2,292.162 ↑ 1.0 1 1,146,081

Index Scan using as_account_pkey on as_account a_2 (cost=0.43..2.19 rows=1 width=23) (actual time=0.002..0.002 rows=1 loops=1,146,081)

  • Index Cond: (a_2.id = fct_transactions.user_account_id)
  • Buffers: shared hit=4,584,629 read=13 dirtied=6
126. 700.899 3,232.077 ↓ 2.2 520,743 1

Hash (cost=131,054.27..131,054.27 rows=234,559 width=291) (actual time=3,232.077..3,232.077 rows=520,743 loops=1)

  • Buffers: shared hit=830,630, temp written=13,493
127. 312.941 2,531.178 ↓ 2.2 520,743 1

Subquery Scan on u_6 (cost=1,354.07..131,054.27 rows=234,559 width=291) (actual time=15.083..2,531.178 rows=520,743 loops=1)

  • Buffers: shared hit=830,630
128. 750.947 2,218.237 ↓ 2.2 520,743 1

Merge Join (cost=1,354.07..128,708.68 rows=234,559 width=3,183) (actual time=15.08..2,218.237 rows=520,743 loops=1)

  • Buffers: shared hit=830,630
129.          

CTE aff_goals

130. 3.230 11.341 ↓ 1.1 4,555 1

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

  • Sort Key: g_3.user_id
  • Sort Method: quicksort Memory: 415kB
  • Buffers: shared hit=378
131. 5.065 8.111 ↓ 1.1 4,555 1

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

  • Group Key: g_3.user_id
  • Buffers: shared hit=378
132. 3.046 3.046 ↓ 1.0 7,156 1

Seq Scan on tbl_affiliate_goals_for_export g_3 (cost=0..496.95 rows=7,155 width=28) (actual time=0.013..3.046 rows=7,156 loops=1)

  • Filter: (g_3.goal_name <> 'INSTALL_EVENT'::text)
  • Buffers: shared hit=378
133. 1,450.633 1,450.633 ↓ 2.2 520,743 1

Index Scan using ix_unq_etl_dim_ums_users on etl_dim_ums_users u_7 (cost=0.42..92,415.68 rows=234,559 width=222) (actual time=0.081..1,450.633 rows=520,743 loops=1)

  • Filter: ((u_7.test_sign IS FALSE) AND (u_7.user_role = ANY ('{USER,PROFESSIONAL}'::text[])) AND (u_7.user_id <> ALL ('{10789518,10788054}'::bigint[])))
  • Buffers: shared hit=830,252
134. 2.980 16.657 ↓ 1.1 4,555 1

Sort (cost=346.12..356.89 rows=4,307 width=8) (actual time=14.975..16.657 rows=4,555 loops=1)

  • Sort Key: aff_goals_3.user_id
  • Sort Method: quicksort Memory: 406kB
  • Buffers: shared hit=378
135. 13.677 13.677 ↓ 1.1 4,555 1

CTE Scan on aff_goals aff_goals_3 (cost=0..86.14 rows=4,307 width=8) (actual time=10.354..13.677 rows=4,555 loops=1)

  • Buffers: shared hit=378
136. 120.273 275.717 ↓ 1.0 279,503 1

Hash (cost=42,309.8..42,309.8 rows=279,002 width=31) (actual time=275.717..275.717 rows=279,503 loops=1)

  • Buffers: shared hit=66,772, temp written=1,352
137. 155.444 155.444 ↓ 1.0 279,503 1

Index Scan using ix_tbl_installs_1 on tbl_installs tbl_installs_4 (cost=0.43..42,309.8 rows=279,002 width=31) (actual time=0.016..155.444 rows=279,503 loops=1)

  • Index Cond: (tbl_installs_4.app_name = 'Investmate'::text)
  • Buffers: shared hit=66,772
138. 3,426.285 3,426.285 ↓ 2.0 4 1,142,095

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=90) (actual time=0.002..0.003 rows=4 loops=1,142,095)

  • Index Cond: (u_6.user_id = h_3.user_id)
  • Buffers: shared hit=4,675,044
139.          

SubPlan (for Nested Loop)

140. 1,142.095 1,142.095 ↑ 1.0 1 1,142,095

Result (cost=0..0.02 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=1,142,095)

141. 330.918 32,949.890 ↓ 12.5 163,246 1

Subquery Scan on *SELECT* 8 (cost=584,140.89..586,234.81 rows=13,087 width=1,517) (actual time=29,721.804..32,949.89 rows=163,246 loops=1)

  • Buffers: shared hit=5,290,678, temp read=97,549 written=97,615
142. 1,547.186 32,618.972 ↓ 12.5 163,246 1

GroupAggregate (cost=584,140.89..585,874.92 rows=13,087 width=1,481) (actual time=29,721.793..32,618.972 rows=163,246 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, (date(fct_transactions_1.ts_timestamp)), u_8.af_install_ts, u_8.af_platform, u_8.user_id, u_8.aff_id, u_8.offer_id, fct_transactions_1.user_id, (CASE WHEN (tbl_installs_5.apps_flyer_id IS NOT NULL) THEN 'InvestmateApp'::text ELSE u_8.media_source END), (CASE WHEN (tbl_installs_5.apps_flyer_id IS NOT NULL) THEN ((u_8.media_source || ' '::text) || u_8.campaign) ELSE u_8.campaign END), u_8.demo_sign, u_8.deposit_bin, u_8.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_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
  • Buffers: shared hit=5,290,678, temp read=97,549 written=97,615
143. 12,999.885 31,071.786 ↓ 76.8 1,004,923 1

Sort (cost=584,140.89..584,173.61 rows=13,087 width=435) (actual time=29,721.744..31,071.786 rows=1,004,923 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, (date(fct_transactions_1.ts_timestamp)), u_8.af_install_ts, u_8.af_platform, u_8.user_id, u_8.aff_id, u_8.offer_id, (CASE WHEN (tbl_installs_5.apps_flyer_id IS NOT NULL) THEN 'InvestmateApp'::text ELSE u_8.media_source END), (CASE WHEN (tbl_installs_5.apps_flyer_id IS NOT NULL) THEN ((u_8.media_source || ' '::text) || u_8.campaign) ELSE u_8.campaign END), u_8.demo_sign, u_8.deposit_bin, u_8.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_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
  • Sort Method: external merge Disk: 286,416kB
  • Buffers: shared hit=5,290,678, temp read=97,549 written=97,615
144. 2,421.879 18,071.901 ↓ 76.8 1,004,923 1

Nested Loop (cost=190,168.01..583,246.01 rows=13,087 width=435) (actual time=3,224.31..18,071.901 rows=1,004,923 loops=1)

  • Buffers: shared hit=5,290,678, temp read=41,319 written=41,319
145. 1,411.759 12,635.253 ↓ 76.8 1,004,923 1

Hash Join (cost=190,167.58..575,861.14 rows=13,087 width=343) (actual time=3,224.28..12,635.253 rows=1,004,923 loops=1)

  • Buffers: shared hit=1,176,766, temp read=41,319 written=41,319
146. 2,744.815 10,941.523 ↓ 76.8 1,004,923 1

Hash Join (cost=142,462.26..525,101.56 rows=13,087 width=312) (actual time=2,825.612..10,941.523 rows=1,004,923 loops=1)

  • Buffers: shared hit=1,109,994, temp read=20,735 written=20,735
147. 5,398.889 5,398.889 ↓ 77.0 1,007,544 1

Seq Scan on etl_fct_transactions fct_transactions_1 (cost=0..373,649.35 rows=13,087 width=73) (actual time=2.414..5,398.889 rows=1,007,544 loops=1)

  • Filter: ((fct_transactions_1.transaction_type <> 'DEMO_TRANSFER'::text) AND (fct_transactions_1.transaction_type = ANY ('{DEPOSIT,WITHDRAWAL,TRADE,REIMBURSEMENT,SWAP}'::text[])) AND (date_trunc('quarter'::text, fct_transactions_1.ts_timestamp) = date_trunc('quarter'::text, (('now'::cstring)::date)::timestamp with time zone)))
  • Buffers: shared hit=279,364
148. 595.609 2,797.819 ↓ 2.2 520,743 1

Hash (cost=131,054.27..131,054.27 rows=234,559 width=267) (actual time=2,797.819..2,797.819 rows=520,743 loops=1)

  • Buffers: shared hit=830,630, temp written=12,861
149. 261.403 2,202.210 ↓ 2.2 520,743 1

Subquery Scan on u_8 (cost=1,354.07..131,054.27 rows=234,559 width=267) (actual time=15.329..2,202.21 rows=520,743 loops=1)

  • Buffers: shared hit=830,630
150. 611.079 1,940.807 ↓ 2.2 520,743 1

Merge Join (cost=1,354.07..128,708.68 rows=234,559 width=3,183) (actual time=15.326..1,940.807 rows=520,743 loops=1)

  • Buffers: shared hit=830,630
151.          

CTE aff_goals

152. 3.193 11.425 ↓ 1.1 4,555 1

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

  • Sort Key: g_4.user_id
  • Sort Method: quicksort Memory: 415kB
  • Buffers: shared hit=378
153. 5.116 8.232 ↓ 1.1 4,555 1

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

  • Group Key: g_4.user_id
  • Buffers: shared hit=378
154. 3.116 3.116 ↓ 1.0 7,156 1

Seq Scan on tbl_affiliate_goals_for_export g_4 (cost=0..496.95 rows=7,155 width=28) (actual time=0.015..3.116 rows=7,156 loops=1)

  • Filter: (g_4.goal_name <> 'INSTALL_EVENT'::text)
  • Buffers: shared hit=378
155. 1,313.082 1,313.082 ↓ 2.2 520,743 1

Index Scan using ix_unq_etl_dim_ums_users on etl_dim_ums_users u_9 (cost=0.42..92,415.68 rows=234,559 width=198) (actual time=0.088..1,313.082 rows=520,743 loops=1)

  • Filter: ((u_9.test_sign IS FALSE) AND (u_9.user_role = ANY ('{USER,PROFESSIONAL}'::text[])) AND (u_9.user_id <> ALL ('{10789518,10788054}'::bigint[])))
  • Buffers: shared hit=830,252
156. 2.791 16.646 ↓ 1.1 4,555 1

Sort (cost=346.12..356.89 rows=4,307 width=8) (actual time=15.213..16.646 rows=4,555 loops=1)

  • Sort Key: aff_goals_4.user_id
  • Sort Method: quicksort Memory: 406kB
  • Buffers: shared hit=378
157. 13.855 13.855 ↓ 1.1 4,555 1

CTE Scan on aff_goals aff_goals_4 (cost=0..86.14 rows=4,307 width=8) (actual time=10.407..13.855 rows=4,555 loops=1)

  • Buffers: shared hit=378
158. 125.201 281.971 ↓ 1.0 279,503 1

Hash (cost=42,309.8..42,309.8 rows=279,002 width=31) (actual time=281.971..281.971 rows=279,503 loops=1)

  • Buffers: shared hit=66,772, temp written=1,352
159. 156.770 156.770 ↓ 1.0 279,503 1

Index Scan using ix_tbl_installs_1 on tbl_installs tbl_installs_5 (cost=0.43..42,309.8 rows=279,002 width=31) (actual time=0.021..156.77 rows=279,503 loops=1)

  • Index Cond: (tbl_installs_5.app_name = 'Investmate'::text)
  • Buffers: shared hit=66,772
160. 3,014.769 3,014.769 ↓ 2.0 4 1,004,923

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=90) (actual time=0.002..0.003 rows=4 loops=1,004,923)

  • Index Cond: (u_8.user_id = h_4.user_id)
  • Buffers: shared hit=4,113,912
161. 3.937 7,109.794 ↑ 22.4 2,234 1

Subquery Scan on *SELECT* 9 (cost=621,563.32..628,818.54 rows=50,036 width=1,517) (actual time=6,921.388..7,109.794 rows=2,234 loops=1)

  • Buffers: shared hit=1,538,569, temp read=19,491 written=19,495
162. 80.855 7,105.857 ↑ 22.4 2,234 1

GroupAggregate (cost=621,563.32..627,442.55 rows=50,036 width=1,481) (actual time=6,921.378..7,105.857 rows=2,234 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, u_10.af_install_ts, u_10.af_platform, u_10.user_id, u_10.aff_id, u_10.offer_id, fct_transactions_2.user_id, (CASE WHEN (tbl_installs_6.apps_flyer_id IS NOT NULL) THEN 'InvestmateApp'::text ELSE u_10.media_source END), (CASE WHEN (tbl_installs_6.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_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_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
  • Buffers: shared hit=1,538,569, temp read=19,491 written=19,495
163. 752.740 7,025.002 ↓ 1.7 87,474 1

Sort (cost=621,563.32..621,688.41 rows=50,036 width=424) (actual time=6,921.31..7,025.002 rows=87,474 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, 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_6.apps_flyer_id IS NOT NULL) THEN 'InvestmateApp'::text ELSE u_10.media_source END), (CASE WHEN (tbl_installs_6.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_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_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: external merge Disk: 23,384kB
  • Buffers: shared hit=1,538,569, temp read=19,491 written=19,495
164. 208.520 6,272.262 ↓ 1.7 87,474 1

Nested Loop (cost=190,168.01..612,046.99 rows=50,036 width=424) (actual time=3,416.388..6,272.262 rows=87,474 loops=1)

  • Buffers: shared hit=1,538,569, temp read=16,568 written=16,568
165. 125.974 5,801.320 ↓ 1.7 87,474 1

Hash Join (cost=190,167.58..583,937.22 rows=50,036 width=336) (actual time=3,416.357..5,801.32 rows=87,474 loops=1)

  • Buffers: shared hit=1,176,766, temp read=16,568 written=16,568
166. 205.729 5,396.293 ↓ 1.7 87,474 1

Hash Join (cost=142,462.26..529,941.66 rows=50,036 width=305) (actual time=3,129.247..5,396.293 rows=87,474 loops=1)

  • Buffers: shared hit=1,109,994, temp read=13,627 written=13,627
167. 2,109.588 2,109.588 ↓ 1.8 87,786 1

Seq Scan on etl_fct_transactions fct_transactions_2 (cost=0..377,141.4 rows=50,036 width=66) (actual time=1.414..2,109.588 rows=87,786 loops=1)

  • Filter: ((fct_transactions_2.transaction_type <> 'DEMO_TRANSFER'::text) AND (fct_transactions_2.transaction_type = 'TRADE'::text) AND (fct_transactions_2.transaction_status = 'PROCESSED'::text) AND (fct_transactions_2.ts_timestamp < ('now'::cstring)::date) AND (fct_transactions_2.ts_timestamp >= (('now'::cstring)::date - 31)))
  • Buffers: shared hit=279,364
168. 683.965 3,080.976 ↓ 2.2 520,743 1

Hash (cost=131,054.27..131,054.27 rows=234,559 width=267) (actual time=3,080.976..3,080.976 rows=520,743 loops=1)

  • Buffers: shared hit=830,630, temp written=12,861
169. 297.558 2,397.011 ↓ 2.2 520,743 1

Subquery Scan on u_10 (cost=1,354.07..131,054.27 rows=234,559 width=267) (actual time=15.92..2,397.011 rows=520,743 loops=1)

  • Buffers: shared hit=830,630
170. 702.924 2,099.453 ↓ 2.2 520,743 1

Merge Join (cost=1,354.07..128,708.68 rows=234,559 width=3,183) (actual time=15.918..2,099.453 rows=520,743 loops=1)

  • Buffers: shared hit=830,630
171.          

CTE aff_goals

172. 3.341 11.933 ↓ 1.1 4,555 1

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

  • Sort Key: g_5.user_id
  • Sort Method: quicksort Memory: 415kB
  • Buffers: shared hit=378
173. 5.384 8.592 ↓ 1.1 4,555 1

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

  • Group Key: g_5.user_id
  • Buffers: shared hit=378
174. 3.208 3.208 ↓ 1.0 7,156 1

Seq Scan on tbl_affiliate_goals_for_export g_5 (cost=0..496.95 rows=7,155 width=28) (actual time=0.013..3.208 rows=7,156 loops=1)

  • Filter: (g_5.goal_name <> 'INSTALL_EVENT'::text)
  • Buffers: shared hit=378
175. 1,379.136 1,379.136 ↓ 2.2 520,743 1

Index Scan using ix_unq_etl_dim_ums_users on etl_dim_ums_users u_11 (cost=0.42..92,415.68 rows=234,559 width=198) (actual time=0.073..1,379.136 rows=520,743 loops=1)

  • Filter: ((NOT u_11.test_sign) AND (u_11.user_role = ANY ('{USER,PROFESSIONAL}'::text[])) AND (u_11.user_id <> ALL ('{10789518,10788054}'::bigint[])))
  • Buffers: shared hit=830,252
176. 2.983 17.393 ↓ 1.1 4,555 1

Sort (cost=346.12..356.89 rows=4,307 width=8) (actual time=15.82..17.393 rows=4,555 loops=1)

  • Sort Key: aff_goals_5.user_id
  • Sort Method: quicksort Memory: 406kB
  • Buffers: shared hit=378
177. 14.410 14.410 ↓ 1.1 4,555 1

CTE Scan on aff_goals aff_goals_5 (cost=0..86.14 rows=4,307 width=8) (actual time=10.884..14.41 rows=4,555 loops=1)

  • Buffers: shared hit=378
178. 124.226 279.053 ↓ 1.0 279,503 1

Hash (cost=42,309.8..42,309.8 rows=279,002 width=31) (actual time=279.053..279.053 rows=279,503 loops=1)

  • Buffers: shared hit=66,772, temp written=1,352
179. 154.827 154.827 ↓ 1.0 279,503 1

Index Scan using ix_tbl_installs_1 on tbl_installs tbl_installs_6 (cost=0.43..42,309.8 rows=279,002 width=31) (actual time=0.02..154.827 rows=279,503 loops=1)

  • Index Cond: (tbl_installs_6.app_name = 'Investmate'::text)
  • Buffers: shared hit=66,772
180. 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=90) (actual time=0.002..0.003 rows=4 loops=87,474)

  • Index Cond: (u_10.user_id = h_5.user_id)
  • Buffers: shared hit=361,803
181. 11.529 11,189.541 ↓ 1.1 3,915 1

Subquery Scan on *SELECT* 10 (cost=573,468.29..574,011.54 rows=3,683 width=1,517) (actual time=10,339.379..11,189.541 rows=3,915 loops=1)

  • Buffers: shared hit=2,242,818, temp read=29,619 written=29,635
182. 360.045 11,178.012 ↓ 1.1 3,915 1

GroupAggregate (cost=573,468.29..573,910.25 rows=3,683 width=1,489) (actual time=10,339.367..11,178.012 rows=3,915 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, (date_trunc('quarter'::text, fct_transactions_3.ts_timestamp)), u_12.af_install_ts, u_12.af_platform, u_12.user_id, u_12.aff_id, u_12.offer_id, fct_transactions_3.user_id, (CASE WHEN (tbl_installs_7.apps_flyer_id IS NOT NULL) THEN 'InvestmateApp'::text ELSE u_12.media_source END), (CASE WHEN (tbl_installs_7.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_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_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
  • Buffers: shared hit=2,242,818, temp read=29,619 written=29,635
183. 2,566.500 10,817.967 ↓ 69.7 256,777 1

Sort (cost=573,468.29..573,477.5 rows=3,683 width=440) (actual time=10,339.207..10,817.967 rows=256,777 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_7.apps_flyer_id IS NOT NULL) THEN 'InvestmateApp'::text ELSE u_12.media_source END), (CASE WHEN (tbl_installs_7.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_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_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: external merge Disk: 72,064kB
  • Buffers: shared hit=2,242,818, temp read=29,619 written=29,635
184. 771.734 8,251.467 ↓ 69.7 256,777 1

Nested Loop (cost=190,168.01..573,250.14 rows=3,683 width=440) (actual time=3,035.046..8,251.467 rows=256,777 loops=1)

  • Buffers: shared hit=2,242,818, temp read=20,611 written=20,611
185. 340.831 6,709.402 ↓ 69.7 256,777 1

Hash Join (cost=190,167.58..571,171.86 rows=3,683 width=336) (actual time=3,035.017..6,709.402 rows=256,777 loops=1)

  • Buffers: shared hit=1,176,766, temp read=20,611 written=20,611
186. 1,180.229 6,094.638 ↓ 69.7 256,777 1

Hash Join (cost=142,462.26..521,234.2 rows=3,683 width=305) (actual time=2,706.098..6,094.638 rows=256,777 loops=1)

  • Buffers: shared hit=1,109,994, temp read=14,852 written=14,852
187. 2,224.654 2,224.654 ↓ 70.0 257,752 1

Seq Scan on etl_fct_transactions fct_transactions_3 (cost=0..370,157.3 rows=3,683 width=66) (actual time=1.004..2,224.654 rows=257,752 loops=1)

  • Filter: ((fct_transactions_3.transaction_type <> 'DEMO_TRANSFER'::text) AND (fct_transactions_3.transaction_type = 'TRADE'::text) AND (fct_transactions_3.transaction_status = 'PROCESSED'::text) AND (date_trunc('quarter'::text, fct_transactions_3.ts_timestamp) = date_trunc('quarter'::text, (('now'::cstring)::date)::timestamp with time zone)))
  • Buffers: shared hit=279,364
188. 566.421 2,689.755 ↓ 2.2 520,743 1

Hash (cost=131,054.27..131,054.27 rows=234,559 width=267) (actual time=2,689.755..2,689.755 rows=520,743 loops=1)

  • Buffers: shared hit=830,630, temp written=12,861
189. 256.998 2,123.334 ↓ 2.2 520,743 1

Subquery Scan on u_12 (cost=1,354.07..131,054.27 rows=234,559 width=267) (actual time=14.651..2,123.334 rows=520,743 loops=1)

  • Buffers: shared hit=830,630
190. 589.370 1,866.336 ↓ 2.2 520,743 1

Merge Join (cost=1,354.07..128,708.68 rows=234,559 width=3,183) (actual time=14.648..1,866.336 rows=520,743 loops=1)

  • Buffers: shared hit=830,630
191.          

CTE aff_goals

192. 3.124 10.933 ↓ 1.1 4,555 1

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

  • Sort Key: g_6.user_id
  • Sort Method: quicksort Memory: 415kB
  • Buffers: shared hit=378
193. 4.910 7.809 ↓ 1.1 4,555 1

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

  • Group Key: g_6.user_id
  • Buffers: shared hit=378
194. 2.899 2.899 ↓ 1.0 7,156 1

Seq Scan on tbl_affiliate_goals_for_export g_6 (cost=0..496.95 rows=7,155 width=28) (actual time=0.012..2.899 rows=7,156 loops=1)

  • Filter: (g_6.goal_name <> 'INSTALL_EVENT'::text)
  • Buffers: shared hit=378
195. 1,260.910 1,260.910 ↓ 2.2 520,743 1

Index Scan using ix_unq_etl_dim_ums_users on etl_dim_ums_users u_13 (cost=0.42..92,415.68 rows=234,559 width=198) (actual time=0.075..1,260.91 rows=520,743 loops=1)

  • Filter: ((NOT u_13.test_sign) AND (u_13.user_role = ANY ('{USER,PROFESSIONAL}'::text[])) AND (u_13.user_id <> ALL ('{10789518,10788054}'::bigint[])))
  • Buffers: shared hit=830,252
196. 2.785 16.056 ↓ 1.1 4,555 1

Sort (cost=346.12..356.89 rows=4,307 width=8) (actual time=14.55..16.056 rows=4,555 loops=1)

  • Sort Key: aff_goals_6.user_id
  • Sort Method: quicksort Memory: 406kB
  • Buffers: shared hit=378
197. 13.271 13.271 ↓ 1.1 4,555 1

CTE Scan on aff_goals aff_goals_6 (cost=0..86.14 rows=4,307 width=8) (actual time=9.96..13.271 rows=4,555 loops=1)

  • Buffers: shared hit=378
198. 124.807 273.933 ↓ 1.0 279,503 1

Hash (cost=42,309.8..42,309.8 rows=279,002 width=31) (actual time=273.933..273.933 rows=279,503 loops=1)

  • Buffers: shared hit=66,772, temp written=1,352
199. 149.126 149.126 ↓ 1.0 279,503 1

Index Scan using ix_tbl_installs_1 on tbl_installs tbl_installs_7 (cost=0.43..42,309.8 rows=279,002 width=31) (actual time=0.019..149.126 rows=279,503 loops=1)

  • Index Cond: (tbl_installs_7.app_name = 'Investmate'::text)
  • Buffers: shared hit=66,772
200. 770.331 770.331 ↓ 2.5 5 256,777

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=90) (actual time=0.002..0.003 rows=5 loops=256,777)

  • Index Cond: (u_12.user_id = h_6.user_id)
  • Buffers: shared hit=1,066,052
201. 503.405 53,939.913 ↓ 9.2 274,217 1

Subquery Scan on *SELECT* 11 (cost=827,366.65..832,705.63 rows=29,661 width=1,449) (actual time=47,403.107..53,939.913 rows=274,217 loops=1)

  • Buffers: shared hit=11,398,951, temp read=134,126 written=134,211
202. 2,860.338 53,436.508 ↓ 9.2 274,217 1

GroupAggregate (cost=827,366.65..831,964.1 rows=29,661 width=1,405) (actual time=47,403.097..53,436.508 rows=274,217 loops=1)

  • Group Key: ((row_to_json((SubPlan 13)))::jsonb), (COALESCE(u_14.reg_country, u_14.af_country_code)), u_14.apps_flyer_id, u_14.adset, u_14.ad, (date(t.created_timestamp)), u_14.af_install_ts, u_14.af_platform, t.currency, u_14.aff_id, u_14.offer_id, (CASE WHEN (tbl_installs_8.apps_flyer_id IS NOT NULL) THEN 'InvestmateApp'::text ELSE u_14.media_source END), (CASE WHEN (tbl_installs_8.apps_flyer_id IS NOT NULL) THEN ((u_14.media_source || ' '::text) || u_14.campaign) ELSE u_14.campaign END), (CASE WHEN (t.first_trade AND (t.created_timestamp = u_14.first_trade_ts)) THEN true ELSE false END), (CASE WHEN (t.status = ANY ('{OPENED,CLOSED}'::text[])) THEN u_14.user_id ELSE NULL::bigint END), u_14.demo_sign, u_14.deposit_bin, u_14.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_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
  • Buffers: shared hit=11,398,951, temp read=134,126 written=134,211
203. 21,652.063 50,576.170 ↓ 42.3 1,253,179 1

Sort (cost=827,366.65..827,440.8 rows=29,661 width=481) (actual time=47,403.019..50,576.17 rows=1,253,179 loops=1)

  • Sort Key: ((row_to_json((SubPlan 13)))::jsonb), (COALESCE(u_14.reg_country, u_14.af_country_code)), u_14.apps_flyer_id, u_14.adset, u_14.ad, (date(t.created_timestamp)), u_14.af_install_ts, u_14.af_platform, t.currency, u_14.aff_id, u_14.offer_id, (CASE WHEN (tbl_installs_8.apps_flyer_id IS NOT NULL) THEN 'InvestmateApp'::text ELSE u_14.media_source END), (CASE WHEN (tbl_installs_8.apps_flyer_id IS NOT NULL) THEN ((u_14.media_source || ' '::text) || u_14.campaign) ELSE u_14.campaign END), (CASE WHEN (t.first_trade AND (t.created_timestamp = u_14.first_trade_ts)) THEN true ELSE false END), (CASE WHEN (t.status = ANY ('{OPENED,CLOSED}'::text[])) THEN u_14.user_id ELSE NULL::bigint END), u_14.demo_sign, u_14.deposit_bin, u_14.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_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: external merge Disk: 387,616kB
  • Buffers: shared hit=11,398,951, temp read=134,126 written=134,211
204. 2,326.742 28,924.107 ↓ 42.3 1,253,179 1

Nested Loop (cost=190,397.44..821,362.69 rows=29,661 width=481) (actual time=3,412.892..28,924.107 rows=1,253,179 loops=1)

  • Buffers: shared hit=11,398,951, temp read=53,369 written=53,369
205. 3,403.737 21,584.649 ↓ 42.3 1,253,179 1

Nested Loop (cost=190,397.01..764,087.65 rows=29,661 width=439) (actual time=3,412.839..21,584.649 rows=1,253,179 loops=1)

  • Buffers: shared hit=6,385,910, temp read=53,369 written=53,369
206. 2,866.000 14,421.375 ↓ 42.3 1,253,179 1

Hash Join (cost=190,396.58..747,720.98 rows=29,661 width=373) (actual time=3,412.8..14,421.375 rows=1,253,179 loops=1)

  • Buffers: shared hit=1,241,986, temp read=53,369 written=53,369
207. 1,452.168 11,175.201 ↓ 42.3 1,253,179 1

Hash Join (cost=142,691.26..695,277.9 rows=29,661 width=342) (actual time=2,928.68..11,175.201 rows=1,253,179 loops=1)

  • Buffers: shared hit=1,175,214, temp read=24,110 written=24,110
208. 6,817.225 6,817.225 ↓ 42.4 1,256,512 1

Seq Scan on tbl_fct_position_history t (cost=0..542,719.8 rows=29,661 width=75) (actual time=1.023..6,817.225 rows=1,256,512 loops=1)

  • Filter: ((t.status = ANY ('{OPENED,CLOSED,SWAP}'::text[])) AND (date_trunc('quarter'::text, t.created_timestamp) = date_trunc('quarter'::text, (('now'::cstring)::date)::timestamp with time zone)))
  • Buffers: shared hit=344,584
209. 600.099 2,905.808 ↓ 2.2 520,743 1

Hash (cost=131,054.27..131,054.27 rows=234,559 width=275) (actual time=2,905.808..2,905.808 rows=520,743 loops=1)

  • Buffers: shared hit=830,630, temp written=12,883
210. 283.381 2,305.709 ↓ 2.2 520,743 1

Subquery Scan on u_14 (cost=1,354.07..131,054.27 rows=234,559 width=275) (actual time=24.223..2,305.709 rows=520,743 loops=1)

  • Buffers: shared hit=830,630
211. 653.383 2,022.328 ↓ 2.2 520,743 1

Merge Join (cost=1,354.07..128,708.68 rows=234,559 width=3,183) (actual time=24.219..2,022.328 rows=520,743 loops=1)

  • Buffers: shared hit=830,630
212.          

CTE aff_goals

213. 4.793 17.823 ↓ 1.1 4,555 1

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

  • Sort Key: g_7.user_id
  • Sort Method: quicksort Memory: 415kB
  • Buffers: shared hit=378
214. 8.192 13.030 ↓ 1.1 4,555 1

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

  • Group Key: g_7.user_id
  • Buffers: shared hit=378
215. 4.838 4.838 ↓ 1.0 7,156 1

Seq Scan on tbl_affiliate_goals_for_export g_7 (cost=0..496.95 rows=7,155 width=28) (actual time=0.017..4.838 rows=7,156 loops=1)

  • Filter: (g_7.goal_name <> 'INSTALL_EVENT'::text)
  • Buffers: shared hit=378
216. 1,343.331 1,343.331 ↓ 2.2 520,743 1

Index Scan using ix_unq_etl_dim_ums_users on etl_dim_ums_users u_15 (cost=0.42..92,415.68 rows=234,559 width=206) (actual time=0.109..1,343.331 rows=520,743 loops=1)

  • Filter: ((u_15.test_sign IS FALSE) AND (u_15.user_role = ANY ('{USER,PROFESSIONAL}'::text[])) AND (u_15.user_id <> ALL ('{10789518,10788054}'::bigint[])))
  • Buffers: shared hit=830,252
217. 3.767 25.614 ↓ 1.1 4,555 1

Sort (cost=346.12..356.89 rows=4,307 width=8) (actual time=24.078..25.614 rows=4,555 loops=1)

  • Sort Key: aff_goals_7.user_id
  • Sort Method: quicksort Memory: 406kB
  • Buffers: shared hit=378
218. 21.847 21.847 ↓ 1.1 4,555 1

CTE Scan on aff_goals aff_goals_7 (cost=0..86.14 rows=4,307 width=8) (actual time=16.231..21.847 rows=4,555 loops=1)

  • Buffers: shared hit=378
219. 170.476 380.174 ↓ 1.0 279,503 1

Hash (cost=42,309.8..42,309.8 rows=279,002 width=31) (actual time=380.174..380.174 rows=279,503 loops=1)

  • Buffers: shared hit=66,772, temp written=1,352
220. 209.698 209.698 ↓ 1.0 279,503 1

Index Scan using ix_tbl_installs_1 on tbl_installs tbl_installs_8 (cost=0.43..42,309.8 rows=279,002 width=31) (actual time=0.027..209.698 rows=279,503 loops=1)

  • Index Cond: (tbl_installs_8.app_name = 'Investmate'::text)
  • Buffers: shared hit=66,772
221. 3,759.537 3,759.537 ↓ 2.0 4 1,253,179

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=90) (actual time=0.002..0.003 rows=4 loops=1,253,179)

  • Index Cond: (u_14.user_id = h_7.user_id)
  • Buffers: shared hit=5,143,924
222. 2,506.358 2,506.358 ↑ 1.0 1 1,253,179

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,253,179)

  • Index Cond: (a_3.id = t.account_id)
  • Buffers: shared hit=5,013,041
223.          

SubPlan (for Nested Loop)

224. 2,506.358 2,506.358 ↑ 1.0 1 1,253,179

Result (cost=0..0.02 rows=1 width=32) (actual time=0.001..0.002 rows=1 loops=1,253,179)

225. 7.966 2,187,036.545 ↓ 946.0 946 1

Subquery Scan on *SELECT* 12 (cost=220,227.73..397,821.52 rows=1 width=1,477) (actual time=757.562..2,187,036.545 rows=946 loops=1)

  • Buffers: shared hit=848,669,795 read=188
226. 10.766 2,187,028.579 ↓ 946.0 946 1

Nested Loop (cost=220,227.73..397,821.48 rows=1 width=1,469) (actual time=757.55..2,187,028.579 rows=946 loops=1)

  • Buffers: shared hit=848,669,795 read=188
227. 65,366.579 2,186,993.217 ↓ 946.0 946 1

Nested Loop (cost=220,227.31..397,820.92 rows=1 width=306) (actual time=757.52..2,186,993.217 rows=946 loops=1)

  • Buffers: shared hit=848,666,214
228. 121,410.963 1,986,021.322 ↓ 946.0 946 1

Nested Loop (cost=220,226.88..352,023.6 rows=1 width=275) (actual time=538.383..1,986,021.322 rows=946 loops=1)

  • Buffers: shared hit=785,499,902
229. 33.390 436.949 ↓ 946.0 946 1

GroupAggregate (cost=218,872.8..218,878.09 rows=1 width=16) (actual time=394.352..436.949 rows=946 loops=1)

  • Group Key: u_1_1.user_id
  • Filter: (date_trunc('quarter'::text, min(e_3.event_datetime)) = date_trunc('quarter'::text, (('now'::cstring)::date)::timestamp with time zone))
  • Buffers: shared hit=81,132
230. 27.511 403.559 ↓ 243.1 36,710 1

Sort (cost=218,872.8..218,873.18 rows=151 width=16) (actual time=392.686..403.559 rows=36,710 loops=1)

  • Sort Key: u_1_1.user_id
  • Sort Method: quicksort Memory: 3,257kB
  • Buffers: shared hit=81,132
231. 27.171 376.048 ↓ 243.1 36,710 1

Hash Join (cost=83,124.03..218,867.34 rows=151 width=16) (actual time=296.157..376.048 rows=36,710 loops=1)

  • Buffers: shared hit=81,132
232. 52.798 52.798 ↓ 95.3 61,543 1

Index Scan using ix_etl_tbl_all_events_t_type on etl_tbl_all_events e_3 (cost=0.56..135,739.94 rows=646 width=12) (actual time=0.043..52.798 rows=61,543 loops=1)

  • Index Cond: (e_3.event_type = 'User documents status'::text)
  • Filter: ((e_3.event_attributes ->> 'status'::text) = 'REJECTED'::text)
  • Buffers: shared hit=6,378
233. 5.459 296.079 ↓ 3.0 17,339 1

Hash (cost=83,050.29..83,050.29 rows=5,854 width=8) (actual time=296.079..296.079 rows=17,339 loops=1)

  • Buffers: shared hit=74,754
234. 8.345 290.620 ↓ 3.0 17,339 1

Subquery Scan on u_1_1 (cost=82,894.31..83,050.29 rows=5,854 width=8) (actual time=268.679..290.62 rows=17,339 loops=1)

  • Buffers: shared hit=74,754
235. 10.139 282.275 ↓ 3.0 17,339 1

Hash Join (cost=82,894.31..82,991.75 rows=5,854 width=3,541) (actual time=268.676..282.275 rows=17,339 loops=1)

  • Buffers: shared hit=74,754
236.          

CTE aff_goals

237. 3.194 11.203 ↓ 1.1 4,555 1

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

  • Sort Key: g_8.user_id
  • Sort Method: quicksort Memory: 415kB
  • Buffers: shared hit=378
238. 4.953 8.009 ↓ 1.1 4,555 1

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

  • Group Key: g_8.user_id
  • Buffers: shared hit=378
239. 3.056 3.056 ↓ 1.0 7,156 1

Seq Scan on tbl_affiliate_goals_for_export g_8 (cost=0..496.95 rows=7,155 width=28) (actual time=0.032..3.056 rows=7,156 loops=1)

  • Filter: (g_8.goal_name <> 'INSTALL_EVENT'::text)
  • Buffers: shared hit=378
240. 13.676 13.676 ↓ 1.1 4,555 1

CTE Scan on aff_goals aff_goals_8 (cost=0..86.14 rows=4,307 width=8) (actual time=10.165..13.676 rows=4,555 loops=1)

  • Buffers: shared hit=378
241. 7.099 258.460 ↓ 3.0 17,339 1

Hash (cost=81,813.6..81,813.6 rows=5,854 width=8) (actual time=258.459..258.46 rows=17,339 loops=1)

  • Buffers: shared hit=74,376
242. 251.361 251.361 ↓ 3.0 17,339 1

Seq Scan on etl_dim_ums_users u_16 (cost=0..81,813.6 rows=5,854 width=8) (actual time=0.015..251.361 rows=17,339 loops=1)

  • Filter: ((u_16.test_sign IS FALSE) AND (u_16.user_role = ANY ('{USER,PROFESSIONAL}'::text[])) AND (u_16.user_status <> 'VERIFIED'::text) AND ((u_16.poa_confirmed = 'POA rejected'::text) OR (u_16.poi_confirmed = 'POI rejected'::text)))
  • Buffers: shared hit=74,376
243. 652,229.160 1,864,173.410 ↓ 2.2 523,510 946

Merge Join (cost=1,354.07..127,856.24 rows=235,078 width=3,183) (actual time=0.027..1,970.585 rows=523,510 loops=946)

  • Buffers: shared hit=785,418,770
244.          

CTE aff_goals

245. 3.201 11.186 ↓ 1.1 4,555 1

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

  • Sort Key: g_9.user_id
  • Sort Method: quicksort Memory: 415kB
  • Buffers: shared hit=378
246. 4.986 7.985 ↓ 1.1 4,555 1

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

  • Group Key: g_9.user_id
  • Buffers: shared hit=378
247. 2.999 2.999 ↓ 1.0 7,156 1

Seq Scan on tbl_affiliate_goals_for_export g_9 (cost=0..496.95 rows=7,155 width=28) (actual time=0.015..2.999 rows=7,156 loops=1)

  • Filter: (g_9.goal_name <> 'INSTALL_EVENT'::text)
  • Buffers: shared hit=378
248. 1,210,588.632 1,210,588.632 ↓ 2.2 523,510 946

Index Scan using ix_unq_etl_dim_ums_users on etl_dim_ums_users u_17 (cost=0.42..91,485.98 rows=235,078 width=198) (actual time=0.007..1,279.692 rows=523,510 loops=946)

  • Filter: (u_17.user_role = ANY ('{USER,PROFESSIONAL}'::text[]))
  • Buffers: shared hit=785,418,392
249. 1,341.989 1,355.618 ↓ 1.1 4,555 946

Sort (cost=346.12..356.89 rows=4,307 width=8) (actual time=0.017..1.433 rows=4,555 loops=946)

  • Sort Key: aff_goals_9.user_id
  • Sort Method: quicksort Memory: 406kB
  • Buffers: shared hit=378
250. 13.629 13.629 ↓ 1.1 4,555 1

CTE Scan on aff_goals aff_goals_9 (cost=0..86.14 rows=4,307 width=8) (actual time=10.145..13.629 rows=4,555 loops=1)

  • Buffers: shared hit=378
251. 135,605.316 135,605.316 ↓ 1.0 279,503 946

Index Scan using ix_tbl_installs_1 on tbl_installs tbl_installs_9 (cost=0.43..42,309.8 rows=279,002 width=31) (actual time=0.014..143.346 rows=279,503 loops=946)

  • Index Cond: (tbl_installs_9.app_name = 'Investmate'::text)
  • Buffers: shared hit=63,166,312
252. 24.596 24.596 ↑ 1.0 2 946

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=81) (actual time=0.025..0.026 rows=2 loops=946)

  • Index Cond: (u_17.user_id = h_8.user_id)
  • Buffers: shared hit=3,581 read=188
253. 0.205 9,680.371 ↓ 7.1 92 1

Subquery Scan on *SELECT* 13 (cost=422,031.74..422,061.98 rows=13 width=1,950) (actual time=9,677.985..9,680.371 rows=92 loops=1)

  • Buffers: shared read=95,575, temp read=6,344 written=11,941
254. 1.162 9,680.166 ↓ 7.1 92 1

Hash Join (cost=422,031.74..422,061.43 rows=13 width=1,962) (actual time=9,677.974..9,680.166 rows=92 loops=1)

  • Buffers: shared read=95,575, temp read=6,344 written=11,941
255. 346.196 610.117 ↓ 1.0 878 1

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

  • Group Key: date(src_finres_b2c_trades_daily.reportdate)
  • Buffers: shared read=9,851
256. 263.921 263.921 ↑ 1.0 326,625 1

Seq Scan on src_finres_b2c_trades_daily src_finres_b2c_trades_daily (cost=0..19,781.51 rows=327,334 width=18) (actual time=1.059..263.921 rows=326,625 loops=1)

  • Filter: ((src_finres_b2c_trades_daily.metric_type)::text = ANY ('{RPL,UPL}'::text[]))
  • Buffers: shared read=9,851
257. 0.135 9,068.795 ↓ 9.2 92 1

Hash (cost=395,703.42..395,703.42 rows=10 width=480) (actual time=9,068.795..9,068.795 rows=92 loops=1)

  • Buffers: shared read=85,724, temp read=6,344 written=11,941
258. 0.928 9,068.660 ↓ 9.2 92 1

Hash Join (cost=395,652.07..395,703.42 rows=10 width=480) (actual time=9,066.918..9,068.66 rows=92 loops=1)

  • 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))
  • Buffers: shared read=85,724, temp read=6,344 written=11,941
259. 0.699 4.221 ↑ 2.4 880 1

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

  • Buffers: shared read=42
260. 0.752 0.752 ↑ 3.4 623 1

Seq Scan on rep_max_daily_position rep_max_daily_position (cost=0..30.9 rows=2,090 width=11) (actual time=0.587..0.752 rows=623 loops=1)

  • Buffers: shared read=4
261. 0.973 2.770 ↓ 1.3 879 1

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

  • Buffers: shared read=38
262. 1.797 1.797 ↓ 1.3 879 1

Seq Scan on rep_capital_pl rep_capital_pl (cost=0..44.84 rows=684 width=401) (actual time=0.392..1.797 rows=879 loops=1)

  • Buffers: shared read=38
263. 0.269 9,063.511 ↓ 4.1 815 1

Hash (cost=395,596.18..395,596.18 rows=200 width=68) (actual time=9,063.511..9,063.511 rows=815 loops=1)

  • Buffers: shared read=85,682, temp read=6,344 written=11,941
264. 540.434 9,063.242 ↓ 4.1 815 1

HashAggregate (cost=395,589.68..395,594.18 rows=200 width=68) (actual time=9,061.168..9,063.242 rows=815 loops=1)

  • Group Key: q1.reportdate
  • Buffers: shared read=85,682, temp read=6,344 written=11,941
265.          

CTE q1

266. 4,422.151 7,546.040 ↑ 1.0 1,344,660 1

WindowAgg (cost=291,913.51..357,575.09 rows=1,382,349 width=20) (actual time=2,682.116..7,546.04 rows=1,344,660 loops=1)

  • Buffers: shared read=85,682, temp read=6,344 written=6,361
267. 1,141.076 3,123.889 ↑ 1.0 1,344,660 1

Sort (cost=291,913.51..295,369.38 rows=1,382,349 width=28) (actual time=2,682.037..3,123.889 rows=1,344,660 loops=1)

  • Sort Key: (date(d.ts_utc))
  • Sort Method: external merge Disk: 50,752kB
  • Buffers: shared read=85,682, temp read=6,344 written=6,361
268. 1,982.813 1,982.813 ↑ 1.0 1,344,660 1

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

  • Filter: ((d.eqity_before_trade_usd <> '0'::numeric) AND (d.order_type = 'OPEN'::text))
  • Buffers: shared read=85,682
269. 8,522.808 8,522.808 ↑ 1.0 1,344,660 1

CTE Scan on q1 q1 (cost=0..27,646.98 rows=1,382,349 width=20) (actual time=2,682.12..8,522.808 rows=1,344,660 loops=1)

  • Buffers: shared read=85,682, temp read=6,344 written=11,941
270.          

SubPlan (for Hash Join)

271. 0.092 0.092 ↑ 1.0 1 92

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

272. 884.705 118,927.970 ↑ 1,641.3 243,094 1

Subquery Scan on *SELECT* 14 (cost=370,301,146.92..478,030,305.87 rows=398,996,885 width=2,549) (actual time=108,616.064..118,927.97 rows=243,094 loops=1)

  • Buffers: shared hit=1,989,138 read=122,247, temp read=426,644 written=605,257
273. 5,525.735 118,043.265 ↑ 1,641.3 243,094 1

GroupAggregate (cost=370,301,146.92..427,158,203.04 rows=398,996,885 width=1,453) (actual time=108,616.051..118,043.265 rows=243,094 loops=1)

  • Group Key: (COALESCE(u_18.reg_country, u_18.af_country_code)), u_18.apps_flyer_id, u_18.adset, u_18.ad, (date(t_1.reportdate)), u_18.af_install_ts, u_18.af_platform, u_18.aff_id, u_18.offer_id, (CASE WHEN (tbl_installs_10.apps_flyer_id IS NOT NULL) THEN 'InvestmateApp'::text ELSE u_18.media_source END), (CASE WHEN (tbl_installs_10.apps_flyer_id IS NOT NULL) THEN ((u_18.media_source || ' '::text) || u_18.campaign) ELSE u_18.campaign END), u_18.user_id, u_18.demo_sign, u_18.deposit_bin, u_18.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_18.keywords, u_18.ad_conceptname, u_18.ad_creativename, u_18.ad_creativetype, u_18.user_role, u_18.tenant, u_18.deposit_bin_total
  • Buffers: shared hit=1,989,138 read=122,247, temp read=426,644 written=605,257
274. 25,485.507 112,517.530 ↑ 169.0 2,361,192 1

Sort (cost=370,301,146.92..371,298,639.14 rows=398,996,885 width=598) (actual time=108,615.982..112,517.53 rows=2,361,192 loops=1)

  • Sort Key: (COALESCE(u_18.reg_country, u_18.af_country_code)), u_18.apps_flyer_id, u_18.adset, u_18.ad, (date(t_1.reportdate)), u_18.af_install_ts, u_18.af_platform, u_18.aff_id, u_18.offer_id, (CASE WHEN (tbl_installs_10.apps_flyer_id IS NOT NULL) THEN 'InvestmateApp'::text ELSE u_18.media_source END), (CASE WHEN (tbl_installs_10.apps_flyer_id IS NOT NULL) THEN ((u_18.media_source || ' '::text) || u_18.campaign) ELSE u_18.campaign END), u_18.user_id, u_18.demo_sign, u_18.deposit_bin, u_18.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_18.keywords, u_18.ad_conceptname, u_18.ad_creativename, u_18.ad_creativetype, u_18.user_role, u_18.tenant, u_18.deposit_bin_total
  • Sort Method: external merge Disk: 639,184kB
  • Buffers: shared hit=1,989,138 read=122,247, temp read=426,644 written=605,257
275. 8,450.586 87,032.023 ↑ 169.0 2,361,192 1

Hash Join (cost=84,034,935.69..126,387,949.31 rows=398,996,885 width=598) (actual time=79,675.565..87,032.023 rows=2,361,192 loops=1)

  • Buffers: shared hit=1,989,138 read=122,247, temp read=264,578 written=443,054
276. 371.299 371.299 ↑ 1.1 725,200 1

Seq Scan on etl_tbl_user_media_source_hist h_9 (cost=0..18,900.96 rows=771,696 width=90) (actual time=0.517..371.299 rows=725,200 loops=1)

  • Buffers: shared hit=47 read=11,137
277. 2,983.496 78,210.138 ↑ 169.0 2,361,192 1

Hash (cost=52,941,232.63..52,941,232.63 rows=398,996,885 width=506) (actual time=78,210.138..78,210.138 rows=2,361,192 loops=1)

  • Buffers: shared hit=1,989,091 read=111,110, temp read=168,860 written=393,046
278. 2,725.710 75,226.642 ↑ 169.0 2,361,192 1

Hash Join (cost=1,398,088.55..52,941,232.63 rows=398,996,885 width=506) (actual time=46,533.349..75,226.642 rows=2,361,192 loops=1)

  • Buffers: shared hit=1,989,091 read=111,110, temp read=168,860 written=323,494
279. 1,155.897 68,725.550 ↑ 167.5 2,382,315 1

Subquery Scan on t_1 (cost=1,186,849.97..23,855,670.88 rows=398,996,885 width=216) (actual time=42,643.2..68,725.55 rows=2,382,315 loops=1)

  • Buffers: shared hit=1,091,689 read=111,110, temp read=122,825 written=277,459
280. 1,009.772 67,569.653 ↑ 167.5 2,382,315 1

Append (cost=1,186,849.97..19,865,702.03 rows=398,996,885 width=536) (actual time=42,643.196..67,569.653 rows=2,382,315 loops=1)

  • Buffers: shared hit=1,091,689 read=111,110, temp read=122,825 written=277,459
281.          

CTE q1

282. 9,558.782 9,558.782 ↑ 1.0 11,710,143 1

Seq Scan on src_spread_capture_to_dlk src_spread_capture_to_dlk (cost=0..443,491.62 rows=11,718,438 width=214) (actual time=0.033..9,558.782 rows=11,710,143 loops=1)

  • Filter: (src_spread_capture_to_dlk.instrument ~ '[0-9]'::text)
  • Buffers: shared hit=97,987 read=111,110
283. 1,366.331 54,282.119 ↑ 173.9 2,276,779 1

Subquery Scan on *SELECT* 1_1 (cost=743,358.35..16,580,192.93 rows=395,821,772 width=479) (actual time=42,643.194..54,282.119 rows=2,276,779 loops=1)

  • Buffers: shared hit=236,541 read=111,110, temp read=107,767 written=262,060
284. 5,016.534 52,915.788 ↑ 173.9 2,276,779 1

Merge Join (cost=743,358.35..11,632,420.78 rows=395,821,772 width=503) (actual time=42,643.189..52,915.788 rows=2,276,779 loops=1)

  • Buffers: shared hit=236,541 read=111,110, temp read=107,767 written=262,060
285. 6,021.932 38,323.545 ↓ 38.9 2,276,779 1

Sort (cost=512,930.57..513,077.05 rows=58,592 width=407) (actual time=36,707.882..38,323.545 rows=2,276,779 loops=1)

  • Sort Key: q1_1.account_id
  • Sort Method: external merge Disk: 326,400kB
  • Buffers: shared hit=173,436 read=111,110, temp read=100,016 written=254,283
286. 1,950.031 32,301.613 ↓ 38.9 2,276,779 1

Hash Join (cost=83,026.55..501,956.04 rows=58,592 width=407) (actual time=1,153.981..32,301.613 rows=2,276,779 loops=1)

  • Buffers: shared hit=173,436 read=111,110, temp read=32,892 written=187,078
287. 2,588.132 30,336.827 ↓ 38.9 2,276,779 1

Hash Join (cost=73,262.08..492,023.05 rows=58,592 width=380) (actual time=1,139.193..30,336.827 rows=2,276,779 loops=1)

  • Buffers: shared hit=160,595 read=111,110, temp read=32,892 written=187,078
288. 26,841.513 26,841.513 ↓ 38.9 2,280,377 1

CTE Scan on q1 q1_1 (cost=0..410,145.33 rows=58,592 width=368) (actual time=231.686..26,841.513 rows=2,280,377 loops=1)

  • Filter: (date_trunc('quarter'::text, q1_1.reportdate) = date_trunc('quarter'::text, (('now'::cstring)::date)::timestamp with time zone))
  • Buffers: shared hit=97,987 read=111,110, temp written=154,186
289. 311.600 907.182 ↓ 2.2 827,404 1

Hash (cost=66,364.48..66,364.48 rows=375,648 width=20) (actual time=907.182..907.182 rows=827,404 loops=1)

  • Buffers: shared hit=62,608, temp written=3,887
290. 595.582 595.582 ↓ 2.2 827,404 1

Seq Scan on etl_tbl_ums_users u_19 (cost=0..66,364.48 rows=375,648 width=20) (actual time=0.014..595.582 rows=827,404 loops=1)

  • Buffers: shared hit=62,608
291. 3.285 14.755 ↑ 1.7 7,557 1

Hash (cost=9,603.52..9,603.52 rows=12,876 width=35) (actual time=14.755..14.755 rows=7,557 loops=1)

  • Buffers: shared hit=12,841
292. 11.470 11.470 ↑ 1.7 7,557 1

Index Scan using ts_instrument__id__idx on ts_instrument i (cost=0.29..9,603.52 rows=12,876 width=35) (actual time=0.034..11.47 rows=7,557 loops=1)

  • Buffers: shared hit=12,841
293. 1,189.515 9,575.709 ↓ 2.7 3,623,582 1

Materialize (cost=230,427.79..237,183.35 rows=1,351,112 width=23) (actual time=5,931.023..9,575.709 rows=3,623,582 loops=1)

  • Buffers: shared hit=63,105, temp read=7,751 written=7,777
294. 7,805.727 8,386.194 ↓ 1.0 1,355,094 1

Sort (cost=230,427.79..233,805.57 rows=1,351,112 width=23) (actual time=5,931.016..8,386.194 rows=1,355,094 loops=1)

  • Sort Key: ((a_4.id)::text)
  • Sort Method: external merge Disk: 62,008kB
  • Buffers: shared hit=63,105, temp read=7,751 written=7,777
295. 580.467 580.467 ↓ 1.0 1,355,487 1

Seq Scan on as_account a_4 (cost=0..76,616.12 rows=1,351,112 width=23) (actual time=0.02..580.467 rows=1,355,487 loops=1)

  • Buffers: shared hit=63,105
296. 700.051 12,277.762 ↑ 30.1 105,536 1

Merge Join (cost=676,954.79..815,281.93 rows=3,175,113 width=452) (actual time=11,110.92..12,277.762 rows=105,536 loops=1)

  • Buffers: shared hit=855,148, temp read=15,058 written=15,399
297. 222.593 3,141.673 ↓ 224.5 105,536 1

Sort (cost=446,527.01..446,528.18 rows=470 width=84) (actual time=3,046.854..3,141.673 rows=105,536 loops=1)

  • Sort Key: ((fct_transactions_4.user_account_id)::text)
  • Sort Method: external merge Disk: 13,928kB
  • Buffers: shared hit=792,043, temp read=7,619 written=7,622
298. 193.980 2,919.080 ↓ 224.5 105,536 1

Hash Join (cost=73,262.37..446,506.15 rows=470 width=84) (actual time=1,870.185..2,919.08 rows=105,536 loops=1)

  • Buffers: shared hit=792,043, temp read=5,878 written=5,878
299. 103.977 1,826.440 ↓ 224.5 105,536 1

Nested Loop (cost=0.29..371,023.61 rows=470 width=72) (actual time=958.194..1,826.44 rows=105,536 loops=1)

  • Buffers: shared hit=729,435
300. 1,511.391 1,511.391 ↓ 219.0 105,536 1

Seq Scan on etl_fct_transactions fct_transactions_4 (cost=0..370,157.3 rows=482 width=73) (actual time=958.107..1,511.391 rows=105,536 loops=1)

  • Filter: ((fct_transactions_4.transaction_type <> 'DEMO_TRANSFER'::text) AND (fct_transactions_4.transaction_type = ANY ('{TRADE_COMMISSION,EXCHANGE_COMMISSION}'::text[])) AND (fct_transactions_4.transaction_status = 'PROCESSED'::text) AND (date_trunc('quarter'::text, fct_transactions_4.ts_timestamp) = date_trunc('quarter'::text, (('now'::cstring)::date)::timestamp with time zone)))
  • Buffers: shared hit=279,364
301. 211.072 211.072 ↑ 1.0 1 105,536

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

  • Index Cond: (i_1.id = fct_transactions_4.details_instrument_id)
  • Buffers: shared hit=450,071
302. 312.636 898.660 ↓ 2.2 827,404 1

Hash (cost=66,364.48..66,364.48 rows=375,648 width=20) (actual time=898.66..898.66 rows=827,404 loops=1)

  • Buffers: shared hit=62,608, temp written=3,887
303. 586.024 586.024 ↓ 2.2 827,404 1

Seq Scan on etl_tbl_ums_users u_20 (cost=0..66,364.48 rows=375,648 width=20) (actual time=0.012..586.024 rows=827,404 loops=1)

  • Buffers: shared hit=62,608
304. 573.726 8,436.038 ↑ 1.0 1,350,647 1

Materialize (cost=230,427.79..237,183.35 rows=1,351,112 width=23) (actual time=5,854.434..8,436.038 rows=1,350,647 loops=1)

  • Buffers: shared hit=63,105, temp read=7,439 written=7,777
305. 7,286.790 7,862.312 ↑ 1.1 1,247,041 1

Sort (cost=230,427.79..233,805.57 rows=1,351,112 width=23) (actual time=5,854.419..7,862.312 rows=1,247,041 loops=1)

  • Sort Key: ((a_5.id)::text)
  • Sort Method: external merge Disk: 62,008kB
  • Buffers: shared hit=63,105, temp read=7,439 written=7,777
306. 575.522 575.522 ↓ 1.0 1,355,487 1

Seq Scan on as_account a_5 (cost=0..76,616.12 rows=1,351,112 width=23) (actual time=0.014..575.522 rows=1,355,487 loops=1)

  • Buffers: shared hit=63,105
307. 552.144 3,775.382 ↓ 2.2 520,743 1

Hash (cost=198,914.59..198,914.59 rows=234,559 width=298) (actual time=3,775.381..3,775.382 rows=520,743 loops=1)

  • Buffers: shared hit=897,402, temp read=10,520 written=23,585
308. 740.573 3,223.238 ↓ 2.2 520,743 1

Hash Join (cost=49,059.4..198,914.59 rows=234,559 width=298) (actual time=291.568..3,223.238 rows=520,743 loops=1)

  • Buffers: shared hit=897,402, temp read=10,520 written=10,520
309. 249.891 2,206.496 ↓ 2.2 520,743 1

Subquery Scan on u_18 (cost=1,354.07..131,054.27 rows=234,559 width=267) (actual time=15.148..2,206.496 rows=520,743 loops=1)

  • Buffers: shared hit=830,630
310. 617.416 1,956.605 ↓ 2.2 520,743 1

Merge Join (cost=1,354.07..128,708.68 rows=234,559 width=3,183) (actual time=15.145..1,956.605 rows=520,743 loops=1)

  • Buffers: shared hit=830,630
311.          

CTE aff_goals

312. 3.174 11.246 ↓ 1.1 4,555 1

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

  • Sort Key: g_10.user_id
  • Sort Method: quicksort Memory: 415kB
  • Buffers: shared hit=378
313. 5.020 8.072 ↓ 1.1 4,555 1

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

  • Group Key: g_10.user_id
  • Buffers: shared hit=378
314. 3.052 3.052 ↓ 1.0 7,156 1

Seq Scan on tbl_affiliate_goals_for_export g_10 (cost=0..496.95 rows=7,155 width=28) (actual time=0.013..3.052 rows=7,156 loops=1)

  • Filter: (g_10.goal_name <> 'INSTALL_EVENT'::text)
  • Buffers: shared hit=378
315. 1,322.693 1,322.693 ↓ 2.2 520,743 1

Index Scan using ix_unq_etl_dim_ums_users on etl_dim_ums_users u_21 (cost=0.42..92,415.68 rows=234,559 width=198) (actual time=0.096..1,322.693 rows=520,743 loops=1)

  • Filter: ((u_21.test_sign IS FALSE) AND (u_21.user_role = ANY ('{USER,PROFESSIONAL}'::text[])) AND (u_21.user_id <> ALL ('{10789518,10788054}'::bigint[])))
  • Buffers: shared hit=830,252
316. 2.824 16.496 ↓ 1.1 4,555 1

Sort (cost=346.12..356.89 rows=4,307 width=8) (actual time=15.025..16.496 rows=4,555 loops=1)

  • Sort Key: aff_goals_10.user_id
  • Sort Method: quicksort Memory: 406kB
  • Buffers: shared hit=378
317. 13.672 13.672 ↓ 1.1 4,555 1

CTE Scan on aff_goals aff_goals_10 (cost=0..86.14 rows=4,307 width=8) (actual time=10.202..13.672 rows=4,555 loops=1)

  • Buffers: shared hit=378
318. 121.525 276.169 ↓ 1.0 279,503 1

Hash (cost=42,309.8..42,309.8 rows=279,002 width=31) (actual time=276.169..276.169 rows=279,503 loops=1)

  • Buffers: shared hit=66,772, temp written=1,352
319. 154.644 154.644 ↓ 1.0 279,503 1

Index Scan using ix_tbl_installs_1 on tbl_installs tbl_installs_10 (cost=0.43..42,309.8 rows=279,002 width=31) (actual time=0.022..154.644 rows=279,503 loops=1)

  • Index Cond: (tbl_installs_10.app_name = 'Investmate'::text)
  • Buffers: shared hit=66,772
320.          

CTE fx_recalc

321. 12,151.771 2,611,734.023 ↑ 1.4 1,415,028 1

Hash Join (cost=1,094.92..14,071,148.16 rows=1,995,646 width=2,988) (actual time=71,095.799..2,611,734.023 rows=1,415,028 loops=1)

  • Buffers: shared hit=886,257,538 read=588,591 dirtied=6, temp read=917,051 written=1,201,818
322. 2,599,557.837 2,599,557.837 ↑ 1.4 1,415,028 1

CTE Scan on main_data main_data (cost=0..13,969,522.42 rows=1,995,646 width=2,940) (actual time=71,071.304..2,599,557.837 rows=1,415,028 loops=1)

  • Filter: (date_trunc('quarter'::text, main_data.event_datetime) = date_trunc('quarter'::text, (('now'::cstring)::date)::timestamp with time zone))
  • Buffers: shared hit=886,257,537 read=588,342 dirtied=6, temp read=917,051 written=1,201,818
323. 12.507 24.415 ↓ 1.0 33,858 1

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

  • Buffers: shared hit=1 read=249
324. 11.908 11.908 ↓ 1.0 33,858 1

Seq Scan on ts_fx_daily ts_fx_daily_1 (cost=0..587.97 rows=33,797 width=16) (actual time=0.484..11.908 rows=33,858 loops=1)

  • Buffers: shared hit=1 read=249
325.          

CTE marketing_manager

326. 2.720 7.513 ↓ 1.3 2,529 1

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

  • Group Key: upper(m_1.media_source), upper(m_1.campaign), m_1.marketing_manager
  • Buffers: shared read=43
327. 4.793 4.793 ↑ 1.0 3,656 1

Seq Scan on scv_campaigns_managers m_1 (cost=0..97.84 rows=3,656 width=79) (actual time=0.662..4.793 rows=3,656 loops=1)

  • Buffers: shared read=43
328. 5,059.490 2,624,867.513 ↑ 1.4 1,415,028 1

Hash Join (cost=66.92..2,433,214.4 rows=1,995,646 width=2,892) (actual time=71,108.627..2,624,867.513 rows=1,415,028 loops=1)

  • Buffers: shared hit=886,257,538 read=588,634 dirtied=6, temp read=917,051 written=1,308,447
329. 2,619,795.241 2,619,795.241 ↑ 1.4 1,415,028 1

CTE Scan on fx_recalc fx_recalc (cost=0..39,912.92 rows=1,995,646 width=2,892) (actual time=71,095.808..2,619,795.241 rows=1,415,028 loops=1)

  • Buffers: shared hit=886,257,538 read=588,591 dirtied=6, temp read=917,051 written=1,308,447
330. 3.370 12.782 ↓ 1.3 2,529 1

Hash (cost=38.24..38.24 rows=1,912 width=96) (actual time=12.782..12.782 rows=2,529 loops=1)

  • Buffers: shared read=43
331. 9.412 9.412 ↓ 1.3 2,529 1

CTE Scan on marketing_manager m (cost=0..38.24 rows=1,912 width=96) (actual time=6.729..9.412 rows=2,529 loops=1)

  • Buffers: shared read=43
332. 0.101 0.177 ↑ 1.0 250 1

Hash (cost=4.5..4.5 rows=250 width=23) (actual time=0.177..0.177 rows=250 loops=1)

  • Buffers: shared hit=2
333. 0.076 0.076 ↑ 1.0 250 1

Seq Scan on scv_geo_country_codes c (cost=0..4.5 rows=250 width=23) (actual time=0.008..0.076 rows=250 loops=1)

  • Buffers: shared hit=2
Planning time : 88.076 ms
Execution time : 2,684,039.358 ms