explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xnX8

Settings
# exclusive inclusive rows x rows loops node
1. 3.245 526,631.040 ↑ 863.0 7,889 1

Merge Right Join (cost=6,077,313.74..6,179,806.91 rows=6,807,991 width=384) (actual time=526,628.846..526,631.040 rows=7,889 loops=1)

  • Merge Cond: (ga_pv.dimension21 = snapchat.as_parameter)
2.          

CTE ga_pv

3. 1.882 283,165.072 ↑ 15.5 686 1

GroupAggregate (cost=4,247,582.45..4,247,821.69 rows=10,633 width=51) (actual time=283,164.086..283,165.072 rows=686 loops=1)

  • Group Key: ga_campaign_creative.date, ga_campaign_creative.dimension21
4. 6.353 283,163.190 ↑ 2.7 3,944 1

Sort (cost=4,247,582.45..4,247,609.03 rows=10,633 width=27) (actual time=283,162.861..283,163.190 rows=3,944 loops=1)

  • Sort Key: ga_campaign_creative.date, ga_campaign_creative.dimension21
  • Sort Method: quicksort Memory: 405kB
5. 2.710 283,156.837 ↑ 2.7 3,944 1

Nested Loop (cost=1,807,196.10..4,246,871.30 rows=10,633 width=27) (actual time=281,614.038..283,156.837 rows=3,944 loops=1)

6. 1.275 281,604.975 ↑ 9.1 22 1

HashAggregate (cost=1,807,195.53..1,807,197.53 rows=200 width=32) (actual time=281,604.895..281,604.975 rows=22 loops=1)

  • Group Key: xyz_1.as_parameter
7. 2.649 281,603.700 ↑ 299.9 427 1

Sort (cost=1,805,274.72..1,805,594.85 rows=128,054 width=328) (actual time=281,603.681..281,603.700 rows=427 loops=1)

  • Sort Key: xyz_1.date DESC, xyz_1.as_parameter
  • Sort Method: quicksort Memory: 58kB
8.          

CTE all_data

9. 122.719 281,548.439 ↑ 158.5 812 1

GroupAggregate (cost=1,723,027.12..1,747,589.34 rows=128,697 width=348) (actual time=281,340.532..281,548.439 rows=812 loops=1)

  • Group Key: (to_char((snapchat_stats.date)::timestamp with time zone, 'YYYY-MM-DD'::text)), abc.as_parameter, abc.campaign_name, snapchat_ads_1.creative_id, snapchat_ads_1.id, snapchat_ads_1.ad_squad_id, snapchat_adsquads_1.campaign_id
  • Filter: ((sum(snapchat_stats.spend) > '0'::double precision) OR (sum(((((COALESCE((sum(connatix_report.revenue)), '0'::double precision) + COALESCE(zyx.totalrevenue, '0'::double precision)) - COALESCE(dfp_daily_revenue_avantis.ad_server_cpm_and_cpc_revenue, '0'::double precision)) + COALESCE(avantis.totalrevenue, '0'::double precision)) + COALESCE(v_taboola_revenue.revenue, '0'::double precision))) > '0'::double precision) OR (sum(COALESCE((sum(connatix_report.revenue)), '0'::double precision)) > '0'::double precision) OR (sum(COALESCE(zyx.totalrevenue, '0'::double precision)) > '0'::double precision) OR (sum(COALESCE(v_taboola_revenue.revenue, '0'::double precision)) > '0'::double precision))
  • Rows Removed by Filter: 143,672
10. 864.904 281,425.720 ↑ 1.0 144,484 1

Sort (cost=1,723,027.12..1,723,397.66 rows=148,215 width=300) (actual time=281,332.980..281,425.720 rows=144,484 loops=1)

  • Sort Key: (to_char((snapchat_stats.date)::timestamp with time zone, 'YYYY-MM-DD'::text)), abc.as_parameter, abc.campaign_name, snapchat_ads_1.creative_id, snapchat_ads_1.id, snapchat_ads_1.ad_squad_id, snapchat_adsquads_1.campaign_id
  • Sort Method: external merge Disk: 33,000kB
11. 33,226.737 280,560.816 ↑ 1.0 144,484 1

Nested Loop Left Join (cost=1,597,695.53..1,698,130.68 rows=148,215 width=300) (actual time=57,373.402..280,560.816 rows=144,484 loops=1)

  • Join Filter: ((dfp_daily_revenue_avantis.date = snapchat_stats.date) AND (split_part(dfp_daily_revenue_avantis.custom_criteria, '='::text, 2) = abc.as_parameter))
  • Rows Removed by Join Filter: 388,517,211
12. 95.200 224,939.059 ↑ 1.0 144,484 1

Hash Left Join (cost=1,596,695.53..1,628,809.96 rows=148,215 width=264) (actual time=55,930.223..224,939.059 rows=144,484 loops=1)

  • Hash Cond: ((abc.as_parameter = avantis.as_id) AND (snapchat_stats.date = avantis.date))
13. 125.159 223,854.653 ↑ 1.0 144,484 1

Hash Left Join (cost=1,558,496.11..1,589,832.37 rows=148,215 width=256) (actual time=54,940.137..223,854.653 rows=144,484 loops=1)

  • Hash Cond: (snapchat_ads_1.ad_squad_id = snapchat_adsquads_1.id)
14. 102.583 223,723.205 ↑ 1.0 144,484 1

Hash Left Join (cost=1,558,457.33..1,587,842.46 rows=148,215 width=219) (actual time=54,932.960..223,723.205 rows=144,484 loops=1)

  • Hash Cond: ((snapchat_stats.date = v_taboola_revenue.date) AND (abc.as_parameter = v_taboola_revenue.as_id))
15. 143.548 210,779.800 ↓ 1.1 144,484 1

Merge Left Join (cost=1,083,120.76..1,111,794.85 rows=135,432 width=211) (actual time=42,086.461..210,779.800 rows=144,484 loops=1)

  • Merge Cond: (snapchat_stats.date = zyx.date)
  • Join Filter: (zyx.as_parameter = abc.as_parameter)
  • Rows Removed by Join Filter: 260,513
16. 89.101 178,507.621 ↓ 1.1 144,484 1

Merge Left Join (cost=377,045.86..399,724.07 rows=135,432 width=203) (actual time=10,008.045..178,507.621 rows=144,484 loops=1)

  • Merge Cond: (snapchat_stats.date = connatix_report.date)
  • Join Filter: (connatix_report.as_id = abc.as_parameter)
  • Rows Removed by Join Filter: 62,340
17. 382.450 168,441.309 ↓ 1.1 144,484 1

Nested Loop Left Join (cost=240.61..21,864.97 rows=135,432 width=195) (actual time=44.269..168,441.309 rows=144,484 loops=1)

18. 664.907 664.907 ↑ 1.0 135,432 1

Index Scan Backward using idx_snapchat_stats_date on snapchat_stats (cost=0.42..6,050.09 rows=135,432 width=57) (actual time=1.450..664.907 rows=135,432 loops=1)

19. 8,803.080 167,393.952 ↑ 1.0 1 135,432

Hash Right Join (cost=240.19..240.30 rows=1 width=175) (actual time=1.131..1.236 rows=1 loops=135,432)

  • Hash Cond: (abc.creative_id = snapchat_ads_1.creative_id)
20. 19,231.344 157,507.416 ↓ 138.8 555 135,432

Group (cost=239.89..239.93 rows=4 width=96) (actual time=0.988..1.163 rows=555 loops=135,432)

  • Group Key: abc.creative_id, abc.as_parameter, abc.campaign_name
21.          

CTE asparam_assoc

22. 0.222 3.110 ↑ 2.4 131 1

HashAggregate (cost=143.77..150.75 rows=310 width=139) (actual time=3.062..3.110 rows=131 loops=1)

  • Group Key: snapchat_creatives_1.ad_account_id, snapchat_creatives_1.headline, concat('/', split_part(split_part(snapchat_creatives_1.url, '/'::text, 4), '/'::text, 1), '/'), split_part(split_part(snapchat_creatives_1.url, 'as='::text, 2), '&'::text, 1), snapchat_creatives_1.type, snapchat_creatives_1.created_at
23. 1.824 2.888 ↑ 2.1 149 1

Hash Join (cost=67.79..139.12 rows=310 width=139) (actual time=1.216..2.888 rows=149 loops=1)

  • Hash Cond: (snapchat_creatives_1.headline = snapchat_creatives_2.headline)
24. 0.359 0.359 ↓ 1.0 477 1

Seq Scan on snapchat_creatives snapchat_creatives_1 (cost=0.00..66.15 rows=469 width=261) (actual time=0.008..0.359 rows=477 loops=1)

  • Filter: ((url <> 'nan'::text) AND (review_status = 'APPROVED'::text))
  • Rows Removed by Filter: 285
25. 0.357 0.705 ↑ 9.0 5 1

Hash (cost=67.23..67.23 rows=45 width=22) (actual time=0.704..0.705 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
26. 0.018 0.348 ↑ 9.0 5 1

HashAggregate (cost=66.33..66.78 rows=45 width=22) (actual time=0.345..0.348 rows=5 loops=1)

  • Group Key: snapchat_creatives_2.headline
27. 0.330 0.330 ↑ 2.3 30 1

Seq Scan on snapchat_creatives snapchat_creatives_2 (cost=0.00..66.15 rows=70 width=22) (actual time=0.088..0.330 rows=30 loops=1)

  • Filter: ((url = 'nan'::text) AND (review_status = 'APPROVED'::text))
  • Rows Removed by Filter: 732
28.          

CTE full_creatives

29. 1.242 1.242 ↑ 1.1 762 1

Seq Scan on snapchat_creatives snapchat_creatives_1_1 (cost=0.00..62.10 rows=810 width=493) (actual time=0.569..1.242 rows=762 loops=1)

30.          

CTE abc

31. 5.313 32.436 ↓ 148.2 593 1

Hash Right Join (cost=18.28..26.92 rows=4 width=96) (actual time=28.201..32.436 rows=593 loops=1)

  • Hash Cond: ((asparam_assoc.ad_account_id = full_creatives.ad_account_id) AND (asparam_assoc.headline = full_creatives.headline))
  • Join Filter: ((full_creatives.type <> asparam_assoc.type) AND (full_creatives.created_at > (asparam_assoc.created_at - '00:05:00'::interval)) AND (full_creatives.created_at < (asparam_assoc.created_at + '00:05:00'::interval)))
  • Rows Removed by Join Filter: 8,207
32. 3.531 3.531 ↑ 2.4 131 1

CTE Scan on asparam_assoc (cost=0.00..6.20 rows=310 width=168) (actual time=3.065..3.531 rows=131 loops=1)

33. 5.688 23.592 ↓ 126.8 507 1

Hash (cost=18.22..18.22 rows=4 width=200) (actual time=23.592..23.592 rows=507 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 189kB
34. 17.904 17.904 ↓ 126.8 507 1

CTE Scan on full_creatives (cost=0.00..18.22 rows=4 width=200) (actual time=0.577..17.904 rows=507 loops=1)

  • Filter: (review_status = 'APPROVED'::text)
  • Rows Removed by Filter: 255
35. 130,150.152 138,276.072 ↓ 148.2 593 135,432

Sort (cost=0.12..0.13 rows=4 width=96) (actual time=0.987..1.021 rows=593 loops=135,432)

  • Sort Key: abc.creative_id, abc.as_parameter, abc.campaign_name
  • Sort Method: quicksort Memory: 108kB
36. 8,125.920 8,125.920 ↓ 148.2 593 135,432

CTE Scan on abc (cost=0.00..0.08 rows=4 width=96) (actual time=0.000..0.060 rows=593 loops=135,432)

37. 270.864 1,083.456 ↑ 1.0 1 135,432

Hash (cost=0.29..0.29 rows=1 width=111) (actual time=0.008..0.008 rows=1 loops=135,432)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
38. 812.592 812.592 ↑ 1.0 1 135,432

Index Scan using idx_snapchat_ads_id on snapchat_ads snapchat_ads_1 (cost=0.28..0.29 rows=1 width=111) (actual time=0.006..0.006 rows=1 loops=135,432)

  • Index Cond: (id = snapchat_stats.id)
39. 6.603 9,977.211 ↓ 755.0 65,688 1

Materialize (cost=376,805.24..376,814.60 rows=87 width=32) (actual time=9,952.183..9,977.211 rows=65,688 loops=1)

40. 0.000 9,970.608 ↓ 43.9 3,822 1

Finalize GroupAggregate (cost=376,805.24..376,813.51 rows=87 width=35) (actual time=9,951.775..9,970.608 rows=3,822 loops=1)

  • Group Key: connatix_report.date, connatix_report.site, connatix_report.as_id
41. 30.879 9,971.920 ↓ 85.4 4,357 1

Gather Merge (cost=376,805.24..376,812.13 rows=51 width=35) (actual time=9,951.744..9,971.920 rows=4,357 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
42. 5.116 9,941.041 ↓ 42.7 2,178 2 / 2

Partial GroupAggregate (cost=375,805.23..375,806.38 rows=51 width=35) (actual time=9,931.385..9,941.041 rows=2,178 loops=2)

  • Group Key: connatix_report.date, connatix_report.site, connatix_report.as_id
43. 52.955 9,935.925 ↓ 286.7 14,620 2 / 2

Sort (cost=375,805.23..375,805.36 rows=51 width=35) (actual time=9,931.368..9,935.925 rows=14,620 loops=2)

  • Sort Key: connatix_report.date, connatix_report.site, connatix_report.as_id
  • Sort Method: quicksort Memory: 1,214kB
  • Worker 0: Sort Method: quicksort Memory: 2,223kB
44. 9,882.970 9,882.970 ↓ 286.7 14,620 2 / 2

Parallel Seq Scan on connatix_report (cost=0.00..375,803.79 rows=51 width=35) (actual time=11.948..9,882.970 rows=14,620 loops=2)

  • Filter: ((as_id ~~ '%%211%%'::text) AND (length(as_id) = 13))
  • Rows Removed by Filter: 3,833,038
45. 53.693 32,128.631 ↓ 380.1 264,933 1

Sort (cost=706,074.90..706,076.64 rows=697 width=44) (actual time=32,077.857..32,128.631 rows=264,933 loops=1)

  • Sort Key: zyx.date
  • Sort Method: quicksort Memory: 529kB
46. 0.393 32,074.938 ↓ 6.2 4,307 1

Subquery Scan on zyx (cost=706,028.04..706,041.98 rows=697 width=44) (actual time=32,073.423..32,074.938 rows=4,307 loops=1)

47. 35.784 32,074.545 ↓ 6.2 4,307 1

HashAggregate (cost=706,028.04..706,035.01 rows=697 width=44) (actual time=32,073.421..32,074.545 rows=4,307 loops=1)

  • Group Key: dfp_custom_criteria.date, (split_part(dfp_custom_criteria.custom_criteria, 'as='::text, 2)), dfp_custom_criteria.total_inventory_level_cpm_and_cpc_revenue
48. 0.000 32,038.761 ↓ 6.2 4,307 1

Append (cost=1,000.00..706,022.82 rows=697 width=44) (actual time=11.397..32,038.761 rows=4,307 loops=1)

49. 0.000 5,640.487 ↓ 7.1 4,210 1

Gather (cost=1,000.00..217,749.57 rows=593 width=44) (actual time=11.395..5,640.487 rows=4,210 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
50. 5,647.722 5,647.722 ↓ 6.0 2,105 2 / 2

Parallel Seq Scan on dfp_custom_criteria (cost=0.00..216,690.27 rows=349 width=44) (actual time=6.347..5,647.722 rows=2,105 loops=2)

  • Filter: ((custom_criteria ~~ '%%as=211%%'::text) AND (date < '2020-08-18'::date))
  • Rows Removed by Filter: 3,116,855
51. 0.170 26,399.729 ↑ 1.1 97 1

GroupAggregate (cost=488,260.71..488,262.79 rows=104 width=31) (actual time=26,399.513..26,399.729 rows=97 loops=1)

  • Group Key: ga_gemini_tag.date, ga_gemini_tag.dimension21
52. 1.952 26,399.559 ↓ 12.7 1,325 1

Sort (cost=488,260.71..488,260.97 rows=104 width=31) (actual time=26,399.501..26,399.559 rows=1,325 loops=1)

  • Sort Key: ga_gemini_tag.date, ga_gemini_tag.dimension21
  • Sort Method: quicksort Memory: 151kB
53. 26,397.607 26,397.607 ↓ 12.7 1,325 1

Index Scan using idx_17910449_date on ga_gemini_tag (cost=0.57..488,257.23 rows=104 width=31) (actual time=73.006..26,397.607 rows=1,325 loops=1)

  • Index Cond: (date >= '2020-08-18'::date)
  • Filter: (dimension21 ~~ '211%%'::text)
  • Rows Removed by Filter: 1,154,646
54. 0.288 12,840.822 ↑ 12,782.4 5 1

Hash (cost=474,377.89..474,377.89 rows=63,912 width=25) (actual time=12,840.822..12,840.822 rows=5 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 513kB
55. 0.003 12,840.534 ↑ 12,782.4 5 1

Subquery Scan on v_taboola_revenue (cost=467,716.93..474,377.89 rows=63,912 width=25) (actual time=12,840.528..12,840.534 rows=5 loops=1)

56. 0.000 12,840.531 ↑ 12,782.4 5 1

Finalize GroupAggregate (cost=467,716.93..473,738.77 rows=63,912 width=57) (actual time=12,840.526..12,840.531 rows=5 loops=1)

  • Group Key: taboola_revenue.date, taboola_revenue.as_id
57. 15.628 12,842.004 ↑ 7,689.6 5 1

Gather Merge (cost=467,716.93..472,811.29 rows=38,448 width=25) (actual time=12,839.696..12,842.004 rows=5 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
58. 0.986 12,826.376 ↑ 19,224.0 2 2 / 2

Partial GroupAggregate (cost=466,716.92..467,485.88 rows=38,448 width=25) (actual time=12,826.365..12,826.376 rows=2 loops=2)

  • Group Key: taboola_revenue.date, taboola_revenue.as_id
59. 0.038 12,825.390 ↑ 9,612.0 4 2 / 2

Sort (cost=466,716.92..466,813.04 rows=38,448 width=25) (actual time=12,825.387..12,825.390 rows=4 loops=2)

  • Sort Key: taboola_revenue.date, taboola_revenue.as_id
  • Sort Method: quicksort Memory: 25kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
60. 12,825.352 12,825.352 ↑ 9,612.0 4 2 / 2

Parallel Seq Scan on taboola_revenue (cost=0.00..463,788.99 rows=38,448 width=25) (actual time=10,271.570..12,825.352 rows=4 loops=2)

  • Filter: ((placement IS NOT NULL) AND (""left""(as_id, 3) = '211'::text))
  • Rows Removed by Filter: 6,572,089
61. 0.352 6.289 ↑ 1.0 223 1

Hash (cost=35.97..35.97 rows=225 width=74) (actual time=6.289..6.289 rows=223 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 32kB
62. 0.923 5.937 ↑ 1.0 223 1

Hash Left Join (cost=15.62..35.97 rows=225 width=74) (actual time=5.049..5.937 rows=223 loops=1)

  • Hash Cond: (snapchat_adsquads_1.campaign_id = snapchat_campaigns_1.id)
63. 2.344 2.344 ↑ 1.0 223 1

Seq Scan on snapchat_adsquads snapchat_adsquads_1 (cost=0.00..17.25 rows=225 width=74) (actual time=1.558..2.344 rows=223 loops=1)

64. 0.573 2.670 ↑ 1.5 172 1

Hash (cost=12.50..12.50 rows=250 width=37) (actual time=2.670..2.670 rows=172 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 20kB
65. 2.097 2.097 ↑ 1.5 172 1

Seq Scan on snapchat_campaigns snapchat_campaigns_1 (cost=0.00..12.50 rows=250 width=37) (actual time=1.274..2.097 rows=172 loops=1)

66. 0.828 989.206 ↑ 3.4 2,112 1

Hash (cost=38,092.17..38,092.17 rows=7,150 width=33) (actual time=989.206..989.206 rows=2,112 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 188kB
67. 0.486 988.378 ↑ 3.4 2,112 1

Subquery Scan on avantis (cost=37,347.03..38,092.17 rows=7,150 width=33) (actual time=986.614..988.378 rows=2,112 loops=1)

68. 0.000 987.892 ↑ 3.4 2,112 1

Finalize GroupAggregate (cost=37,347.03..38,020.67 rows=7,150 width=33) (actual time=986.356..987.892 rows=2,112 loops=1)

  • Group Key: avantis_subid_daily_report.date, avantis_subid_daily_report.sub_id
69. 12.068 989.019 ↑ 2.0 2,112 1

Gather Merge (cost=37,347.03..37,916.91 rows=4,301 width=33) (actual time=985.206..989.019 rows=2,112 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
70. 1.175 976.951 ↑ 4.1 1,056 2 / 2

Partial GroupAggregate (cost=36,347.02..36,433.04 rows=4,301 width=33) (actual time=976.486..976.951 rows=1,056 loops=2)

  • Group Key: avantis_subid_daily_report.date, avantis_subid_daily_report.sub_id
71. 2.892 975.776 ↑ 4.1 1,058 2 / 2

Sort (cost=36,347.02..36,357.77 rows=4,301 width=33) (actual time=975.674..975.776 rows=1,058 loops=2)

  • Sort Key: avantis_subid_daily_report.date, avantis_subid_daily_report.sub_id
  • Sort Method: quicksort Memory: 139kB
  • Worker 0: Sort Method: quicksort Memory: 99kB
72. 972.884 972.884 ↑ 4.1 1,058 2 / 2

Parallel Seq Scan on avantis_subid_daily_report (cost=0.00..36,087.44 rows=4,301 width=33) (actual time=0.842..972.884 rows=1,058 loops=2)

  • Filter: (""left""(sub_id, 3) = '211'::text)
  • Rows Removed by Filter: 730,170
73. 20,962.253 22,395.020 ↓ 2,689.0 2,689 144,484

Materialize (cost=1,000.00..65,615.35 rows=1 width=37) (actual time=0.000..0.155 rows=2,689 loops=144,484)

74. 1.258 1,432.767 ↓ 2,689.0 2,689 1

Gather (cost=1,000.00..65,615.35 rows=1 width=37) (actual time=6.401..1,432.767 rows=2,689 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
75. 1,431.509 1,431.509 ↓ 1,344.0 1,344 2 / 2

Parallel Seq Scan on dfp_daily_revenue_avantis (cost=0.00..64,615.25 rows=1 width=37) (actual time=3.766..1,431.509 rows=1,344 loops=2)

  • Filter: ((custom_criteria ~~ '%%as=211%%'::text) AND (length(split_part(custom_criteria, '='::text, 2)) = 13))
  • Rows Removed by Filter: 1,094,428
76.          

CTE swipes_by_campaign_date

77. 23.243 23.685 ↑ 47.7 270 1

HashAggregate (cost=3,539.17..3,700.04 rows=12,870 width=96) (actual time=23.222..23.685 rows=270 loops=1)

  • Group Key: all_data.date, all_data.campaign_id
78. 0.442 0.442 ↑ 158.5 812 1

CTE Scan on all_data (cost=0.00..2,573.94 rows=128,697 width=72) (actual time=0.321..0.442 rows=812 loops=1)

79.          

CTE xyz

80. 3.881 281,592.044 ↑ 158.5 812 1

Merge Left Join (cost=25,968.60..28,945.44 rows=128,697 width=360) (actual time=281,590.830..281,592.044 rows=812 loops=1)

  • Merge Cond: ((all_data_1.date = swipes_by_campaign_date.date) AND (all_data_1.campaign_id = swipes_by_campaign_date.campaign_id))
81. 4.194 281,560.761 ↑ 158.5 812 1

Sort (cost=24,832.71..25,154.45 rows=128,697 width=328) (actual time=281,560.683..281,560.761 rows=812 loops=1)

  • Sort Key: all_data_1.date, all_data_1.campaign_id
  • Sort Method: quicksort Memory: 443kB
82. 281,556.567 281,556.567 ↑ 158.5 812 1

CTE Scan on all_data all_data_1 (cost=0.00..2,573.94 rows=128,697 width=328) (actual time=281,341.781..281,556.567 rows=812 loops=1)

83. 1.411 27.402 ↑ 16.1 799 1

Sort (cost=1,135.89..1,168.06 rows=12,870 width=96) (actual time=27.355..27.402 rows=799 loops=1)

  • Sort Key: swipes_by_campaign_date.date, swipes_by_campaign_date.campaign_id
  • Sort Method: quicksort Memory: 62kB
84. 25.991 25.991 ↑ 47.7 270 1

CTE Scan on swipes_by_campaign_date (cost=0.00..257.40 rows=12,870 width=96) (actual time=23.935..25.991 rows=270 loops=1)

85. 281,601.051 281,601.051 ↑ 299.9 427 1

CTE Scan on xyz xyz_1 (cost=0.00..2,895.68 rows=128,054 width=328) (actual time=281,592.685..281,601.051 rows=427 loops=1)

  • Filter: (impressions <> 0)
  • Rows Removed by Filter: 385
86. 1,549.152 1,549.152 ↓ 179.0 179 22

Index Scan using idx_22424699_dimension21 on ga_campaign_creative (cost=0.57..12,198.36 rows=1 width=27) (actual time=4.529..70.416 rows=179 loops=22)

  • Index Cond: (dimension21 = xyz_1.as_parameter)
  • Filter: (dimension21 ~~ '211%%'::text)
87. 3.792 283,179.619 ↑ 15.5 686 1

Sort (cost=923.81..950.39 rows=10,633 width=152) (actual time=283,179.563..283,179.619 rows=686 loops=1)

  • Sort Key: ga_pv.dimension21
  • Sort Method: quicksort Memory: 121kB
88. 283,175.827 283,175.827 ↑ 15.5 686 1

CTE Scan on ga_pv (cost=0.00..212.66 rows=10,633 width=152) (actual time=283,172.691..283,175.827 rows=686 loops=1)

89. 0.987 243,448.176 ↑ 16.3 7,834 1

Materialize (cost=1,828,568.24..1,829,208.51 rows=128,054 width=264) (actual time=243,447.756..243,448.176 rows=7,834 loops=1)

90. 0.324 243,447.189 ↑ 299.9 427 1

Sort (cost=1,828,568.24..1,828,888.38 rows=128,054 width=264) (actual time=243,447.169..243,447.189 rows=427 loops=1)

  • Sort Key: snapchat.as_parameter
  • Sort Method: quicksort Memory: 172kB
91. 0.424 243,446.865 ↑ 299.9 427 1

Subquery Scan on snapchat (cost=1,806,875.39..1,808,476.07 rows=128,054 width=264) (actual time=243,446.765..243,446.865 rows=427 loops=1)

92. 2.565 243,446.441 ↑ 299.9 427 1

Sort (cost=1,806,875.39..1,807,195.53 rows=128,054 width=328) (actual time=243,446.422..243,446.441 rows=427 loops=1)

  • Sort Key: xyz.date DESC, xyz.as_parameter
  • Sort Method: quicksort Memory: 245kB
93.          

CTE all_data

94. 134.058 243,400.436 ↑ 158.5 812 1

GroupAggregate (cost=1,723,027.12..1,747,589.34 rows=128,697 width=348) (actual time=243,174.304..243,400.436 rows=812 loops=1)

  • Group Key: (to_char((snapchat_stats_1.date)::timestamp with time zone, 'YYYY-MM-DD'::text)), abc_1.as_parameter, abc_1.campaign_name, snapchat_ads_1_1.creative_id, snapchat_ads_1_1.id, snapchat_ads_1_1.ad_squad_id, snapchat_adsquads_1_1.campaign_id
  • Filter: ((sum(snapchat_stats_1.spend) > '0'::double precision) OR (sum(((((COALESCE((sum(connatix_report_1.revenue)), '0'::double precision) + COALESCE(zyx_1.totalrevenue, '0'::double precision)) - COALESCE(dfp_daily_revenue_avantis_1.ad_server_cpm_and_cpc_revenue, '0'::double precision)) + COALESCE(avantis_1.totalrevenue, '0'::double precision)) + COALESCE(v_taboola_revenue_1.revenue, '0'::double precision))) > '0'::double precision) OR (sum(COALESCE((sum(connatix_report_1.revenue)), '0'::double precision)) > '0'::double precision) OR (sum(COALESCE(zyx_1.totalrevenue, '0'::double precision)) > '0'::double precision) OR (sum(COALESCE(v_taboola_revenue_1.revenue, '0'::double precision)) > '0'::double precision))
  • Rows Removed by Filter: 143,672
95. 755.967 243,266.378 ↑ 1.0 144,484 1

Sort (cost=1,723,027.12..1,723,397.66 rows=148,215 width=300) (actual time=243,166.624..243,266.378 rows=144,484 loops=1)

  • Sort Key: (to_char((snapchat_stats_1.date)::timestamp with time zone, 'YYYY-MM-DD'::text)), abc_1.as_parameter, abc_1.campaign_name, snapchat_ads_1_1.creative_id, snapchat_ads_1_1.id, snapchat_ads_1_1.ad_squad_id, snapchat_adsquads_1_1.campaign_id
  • Sort Method: external merge Disk: 33,000kB
96. 26,424.461 242,510.411 ↑ 1.0 144,484 1

Nested Loop Left Join (cost=1,597,695.53..1,698,130.68 rows=148,215 width=300) (actual time=59,782.088..242,510.411 rows=144,484 loops=1)

  • Join Filter: ((dfp_daily_revenue_avantis_1.date = snapchat_stats_1.date) AND (split_part(dfp_daily_revenue_avantis_1.custom_criteria, '='::text, 2) = abc_1.as_parameter))
  • Rows Removed by Join Filter: 388,517,211
97. 65.070 196,869.578 ↑ 1.0 144,484 1

Hash Left Join (cost=1,596,695.53..1,628,809.96 rows=148,215 width=264) (actual time=58,141.684..196,869.578 rows=144,484 loops=1)

  • Hash Cond: ((abc_1.as_parameter = avantis_1.as_id) AND (snapchat_stats_1.date = avantis_1.date))
98. 94.324 195,983.953 ↑ 1.0 144,484 1

Hash Left Join (cost=1,558,496.11..1,589,832.37 rows=148,215 width=256) (actual time=57,319.921..195,983.953 rows=144,484 loops=1)

  • Hash Cond: (snapchat_ads_1_1.ad_squad_id = snapchat_adsquads_1_1.id)
99. 74.202 195,880.004 ↑ 1.0 144,484 1

Hash Left Join (cost=1,558,457.33..1,587,842.46 rows=148,215 width=219) (actual time=57,308.927..195,880.004 rows=144,484 loops=1)

  • Hash Cond: ((snapchat_stats_1.date = v_taboola_revenue_1.date) AND (abc_1.as_parameter = v_taboola_revenue_1.as_id))
100. 98.644 183,577.468 ↓ 1.1 144,484 1

Merge Left Join (cost=1,083,120.76..1,111,794.85 rows=135,432 width=211) (actual time=45,077.359..183,577.468 rows=144,484 loops=1)

  • Merge Cond: (snapchat_stats_1.date = zyx_1.date)
  • Join Filter: (zyx_1.as_parameter = abc_1.as_parameter)
  • Rows Removed by Join Filter: 260,513
101. 56.134 150,042.136 ↓ 1.1 144,484 1

Merge Left Join (cost=377,045.86..399,724.07 rows=135,432 width=203) (actual time=11,655.248..150,042.136 rows=144,484 loops=1)

  • Merge Cond: (snapchat_stats_1.date = connatix_report_1.date)
  • Join Filter: (connatix_report_1.as_id = abc_1.as_parameter)
  • Rows Removed by Join Filter: 62,340
102. 233.641 138,385.300 ↓ 1.1 144,484 1

Nested Loop Left Join (cost=240.61..21,864.97 rows=135,432 width=195) (actual time=56.493..138,385.300 rows=144,484 loops=1)

103. 146.451 146.451 ↑ 1.0 135,432 1

Index Scan Backward using idx_snapchat_stats_date on snapchat_stats snapchat_stats_1 (cost=0.42..6,050.09 rows=135,432 width=57) (actual time=3.754..146.451 rows=135,432 loops=1)

104. 7,313.328 138,005.208 ↑ 1.0 1 135,432

Hash Right Join (cost=240.19..240.30 rows=1 width=175) (actual time=0.931..1.019 rows=1 loops=135,432)

  • Hash Cond: (abc_1.creative_id = snapchat_ads_1_1.creative_id)
105. 15,980.976 130,014.720 ↓ 138.8 555 135,432

Group (cost=239.89..239.93 rows=4 width=96) (actual time=0.812..0.960 rows=555 loops=135,432)

  • Group Key: abc_1.creative_id, abc_1.as_parameter, abc_1.campaign_name
106.          

CTE asparam_assoc

107. 3.186 13.652 ↑ 2.4 131 1

HashAggregate (cost=143.77..150.75 rows=310 width=139) (actual time=13.610..13.652 rows=131 loops=1)

  • Group Key: snapchat_creatives_1_2.ad_account_id, snapchat_creatives_1_2.headline, concat('/', split_part(split_part(snapchat_creatives_1_2.url, '/'::text, 4), '/'::text, 1), '/'), split_part(split_part(snapchat_creatives_1_2.url, 'as='::text, 2), '&'::text, 1), snapchat_creatives_1_2.type, snapchat_creatives_1_2.created_at
108. 5.779 10.466 ↑ 2.1 149 1

Hash Join (cost=67.79..139.12 rows=310 width=139) (actual time=9.100..10.466 rows=149 loops=1)

  • Hash Cond: (snapchat_creatives_1_2.headline = snapchat_creatives_2_1.headline)
109. 0.947 0.947 ↓ 1.0 477 1

Seq Scan on snapchat_creatives snapchat_creatives_1_2 (cost=0.00..66.15 rows=469 width=261) (actual time=0.512..0.947 rows=477 loops=1)

  • Filter: ((url <> 'nan'::text) AND (review_status = 'APPROVED'::text))
  • Rows Removed by Filter: 285
110. 0.807 3.740 ↑ 9.0 5 1

Hash (cost=67.23..67.23 rows=45 width=22) (actual time=3.740..3.740 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
111. 1.809 2.933 ↑ 9.0 5 1

HashAggregate (cost=66.33..66.78 rows=45 width=22) (actual time=2.931..2.933 rows=5 loops=1)

  • Group Key: snapchat_creatives_2_1.headline
112. 1.124 1.124 ↑ 2.3 30 1

Seq Scan on snapchat_creatives snapchat_creatives_2_1 (cost=0.00..66.15 rows=70 width=22) (actual time=0.945..1.124 rows=30 loops=1)

  • Filter: ((url = 'nan'::text) AND (review_status = 'APPROVED'::text))
  • Rows Removed by Filter: 732
113.          

CTE full_creatives

114. 2.287 2.287 ↑ 1.1 762 1

Seq Scan on snapchat_creatives snapchat_creatives_1_3 (cost=0.00..62.10 rows=810 width=493) (actual time=0.968..2.287 rows=762 loops=1)

115.          

CTE abc

116. 8.468 41.122 ↓ 148.2 593 1

Hash Right Join (cost=18.28..26.92 rows=4 width=96) (actual time=36.644..41.122 rows=593 loops=1)

  • Hash Cond: ((asparam_assoc_1.ad_account_id = full_creatives_1.ad_account_id) AND (asparam_assoc_1.headline = full_creatives_1.headline))
  • Join Filter: ((full_creatives_1.type <> asparam_assoc_1.type) AND (full_creatives_1.created_at > (asparam_assoc_1.created_at - '00:05:00'::interval)) AND (full_creatives_1.created_at < (asparam_assoc_1.created_at + '00:05:00'::interval)))
  • Rows Removed by Join Filter: 8,207
117. 14.569 14.569 ↑ 2.4 131 1

CTE Scan on asparam_assoc asparam_assoc_1 (cost=0.00..6.20 rows=310 width=168) (actual time=14.467..14.569 rows=131 loops=1)

118. 0.603 18.085 ↓ 126.8 507 1

Hash (cost=18.22..18.22 rows=4 width=200) (actual time=18.085..18.085 rows=507 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 189kB
119. 17.482 17.482 ↓ 126.8 507 1

CTE Scan on full_creatives full_creatives_1 (cost=0.00..18.22 rows=4 width=200) (actual time=3.152..17.482 rows=507 loops=1)

  • Filter: (review_status = 'APPROVED'::text)
  • Rows Removed by Filter: 255
120. 107,126.712 114,033.744 ↓ 148.2 593 135,432

Sort (cost=0.12..0.13 rows=4 width=96) (actual time=0.811..0.842 rows=593 loops=135,432)

  • Sort Key: abc_1.creative_id, abc_1.as_parameter, abc_1.campaign_name
  • Sort Method: quicksort Memory: 108kB
121. 6,907.032 6,907.032 ↓ 148.2 593 135,432

CTE Scan on abc abc_1 (cost=0.00..0.08 rows=4 width=96) (actual time=0.000..0.051 rows=593 loops=135,432)

122. 135.432 677.160 ↑ 1.0 1 135,432

Hash (cost=0.29..0.29 rows=1 width=111) (actual time=0.005..0.005 rows=1 loops=135,432)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
123. 541.728 541.728 ↑ 1.0 1 135,432

Index Scan using idx_snapchat_ads_id on snapchat_ads snapchat_ads_1_1 (cost=0.28..0.29 rows=1 width=111) (actual time=0.003..0.004 rows=1 loops=135,432)

  • Index Cond: (id = snapchat_stats_1.id)
124. 5.104 11,600.702 ↓ 755.0 65,688 1

Materialize (cost=376,805.24..376,814.60 rows=87 width=32) (actual time=11,588.688..11,600.702 rows=65,688 loops=1)

125. 5.041 11,595.598 ↓ 43.9 3,822 1

Finalize GroupAggregate (cost=376,805.24..376,813.51 rows=87 width=35) (actual time=11,588.681..11,595.598 rows=3,822 loops=1)

  • Group Key: connatix_report_1.date, connatix_report_1.site, connatix_report_1.as_id
126. 148.450 11,590.557 ↓ 88.5 4,516 1

Gather Merge (cost=376,805.24..376,812.13 rows=51 width=35) (actual time=11,584.973..11,590.557 rows=4,516 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
127. 4.193 11,442.107 ↓ 44.3 2,258 2 / 2

Partial GroupAggregate (cost=375,805.23..375,806.38 rows=51 width=35) (actual time=11,437.905..11,442.107 rows=2,258 loops=2)

  • Group Key: connatix_report_1.date, connatix_report_1.site, connatix_report_1.as_id
128. 19.207 11,437.914 ↓ 286.7 14,620 2 / 2

Sort (cost=375,805.23..375,805.36 rows=51 width=35) (actual time=11,436.911..11,437.914 rows=14,620 loops=2)

  • Sort Key: connatix_report_1.date, connatix_report_1.site, connatix_report_1.as_id
  • Sort Method: quicksort Memory: 1,589kB
  • Worker 0: Sort Method: quicksort Memory: 1,464kB
129. 11,418.707 11,418.707 ↓ 286.7 14,620 2 / 2

Parallel Seq Scan on connatix_report connatix_report_1 (cost=0.00..375,803.79 rows=51 width=35) (actual time=32.805..11,418.707 rows=14,620 loops=2)

  • Filter: ((as_id ~~ '%%211%%'::text) AND (length(as_id) = 13))
  • Rows Removed by Filter: 3,833,038
130. 24.911 33,436.688 ↓ 380.1 264,933 1

Sort (cost=706,074.90..706,076.64 rows=697 width=44) (actual time=33,418.843..33,436.688 rows=264,933 loops=1)

  • Sort Key: zyx_1.date
  • Sort Method: quicksort Memory: 529kB
131. 0.871 33,411.777 ↓ 6.2 4,307 1

Subquery Scan on zyx_1 (cost=706,028.04..706,041.98 rows=697 width=44) (actual time=33,410.459..33,411.777 rows=4,307 loops=1)

132. 18.779 33,410.906 ↓ 6.2 4,307 1

HashAggregate (cost=706,028.04..706,035.01 rows=697 width=44) (actual time=33,409.948..33,410.906 rows=4,307 loops=1)

  • Group Key: dfp_custom_criteria_1.date, (split_part(dfp_custom_criteria_1.custom_criteria, 'as='::text, 2)), dfp_custom_criteria_1.total_inventory_level_cpm_and_cpc_revenue
133. 1.037 33,392.127 ↓ 6.2 4,307 1

Append (cost=1,000.00..706,022.82 rows=697 width=44) (actual time=7.956..33,392.127 rows=4,307 loops=1)

134. 0.000 7,350.931 ↓ 7.1 4,210 1

Gather (cost=1,000.00..217,749.57 rows=593 width=44) (actual time=7.596..7,350.931 rows=4,210 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
135. 7,351.333 7,351.333 ↓ 6.0 2,105 2 / 2

Parallel Seq Scan on dfp_custom_criteria dfp_custom_criteria_1 (cost=0.00..216,690.27 rows=349 width=44) (actual time=2.971..7,351.333 rows=2,105 loops=2)

  • Filter: ((custom_criteria ~~ '%%as=211%%'::text) AND (date < '2020-08-18'::date))
  • Rows Removed by Filter: 3,116,855
136. 1.875 26,040.159 ↑ 1.1 97 1

GroupAggregate (cost=488,260.71..488,262.79 rows=104 width=31) (actual time=26,039.930..26,040.159 rows=97 loops=1)

  • Group Key: ga_gemini_tag_1.date, ga_gemini_tag_1.dimension21
137. 1.835 26,038.284 ↓ 12.7 1,325 1

Sort (cost=488,260.71..488,260.97 rows=104 width=31) (actual time=26,038.219..26,038.284 rows=1,325 loops=1)

  • Sort Key: ga_gemini_tag_1.date, ga_gemini_tag_1.dimension21
  • Sort Method: quicksort Memory: 151kB
138. 26,036.449 26,036.449 ↓ 12.7 1,325 1

Index Scan using idx_17910449_date on ga_gemini_tag ga_gemini_tag_1 (cost=0.57..488,257.23 rows=104 width=31) (actual time=79.833..26,036.449 rows=1,325 loops=1)

  • Index Cond: (date >= '2020-08-18'::date)
  • Filter: (dimension21 ~~ '211%%'::text)
  • Rows Removed by Filter: 1,154,646
139. 0.012 12,228.334 ↑ 12,782.4 5 1

Hash (cost=474,377.89..474,377.89 rows=63,912 width=25) (actual time=12,228.334..12,228.334 rows=5 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 513kB
140. 0.359 12,228.322 ↑ 12,782.4 5 1

Subquery Scan on v_taboola_revenue_1 (cost=467,716.93..474,377.89 rows=63,912 width=25) (actual time=12,228.315..12,228.322 rows=5 loops=1)

141. 2.499 12,227.963 ↑ 12,782.4 5 1

Finalize GroupAggregate (cost=467,716.93..473,738.77 rows=63,912 width=57) (actual time=12,227.958..12,227.963 rows=5 loops=1)

  • Group Key: taboola_revenue_1.date, taboola_revenue_1.as_id
142. 10.044 12,225.464 ↑ 7,689.6 5 1

Gather Merge (cost=467,716.93..472,811.29 rows=38,448 width=25) (actual time=12,225.424..12,225.464 rows=5 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
143. 0.533 12,215.420 ↑ 19,224.0 2 2 / 2

Partial GroupAggregate (cost=466,716.92..467,485.88 rows=38,448 width=25) (actual time=12,215.413..12,215.420 rows=2 loops=2)

  • Group Key: taboola_revenue_1.date, taboola_revenue_1.as_id
144. 0.038 12,214.887 ↑ 9,612.0 4 2 / 2

Sort (cost=466,716.92..466,813.04 rows=38,448 width=25) (actual time=12,214.886..12,214.887 rows=4 loops=2)

  • Sort Key: taboola_revenue_1.date, taboola_revenue_1.as_id
  • Sort Method: quicksort Memory: 25kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
145. 12,214.849 12,214.849 ↑ 9,612.0 4 2 / 2

Parallel Seq Scan on taboola_revenue taboola_revenue_1 (cost=0.00..463,788.99 rows=38,448 width=25) (actual time=9,669.989..12,214.849 rows=4 loops=2)

  • Filter: ((placement IS NOT NULL) AND (""left""(as_id, 3) = '211'::text))
  • Rows Removed by Filter: 6,572,089
146. 0.064 9.625 ↑ 1.0 223 1

Hash (cost=35.97..35.97 rows=225 width=74) (actual time=9.625..9.625 rows=223 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 32kB
147. 1.436 9.561 ↑ 1.0 223 1

Hash Left Join (cost=15.62..35.97 rows=225 width=74) (actual time=7.209..9.561 rows=223 loops=1)

  • Hash Cond: (snapchat_adsquads_1_1.campaign_id = snapchat_campaigns_1_1.id)
148. 3.285 3.285 ↑ 1.0 223 1

Seq Scan on snapchat_adsquads snapchat_adsquads_1_1 (cost=0.00..17.25 rows=225 width=74) (actual time=0.992..3.285 rows=223 loops=1)

149. 0.379 4.840 ↑ 1.5 172 1

Hash (cost=12.50..12.50 rows=250 width=37) (actual time=4.840..4.840 rows=172 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 20kB
150. 4.461 4.461 ↑ 1.5 172 1

Seq Scan on snapchat_campaigns snapchat_campaigns_1_1 (cost=0.00..12.50 rows=250 width=37) (actual time=2.370..4.461 rows=172 loops=1)

151. 0.561 820.555 ↑ 3.4 2,112 1

Hash (cost=38,092.17..38,092.17 rows=7,150 width=33) (actual time=820.554..820.555 rows=2,112 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 188kB
152. 0.599 819.994 ↑ 3.4 2,112 1

Subquery Scan on avantis_1 (cost=37,347.03..38,092.17 rows=7,150 width=33) (actual time=818.371..819.994 rows=2,112 loops=1)

153. 2.402 819.395 ↑ 3.4 2,112 1

Finalize GroupAggregate (cost=37,347.03..38,020.67 rows=7,150 width=33) (actual time=817.986..819.395 rows=2,112 loops=1)

  • Group Key: avantis_subid_daily_report_1.date, avantis_subid_daily_report_1.sub_id
154. 8.258 816.993 ↑ 2.0 2,112 1

Gather Merge (cost=37,347.03..37,916.91 rows=4,301 width=33) (actual time=816.196..816.993 rows=2,112 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
155. 1.254 808.735 ↑ 4.1 1,056 2 / 2

Partial GroupAggregate (cost=36,347.02..36,433.04 rows=4,301 width=33) (actual time=808.284..808.735 rows=1,056 loops=2)

  • Group Key: avantis_subid_daily_report_1.date, avantis_subid_daily_report_1.sub_id
156. 2.845 807.481 ↑ 4.1 1,058 2 / 2

Sort (cost=36,347.02..36,357.77 rows=4,301 width=33) (actual time=807.402..807.481 rows=1,058 loops=2)

  • Sort Key: avantis_subid_daily_report_1.date, avantis_subid_daily_report_1.sub_id
  • Sort Method: quicksort Memory: 138kB
  • Worker 0: Sort Method: quicksort Memory: 100kB
157. 804.636 804.636 ↑ 4.1 1,058 2 / 2

Parallel Seq Scan on avantis_subid_daily_report avantis_subid_daily_report_1 (cost=0.00..36,087.44 rows=4,301 width=33) (actual time=0.836..804.636 rows=1,058 loops=2)

  • Filter: (""left""(sub_id, 3) = '211'::text)
  • Rows Removed by Filter: 730,170
158. 17,581.387 19,216.372 ↓ 2,689.0 2,689 144,484

Materialize (cost=1,000.00..65,615.35 rows=1 width=37) (actual time=0.000..0.133 rows=2,689 loops=144,484)

159. 2.587 1,634.985 ↓ 2,689.0 2,689 1

Gather (cost=1,000.00..65,615.35 rows=1 width=37) (actual time=4.852..1,634.985 rows=2,689 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
160. 1,632.398 1,632.398 ↓ 1,344.0 1,344 2 / 2

Parallel Seq Scan on dfp_daily_revenue_avantis dfp_daily_revenue_avantis_1 (cost=0.00..64,615.25 rows=1 width=37) (actual time=2.884..1,632.398 rows=1,344 loops=2)

  • Filter: ((custom_criteria ~~ '%%as=211%%'::text) AND (length(split_part(custom_criteria, '='::text, 2)) = 13))
  • Rows Removed by Filter: 1,094,428
161.          

CTE swipes_by_campaign_date

162. 30.147 30.281 ↑ 47.7 270 1

HashAggregate (cost=3,539.17..3,700.04 rows=12,870 width=96) (actual time=29.479..30.281 rows=270 loops=1)

  • Group Key: all_data_2.date, all_data_2.campaign_id
163. 0.134 0.134 ↑ 158.5 812 1

CTE Scan on all_data all_data_2 (cost=0.00..2,573.94 rows=128,697 width=72) (actual time=0.002..0.134 rows=812 loops=1)

164.          

CTE xyz

165. 4.037 243,441.090 ↑ 158.5 812 1

Merge Left Join (cost=25,968.60..28,945.44 rows=128,697 width=360) (actual time=243,440.294..243,441.090 rows=812 loops=1)

  • Merge Cond: ((all_data_3.date = swipes_by_campaign_date_1.date) AND (all_data_3.campaign_id = swipes_by_campaign_date_1.campaign_id))
166. 1.984 243,405.721 ↑ 158.5 812 1

Sort (cost=24,832.71..25,154.45 rows=128,697 width=328) (actual time=243,405.649..243,405.721 rows=812 loops=1)

  • Sort Key: all_data_3.date, all_data_3.campaign_id
  • Sort Method: quicksort Memory: 443kB
167. 243,403.737 243,403.737 ↑ 158.5 812 1

CTE Scan on all_data all_data_3 (cost=0.00..2,573.94 rows=128,697 width=328) (actual time=243,176.431..243,403.737 rows=812 loops=1)

168. 0.579 31.332 ↑ 16.1 799 1

Sort (cost=1,135.89..1,168.06 rows=12,870 width=96) (actual time=31.290..31.332 rows=799 loops=1)

  • Sort Key: swipes_by_campaign_date_1.date, swipes_by_campaign_date_1.campaign_id
  • Sort Method: quicksort Memory: 62kB
169. 30.753 30.753 ↑ 47.7 270 1

CTE Scan on swipes_by_campaign_date swipes_by_campaign_date_1 (cost=0.00..257.40 rows=12,870 width=96) (actual time=29.859..30.753 rows=270 loops=1)

170. 243,443.876 243,443.876 ↑ 299.9 427 1

CTE Scan on xyz (cost=0.00..4,496.36 rows=128,054 width=328) (actual time=243,442.113..243,443.876 rows=427 loops=1)

  • Filter: (impressions <> 0)
  • Rows Removed by Filter: 385
Planning time : 163.995 ms