explain.depesz.com

PostgreSQL's explain analyze made readable

Result: T5xp

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 32,286.066 ↓ 0.0 0 1

Subquery Scan on main (cost=216,037,505.49..216,977,143.05 rows=40,000 width=1,021) (actual time=32,286.066..32,286.066 rows=0 loops=1)

  • Functions: 370
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 48.135 ms, Inlining 29.051 ms, Optimization 4369.566 ms, Emission 2961.594 ms, Total 7408.345 ms
2.          

CTE t_load_from

3. 0.160 0.160 ↑ 1.0 1 1

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

4.          

CTE t_load_to

5. 0.103 0.103 ↑ 1.0 1 1

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

6.          

CTE apps_flyer_ids

7. 0.023 0.023 ↑ 1.0 1 1

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

8.          

CTE ad_performance_report

9. 1,149.969 12,397.645 ↓ 7.2 286,172 1

HashAggregate (cost=267,165.73..267,765.73 rows=40,000 width=17) (actual time=12,333.514..12,397.645 rows=286,172 loops=1)

  • Group Key: (ad_performance_report.ad_id)::bigint, ad_performance_report.ad_name
10. 464.898 11,247.676 ↓ 1.0 3,617,185 1

Result (cost=0.00..249,160.34 rows=3,601,078 width=17) (actual time=7,378.122..11,247.676 rows=3,617,185 loops=1)

11. 218.334 10,782.778 ↓ 1.0 3,617,185 1

Append (cost=0.00..195,144.17 rows=3,601,078 width=22) (actual time=7,378.091..10,782.778 rows=3,617,185 loops=1)

12. 10,564.131 10,564.131 ↓ 1.0 3,616,547 1

Seq Scan on ad_performance_report (cost=0.00..177,099.40 rows=3,600,440 width=22) (actual time=7,378.090..10,564.131 rows=3,616,547 loops=1)

13. 0.313 0.313 ↑ 1.0 638 1

Seq Scan on ad_performance_report_2475468088 (cost=0.00..39.38 rows=638 width=45) (actual time=0.025..0.313 rows=638 loops=1)

14.          

CTE click_performance_report

15. 0.000 0.000 ↓ 0.0 0

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

16.          

Initplan (for Result)

17. 0.000 0.000 ↓ 0.0 0

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

18. 0.000 0.000 ↓ 0.0 0

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

19. 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 >= ($4 - '1 mon'::interval))
20. 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 >= ($4 - '1 mon'::interval))
21.          

CTE sdk_installs

22. 0.005 14,515.021 ↑ 52,783.0 1 1

Append (cost=1,189,075.76..1,623,235.96 rows=52,783 width=645) (actual time=11,201.400..14,515.021 rows=1 loops=1)

23. 2.046 11,201.399 ↑ 41,370.0 1 1

Merge Left Join (cost=1,189,075.76..1,196,113.61 rows=41,370 width=642) (actual time=11,201.399..11,201.399 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)
24.          

Initplan (for Merge Left Join)

25. 0.002 0.002 ↑ 1.0 1 1

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

26. 0.002 0.002 ↑ 1.0 1 1

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

27. 0.001 0.001 ↑ 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.001 rows=1 loops=1)

28. 0.011 11,159.906 ↑ 41,370.0 1 1

Sort (cost=1,187,376.89..1,187,480.32 rows=41,370 width=922) (actual time=11,159.905..11,159.906 rows=1 loops=1)

  • Sort Key: t2.device_model, (upper(t2.device_brand))
  • Sort Method: quicksort Memory: 27kB
29. 0.016 11,159.895 ↑ 41,370.0 1 1

Nested Loop (cost=430,924.20..1,174,348.18 rows=41,370 width=922) (actual time=10,989.966..11,159.895 rows=1 loops=1)

30. 0.010 11,159.566 ↑ 5,465.0 1 1

Nested Loop Left Join (cost=430,923.78..1,139,727.39 rows=5,465 width=860) (actual time=10,989.727..11,159.566 rows=1 loops=1)

31. 1,379.293 11,159.496 ↑ 5,465.0 1 1

Hash Left Join (cost=430,923.22..1,135,556.01 rows=5,465 width=759) (actual time=10,989.658..11,159.496 rows=1 loops=1)

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

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

  • Filter: (to_timestamp((("timestamp" / 1000))::double precision) >= ($6 - '1 mon'::interval))
  • Rows Removed by Filter: 2,627,876
33. 7,451.050 9,173.589 ↓ 1.0 2,112,583 1

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

  • Buckets: 16,384 Batches: 256 Memory Usage: 6,035kB
34. 1,722.539 1,722.539 ↓ 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=0.011..1,722.539 rows=2,112,583 loops=1)

35. 0.060 0.060 ↓ 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=0.060..0.060 rows=0 loops=1)

  • Index Cond: (session_id = t1_1.session_id)
  • Filter: (referrer ~~ '%iad-keyword%'::text)
36. 0.027 0.313 ↑ 12.0 1 1

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

37. 0.029 0.029 ↓ 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=0.029..0.029 rows=0 loops=1)

  • Index Cond: (session_id = i.session_id)
38. 0.036 0.036 ↓ 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=0.036..0.036 rows=0 loops=1)

  • Index Cond: (session_id = i.session_id)
39. 0.032 0.032 ↓ 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=0.032..0.032 rows=0 loops=1)

  • Index Cond: (session_id = i.session_id)
40. 0.035 0.035 ↓ 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=0.035..0.035 rows=0 loops=1)

  • Index Cond: (session_id = i.session_id)
41. 0.033 0.033 ↓ 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=0.033..0.033 rows=0 loops=1)

  • Index Cond: (session_id = i.session_id)
42. 0.047 0.047 ↑ 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=0.046..0.047 rows=1 loops=1)

  • Index Cond: (session_id = i.session_id)
43. 0.011 0.011 ↓ 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.010..0.011 rows=0 loops=1)

  • Index Cond: (session_id = i.session_id)
44. 0.010 0.010 ↓ 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=0.010..0.010 rows=0 loops=1)

  • Index Cond: (session_id = i.session_id)
45. 0.015 0.015 ↓ 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.015..0.015 rows=0 loops=1)

  • Index Cond: (session_id = i.session_id)
46. 0.010 0.010 ↓ 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.010..0.010 rows=0 loops=1)

  • Index Cond: (session_id = i.session_id)
47. 0.010 0.010 ↓ 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.010..0.010 rows=0 loops=1)

  • Index Cond: (session_id = i.session_id)
48. 0.018 0.018 ↓ 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=0.018..0.018 rows=0 loops=1)

  • Index Cond: (session_id = i.session_id)
49. 32.644 39.442 ↑ 1.3 14,570 1

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

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

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

51. 0.000 2,990.432 ↓ 0.0 0 1

Merge Left Join (cost=357,528.48..359,081.50 rows=10,760 width=655) (actual time=2,990.432..2,990.432 rows=0 loops=1)

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

Initplan (for Merge Left Join)

53. 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)

54. 0.000 0.000 ↑ 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.000..0.000 rows=1 loops=1)

55. 0.008 2,990.430 ↓ 0.0 0 1

Sort (cost=355,829.63..355,856.53 rows=10,760 width=424) (actual time=2,990.430..2,990.430 rows=0 loops=1)

  • Sort Key: t2_12.device_model, (upper(t2_12.device_brand))
  • Sort Method: quicksort Memory: 25kB
56. 74.074 2,990.422 ↓ 0.0 0 1

Hash Right Join (cost=316,604.59..355,109.06 rows=10,760 width=424) (actual time=2,990.422..2,990.422 rows=0 loops=1)

  • Hash Cond: (t3.installation_id = t2_12.installation_id)
  • Filter: ((COALESCE(array_length(($9)::text[], 1), 0) = 0) OR (t3.apps_flyer_u_i_d = ANY (($10)::text[])))
  • Rows Removed by Filter: 49,057
57. 4.262 448.690 ↑ 4.2 47,833 1

Subquery Scan on t3 (cost=90,381.55..94,643.52 rows=201,646 width=251) (actual time=406.479..448.690 rows=47,833 loops=1)

58. 8.941 444.428 ↑ 4.2 47,833 1

Unique (cost=90,381.55..92,627.06 rows=201,646 width=328) (actual time=406.469..444.428 rows=47,833 loops=1)

59.          

Initplan (for Unique)

60. 0.002 0.002 ↑ 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.002 rows=1 loops=1)

61. 199.192 435.485 ↑ 3.4 65,858 1

Sort (cost=90,381.53..90,942.90 rows=224,551 width=328) (actual time=406.466..435.485 rows=65,858 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,072kB
62. 236.293 236.293 ↑ 3.4 65,858 1

Seq Scan on apps_flyer (cost=0.00..50,642.21 rows=224,551 width=328) (actual time=9.365..236.293 rows=65,858 loops=1)

  • Filter: (to_timestamp((("timestamp" / 1000))::double precision) >= ($12 - '1 mon'::interval))
  • Rows Removed by Filter: 614,674
63. 34.250 2,467.658 ↑ 4.2 47,917 1

Hash (cost=217,838.87..217,838.87 rows=200,574 width=210) (actual time=2,467.658..2,467.658 rows=47,917 loops=1)

  • Buckets: 32,768 Batches: 8 Memory Usage: 1,556kB
64. 399.943 2,433.408 ↑ 4.2 47,917 1

Merge Join (cost=46,223.54..217,838.87 rows=200,574 width=210) (actual time=189.060..2,433.408 rows=47,917 loops=1)

  • Merge Cond: (install.session_id = t2_12.session_id)
65. 22.062 222.169 ↑ 4.2 47,918 1

GroupAggregate (cost=46,222.99..50,234.49 rows=200,574 width=66) (actual time=188.728..222.169 rows=47,918 loops=1)

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

Initplan (for GroupAggregate)

67. 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)

68. 87.402 200.106 ↑ 4.2 47,918 1

Sort (cost=46,222.97..46,724.41 rows=200,576 width=66) (actual time=188.711..200.106 rows=47,918 loops=1)

  • Sort Key: install.session_id, install.device_id
  • Sort Method: quicksort Memory: 7,909kB
69. 112.704 112.704 ↑ 4.2 47,918 1

Seq Scan on install (cost=0.00..23,738.90 rows=200,576 width=66) (actual time=54.153..112.704 rows=47,918 loops=1)

  • Filter: (to_timestamp((("timestamp" / 1000))::double precision) >= ($11 - '1 mon'::interval))
  • Rows Removed by Filter: 553,264
70. 1,811.296 1,811.296 ↑ 1.0 1,806,668 1

Index Scan using session_pkey on session t2_12 (cost=0.55..158,574.63 rows=1,806,732 width=218) (actual time=0.058..1,811.296 rows=1,806,668 loops=1)

71. 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))
72. 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)

73. 0.000 323.185 ↓ 0.0 0 1

Nested Loop Left Join (cost=26,793.69..67,249.11 rows=653 width=643) (actual time=323.185..323.185 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)
74.          

Initplan (for Nested Loop Left Join)

75. 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)

76. 0.001 0.001 ↑ 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.001 rows=1 loops=1)

77. 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.000..0.001 rows=1 loops=1)

78. 0.001 323.184 ↓ 0.0 0 1

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

79. 68.565 323.183 ↓ 0.0 0 1

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

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

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

  • Filter: (to_timestamp((("timestamp" / 1000))::double precision) >= ($13 - '1 mon'::interval))
  • Rows Removed by Filter: 367,362
81. 93.935 166.090 ↓ 1.0 188,703 1

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

  • Buckets: 32,768 Batches: 16 Memory Usage: 5,216kB
82. 72.155 72.155 ↓ 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=0.022..72.155 rows=188,703 loops=1)

83. 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)
84. 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))
85. 0.000 32,286.065 ↓ 0.0 0 1

Unique (cost=213,698,540.74..214,636,278.31 rows=40,000 width=861) (actual time=32,286.064..32,286.065 rows=0 loops=1)

86.          

Initplan (for Unique)

87. 0.162 0.162 ↑ 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.162..0.162 rows=1 loops=1)

88. 0.106 0.106 ↑ 1.0 1 1

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

89. 0.000 0.000 ↓ 0.0 0

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

90. 0.009 32,286.064 ↓ 0.0 0 1

Sort (cost=213,698,540.68..214,011,119.87 rows=125,031,675 width=861) (actual time=32,286.064..32,286.064 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
91. 0.001 32,286.055 ↓ 0.0 0 1

Hash Left Join (cost=1,113,571.19..113,530,786.92 rows=125,031,675 width=861) (actual time=32,286.055..32,286.055 rows=0 loops=1)

  • Hash Cond: ("*SELECT* 1".app_id = apps.app_id)
92. 0.000 32,286.054 ↓ 0.0 0 1

Hash Left Join (cost=1,113,569.83..69,200,805.18 rows=125,031,675 width=821) (actual time=32,286.054..32,286.054 rows=0 loops=1)

  • Hash Cond: (upper("*SELECT* 1".campaign) = upper(cmp.campaign))
93. 0.001 32,286.054 ↓ 0.0 0 1

Hash Left Join (cost=1,113,565.94..67,894,220.29 rows=125,031,675 width=789) (actual time=32,286.054..32,286.054 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))
94. 0.001 32,286.053 ↓ 0.0 0 1

Hash Left Join (cost=1,113,553.16..66,009,065.84 rows=125,031,675 width=783) (actual time=32,286.053..32,286.053 rows=0 loops=1)

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

Hash Anti Join (cost=1,113,531.46..65,662,261.57 rows=125,031,675 width=777) (actual time=32,286.052..32,286.052 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))
96. 0.003 32,286.051 ↓ 0.0 0 1

Append (cost=876,990.74..30,312,414.61 rows=166,708,900 width=777) (actual time=32,286.051..32,286.051 rows=0 loops=1)

97. 0.000 17,635.052 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1 (cost=876,990.74..8,912,243.59 rows=32,628,000 width=914) (actual time=17,635.052..17,635.052 rows=0 loops=1)

98. 0.000 17,635.052 ↓ 0.0 0 1

Merge Right Join (cost=876,990.74..8,585,963.59 rows=32,628,000 width=914) (actual time=17,635.052..17,635.052 rows=0 loops=1)

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

Initplan (for Merge Right Join)

100. 0.001 0.001 ↑ 1.0 1 1

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

101. 0.024 0.024 ↑ 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.024..0.024 rows=1 loops=1)

102. 0.000 0.000 ↑ 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.000 rows=1 loops=1)

103. 103.801 12,578.050 ↑ 40,000.0 1 1

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

  • Sort Key: ad.ad_id
  • Sort Method: external merge Disk: 9,584kB
104. 12,474.249 12,474.249 ↓ 7.2 286,172 1

CTE Scan on ad_performance_report ad (cost=0.00..800.00 rows=40,000 width=40) (actual time=12,333.519..12,474.249 rows=286,172 loops=1)

105. 0.003 5,056.986 ↓ 0.0 0 1

Materialize (cost=873,133.13..873,948.83 rows=163,140 width=1,065) (actual time=5,056.986..5,056.986 rows=0 loops=1)

106. 0.009 5,056.983 ↓ 0.0 0 1

Sort (cost=873,133.13..873,540.98 rows=163,140 width=1,065) (actual time=5,056.983..5,056.983 rows=0 loops=1)

  • Sort Key: c_1.ad_i_d
  • Sort Method: quicksort Memory: 25kB
107. 0.009 5,056.974 ↓ 0.0 0 1

Hash Right Join (cost=731,061.81..814,263.33 rows=163,140 width=1,065) (actual time=5,056.974..5,056.974 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))))
108. 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)

109. 0.000 5,056.965 ↓ 0.0 0 1

Hash (cost=731,061.61..731,061.61 rows=16 width=961) (actual time=5,056.965..5,056.965 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
110. 0.001 5,056.965 ↓ 0.0 0 1

Hash Left Join (cost=494,439.70..731,061.61 rows=16 width=961) (actual time=5,056.965..5,056.965 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)))
111. 0.001 5,056.964 ↓ 0.0 0 1

Hash Left Join (cost=491,538.25..726,864.54 rows=16 width=833) (actual time=5,056.964..5,056.964 rows=0 loops=1)

  • Hash Cond: (COALESCE(ro.campaign, ((r.referrer)::jsonb ->> 'iad-campaign-name'::text)) = c_2.campaign_id)
112. 0.000 5,056.963 ↓ 0.0 0 1

Nested Loop Left Join (cost=464,387.03..699,713.27 rows=16 width=810) (actual time=5,056.963..5,056.963 rows=0 loops=1)

113. 0.005 5,056.963 ↓ 0.0 0 1

Hash Right Join (cost=464,386.47..699,701.06 rows=16 width=709) (actual time=5,056.962..5,056.963 rows=0 loops=1)

  • Hash Cond: (a.apps_flyer_u_i_d = ro.appsflyer_device_id)
114. 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)

115. 0.001 5,056.958 ↓ 0.0 0 1

Hash (cost=464,386.27..464,386.27 rows=16 width=650) (actual time=5,056.958..5,056.958 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
116. 5,056.957 5,056.957 ↓ 0.0 0 1

Seq Scan on push_report_install ro (cost=0.00..464,386.27 rows=16 width=650) (actual time=5,056.957..5,056.957 rows=0 loops=1)

  • Filter: ((event_type = 'install'::text) AND ((to_timestamp(install_time, 'YYYY-MM-DD HH24:MI:SS'::text))::date >= $22) AND ((to_timestamp(install_time, 'YYYY-MM-DD HH24:MI:SS'::text))::date <= $23) AND ((NULLIF(install_time, ''::text))::date >= $25) 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(($26)::text[], 1), 0) = 0) OR (appsflyer_device_id = ANY (($27)::text[]))))
  • Rows Removed by Filter: 2,839,314
117. 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)
118. 0.000 0.000 ↓ 0.0 0

Hash (cost=27,148.72..27,148.72 rows=200 width=33) (never executed)

119. 0.000 0.000 ↓ 0.0 0

Subquery Scan on c_2 (cost=26,206.68..27,148.72 rows=200 width=33) (never executed)

120. 0.000 0.000 ↓ 0.0 0

Unique (cost=26,206.68..27,146.72 rows=200 width=44) (never executed)

121. 0.000 0.000 ↓ 0.0 0

Sort (cost=26,206.68..26,676.70 rows=188,008 width=44) (never executed)

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

Append (cost=0.00..6,348.12 rows=188,008 width=44) (never executed)

123. 0.000 0.000 ↓ 0.0 0

Seq Scan on adwords_campaign_performance_report (cost=0.00..5,394.41 rows=187,541 width=44) (never executed)

124. 0.000 0.000 ↓ 0.0 0

Seq Scan on adwords_campaign_performance_report_2475468088 (cost=0.00..13.67 rows=467 width=46) (never executed)

125. 0.000 0.000 ↓ 0.0 0

Hash (cost=1,055.66..1,055.66 rows=52,783 width=160) (never executed)

126. 0.000 0.000 ↓ 0.0 0

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

127. 0.001 14,650.996 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=452,617.01..20,566,626.52 rows=134,080,900 width=744) (actual time=14,650.996..14,650.996 rows=0 loops=1)

128. 0.013 14,650.995 ↓ 0.0 0 1

Merge Right Join (cost=452,617.01..19,225,817.52 rows=134,080,900 width=744) (actual time=14,650.995..14,650.995 rows=0 loops=1)

  • Merge Cond: (ad_1.ad_id = c1.ad_i_d)
129. 102.246 135.924 ↑ 40,000.0 1 1

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

  • Sort Key: ad_1.ad_id
  • Sort Method: external merge Disk: 9,584kB
130. 33.678 33.678 ↓ 7.2 286,172 1

CTE Scan on ad_performance_report ad_1 (cost=0.00..800.00 rows=40,000 width=40) (actual time=0.031..33.678 rows=286,172 loops=1)

131. 0.003 14,515.058 ↓ 0.0 0 1

Materialize (cost=448,759.47..452,111.49 rows=670,404 width=787) (actual time=14,515.058..14,515.058 rows=0 loops=1)

132. 0.008 14,515.055 ↓ 0.0 0 1

Sort (cost=448,759.47..450,435.48 rows=670,404 width=787) (actual time=14,515.055..14,515.055 rows=0 loops=1)

  • Sort Key: c1.ad_i_d
  • Sort Method: quicksort Memory: 25kB
133. 0.008 14,515.047 ↓ 0.0 0 1

Hash Right Join (cost=3,945.21..110,088.32 rows=670,404 width=787) (actual time=14,515.047..14,515.047 rows=0 loops=1)

  • Hash Cond: (upper(c1.google_click_i_d) = upper(t1.gclid))
134. 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)

135. 0.001 14,515.039 ↓ 0.0 0 1

Hash (cost=3,944.39..3,944.39 rows=66 width=683) (actual time=14,515.039..14,515.039 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
136. 0.001 14,515.038 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.67..3,944.39 rows=66 width=683) (actual time=14,515.038..14,515.038 rows=0 loops=1)

137. 0.000 14,515.037 ↓ 0.0 0 1

Nested Loop Anti Join (cost=1.11..3,760.41 rows=66 width=680) (actual time=14,515.037..14,515.037 rows=0 loops=1)

138. 0.001 14,515.037 ↓ 0.0 0 1

Nested Loop Anti Join (cost=0.56..3,561.74 rows=132 width=680) (actual time=14,515.037..14,515.037 rows=0 loops=1)

139. 14,515.036 14,515.036 ↓ 0.0 0 1

CTE Scan on sdk_installs t1 (cost=0.00..3,166.98 rows=263 width=680) (actual time=14,515.036..14,515.036 rows=0 loops=1)

  • Filter: (((to_timestamp(((install_ts / 1000))::double precision))::date >= $22) AND ((to_timestamp(((install_ts / 1000))::double precision))::date <= $23) 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
140. 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
141. 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
142. 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)
143. 0.000 0.000 ↓ 0.0 0

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

144. 0.000 0.000 ↓ 0.0 0

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

145. 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 < $24)
146. 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 < $24)
147. 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 < $24)
148. 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 < $24)
149. 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 < $24)
150. 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 < $24)
151. 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 < $24)
152. 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 < $24)
153. 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 < $24)
154. 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 < $24)
155. 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 < $24)
156. 0.000 0.000 ↓ 0.0 0

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

157. 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)

158. 0.000 0.000 ↓ 0.0 0

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

159. 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)

160. 0.000 0.000 ↓ 0.0 0

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

161. 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)

162. 0.000 0.000 ↓ 0.0 0

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

163. 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 : 32,345.992 ms