explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QzXO

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 133,613.557 ↓ 0.0 0 1

Subquery Scan on main (cost=215,999,272.99..216,938,910.55 rows=40,000 width=1,021) (actual time=133,613.557..133,613.557 rows=0 loops=1)

  • Functions: 378
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 46.662 ms, Inlining 51.150 ms, Optimization 4459.035 ms, Emission 3042.780 ms, Total 7599.627 ms
2.          

CTE t_load_from

3. 0.165 0.165 ↑ 1.0 1 1

Result (cost=0.00..0.27 rows=1 width=4) (actual time=0.165..0.165 rows=1 loops=1)

4.          

CTE t_load_to

5. 0.119 0.119 ↑ 1.0 1 1

Result (cost=0.00..0.27 rows=1 width=4) (actual time=0.119..0.119 rows=1 loops=1)

6.          

CTE apps_flyer_ids

7. 0.031 0.031 ↑ 1.0 1 1

Result (cost=0.00..0.26 rows=1 width=32) (actual time=0.031..0.031 rows=1 loops=1)

8.          

CTE ad_performance_report

9. 73.769 14,450.751 ↑ 1.7 23,289 1

HashAggregate (cost=243,158.58..243,758.58 rows=40,000 width=17) (actual time=14,445.252..14,450.751 rows=23,289 loops=1)

  • Group Key: (ad_performance_report.ad_id)::bigint, ad_performance_report.ad_name
10.          

Initplan (for HashAggregate)

11. 0.168 0.168 ↑ 1.0 1 1

CTE Scan on t_load_from (cost=0.00..0.02 rows=1 width=4) (actual time=0.168..0.168 rows=1 loops=1)

12. 29.848 14,376.814 ↑ 7.4 161,669 1

Result (cost=0.00..237,156.76 rows=1,200,360 width=17) (actual time=7,599.994..14,376.814 rows=161,669 loops=1)

13. 14.368 14,346.966 ↑ 7.4 161,669 1

Append (cost=0.00..219,151.36 rows=1,200,360 width=22) (actual time=7,599.991..14,346.966 rows=161,669 loops=1)

14. 14,329.024 14,329.024 ↑ 7.4 161,315 1

Seq Scan on ad_performance_report (cost=0.00..213,103.80 rows=1,200,147 width=22) (actual time=7,599.990..14,329.024 rows=161,315 loops=1)

  • Filter: ((date)::date >= ($3 - '2 mons'::interval))
  • Rows Removed by Filter: 3,455,232
15. 3.574 3.574 ↓ 1.7 354 1

Seq Scan on ad_performance_report_2475468088 (cost=0.00..45.76 rows=213 width=45) (actual time=1.947..3.574 rows=354 loops=1)

  • Filter: ((date)::date >= ($3 - '2 mons'::interval))
  • Rows Removed by Filter: 284
16.          

CTE click_performance_report

17. 0.000 0.000 ↓ 0.0 0

Result (cost=0.02..447,962.26 rows=2,039,253 width=122) (never executed)

18.          

Initplan (for Result)

19. 0.000 0.000 ↓ 0.0 0

CTE Scan on t_load_from t_load_from_1 (cost=0.00..0.02 rows=1 width=4) (never executed)

20. 0.000 0.000 ↓ 0.0 0

Append (cost=0.00..417,373.45 rows=2,039,253 width=126) (never executed)

21. 0.000 0.000 ↓ 0.0 0

Seq Scan on click_performance_report (cost=0.00..392,184.34 rows=1,958,356 width=125) (never executed)

  • Filter: ((day)::date >= ($5 - '1 mon'::interval))
22. 0.000 0.000 ↓ 0.0 0

Seq Scan on click_performance_report_2475468088 (cost=0.00..14,992.84 rows=80,897 width=155) (never executed)

  • Filter: ((day)::date >= ($5 - '1 mon'::interval))
23.          

CTE sdk_installs

24. 0.005 95,927.051 ↑ 52,784.0 1 1

Append (cost=1,189,075.76..1,623,243.31 rows=52,784 width=645) (actual time=25,590.608..95,927.051 rows=1 loops=1)

25. 1.950 25,590.608 ↑ 41,370.0 1 1

Merge Left Join (cost=1,189,075.76..1,196,113.61 rows=41,370 width=642) (actual time=25,590.607..25,590.608 rows=1 loops=1)

  • Merge Cond: ((t2.device_model = d.model) AND ((upper(t2.device_brand)) = (upper(d.retail_branding))))
  • Join Filter: (to_timestamp(((t1_1."timestamp" / 1000))::double precision) > '2017-04-17 21:00:00+00'::timestamp with time zone)
26.          

Initplan (for Merge Left Join)

27. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on t_load_from t_load_from_2 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

28. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on apps_flyer_ids (cost=0.00..0.02 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=1)

29. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on apps_flyer_ids apps_flyer_ids_1 (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=1)

30. 0.010 25,534.537 ↑ 41,370.0 1 1

Sort (cost=1,187,376.89..1,187,480.32 rows=41,370 width=922) (actual time=25,534.536..25,534.537 rows=1 loops=1)

  • Sort Key: t2.device_model, (upper(t2.device_brand))
  • Sort Method: quicksort Memory: 27kB
31. 0.019 25,534.527 ↑ 41,370.0 1 1

Nested Loop (cost=430,924.20..1,174,348.18 rows=41,370 width=922) (actual time=25,363.401..25,534.527 rows=1 loops=1)

32. 0.009 25,504.039 ↑ 5,465.0 1 1

Nested Loop Left Join (cost=430,923.78..1,139,727.39 rows=5,465 width=860) (actual time=25,338.615..25,504.039 rows=1 loops=1)

33. 1,388.688 25,500.627 ↑ 5,465.0 1 1

Hash Left Join (cost=430,923.22..1,135,556.01 rows=5,465 width=759) (actual time=25,335.204..25,500.627 rows=1 loops=1)

  • Hash Cond: (i.client_installation_id = t1_1.installation_id)
  • Filter: ((COALESCE(array_length(($8)::text[], 1), 0) = 0) OR (t1_1.apps_flyer_u_i_d = ANY (($9)::text[])))
  • Rows Removed by Filter: 127,101
34. 5,554.394 5,554.394 ↑ 7.3 126,330 1

Seq Scan on install i (cost=0.00..125,620.07 rows=918,801 width=131) (actual time=1,127.452..5,554.394 rows=126,330 loops=1)

  • Filter: (to_timestamp((("timestamp" / 1000))::double precision) >= ($7 - '1 mon'::interval))
  • Rows Removed by Filter: 2,627,876
35. 3,398.398 18,557.545 ↓ 1.0 2,112,583 1

Hash (cost=227,404.43..227,404.43 rows=2,109,343 width=664) (actual time=18,557.545..18,557.545 rows=2,112,583 loops=1)

  • Buckets: 16,384 Batches: 256 Memory Usage: 6,035kB
36. 15,159.147 15,159.147 ↓ 1.0 2,112,583 1

Seq Scan on apps_flyer t1_1 (cost=0.00..227,404.43 rows=2,109,343 width=664) (actual time=1.952..15,159.147 rows=2,112,583 loops=1)

37. 3.403 3.403 ↓ 0.0 0 1

Index Scan using install_referrer_success_pkey on install_referrer_success r_1 (cost=0.56..0.75 rows=1 width=232) (actual time=3.403..3.403 rows=0 loops=1)

  • Index Cond: (session_id = t1_1.session_id)
  • Filter: (referrer ~~ '%iad-keyword%'::text)
38. 0.047 30.469 ↑ 12.0 1 1

Append (cost=0.42..6.22 rows=12 width=184) (actual time=24.769..30.469 rows=1 loops=1)

39. 3.857 3.857 ↓ 0.0 0 1

Index Scan using session_2017_session_id_session_event_num_timestamp_key on session_2017 t2 (cost=0.42..0.51 rows=1 width=158) (actual time=3.857..3.857 rows=0 loops=1)

  • Index Cond: (session_id = i.session_id)
40. 3.564 3.564 ↓ 0.0 0 1

Index Scan using session_2018_session_id_session_event_num_timestamp_key on session_2018 t2_1 (cost=0.56..0.96 rows=1 width=178) (actual time=3.564..3.564 rows=0 loops=1)

  • Index Cond: (session_id = i.session_id)
41. 3.980 3.980 ↓ 0.0 0 1

Index Scan using session_2019_session_id_session_event_num_timestamp_key on session_2019 t2_2 (cost=0.56..0.94 rows=1 width=184) (actual time=3.980..3.980 rows=0 loops=1)

  • Index Cond: (session_id = i.session_id)
42. 4.161 4.161 ↓ 0.0 0 1

Index Scan using session_2020_1_session_id_session_event_num_timestamp_key on session_2020_1 t2_3 (cost=0.56..0.78 rows=1 width=187) (actual time=4.161..4.161 rows=0 loops=1)

  • Index Cond: (session_id = i.session_id)
43. 4.114 4.114 ↓ 0.0 0 1

Index Scan using session_2020_2_session_id_session_event_num_timestamp_key on session_2020_2 t2_4 (cost=0.56..1.15 rows=1 width=189) (actual time=4.114..4.114 rows=0 loops=1)

  • Index Cond: (session_id = i.session_id)
44. 5.068 5.068 ↑ 1.0 1 1

Index Scan using session_2020_3_session_id_session_event_num_timestamp_key on session_2020_3 t2_5 (cost=0.56..0.77 rows=1 width=187) (actual time=5.066..5.068 rows=1 loops=1)

  • Index Cond: (session_id = i.session_id)
45. 0.865 0.865 ↓ 0.0 0 1

Index Scan using session_2020_4_session_id_session_event_num_timestamp_key on session_2020_4 t2_6 (cost=0.13..0.15 rows=1 width=256) (actual time=0.865..0.865 rows=0 loops=1)

  • Index Cond: (session_id = i.session_id)
46. 1.552 1.552 ↓ 0.0 0 1

Index Scan using session_2021_1_session_id_session_event_num_timestamp_key on session_2021_1 t2_7 (cost=0.12..0.14 rows=1 width=256) (actual time=1.552..1.552 rows=0 loops=1)

  • Index Cond: (session_id = i.session_id)
47. 0.657 0.657 ↓ 0.0 0 1

Index Scan using session_2021_2_session_id_session_event_num_timestamp_key on session_2021_2 t2_8 (cost=0.12..0.14 rows=1 width=256) (actual time=0.656..0.657 rows=0 loops=1)

  • Index Cond: (session_id = i.session_id)
48. 0.016 0.016 ↓ 0.0 0 1

Index Scan using session_2021_3_session_id_session_event_num_timestamp_key on session_2021_3 t2_9 (cost=0.14..0.16 rows=1 width=256) (actual time=0.016..0.016 rows=0 loops=1)

  • Index Cond: (session_id = i.session_id)
49. 0.011 0.011 ↓ 0.0 0 1

Index Scan using session_2021_4_session_id_session_event_num_timestamp_key on session_2021_4 t2_10 (cost=0.14..0.16 rows=1 width=256) (actual time=0.011..0.011 rows=0 loops=1)

  • Index Cond: (session_id = i.session_id)
50. 2.577 2.577 ↓ 0.0 0 1

Index Scan using session_def_session_id_session_event_num_timestamp_key on session_def t2_11 (cost=0.27..0.29 rows=1 width=152) (actual time=2.577..2.577 rows=0 loops=1)

  • Index Cond: (session_id = i.session_id)
51. 32.163 54.119 ↑ 1.3 14,570 1

Sort (cost=1,698.81..1,745.34 rows=18,609 width=96) (actual time=53.275..54.119 rows=14,570 loops=1)

  • Sort Key: d.model, (upper(d.retail_branding))
  • Sort Method: quicksort Memory: 2,518kB
52. 21.956 21.956 ↓ 1.0 18,647 1

Seq Scan on svc_all_devices d (cost=0.00..379.09 rows=18,609 width=96) (actual time=1.029..21.956 rows=18,647 loops=1)

53. 0.000 69,194.164 ↓ 0.0 0 1

Merge Left Join (cost=357,535.68..359,088.83 rows=10,761 width=655) (actual time=69,194.164..69,194.164 rows=0 loops=1)

  • Merge Cond: ((t2_12.device_model = d_1.model) AND ((upper(t2_12.device_brand)) = (upper(d_1.retail_branding))))
54.          

Initplan (for Merge Left Join)

55. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on apps_flyer_ids apps_flyer_ids_2 (cost=0.00..0.02 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=1)

56. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on apps_flyer_ids apps_flyer_ids_3 (cost=0.00..0.02 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=1)

57. 0.009 69,194.162 ↓ 0.0 0 1

Sort (cost=355,836.83..355,863.73 rows=10,761 width=424) (actual time=69,194.162..69,194.162 rows=0 loops=1)

  • Sort Key: t2_12.device_model, (upper(t2_12.device_brand))
  • Sort Method: quicksort Memory: 25kB
58. 79.457 69,194.153 ↓ 0.0 0 1

Hash Right Join (cost=316,611.34..355,116.19 rows=10,761 width=424) (actual time=69,194.153..69,194.153 rows=0 loops=1)

  • Hash Cond: (t3.installation_id = t2_12.installation_id)
  • Filter: ((COALESCE(array_length(($10)::text[], 1), 0) = 0) OR (t3.apps_flyer_u_i_d = ANY (($11)::text[])))
  • Rows Removed by Filter: 49,064
59. 5.865 2,978.576 ↑ 4.2 47,839 1

Subquery Scan on t3 (cost=90,381.55..94,643.52 rows=201,646 width=251) (actual time=2,928.432..2,978.576 rows=47,839 loops=1)

60. 10.115 2,972.711 ↑ 4.2 47,839 1

Unique (cost=90,381.55..92,627.06 rows=201,646 width=328) (actual time=2,928.418..2,972.711 rows=47,839 loops=1)

61.          

Initplan (for Unique)

62. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on t_load_from t_load_from_4 (cost=0.00..0.02 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1)

63. 255.361 2,962.594 ↑ 3.4 65,874 1

Sort (cost=90,381.53..90,942.90 rows=224,551 width=328) (actual time=2,928.416..2,962.594 rows=65,874 loops=1)

  • Sort Key: apps_flyer.apps_flyer_u_i_d, apps_flyer.installation_id, apps_flyer.session_id, apps_flyer."timestamp
  • Sort Method: external merge Disk: 19,080kB
64. 2,707.233 2,707.233 ↑ 3.4 65,874 1

Seq Scan on apps_flyer (cost=0.00..50,642.21 rows=224,551 width=328) (actual time=179.778..2,707.233 rows=65,874 loops=1)

  • Filter: (to_timestamp((("timestamp" / 1000))::double precision) >= ($13 - '1 mon'::interval))
  • Rows Removed by Filter: 614,674
65. 1,252.801 66,136.120 ↑ 4.2 47,923 1

Hash (cost=217,845.37..217,845.37 rows=200,594 width=210) (actual time=66,136.120..66,136.120 rows=47,923 loops=1)

  • Buckets: 32,768 Batches: 8 Memory Usage: 1,556kB
66. 728.223 64,883.319 ↑ 4.2 47,923 1

Merge Join (cost=46,227.78..217,845.37 rows=200,594 width=210) (actual time=741.011..64,883.319 rows=47,923 loops=1)

  • Merge Cond: (install.session_id = t2_12.session_id)
67. 55.296 737.283 ↑ 4.2 47,924 1

GroupAggregate (cost=46,227.22..50,239.12 rows=200,594 width=66) (actual time=663.673..737.283 rows=47,924 loops=1)

  • Group Key: install.session_id, install.device_id
68.          

Initplan (for GroupAggregate)

69. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on t_load_from t_load_from_3 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

70. 112.687 681.986 ↑ 4.2 47,924 1

Sort (cost=46,227.20..46,728.69 rows=200,596 width=66) (actual time=663.650..681.986 rows=47,924 loops=1)

  • Sort Key: install.session_id, install.device_id
  • Sort Method: quicksort Memory: 7,910kB
71. 569.299 569.299 ↑ 4.2 47,924 1

Seq Scan on install (cost=0.00..23,741.23 rows=200,596 width=66) (actual time=271.787..569.299 rows=47,924 loops=1)

  • Filter: (to_timestamp((("timestamp" / 1000))::double precision) >= ($12 - '1 mon'::interval))
  • Rows Removed by Filter: 553,264
72. 63,417.813 63,417.813 ↑ 1.0 1,806,686 1

Index Scan using session_pkey on session t2_12 (cost=0.55..158,576.00 rows=1,806,750 width=218) (actual time=3.345..63,417.813 rows=1,806,686 loops=1)

73. 0.000 0.000 ↓ 0.0 0

Sort (cost=1,698.81..1,745.34 rows=18,609 width=96) (never executed)

  • Sort Key: d_1.model, (upper(d_1.retail_branding))
74. 0.000 0.000 ↓ 0.0 0

Seq Scan on svc_all_devices d_1 (cost=0.00..379.09 rows=18,609 width=96) (never executed)

75. 0.000 1,142.274 ↓ 0.0 0 1

Nested Loop Left Join (cost=26,793.69..67,249.11 rows=653 width=643) (actual time=1,142.274..1,142.274 rows=0 loops=1)

  • Join Filter: (to_timestamp(((t1_2."timestamp" / 1000))::double precision) > '2017-04-17 21:00:00+00'::timestamp with time zone)
76.          

Initplan (for Nested Loop Left Join)

77. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on t_load_from t_load_from_5 (cost=0.00..0.02 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1)

78. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on apps_flyer_ids apps_flyer_ids_4 (cost=0.00..0.02 rows=1 width=32) (actual time=0.001..0.002 rows=1 loops=1)

79. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on apps_flyer_ids apps_flyer_ids_5 (cost=0.00..0.02 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=1)

80. 0.001 1,142.272 ↓ 0.0 0 1

Nested Loop (cost=26,793.35..65,454.86 rows=653 width=546) (actual time=1,142.272..1,142.272 rows=0 loops=1)

81. 100.214 1,142.271 ↓ 0.0 0 1

Hash Left Join (cost=26,792.79..64,298.83 rows=653 width=487) (actual time=1,142.271..1,142.271 rows=0 loops=1)

  • Hash Cond: (i_1.client_installation_id = t1_2.installation_id)
  • Filter: ((COALESCE(array_length(($15)::text[], 1), 0) = 0) OR (t1_2.apps_flyer_u_i_d = ANY (($16)::text[])))
  • Rows Removed by Filter: 19,038
82. 421.190 421.190 ↑ 6.8 18,963 1

Seq Scan on install i_1 (cost=0.00..17,565.31 rows=128,849 width=135) (actual time=396.533..421.190 rows=18,963 loops=1)

  • Filter: (to_timestamp((("timestamp" / 1000))::double precision) >= ($14 - '1 mon'::interval))
  • Rows Removed by Filter: 367,362
83. 160.115 620.867 ↓ 1.0 188,703 1

Hash (cost=14,856.13..14,856.13 rows=188,613 width=389) (actual time=620.867..620.867 rows=188,703 loops=1)

  • Buckets: 32,768 Batches: 16 Memory Usage: 5,216kB
84. 460.752 460.752 ↓ 1.0 188,703 1

Seq Scan on apps_flyer t1_2 (cost=0.00..14,856.13 rows=188,613 width=389) (actual time=1.409..460.752 rows=188,703 loops=1)

85. 0.000 0.000 ↓ 0.0 0

Index Scan using session_pkey on session t2_13 (cost=0.56..1.76 rows=1 width=183) (never executed)

  • Index Cond: (session_id = i_1.session_id)
86. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_svc_all_devices_1 on svc_all_devices d_2 (cost=0.29..2.61 rows=1 width=96) (never executed)

  • Index Cond: (model = t2_13.device_model)
  • Filter: (upper(t2_13.device_brand) = upper(retail_branding))
87. 0.000 133,613.556 ↓ 0.0 0 1

Unique (cost=213,684,308.05..214,622,045.61 rows=40,000 width=861) (actual time=133,613.556..133,613.556 rows=0 loops=1)

88.          

Initplan (for Unique)

89. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on t_load_from t_load_from_6 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1)

90. 0.122 0.122 ↑ 1.0 1 1

CTE Scan on t_load_to (cost=0.00..0.02 rows=1 width=4) (actual time=0.122..0.122 rows=1 loops=1)

91. 0.000 0.000 ↓ 0.0 0

CTE Scan on t_load_from t_load_from_7 (cost=0.00..0.02 rows=1 width=4) (never executed)

92. 0.008 133,613.555 ↓ 0.0 0 1

Sort (cost=213,684,307.99..213,996,887.18 rows=125,031,675 width=861) (actual time=133,613.555..133,613.555 rows=0 loops=1)

  • Sort Key: "*SELECT* 1".apps_flyer_id, "*SELECT* 1".tenant, "*SELECT* 1".install_ts, "*SELECT* 1".installation_id
  • Sort Method: quicksort Memory: 25kB
93. 0.000 133,613.547 ↓ 0.0 0 1

Hash Left Join (cost=1,099,338.43..113,516,554.22 rows=125,031,675 width=861) (actual time=133,613.547..133,613.547 rows=0 loops=1)

  • Hash Cond: ("*SELECT* 1".app_id = apps.app_id)
94. 0.000 133,613.547 ↓ 0.0 0 1

Hash Left Join (cost=1,099,337.07..69,186,572.49 rows=125,031,675 width=821) (actual time=133,613.547..133,613.547 rows=0 loops=1)

  • Hash Cond: (upper("*SELECT* 1".campaign) = upper(cmp.campaign))
95. 0.001 133,613.547 ↓ 0.0 0 1

Hash Left Join (cost=1,099,333.18..67,879,987.59 rows=125,031,675 width=789) (actual time=133,613.546..133,613.547 rows=0 loops=1)

  • Hash Cond: (("*SELECT* 1".app_id = etl_external_x_internal_build_version.app_id) AND ("*SELECT* 1".app_version = etl_external_x_internal_build_version.app_version_build))
96. 0.000 133,613.546 ↓ 0.0 0 1

Hash Left Join (cost=1,099,320.41..65,994,833.14 rows=125,031,675 width=783) (actual time=133,613.546..133,613.546 rows=0 loops=1)

  • Hash Cond: (replace("*SELECT* 1".country_code, 'UK'::text, 'GB'::text) = c.country_code)
97. 0.001 133,613.546 ↓ 0.0 0 1

Hash Anti Join (cost=1,099,298.71..65,648,028.87 rows=125,031,675 width=777) (actual time=133,613.545..133,613.546 rows=0 loops=1)

  • Hash Cond: (("*SELECT* 1".apps_flyer_id = etl_tbl_installs_2018.apps_flyer_id) AND ("*SELECT* 1".tenant = etl_tbl_installs_2018.tenant))
98. 0.003 133,613.545 ↓ 0.0 0 1

Append (cost=862,757.98..30,298,181.92 rows=166,708,900 width=777) (actual time=133,613.545..133,613.545 rows=0 loops=1)

99. 0.001 37,680.266 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1 (cost=862,757.98..8,898,010.83 rows=32,628,000 width=914) (actual time=37,680.266..37,680.266 rows=0 loops=1)

100. 0.000 37,680.265 ↓ 0.0 0 1

Merge Right Join (cost=862,757.98..8,571,730.83 rows=32,628,000 width=914) (actual time=37,680.265..37,680.265 rows=0 loops=1)

  • Merge Cond: (ad.ad_id = c_1.ad_i_d)
101.          

Initplan (for Merge Right Join)

102. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on t_load_from t_load_from_8 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1)

103. 0.033 0.033 ↑ 1.0 1 1

CTE Scan on apps_flyer_ids apps_flyer_ids_6 (cost=0.00..0.02 rows=1 width=32) (actual time=0.033..0.033 rows=1 loops=1)

104. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on apps_flyer_ids apps_flyer_ids_7 (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.001 rows=1 loops=1)

105. 5.519 14,462.227 ↑ 40,000.0 1 1

Sort (cost=3,857.54..3,957.54 rows=40,000 width=40) (actual time=14,462.227..14,462.227 rows=1 loops=1)

  • Sort Key: ad.ad_id
  • Sort Method: quicksort Memory: 1,901kB
106. 14,456.708 14,456.708 ↑ 1.7 23,289 1

CTE Scan on ad_performance_report ad (cost=0.00..800.00 rows=40,000 width=40) (actual time=14,445.257..14,456.708 rows=23,289 loops=1)

107. 0.003 23,218.028 ↓ 0.0 0 1

Materialize (cost=858,900.38..859,716.08 rows=163,140 width=1,065) (actual time=23,218.028..23,218.028 rows=0 loops=1)

108. 0.010 23,218.025 ↓ 0.0 0 1

Sort (cost=858,900.38..859,308.23 rows=163,140 width=1,065) (actual time=23,218.025..23,218.025 rows=0 loops=1)

  • Sort Key: c_1.ad_i_d
  • Sort Method: quicksort Memory: 25kB
109. 0.009 23,218.015 ↓ 0.0 0 1

Hash Right Join (cost=716,829.05..800,030.57 rows=163,140 width=1,065) (actual time=23,218.015..23,218.015 rows=0 loops=1)

  • Hash Cond: (upper(c_1.google_click_i_d) = upper(COALESCE("substring"((ro.http_referrer || '&'::text), 'gclid=([^&]*)&'::text), "substring"((ro.click_url || '&'::text), 'gclid=([^&]*)&'::text))))
110. 0.000 0.000 ↓ 0.0 0

CTE Scan on click_performance_report c_1 (cost=0.00..40,785.06 rows=2,039,253 width=104) (never executed)

111. 0.000 23,218.006 ↓ 0.0 0 1

Hash (cost=716,828.85..716,828.85 rows=16 width=961) (actual time=23,218.006..23,218.006 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
112. 0.001 23,218.006 ↓ 0.0 0 1

Hash Left Join (cost=480,206.94..716,828.85 rows=16 width=961) (actual time=23,218.006..23,218.006 rows=0 loops=1)

  • Hash Cond: (ro.appsflyer_device_id = s.apps_flyer_u_i_d)
  • Filter: ((COALESCE(s.installation_id, ro.customer_user_id) IS NOT NULL) OR ((ro.app_id = 'id1478283282'::text) AND ((to_timestamp(ro.install_time, 'YYYY-MM-DD HH24:MI:SS'::text))::date >= '2019-10-10'::date)))
113. 0.002 23,218.005 ↓ 0.0 0 1

Hash Left Join (cost=477,305.46..712,631.75 rows=16 width=833) (actual time=23,218.005..23,218.005 rows=0 loops=1)

  • Hash Cond: (COALESCE(ro.campaign, ((r.referrer)::jsonb ->> 'iad-campaign-name'::text)) = c_2.campaign_id)
114. 0.001 23,218.003 ↓ 0.0 0 1

Nested Loop Left Join (cost=464,392.70..699,718.94 rows=16 width=810) (actual time=23,218.003..23,218.003 rows=0 loops=1)

115. 0.003 23,218.002 ↓ 0.0 0 1

Hash Right Join (cost=464,392.14..699,706.73 rows=16 width=709) (actual time=23,218.002..23,218.002 rows=0 loops=1)

  • Hash Cond: (a.apps_flyer_u_i_d = ro.appsflyer_device_id)
116. 0.000 0.000 ↓ 0.0 0

Seq Scan on apps_flyer a (cost=0.00..227,404.43 rows=2,109,343 width=86) (never executed)

117. 0.001 23,217.999 ↓ 0.0 0 1

Hash (cost=464,391.94..464,391.94 rows=16 width=650) (actual time=23,217.999..23,217.999 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
118. 23,217.998 23,217.998 ↓ 0.0 0 1

Seq Scan on push_report_install ro (cost=0.00..464,391.94 rows=16 width=650) (actual time=23,217.998..23,217.998 rows=0 loops=1)

  • Filter: ((event_type = 'install'::text) AND ((to_timestamp(install_time, 'YYYY-MM-DD HH24:MI:SS'::text))::date >= $23) AND ((to_timestamp(install_time, 'YYYY-MM-DD HH24:MI:SS'::text))::date <= $24) AND ((NULLIF(install_time, ''::text))::date >= $26) AND (app_id = ANY ('{com.capital.trading,id1230088754,com.capital.investmate,id1229995329,id1487443266,id1490235342,com.currency.exchange.prod2,id1458917114,id1478283282}'::text[])) AND ((COALESCE(array_length(($27)::text[], 1), 0) = 0) OR (appsflyer_device_id = ANY (($28)::text[]))))
  • Rows Removed by Filter: 2,839,347
119. 0.000 0.000 ↓ 0.0 0

Index Scan using install_referrer_success_pkey on install_referrer_success r (cost=0.56..0.75 rows=1 width=232) (never executed)

  • Index Cond: (session_id = a.session_id)
  • Filter: (referrer ~~ '%iad-keyword%'::text)
120. 0.000 0.000 ↓ 0.0 0

Hash (cost=12,910.26..12,910.26 rows=200 width=33) (never executed)

121. 0.000 0.000 ↓ 0.0 0

Subquery Scan on c_2 (cost=12,594.91..12,910.26 rows=200 width=33) (never executed)

122. 0.000 0.000 ↓ 0.0 0

Unique (cost=12,594.91..12,908.26 rows=200 width=44) (never executed)

123.          

Initplan (for Unique)

124. 0.000 0.000 ↓ 0.0 0

CTE Scan on t_load_from t_load_from_9 (cost=0.00..0.02 rows=1 width=4) (never executed)

125. 0.000 0.000 ↓ 0.0 0

Sort (cost=12,594.89..12,751.57 rows=62,670 width=44) (never executed)

  • Sort Key: adwords_campaign_performance_report.campaign_id, adwords_campaign_performance_report.date DESC
126. 0.000 0.000 ↓ 0.0 0

Append (cost=0.00..7,601.51 rows=62,670 width=44) (never executed)

127. 0.000 0.000 ↓ 0.0 0

Seq Scan on adwords_campaign_performance_report (cost=0.00..7,269.82 rows=62,514 width=44) (never executed)

  • Filter: ((date)::date >= ($29 - '2 mons'::interval))
128. 0.000 0.000 ↓ 0.0 0

Seq Scan on adwords_campaign_performance_report_2475468088 (cost=0.00..18.34 rows=156 width=46) (never executed)

  • Filter: ((date)::date >= ($29 - '2 mons'::interval))
129. 0.000 0.000 ↓ 0.0 0

Hash (cost=1,055.68..1,055.68 rows=52,784 width=160) (never executed)

130. 0.000 0.000 ↓ 0.0 0

CTE Scan on sdk_installs s (cost=0.00..1,055.68 rows=52,784 width=160) (never executed)

131. 0.001 95,933.276 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=452,617.07..20,566,626.58 rows=134,080,900 width=744) (actual time=95,933.276..95,933.276 rows=0 loops=1)

132. 0.007 95,933.275 ↓ 0.0 0 1

Merge Right Join (cost=452,617.07..19,225,817.58 rows=134,080,900 width=744) (actual time=95,933.275..95,933.275 rows=0 loops=1)

  • Merge Cond: (ad_1.ad_id = c1.ad_i_d)
133. 4.406 6.189 ↑ 40,000.0 1 1

Sort (cost=3,857.54..3,957.54 rows=40,000 width=40) (actual time=6.189..6.189 rows=1 loops=1)

  • Sort Key: ad_1.ad_id
  • Sort Method: quicksort Memory: 1,901kB
134. 1.783 1.783 ↑ 1.7 23,289 1

CTE Scan on ad_performance_report ad_1 (cost=0.00..800.00 rows=40,000 width=40) (actual time=0.013..1.783 rows=23,289 loops=1)

135. 0.002 95,927.079 ↓ 0.0 0 1

Materialize (cost=448,759.53..452,111.55 rows=670,404 width=787) (actual time=95,927.079..95,927.079 rows=0 loops=1)

136. 0.005 95,927.077 ↓ 0.0 0 1

Sort (cost=448,759.53..450,435.54 rows=670,404 width=787) (actual time=95,927.077..95,927.077 rows=0 loops=1)

  • Sort Key: c1.ad_i_d
  • Sort Method: quicksort Memory: 25kB
137. 0.007 95,927.072 ↓ 0.0 0 1

Hash Right Join (cost=3,945.27..110,088.38 rows=670,404 width=787) (actual time=95,927.072..95,927.072 rows=0 loops=1)

  • Hash Cond: (upper(c1.google_click_i_d) = upper(t1.gclid))
138. 0.000 0.000 ↓ 0.0 0

CTE Scan on click_performance_report c1 (cost=0.00..40,785.06 rows=2,039,253 width=104) (never executed)

139. 0.001 95,927.065 ↓ 0.0 0 1

Hash (cost=3,944.45..3,944.45 rows=66 width=683) (actual time=95,927.065..95,927.065 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
140. 0.000 95,927.064 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.67..3,944.45 rows=66 width=683) (actual time=95,927.064..95,927.064 rows=0 loops=1)

141. 0.001 95,927.064 ↓ 0.0 0 1

Nested Loop Anti Join (cost=1.11..3,760.47 rows=66 width=680) (actual time=95,927.064..95,927.064 rows=0 loops=1)

142. 0.001 95,927.063 ↓ 0.0 0 1

Nested Loop Anti Join (cost=0.56..3,561.80 rows=132 width=680) (actual time=95,927.063..95,927.063 rows=0 loops=1)

143. 95,927.062 95,927.062 ↓ 0.0 0 1

CTE Scan on sdk_installs t1 (cost=0.00..3,167.04 rows=263 width=680) (actual time=95,927.062..95,927.062 rows=0 loops=1)

  • Filter: (((to_timestamp(((install_ts / 1000))::double precision))::date >= $23) AND ((to_timestamp(((install_ts / 1000))::double precision))::date <= $24) AND ((installation_id IS NOT NULL) OR ((app_id = 'id1478283282'::text) AND ((to_timestamp(((install_ts / 1000))::double precision))::date >= '2019-10-10'::date))))
  • Rows Removed by Filter: 1
144. 0.000 0.000 ↓ 0.0 0

Index Only Scan using ix_push_report_install_customer_user_id on push_report_install rr_1 (cost=0.56..1.69 rows=1 width=36) (never executed)

  • Index Cond: (customer_user_id = t1.installation_id)
  • Heap Fetches: 0
145. 0.000 0.000 ↓ 0.0 0

Index Only Scan using push_report_install_appsflyer_device_id_id_event_type_key on push_report_install rr (cost=0.56..1.69 rows=1 width=28) (never executed)

  • Index Cond: (appsflyer_device_id = t1.apps_flyer_u_i_d)
  • Heap Fetches: 0
146. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_ip_country_session_1 on etl_tbl_first_ip_country_session c_3 (cost=0.56..2.78 rows=1 width=24) (never executed)

  • Index Cond: (device_id = t1.device_id)
147. 0.000 0.000 ↓ 0.0 0

Hash (cost=213,012.50..213,012.50 rows=1,031,348 width=40) (never executed)

148. 0.000 0.000 ↓ 0.0 0

Append (cost=0.00..213,012.50 rows=1,031,348 width=40) (never executed)

149. 0.000 0.000 ↓ 0.0 0

Seq Scan on etl_tbl_installs_2018 (cost=0.00..94,902.09 rows=444,069 width=43) (never executed)

  • Filter: ((install_ts)::date < $25)
150. 0.000 0.000 ↓ 0.0 0

Seq Scan on etl_tbl_installs_2019 (cost=0.00..28,144.92 rows=148,509 width=39) (never executed)

  • Filter: ((install_ts)::date < $25)
151. 0.000 0.000 ↓ 0.0 0

Seq Scan on etl_tbl_installs_2020_1 (cost=0.00..27,782.72 rows=146,416 width=37) (never executed)

  • Filter: ((install_ts)::date < $25)
152. 0.000 0.000 ↓ 0.0 0

Seq Scan on etl_tbl_installs_2020_2 (cost=0.00..43,065.66 rows=221,881 width=37) (never executed)

  • Filter: ((install_ts)::date < $25)
153. 0.000 0.000 ↓ 0.0 0

Seq Scan on etl_tbl_installs_2020_3 (cost=0.00..13,893.62 rows=70,325 width=39) (never executed)

  • Filter: ((install_ts)::date < $25)
154. 0.000 0.000 ↓ 0.0 0

Seq Scan on etl_tbl_installs_2020_4 (cost=0.00..11.05 rows=23 width=64) (never executed)

  • Filter: ((install_ts)::date < $25)
155. 0.000 0.000 ↓ 0.0 0

Seq Scan on etl_tbl_installs_2021_1 (cost=0.00..11.05 rows=23 width=64) (never executed)

  • Filter: ((install_ts)::date < $25)
156. 0.000 0.000 ↓ 0.0 0

Seq Scan on etl_tbl_installs_2021_2 (cost=0.00..11.05 rows=23 width=64) (never executed)

  • Filter: ((install_ts)::date < $25)
157. 0.000 0.000 ↓ 0.0 0

Seq Scan on etl_tbl_installs_2021_3 (cost=0.00..11.05 rows=23 width=64) (never executed)

  • Filter: ((install_ts)::date < $25)
158. 0.000 0.000 ↓ 0.0 0

Seq Scan on etl_tbl_installs_2021_4 (cost=0.00..11.05 rows=23 width=64) (never executed)

  • Filter: ((install_ts)::date < $25)
159. 0.000 0.000 ↓ 0.0 0

Seq Scan on etl_tbl_installs_def (cost=0.00..11.50 rows=33 width=42) (never executed)

  • Filter: ((install_ts)::date < $25)
160. 0.000 0.000 ↓ 0.0 0

Hash (cost=15.20..15.20 rows=520 width=32) (never executed)

161. 0.000 0.000 ↓ 0.0 0

Seq Scan on scv_geo_country_codes c (cost=0.00..15.20 rows=520 width=32) (never executed)

162. 0.000 0.000 ↓ 0.0 0

Hash (cost=7.51..7.51 rows=351 width=29) (never executed)

163. 0.000 0.000 ↓ 0.0 0

Seq Scan on etl_tbl_external_x_internal_build_version etl_external_x_internal_build_version (cost=0.00..7.51 rows=351 width=29) (never executed)

164. 0.000 0.000 ↓ 0.0 0

Hash (cost=2.84..2.84 rows=84 width=64) (never executed)

165. 0.000 0.000 ↓ 0.0 0

Seq Scan on scv_campaigns_source_changes cmp (cost=0.00..2.84 rows=84 width=64) (never executed)

166. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.16..1.16 rows=16 width=97) (never executed)

167. 0.000 0.000 ↓ 0.0 0

Seq Scan on scv_appsflyer_app_id apps (cost=0.00..1.16 rows=16 width=97) (never executed)

Execution time : 133,664.711 ms