explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Wiph

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 30,230.902 ↓ 0.0 0 1

Subquery Scan on main (cost=215,999,417.12..216,939,054.68 rows=40,000 width=1,021) (actual time=30,230.902..30,230.902 rows=0 loops=1)

  • Functions: 378
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 44.977 ms, Inlining 29.747 ms, Optimization 4377.813 ms, Emission 2812.813 ms, Total 7265.349 ms
2.          

CTE t_load_from

3. 0.230 0.230 ↑ 1.0 1 1

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

4.          

CTE t_load_to

5. 0.123 0.123 ↑ 1.0 1 1

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

6.          

CTE apps_flyer_ids

7. 0.027 0.027 ↑ 1.0 1 1

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

8.          

CTE ad_performance_report

9. 59.215 10,687.252 ↑ 1.7 23,289 1

HashAggregate (cost=243,158.58..243,758.58 rows=40,000 width=17) (actual time=10,681.141..10,687.252 rows=23,289 loops=1)

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

Initplan (for HashAggregate)

11. 0.234 0.234 ↑ 1.0 1 1

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

12. 25.916 10,627.803 ↑ 7.4 161,669 1

Result (cost=0.00..237,156.76 rows=1,200,360 width=17) (actual time=7,238.131..10,627.803 rows=161,669 loops=1)

13. 11.977 10,601.887 ↑ 7.4 161,669 1

Append (cost=0.00..219,151.36 rows=1,200,360 width=22) (actual time=7,238.128..10,601.887 rows=161,669 loops=1)

14. 10,588.213 10,588.213 ↑ 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,238.127..10,588.213 rows=161,315 loops=1)

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

Seq Scan on ad_performance_report_2475468088 (cost=0.00..45.76 rows=213 width=45) (actual time=0.785..1.697 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.006 16,729.370 ↑ 52,785.0 1 1

Append (cost=1,189,075.76..1,623,266.36 rows=52,785 width=645) (actual time=6,544.075..16,729.370 rows=1 loops=1)

25. 1.982 6,544.074 ↑ 41,370.0 1 1

Merge Left Join (cost=1,189,075.76..1,196,113.61 rows=41,370 width=642) (actual time=6,544.073..6,544.074 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.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.001..0.002 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 6,503.610 ↑ 41,370.0 1 1

Sort (cost=1,187,376.89..1,187,480.32 rows=41,370 width=922) (actual time=6,503.609..6,503.610 rows=1 loops=1)

  • Sort Key: t2.device_model, (upper(t2.device_brand))
  • Sort Method: quicksort Memory: 27kB
31. 0.015 6,503.600 ↑ 41,370.0 1 1

Nested Loop (cost=430,924.20..1,174,348.18 rows=41,370 width=922) (actual time=6,348.825..6,503.600 rows=1 loops=1)

32. 0.009 6,500.010 ↑ 5,465.0 1 1

Nested Loop Left Join (cost=430,923.78..1,139,727.39 rows=5,465 width=860) (actual time=6,345.571..6,500.010 rows=1 loops=1)

33. 1,140.041 6,499.685 ↑ 5,465.0 1 1

Hash Left Join (cost=430,923.22..1,135,556.01 rows=5,465 width=759) (actual time=6,345.246..6,499.685 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. 1,444.049 1,444.049 ↑ 7.3 126,330 1

Seq Scan on install i (cost=0.00..125,620.07 rows=918,801 width=131) (actual time=410.583..1,444.049 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,025.248 3,915.595 ↓ 1.0 2,112,583 1

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

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

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

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

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

39. 0.464 0.464 ↓ 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.464..0.464 rows=0 loops=1)

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

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

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

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

  • Index Cond: (session_id = i.session_id)
44. 0.556 0.556 ↑ 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.556..0.556 rows=1 loops=1)

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

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

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

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

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

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

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

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

  • Sort Key: d.model, (upper(d.retail_branding))
  • Sort Method: quicksort Memory: 2,518kB
52. 6.768 6.768 ↓ 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.023..6.768 rows=18,647 loops=1)

53. 0.000 9,467.286 ↓ 0.0 0 1

Merge Left Join (cost=357,558.60..359,111.87 rows=10,762 width=655) (actual time=9,467.286..9,467.286 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.001..0.002 rows=1 loops=1)

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

57. 0.008 9,467.285 ↓ 0.0 0 1

Sort (cost=355,859.74..355,886.65 rows=10,762 width=424) (actual time=9,467.285..9,467.285 rows=0 loops=1)

  • Sort Key: t2_12.device_model, (upper(t2_12.device_brand))
  • Sort Method: quicksort Memory: 25kB
58. 80.642 9,467.277 ↓ 0.0 0 1

Hash Right Join (cost=316,633.22..355,139.03 rows=10,762 width=424) (actual time=9,467.276..9,467.277 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,128
59. 4.669 1,595.981 ↑ 4.2 47,903 1

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

60. 9.350 1,591.312 ↑ 4.2 47,903 1

Unique (cost=90,381.55..92,627.06 rows=201,646 width=328) (actual time=1,551.617..1,591.312 rows=47,903 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. 227.184 1,581.960 ↑ 3.4 65,972 1

Sort (cost=90,381.53..90,942.90 rows=224,551 width=328) (actual time=1,551.615..1,581.960 rows=65,972 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,104kB
64. 1,354.776 1,354.776 ↑ 3.4 65,972 1

Seq Scan on apps_flyer (cost=0.00..50,642.21 rows=224,551 width=328) (actual time=38.117..1,354.776 rows=65,972 loops=1)

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

Hash (cost=217,866.00..217,866.00 rows=200,614 width=210) (actual time=7,790.654..7,790.654 rows=47,982 loops=1)

  • Buckets: 32,768 Batches: 8 Memory Usage: 1,557kB
66. 562.923 7,673.235 ↑ 4.2 47,982 1

Merge Join (cost=46,232.01..217,866.00 rows=200,614 width=210) (actual time=404.820..7,673.235 rows=47,982 loops=1)

  • Merge Cond: (install.session_id = t2_12.session_id)
67. 35.403 453.220 ↑ 4.2 47,983 1

GroupAggregate (cost=46,231.46..50,243.76 rows=200,614 width=66) (actual time=403.748..453.220 rows=47,983 loops=1)

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

Initplan (for GroupAggregate)

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

70. 104.356 417.815 ↑ 4.2 47,983 1

Sort (cost=46,231.44..46,732.98 rows=200,616 width=66) (actual time=403.723..417.815 rows=47,983 loops=1)

  • Sort Key: install.session_id, install.device_id
  • Sort Method: quicksort Memory: 7,918kB
71. 313.459 313.459 ↑ 4.2 47,983 1

Seq Scan on install (cost=0.00..23,743.56 rows=200,616 width=66) (actual time=142.131..313.459 rows=47,983 loops=1)

  • Filter: (to_timestamp((("timestamp" / 1000))::double precision) >= ($12 - '1 mon'::interval))
  • Rows Removed by Filter: 553,265
72. 6,657.092 6,657.092 ↑ 1.0 1,806,892 1

Index Scan using session_pkey on session t2_12 (cost=0.55..158,591.06 rows=1,806,947 width=218) (actual time=0.625..6,657.092 rows=1,806,892 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 718.004 ↓ 0.0 0 1

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

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

80. 0.001 718.003 ↓ 0.0 0 1

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

81. 82.520 718.002 ↓ 0.0 0 1

Hash Left Join (cost=26,792.79..64,298.83 rows=653 width=487) (actual time=718.002..718.002 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. 240.026 240.026 ↑ 6.8 18,963 1

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

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

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

  • Buckets: 32,768 Batches: 16 Memory Usage: 5,216kB
84. 279.993 279.993 ↓ 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.402..279.993 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 30,230.901 ↓ 0.0 0 1

Unique (cost=213,684,429.13..214,622,166.69 rows=40,000 width=861) (actual time=30,230.901..30,230.901 rows=0 loops=1)

88.          

Initplan (for Unique)

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

90. 0.127 0.127 ↑ 1.0 1 1

CTE Scan on t_load_to (cost=0.00..0.02 rows=1 width=4) (actual time=0.127..0.127 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.034 30,230.901 ↓ 0.0 0 1

Sort (cost=213,684,429.07..213,997,008.25 rows=125,031,675 width=861) (actual time=30,230.901..30,230.901 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 30,230.867 ↓ 0.0 0 1

Hash Left Join (cost=1,099,459.92..113,516,675.30 rows=125,031,675 width=861) (actual time=30,230.867..30,230.867 rows=0 loops=1)

  • Hash Cond: ("*SELECT* 1".app_id = apps.app_id)
94. 0.001 30,230.867 ↓ 0.0 0 1

Hash Left Join (cost=1,099,458.56..69,186,693.57 rows=125,031,675 width=821) (actual time=30,230.867..30,230.867 rows=0 loops=1)

  • Hash Cond: (upper("*SELECT* 1".campaign) = upper(cmp.campaign))
95. 0.000 30,230.866 ↓ 0.0 0 1

Hash Left Join (cost=1,099,454.67..67,880,108.67 rows=125,031,675 width=789) (actual time=30,230.866..30,230.866 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.001 30,230.866 ↓ 0.0 0 1

Hash Left Join (cost=1,099,441.90..65,994,954.22 rows=125,031,675 width=783) (actual time=30,230.866..30,230.866 rows=0 loops=1)

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

Hash Anti Join (cost=1,099,420.20..65,648,149.95 rows=125,031,675 width=777) (actual time=30,230.865..30,230.865 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 30,230.865 ↓ 0.0 0 1

Append (cost=862,879.47..30,298,302.99 rows=166,708,900 width=777) (actual time=30,230.865..30,230.865 rows=0 loops=1)

99. 0.000 13,494.023 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1 (cost=862,879.47..8,898,132.32 rows=32,628,000 width=914) (actual time=13,494.023..13,494.023 rows=0 loops=1)

100. 0.000 13,494.023 ↓ 0.0 0 1

Merge Right Join (cost=862,879.47..8,571,852.32 rows=32,628,000 width=914) (actual time=13,494.023..13,494.023 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.028 0.028 ↑ 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.028..0.028 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. 6.401 10,700.907 ↑ 40,000.0 1 1

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

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

CTE Scan on ad_performance_report ad (cost=0.00..800.00 rows=40,000 width=40) (actual time=10,681.146..10,694.506 rows=23,289 loops=1)

107. 0.004 2,793.103 ↓ 0.0 0 1

Materialize (cost=859,021.87..859,837.57 rows=163,140 width=1,065) (actual time=2,793.103..2,793.103 rows=0 loops=1)

108. 0.009 2,793.099 ↓ 0.0 0 1

Sort (cost=859,021.87..859,429.72 rows=163,140 width=1,065) (actual time=2,793.099..2,793.099 rows=0 loops=1)

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

Hash Right Join (cost=716,950.54..800,152.06 rows=163,140 width=1,065) (actual time=2,793.090..2,793.090 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.001 2,793.081 ↓ 0.0 0 1

Hash (cost=716,950.34..716,950.34 rows=16 width=961) (actual time=2,793.081..2,793.081 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
112. 0.000 2,793.080 ↓ 0.0 0 1

Hash Left Join (cost=480,328.43..716,950.34 rows=16 width=961) (actual time=2,793.080..2,793.080 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.001 2,793.080 ↓ 0.0 0 1

Hash Left Join (cost=477,426.92..712,753.21 rows=16 width=833) (actual time=2,793.080..2,793.080 rows=0 loops=1)

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

Nested Loop Left Join (cost=464,514.15..699,840.40 rows=16 width=810) (actual time=2,793.079..2,793.079 rows=0 loops=1)

115. 0.003 2,793.078 ↓ 0.0 0 1

Hash Right Join (cost=464,513.60..699,828.18 rows=16 width=709) (actual time=2,793.078..2,793.078 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 2,793.075 ↓ 0.0 0 1

Hash (cost=464,513.40..464,513.40 rows=16 width=650) (actual time=2,793.075..2,793.075 rows=0 loops=1)

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

Seq Scan on push_report_install ro (cost=0.00..464,513.40 rows=16 width=650) (actual time=2,793.074..2,793.074 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,657
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.70..1,055.70 rows=52,785 width=160) (never executed)

130. 0.000 0.000 ↓ 0.0 0

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

131. 0.002 16,736.839 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=452,616.66..20,566,626.17 rows=134,080,900 width=744) (actual time=16,736.839..16,736.839 rows=0 loops=1)

132. 0.011 16,736.837 ↓ 0.0 0 1

Merge Right Join (cost=452,616.66..19,225,817.17 rows=134,080,900 width=744) (actual time=16,736.837..16,736.837 rows=0 loops=1)

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

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

  • Sort Key: ad_1.ad_id
  • Sort Method: quicksort Memory: 1,901kB
134. 2.276 2.276 ↑ 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.016..2.276 rows=23,289 loops=1)

135. 0.003 16,729.408 ↓ 0.0 0 1

Materialize (cost=448,759.12..452,111.14 rows=670,404 width=787) (actual time=16,729.407..16,729.408 rows=0 loops=1)

136. 0.008 16,729.405 ↓ 0.0 0 1

Sort (cost=448,759.12..450,435.13 rows=670,404 width=787) (actual time=16,729.405..16,729.405 rows=0 loops=1)

  • Sort Key: c1.ad_i_d
  • Sort Method: quicksort Memory: 25kB
137. 0.009 16,729.397 ↓ 0.0 0 1

Hash Right Join (cost=3,944.86..110,087.97 rows=670,404 width=787) (actual time=16,729.397..16,729.397 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 16,729.388 ↓ 0.0 0 1

Hash (cost=3,944.03..3,944.03 rows=66 width=683) (actual time=16,729.388..16,729.388 rows=0 loops=1)

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

Nested Loop Left Join (cost=1.67..3,944.03 rows=66 width=683) (actual time=16,729.387..16,729.387 rows=0 loops=1)

141. 0.001 16,729.387 ↓ 0.0 0 1

Nested Loop Anti Join (cost=1.11..3,760.06 rows=66 width=680) (actual time=16,729.387..16,729.387 rows=0 loops=1)

142. 0.001 16,729.386 ↓ 0.0 0 1

Nested Loop Anti Join (cost=0.56..3,561.83 rows=132 width=680) (actual time=16,729.386..16,729.386 rows=0 loops=1)

143. 16,729.385 16,729.385 ↓ 0.0 0 1

CTE Scan on sdk_installs t1 (cost=0.00..3,167.10 rows=263 width=680) (actual time=16,729.385..16,729.385 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_appsflyer_device_id 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
145. 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
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 : 30,281.614 ms