explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dkvk

Settings
# exclusive inclusive rows x rows loops node
1. 2.994 454,077.985 ↑ 863.0 7,889 1

Merge Right Join (cost=6,077,294.85..6,179,788.02 rows=6,807,991 width=264) (actual time=454,071.537..454,077.985 rows=7,889 loops=1)

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

CTE ga_pv

3. 0.638 238,094.410 ↑ 15.5 686 1

GroupAggregate (cost=4,247,573.00..4,247,812.25 rows=10,633 width=51) (actual time=238,093.438..238,094.410 rows=686 loops=1)

  • Group Key: ga_campaign_creative.date, ga_campaign_creative.dimension21
4. 3.443 238,093.772 ↑ 2.7 3,944 1

Sort (cost=4,247,573.00..4,247,599.59 rows=10,633 width=27) (actual time=238,093.424..238,093.772 rows=3,944 loops=1)

  • Sort Key: ga_campaign_creative.date, ga_campaign_creative.dimension21
  • Sort Method: quicksort Memory: 405kB
5. 0.961 238,090.329 ↑ 2.7 3,944 1

Nested Loop (cost=1,807,186.66..4,246,861.85 rows=10,633 width=27) (actual time=236,650.016..238,090.329 rows=3,944 loops=1)

6. 0.133 236,646.520 ↑ 9.1 22 1

HashAggregate (cost=1,807,186.09..1,807,188.09 rows=200 width=32) (actual time=236,646.454..236,646.520 rows=22 loops=1)

  • Group Key: xyz_1.as_parameter
7. 0.530 236,646.387 ↑ 299.9 427 1

Sort (cost=1,805,265.28..1,805,585.41 rows=128,054 width=328) (actual time=236,646.369..236,646.387 rows=427 loops=1)

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

CTE all_data

9. 112.299 236,640.971 ↑ 158.5 812 1

GroupAggregate (cost=1,723,017.68..1,747,579.89 rows=128,697 width=348) (actual time=236,441.791..236,640.971 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. 736.846 236,528.672 ↑ 1.0 144,484 1

Sort (cost=1,723,017.68..1,723,388.22 rows=148,215 width=300) (actual time=236,441.694..236,528.672 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. 26,905.226 235,791.826 ↑ 1.0 144,484 1

Nested Loop Left Join (cost=1,597,686.08..1,698,121.24 rows=148,215 width=300) (actual time=53,539.921..235,791.826 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. 67.574 190,248.164 ↑ 1.0 144,484 1

Hash Left Join (cost=1,596,686.08..1,628,800.51 rows=148,215 width=264) (actual time=52,306.201..190,248.164 rows=144,484 loops=1)

  • Hash Cond: ((abc.as_parameter = avantis.as_id) AND (snapchat_stats.date = avantis.date))
13. 92.826 189,430.034 ↑ 1.0 144,484 1

Hash Left Join (cost=1,558,496.11..1,589,832.37 rows=148,215 width=256) (actual time=51,555.602..189,430.034 rows=144,484 loops=1)

  • Hash Cond: (snapchat_ads_1.ad_squad_id = snapchat_adsquads_1.id)
14. 65.234 189,329.496 ↑ 1.0 144,484 1

Hash Left Join (cost=1,558,457.33..1,587,842.46 rows=148,215 width=219) (actual time=51,547.873..189,329.496 rows=144,484 loops=1)

  • Hash Cond: ((snapchat_stats.date = v_taboola_revenue.date) AND (abc.as_parameter = v_taboola_revenue.as_id))
15. 96.920 178,658.563 ↓ 1.1 144,484 1

Merge Left Join (cost=1,083,120.76..1,111,794.85 rows=135,432 width=211) (actual time=40,941.912..178,658.563 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. 53.222 146,407.959 ↓ 1.1 144,484 1

Merge Left Join (cost=377,045.86..399,724.07 rows=135,432 width=203) (actual time=8,803.680..146,407.959 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. 279.433 137,555.407 ↓ 1.1 144,484 1

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

18. 218.790 218.790 ↑ 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=0.012..218.790 rows=135,432 loops=1)

19. 7,042.464 137,057.184 ↑ 1.0 1 135,432

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

  • Hash Cond: (abc.creative_id = snapchat_ads_1.creative_id)
20. 15,845.544 129,337.560 ↓ 138.8 555 135,432

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

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

CTE asparam_assoc

22. 0.000 1.805 ↑ 2.4 131 1

HashAggregate (cost=143.77..150.75 rows=310 width=139) (actual time=1.774..1.805 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.361 2.027 ↑ 2.1 149 1

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

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

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

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

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

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

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

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

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

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

CTE full_creatives

29. 0.121 0.121 ↑ 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.004..0.121 rows=762 loops=1)

30.          

CTE abc

31. 3.754 6.827 ↓ 148.2 593 1

Hash Right Join (cost=18.28..26.92 rows=4 width=96) (actual time=2.993..6.827 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. 1.885 1.885 ↑ 2.4 131 1

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

33. 0.293 1.188 ↓ 126.8 507 1

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

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

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

  • Filter: (review_status = 'APPROVED'::text)
  • Rows Removed by Filter: 255
35. 106,991.280 113,492.016 ↓ 148.2 593 135,432

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

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

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

37. 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
38. 541.728 541.728 ↑ 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.004..0.004 rows=1 loops=135,432)

  • Index Cond: (id = snapchat_stats.id)
39. 4.632 8,799.330 ↓ 755.0 65,688 1

Materialize (cost=376,805.24..376,814.60 rows=87 width=32) (actual time=8,780.840..8,799.330 rows=65,688 loops=1)

40. 0.000 8,794.698 ↓ 43.9 3,822 1

Finalize GroupAggregate (cost=376,805.24..376,813.51 rows=87 width=35) (actual time=8,780.836..8,794.698 rows=3,822 loops=1)

  • Group Key: connatix_report.date, connatix_report.site, connatix_report.as_id
41. 15.670 8,798.285 ↓ 88.2 4,499 1

Gather Merge (cost=376,805.24..376,812.13 rows=51 width=35) (actual time=8,780.817..8,798.285 rows=4,499 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
42. 4.356 8,782.615 ↓ 44.1 2,250 2 / 2

Partial GroupAggregate (cost=375,805.23..375,806.38 rows=51 width=35) (actual time=8,776.916..8,782.615 rows=2,250 loops=2)

  • Group Key: connatix_report.date, connatix_report.site, connatix_report.as_id
43. 20.750 8,778.259 ↓ 286.7 14,620 2 / 2

Sort (cost=375,805.23..375,805.36 rows=51 width=35) (actual time=8,776.895..8,778.259 rows=14,620 loops=2)

  • Sort Key: connatix_report.date, connatix_report.site, connatix_report.as_id
  • Sort Method: quicksort Memory: 1,551kB
  • Worker 0: Sort Method: quicksort Memory: 1,503kB
44. 8,757.509 8,757.509 ↓ 286.7 14,620 2 / 2

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

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

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

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

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

47. 8.914 32,136.302 ↓ 6.2 4,307 1

HashAggregate (cost=706,028.04..706,035.01 rows=697 width=44) (actual time=32,135.380..32,136.302 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,127.388 ↓ 6.2 4,307 1

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

49. 3.983 5,307.983 ↓ 7.1 4,210 1

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

  • Workers Planned: 1
  • Workers Launched: 1
50. 5,304.000 5,304.000 ↓ 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=4.044..5,304.000 rows=2,105 loops=2)

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

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

  • Group Key: ga_gemini_tag.date, ga_gemini_tag.dimension21
52. 0.908 26,820.615 ↓ 12.7 1,325 1

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

  • Sort Key: ga_gemini_tag.date, ga_gemini_tag.dimension21
  • Sort Method: quicksort Memory: 151kB
53. 26,819.707 26,819.707 ↓ 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=77.637..26,819.707 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.010 10,605.699 ↑ 12,782.4 5 1

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

  • Buckets: 65,536 Batches: 1 Memory Usage: 513kB
55. 0.003 10,605.689 ↑ 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=10,605.684..10,605.689 rows=5 loops=1)

56. 0.000 10,605.686 ↑ 12,782.4 5 1

Finalize GroupAggregate (cost=467,716.93..473,738.77 rows=63,912 width=57) (actual time=10,605.683..10,605.686 rows=5 loops=1)

  • Group Key: taboola_revenue.date, taboola_revenue.as_id
57. 8.234 10,609.122 ↑ 7,689.6 5 1

Gather Merge (cost=467,716.93..472,811.29 rows=38,448 width=25) (actual time=10,605.670..10,609.122 rows=5 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
58. 0.043 10,600.888 ↑ 19,224.0 2 2 / 2

Partial GroupAggregate (cost=466,716.92..467,485.88 rows=38,448 width=25) (actual time=10,600.871..10,600.888 rows=2 loops=2)

  • Group Key: taboola_revenue.date, taboola_revenue.as_id
59. 0.039 10,600.845 ↑ 9,612.0 4 2 / 2

Sort (cost=466,716.92..466,813.04 rows=38,448 width=25) (actual time=10,600.844..10,600.845 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. 10,600.806 10,600.806 ↑ 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=8,662.138..10,600.806 rows=4 loops=2)

  • Filter: ((placement IS NOT NULL) AND (""left""(as_id, 3) = '211'::text))
  • Rows Removed by Filter: 6,571,838
61. 0.052 7.712 ↑ 1.0 223 1

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

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

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

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

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

64. 0.036 6.148 ↑ 1.5 172 1

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

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

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

66. 0.744 750.556 ↑ 3.4 2,112 1

Hash (cost=38,082.80..38,082.80 rows=7,145 width=33) (actual time=750.556..750.556 rows=2,112 loops=1)

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

Subquery Scan on avantis (cost=37,338.18..38,082.80 rows=7,145 width=33) (actual time=744.052..749.812 rows=2,112 loops=1)

68. 0.000 749.508 ↑ 3.4 2,112 1

Finalize GroupAggregate (cost=37,338.18..38,011.35 rows=7,145 width=33) (actual time=744.050..749.508 rows=2,112 loops=1)

  • Group Key: avantis_subid_daily_report.date, avantis_subid_daily_report.sub_id
69. 7.521 749.629 ↑ 2.0 2,112 1

Gather Merge (cost=37,338.18..37,907.66 rows=4,298 width=33) (actual time=744.042..749.629 rows=2,112 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
70. 1.354 742.108 ↑ 4.1 1,056 2 / 2

Partial GroupAggregate (cost=36,338.17..36,424.13 rows=4,298 width=33) (actual time=740.655..742.108 rows=1,056 loops=2)

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

Sort (cost=36,338.17..36,348.91 rows=4,298 width=33) (actual time=740.642..740.754 rows=1,058 loops=2)

  • Sort Key: avantis_subid_daily_report.date, avantis_subid_daily_report.sub_id
  • Sort Method: quicksort Memory: 129kB
  • Worker 0: Sort Method: quicksort Memory: 132kB
72. 735.584 735.584 ↑ 4.1 1,058 2 / 2

Parallel Seq Scan on avantis_subid_daily_report (cost=0.00..36,078.79 rows=4,298 width=33) (actual time=0.814..735.584 rows=1,058 loops=2)

  • Filter: (""left""(sub_id, 3) = '211'::text)
  • Rows Removed by Filter: 730,100
73. 17,404.489 18,638.436 ↓ 2,689.0 2,689 144,484

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

74. 6.926 1,233.947 ↓ 2,689.0 2,689 1

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

  • Workers Planned: 1
  • Workers Launched: 1
75. 1,227.021 1,227.021 ↓ 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.514..1,227.021 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,395
76.          

CTE swipes_by_campaign_date

77. 0.438 0.543 ↑ 47.7 270 1

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

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

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

79.          

CTE xyz

80. 0.684 236,645.181 ↑ 158.5 812 1

Merge Left Join (cost=25,968.60..28,945.44 rows=128,697 width=360) (actual time=236,644.420..236,645.181 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. 1.429 236,643.251 ↑ 158.5 812 1

Sort (cost=24,832.71..25,154.45 rows=128,697 width=328) (actual time=236,643.197..236,643.251 rows=812 loops=1)

  • Sort Key: all_data_1.date, all_data_1.campaign_id
  • Sort Method: quicksort Memory: 443kB
82. 236,641.822 236,641.822 ↑ 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=236,441.796..236,641.822 rows=812 loops=1)

83. 0.598 1.246 ↑ 16.1 799 1

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

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

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

85. 236,645.857 236,645.857 ↑ 299.9 427 1

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

  • Filter: (impressions <> 0)
  • Rows Removed by Filter: 385
86. 1,442.848 1,442.848 ↓ 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.268..65.584 rows=179 loops=22)

  • Index Cond: (dimension21 = xyz_1.as_parameter)
  • Filter: (dimension21 ~~ '211%%'::text)
87. 4.811 238,099.419 ↑ 15.5 686 1

Sort (cost=923.81..950.39 rows=10,633 width=32) (actual time=238,095.244..238,099.419 rows=686 loops=1)

  • Sort Key: ga_pv.dimension21
  • Sort Method: quicksort Memory: 57kB
88. 238,094.608 238,094.608 ↑ 15.5 686 1

CTE Scan on ga_pv (cost=0.00..212.66 rows=10,633 width=32) (actual time=238,093.442..238,094.608 rows=686 loops=1)

89. 0.879 215,975.572 ↑ 16.3 7,834 1

Materialize (cost=1,828,558.80..1,829,199.07 rows=128,054 width=264) (actual time=215,975.159..215,975.572 rows=7,834 loops=1)

90. 3.056 215,974.693 ↑ 299.9 427 1

Sort (cost=1,828,558.80..1,828,878.93 rows=128,054 width=264) (actual time=215,974.671..215,974.693 rows=427 loops=1)

  • Sort Key: snapchat.as_parameter
  • Sort Method: quicksort Memory: 172kB
91. 0.322 215,971.637 ↑ 299.9 427 1

Subquery Scan on snapchat (cost=1,806,865.95..1,808,466.63 rows=128,054 width=264) (actual time=215,971.296..215,971.637 rows=427 loops=1)

92. 3.653 215,971.315 ↑ 299.9 427 1

Sort (cost=1,806,865.95..1,807,186.09 rows=128,054 width=328) (actual time=215,971.294..215,971.315 rows=427 loops=1)

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

CTE all_data

94. 117.185 215,919.954 ↑ 158.5 812 1

GroupAggregate (cost=1,723,017.68..1,747,579.89 rows=128,697 width=348) (actual time=215,720.675..215,919.954 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. 762.013 215,802.769 ↑ 1.0 144,484 1

Sort (cost=1,723,017.68..1,723,388.22 rows=148,215 width=300) (actual time=215,713.535..215,802.769 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. 27,240.310 215,040.756 ↑ 1.0 144,484 1

Nested Loop Left Join (cost=1,597,686.08..1,698,121.24 rows=148,215 width=300) (actual time=27,095.824..215,040.756 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. 69.428 168,873.042 ↑ 1.0 144,484 1

Hash Left Join (cost=1,596,686.08..1,628,800.51 rows=148,215 width=264) (actual time=26,045.024..168,873.042 rows=144,484 loops=1)

  • Hash Cond: ((abc_1.as_parameter = avantis_1.as_id) AND (snapchat_stats_1.date = avantis_1.date))
98. 97.440 168,599.169 ↑ 1.0 144,484 1

Hash Left Join (cost=1,558,496.11..1,589,832.37 rows=148,215 width=256) (actual time=25,839.807..168,599.169 rows=144,484 loops=1)

  • Hash Cond: (snapchat_ads_1_1.ad_squad_id = snapchat_adsquads_1_1.id)
99. 66.562 168,499.836 ↑ 1.0 144,484 1

Hash Left Join (cost=1,558,457.33..1,587,842.46 rows=148,215 width=219) (actual time=25,837.162..168,499.836 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. 103.832 158,157.585 ↓ 1.1 144,484 1

Merge Left Join (cost=1,083,120.76..1,111,794.85 rows=135,432 width=211) (actual time=15,560.670..158,157.585 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. 55.899 151,169.105 ↓ 1.1 144,484 1

Merge Left Join (cost=377,045.86..399,724.07 rows=135,432 width=203) (actual time=8,691.524..151,169.105 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. 204.653 142,424.358 ↓ 1.1 144,484 1

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

103. 286.969 286.969 ↑ 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=0.010..286.969 rows=135,432 loops=1)

104. 7,448.760 141,932.736 ↑ 1.0 1 135,432

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

  • Hash Cond: (abc_1.creative_id = snapchat_ads_1_1.creative_id)
105. 16,658.136 133,806.816 ↓ 138.8 555 135,432

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

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

CTE asparam_assoc

107. 0.000 1.079 ↑ 2.4 131 1

HashAggregate (cost=143.77..150.75 rows=310 width=139) (actual time=1.047..1.079 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. 0.959 1.337 ↑ 2.1 149 1

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

  • Hash Cond: (snapchat_creatives_1_2.headline = snapchat_creatives_2_1.headline)
109. 0.169 0.169 ↓ 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.007..0.169 rows=477 loops=1)

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

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

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

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

  • Group Key: snapchat_creatives_2_1.headline
112. 0.195 0.195 ↑ 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.068..0.195 rows=30 loops=1)

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

CTE full_creatives

114. 0.169 0.169 ↑ 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.007..0.169 rows=762 loops=1)

115.          

CTE abc

116. 3.261 5.333 ↓ 148.2 593 1

Hash Right Join (cost=18.28..26.92 rows=4 width=96) (actual time=2.008..5.333 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. 1.139 1.139 ↑ 2.4 131 1

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

118. 0.180 0.933 ↓ 126.8 507 1

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

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

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

  • Filter: (review_status = 'APPROVED'::text)
  • Rows Removed by Filter: 255
120. 110,241.648 117,148.680 ↓ 148.2 593 135,432

Sort (cost=0.12..0.13 rows=4 width=96) (actual time=0.836..0.865 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.004..0.004 rows=1 loops=135,432)

  • Index Cond: (id = snapchat_stats_1.id)
124. 4.664 8,688.848 ↓ 755.0 65,688 1

Materialize (cost=376,805.24..376,814.60 rows=87 width=32) (actual time=8,674.369..8,688.848 rows=65,688 loops=1)

125. 0.190 8,684.184 ↓ 43.9 3,822 1

Finalize GroupAggregate (cost=376,805.24..376,813.51 rows=87 width=35) (actual time=8,674.367..8,684.184 rows=3,822 loops=1)

  • Group Key: connatix_report_1.date, connatix_report_1.site, connatix_report_1.as_id
126. 8.722 8,683.994 ↓ 89.0 4,540 1

Gather Merge (cost=376,805.24..376,812.13 rows=51 width=35) (actual time=8,674.345..8,683.994 rows=4,540 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
127. 4.561 8,675.272 ↓ 44.5 2,270 2 / 2

Partial GroupAggregate (cost=375,805.23..375,806.38 rows=51 width=35) (actual time=8,669.310..8,675.272 rows=2,270 loops=2)

  • Group Key: connatix_report_1.date, connatix_report_1.site, connatix_report_1.as_id
128. 20.090 8,670.711 ↓ 286.7 14,620 2 / 2

Sort (cost=375,805.23..375,805.36 rows=51 width=35) (actual time=8,669.289..8,670.711 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,545kB
  • Worker 0: Sort Method: quicksort Memory: 1,509kB
129. 8,650.621 8,650.621 ↓ 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=14.202..8,650.621 rows=14,620 loops=2)

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

Sort (cost=706,074.90..706,076.64 rows=697 width=44) (actual time=6,868.124..6,884.648 rows=264,933 loops=1)

  • Sort Key: zyx_1.date
  • Sort Method: quicksort Memory: 529kB
131. 0.374 6,866.671 ↓ 6.2 4,307 1

Subquery Scan on zyx_1 (cost=706,028.04..706,041.98 rows=697 width=44) (actual time=6,865.329..6,866.671 rows=4,307 loops=1)

132. 5.357 6,866.297 ↓ 6.2 4,307 1

HashAggregate (cost=706,028.04..706,035.01 rows=697 width=44) (actual time=6,865.327..6,866.297 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. 0.916 6,860.940 ↓ 6.2 4,307 1

Append (cost=1,000.00..706,022.82 rows=697 width=44) (actual time=9.397..6,860.940 rows=4,307 loops=1)

134. 3.391 5,197.625 ↓ 7.1 4,210 1

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

  • Workers Planned: 1
  • Workers Launched: 1
135. 5,194.234 5,194.234 ↓ 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=4.717..5,194.234 rows=2,105 loops=2)

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

GroupAggregate (cost=488,260.71..488,262.79 rows=104 width=31) (actual time=1,662.181..1,662.399 rows=97 loops=1)

  • Group Key: ga_gemini_tag_1.date, ga_gemini_tag_1.dimension21
137. 0.720 1,660.757 ↓ 12.7 1,325 1

Sort (cost=488,260.71..488,260.97 rows=104 width=31) (actual time=1,660.697..1,660.757 rows=1,325 loops=1)

  • Sort Key: ga_gemini_tag_1.date, ga_gemini_tag_1.dimension21
  • Sort Method: quicksort Memory: 151kB
138. 1,660.037 1,660.037 ↓ 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=4.778..1,660.037 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.266 10,275.689 ↑ 12,782.4 5 1

Hash (cost=474,377.89..474,377.89 rows=63,912 width=25) (actual time=10,275.688..10,275.689 rows=5 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 513kB
140. 0.226 10,275.423 ↑ 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=10,275.417..10,275.423 rows=5 loops=1)

141. 1.128 10,275.197 ↑ 12,782.4 5 1

Finalize GroupAggregate (cost=467,716.93..473,738.77 rows=63,912 width=57) (actual time=10,275.192..10,275.197 rows=5 loops=1)

  • Group Key: taboola_revenue_1.date, taboola_revenue_1.as_id
142. 4.235 10,274.069 ↑ 7,689.6 5 1

Gather Merge (cost=467,716.93..472,811.29 rows=38,448 width=25) (actual time=10,274.006..10,274.069 rows=5 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
143. 0.665 10,269.834 ↑ 19,224.0 2 2 / 2

Partial GroupAggregate (cost=466,716.92..467,485.88 rows=38,448 width=25) (actual time=10,269.831..10,269.834 rows=2 loops=2)

  • Group Key: taboola_revenue_1.date, taboola_revenue_1.as_id
144. 0.034 10,269.169 ↑ 9,612.0 4 2 / 2

Sort (cost=466,716.92..466,813.04 rows=38,448 width=25) (actual time=10,269.165..10,269.169 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. 10,269.135 10,269.135 ↑ 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=8,154.790..10,269.135 rows=4 loops=2)

  • Filter: ((placement IS NOT NULL) AND (""left""(as_id, 3) = '211'::text))
  • Rows Removed by Filter: 6,571,838
146. 0.325 1.893 ↑ 1.0 223 1

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

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

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

  • Hash Cond: (snapchat_adsquads_1_1.campaign_id = snapchat_campaigns_1_1.id)
148. 0.286 0.286 ↑ 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.258..0.286 rows=223 loops=1)

149. 0.240 0.483 ↑ 1.5 172 1

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

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

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

151. 0.807 204.445 ↑ 3.4 2,112 1

Hash (cost=38,082.80..38,082.80 rows=7,145 width=33) (actual time=204.445..204.445 rows=2,112 loops=1)

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

Subquery Scan on avantis_1 (cost=37,338.18..38,082.80 rows=7,145 width=33) (actual time=201.898..203.638 rows=2,112 loops=1)

153. 2.323 202.880 ↑ 3.4 2,112 1

Finalize GroupAggregate (cost=37,338.18..38,011.35 rows=7,145 width=33) (actual time=201.367..202.880 rows=2,112 loops=1)

  • Group Key: avantis_subid_daily_report_1.date, avantis_subid_daily_report_1.sub_id
154. 4.474 200.557 ↑ 2.0 2,112 1

Gather Merge (cost=37,338.18..37,907.66 rows=4,298 width=33) (actual time=199.727..200.557 rows=2,112 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
155. 1.097 196.083 ↑ 4.1 1,056 2 / 2

Partial GroupAggregate (cost=36,338.17..36,424.13 rows=4,298 width=33) (actual time=195.623..196.083 rows=1,056 loops=2)

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

Sort (cost=36,338.17..36,348.91 rows=4,298 width=33) (actual time=194.906..194.986 rows=1,058 loops=2)

  • Sort Key: avantis_subid_daily_report_1.date, avantis_subid_daily_report_1.sub_id
  • Sort Method: quicksort Memory: 132kB
  • Worker 0: Sort Method: quicksort Memory: 130kB
157. 192.063 192.063 ↑ 4.1 1,058 2 / 2

Parallel Seq Scan on avantis_subid_daily_report avantis_subid_daily_report_1 (cost=0.00..36,078.79 rows=4,298 width=33) (actual time=0.410..192.063 rows=1,058 loops=2)

  • Filter: (""left""(sub_id, 3) = '211'::text)
  • Rows Removed by Filter: 730,100
158. 17,879.473 18,927.404 ↓ 2,689.0 2,689 144,484

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

159. 2.673 1,047.931 ↓ 2,689.0 2,689 1

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

  • Workers Planned: 1
  • Workers Launched: 1
160. 1,045.258 1,045.258 ↓ 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.346..1,045.258 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,395
161.          

CTE swipes_by_campaign_date

162. 30.854 31.343 ↑ 47.7 270 1

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

  • Group Key: all_data_2.date, all_data_2.campaign_id
163. 0.489 0.489 ↑ 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.358..0.489 rows=812 loops=1)

164.          

CTE xyz

165. 2.654 215,964.593 ↑ 158.5 812 1

Merge Left Join (cost=25,968.60..28,945.44 rows=128,697 width=360) (actual time=215,963.764..215,964.593 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. 2.898 215,928.664 ↑ 158.5 812 1

Sort (cost=24,832.71..25,154.45 rows=128,697 width=328) (actual time=215,928.586..215,928.664 rows=812 loops=1)

  • Sort Key: all_data_3.date, all_data_3.campaign_id
  • Sort Method: quicksort Memory: 443kB
167. 215,925.766 215,925.766 ↑ 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=215,721.605..215,925.766 rows=812 loops=1)

168. 1.106 33.275 ↑ 16.1 799 1

Sort (cost=1,135.89..1,168.06 rows=12,870 width=96) (actual time=33.234..33.275 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. 32.169 32.169 ↑ 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=31.599..32.169 rows=270 loops=1)

170. 215,967.662 215,967.662 ↑ 299.9 427 1

CTE Scan on xyz (cost=0.00..4,496.36 rows=128,054 width=328) (actual time=215,965.833..215,967.662 rows=427 loops=1)

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