explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eVEY

Settings
# exclusive inclusive rows x rows loops node
1. 2.096 440,511.991 ↑ 299.9 427 1

Merge Left Join (cost=6,077,315.71..6,080,322.55 rows=128,054 width=360) (actual time=440,507.039..440,511.991 rows=427 loops=1)

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

CTE ga_pv

3. 1.256 209,760.240 ↑ 15.5 686 1

GroupAggregate (cost=4,247,584.41..4,247,823.66 rows=10,633 width=51) (actual time=209,759.406..209,760.240 rows=686 loops=1)

  • Group Key: ga_campaign_creative.date, ga_campaign_creative.dimension21
4. 4.648 209,758.984 ↑ 2.7 3,944 1

Sort (cost=4,247,584.41..4,247,611.00 rows=10,633 width=27) (actual time=209,758.753..209,758.984 rows=3,944 loops=1)

  • Sort Key: ga_campaign_creative.date, ga_campaign_creative.dimension21
  • Sort Method: quicksort Memory: 405kB
5. 1.861 209,754.336 ↑ 2.7 3,944 1

Nested Loop (cost=1,807,196.10..4,246,873.27 rows=10,633 width=27) (actual time=208,724.024..209,754.336 rows=3,944 loops=1)

6. 2.227 208,720.081 ↑ 9.1 22 1

HashAggregate (cost=1,807,195.53..1,807,197.53 rows=200 width=32) (actual time=208,720.023..208,720.081 rows=22 loops=1)

  • Group Key: xyz_1.as_parameter
7. 4.410 208,717.854 ↑ 299.9 427 1

Sort (cost=1,805,274.72..1,805,594.85 rows=128,054 width=328) (actual time=208,717.835..208,717.854 rows=427 loops=1)

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

CTE all_data

9. 118.239 208,673.520 ↑ 158.5 812 1

GroupAggregate (cost=1,723,027.12..1,747,589.34 rows=128,697 width=348) (actual time=208,468.492..208,673.520 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. 727.376 208,555.281 ↑ 1.0 144,484 1

Sort (cost=1,723,027.12..1,723,397.66 rows=148,215 width=300) (actual time=208,464.445..208,555.281 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,735.567 207,827.905 ↑ 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=25,689.754..207,827.905 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. 66.811 162,598.386 ↑ 1.0 144,484 1

Hash Left Join (cost=1,596,695.53..1,628,809.96 rows=148,215 width=264) (actual time=24,675.247..162,598.386 rows=144,484 loops=1)

  • Hash Cond: ((abc.as_parameter = avantis.as_id) AND (snapchat_stats.date = avantis.date))
13. 89.915 162,328.576 ↑ 1.0 144,484 1

Hash Left Join (cost=1,558,496.11..1,589,832.37 rows=148,215 width=256) (actual time=24,470.970..162,328.576 rows=144,484 loops=1)

  • Hash Cond: (snapchat_ads_1.ad_squad_id = snapchat_adsquads_1.id)
14. 65.512 162,234.823 ↑ 1.0 144,484 1

Hash Left Join (cost=1,558,457.33..1,587,842.46 rows=148,215 width=219) (actual time=24,465.926..162,234.823 rows=144,484 loops=1)

  • Hash Cond: ((snapchat_stats.date = v_taboola_revenue.date) AND (abc.as_parameter = v_taboola_revenue.as_id))
15. 94.225 151,461.767 ↓ 1.1 144,484 1

Merge Left Join (cost=1,083,120.76..1,111,794.85 rows=135,432 width=211) (actual time=13,756.398..151,461.767 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. 52.583 146,021.335 ↓ 1.1 144,484 1

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

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

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

19. 7,313.328 137,328.048 ↑ 1.0 1 135,432

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

  • Hash Cond: (abc.creative_id = snapchat_ads_1.creative_id)
20. 15,980.976 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. 3.100 8.718 ↑ 2.4 131 1

HashAggregate (cost=143.77..150.75 rows=310 width=139) (actual time=8.687..8.718 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. 2.418 5.618 ↑ 2.1 149 1

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

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

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

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

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

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

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

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

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

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

CTE full_creatives

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

30.          

CTE abc

31. 6.669 34.579 ↓ 148.2 593 1

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

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

33. 0.538 18.636 ↓ 126.8 507 1

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

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

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

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

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

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

CTE Scan on abc (cost=0.00..0.08 rows=4 width=96) (actual time=0.000..0.049 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.003..0.004 rows=1 loops=135,432)

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

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

40. 4.243 8,375.920 ↓ 43.9 3,822 1

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

  • Group Key: connatix_report.date, connatix_report.site, connatix_report.as_id
41. 7.571 8,371.677 ↓ 89.5 4,564 1

Gather Merge (cost=376,805.24..376,812.13 rows=51 width=35) (actual time=8,364.682..8,371.677 rows=4,564 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
42. 4.769 8,364.106 ↓ 44.7 2,282 2 / 2

Partial GroupAggregate (cost=375,805.23..375,806.38 rows=51 width=35) (actual time=8,359.419..8,364.106 rows=2,282 loops=2)

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

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

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

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

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

Sort (cost=706,074.90..706,076.64 rows=697 width=44) (actual time=5,328.640..5,346.207 rows=264,933 loops=1)

  • Sort Key: zyx.date
  • Sort Method: quicksort Memory: 529kB
46. 0.716 5,327.103 ↓ 6.2 4,307 1

Subquery Scan on zyx (cost=706,028.04..706,041.98 rows=697 width=44) (actual time=5,325.769..5,327.103 rows=4,307 loops=1)

47. 7.789 5,326.387 ↓ 6.2 4,307 1

HashAggregate (cost=706,028.04..706,035.01 rows=697 width=44) (actual time=5,325.440..5,326.387 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.914 5,318.598 ↓ 6.2 4,307 1

Append (cost=1,000.00..706,022.82 rows=697 width=44) (actual time=11.661..5,318.598 rows=4,307 loops=1)

49. 2.270 5,049.960 ↓ 7.1 4,210 1

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

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

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

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

  • Group Key: ga_gemini_tag.date, ga_gemini_tag.dimension21
52. 0.939 266.419 ↓ 12.8 1,326 1

Sort (cost=488,260.71..488,260.97 rows=104 width=31) (actual time=266.359..266.419 rows=1,326 loops=1)

  • Sort Key: ga_gemini_tag.date, ga_gemini_tag.dimension21
  • Sort Method: quicksort Memory: 151kB
53. 265.480 265.480 ↓ 12.8 1,326 1

Index Scan using idx_17910449_date on ga_gemini_tag (cost=0.57..488,257.23 rows=104 width=31) (actual time=0.705..265.480 rows=1,326 loops=1)

  • Index Cond: (date >= '2020-08-18'::date)
  • Filter: (dimension21 ~~ '211%%'::text)
  • Rows Removed by Filter: 1,157,659
54. 0.549 10,707.544 ↑ 12,782.4 5 1

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

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

56. 1.171 10,706.710 ↑ 12,782.4 5 1

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

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

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

  • Workers Planned: 1
  • Workers Launched: 1
58. 0.573 10,700.809 ↑ 19,224.0 2 2 / 2

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

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

Sort (cost=466,716.92..466,813.04 rows=38,448 width=25) (actual time=10,700.232..10,700.236 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,700.197 10,700.197 ↑ 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,664.558..10,700.197 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.049 3.838 ↑ 1.0 223 1

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

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

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

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

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

64. 0.265 1.111 ↑ 1.5 172 1

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

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

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

66. 1.023 202.999 ↑ 3.4 2,112 1

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

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

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

68. 2.113 201.262 ↑ 3.4 2,112 1

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

  • Group Key: avantis_subid_daily_report.date, avantis_subid_daily_report.sub_id
69. 6.217 199.149 ↑ 2.0 2,112 1

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

  • Workers Planned: 1
  • Workers Launched: 1
70. 1.021 192.932 ↑ 4.1 1,056 2 / 2

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

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

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

  • Sort Key: avantis_subid_daily_report.date, avantis_subid_daily_report.sub_id
  • Sort Method: quicksort Memory: 131kB
  • Worker 0: Sort Method: quicksort Memory: 131kB
72. 189.011 189.011 ↑ 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.552..189.011 rows=1,058 loops=2)

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

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

74. 3.006 1,008.854 ↓ 2,689.0 2,689 1

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

  • Workers Planned: 1
  • Workers Launched: 1
75. 1,005.848 1,005.848 ↓ 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=1.400..1,005.848 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,451
76.          

CTE swipes_by_campaign_date

77. 21.319 21.822 ↑ 47.7 270 1

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

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

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

79.          

CTE xyz

80. 3.153 208,710.878 ↑ 158.5 812 1

Merge Left Join (cost=25,968.60..28,945.44 rows=128,697 width=360) (actual time=208,710.052..208,710.878 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. 3.426 208,684.372 ↑ 158.5 812 1

Sort (cost=24,832.71..25,154.45 rows=128,697 width=328) (actual time=208,684.300..208,684.372 rows=812 loops=1)

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

83. 0.565 23.353 ↑ 16.1 799 1

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

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

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

85. 208,713.444 208,713.444 ↑ 299.9 427 1

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

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

Index Scan using idx_22424699_dimension21 on ga_campaign_creative (cost=0.57..12,198.37 rows=1 width=27) (actual time=1.211..46.927 rows=179 loops=22)

  • Index Cond: (dimension21 = xyz_1.as_parameter)
  • Filter: (dimension21 ~~ '211%%'::text)
87. 5.640 230,743.579 ↑ 299.9 427 1

Sort (cost=1,828,568.24..1,828,888.38 rows=128,054 width=264) (actual time=230,739.303..230,743.579 rows=427 loops=1)

  • Sort Key: ((snapchat.date)::date), snapchat.as_parameter
  • Sort Method: quicksort Memory: 172kB
88. 0.200 230,737.939 ↑ 299.9 427 1

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

89. 2.086 230,737.739 ↑ 299.9 427 1

Sort (cost=1,806,875.39..1,807,195.53 rows=128,054 width=328) (actual time=230,737.720..230,737.739 rows=427 loops=1)

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

CTE all_data

91. 109.061 230,710.872 ↑ 158.5 812 1

GroupAggregate (cost=1,723,027.12..1,747,589.34 rows=128,697 width=348) (actual time=230,514.924..230,710.872 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
92. 677.215 230,601.811 ↑ 1.0 144,484 1

Sort (cost=1,723,027.12..1,723,397.66 rows=148,215 width=300) (actual time=230,514.828..230,601.811 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
93. 26,491.780 229,924.596 ↑ 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=49,616.633..229,924.596 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
94. 66.384 184,938.864 ↑ 1.0 144,484 1

Hash Left Join (cost=1,596,695.53..1,628,809.96 rows=148,215 width=264) (actual time=48,380.401..184,938.864 rows=144,484 loops=1)

  • Hash Cond: ((abc_1.as_parameter = avantis_1.as_id) AND (snapchat_stats_1.date = avantis_1.date))
95. 93.640 184,127.123 ↑ 1.0 144,484 1

Hash Left Join (cost=1,558,496.11..1,589,832.37 rows=148,215 width=256) (actual time=47,634.995..184,127.123 rows=144,484 loops=1)

  • Hash Cond: (snapchat_ads_1_1.ad_squad_id = snapchat_adsquads_1_1.id)
96. 66.655 184,029.476 ↑ 1.0 144,484 1

Hash Left Join (cost=1,558,457.33..1,587,842.46 rows=148,215 width=219) (actual time=47,630.971..184,029.476 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))
97. 88.783 173,793.052 ↓ 1.1 144,484 1

Merge Left Join (cost=1,083,120.76..1,111,794.85 rows=135,432 width=211) (actual time=37,456.278..173,793.052 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
98. 50.362 144,785.540 ↓ 1.1 144,484 1

Merge Left Join (cost=377,045.86..399,724.07 rows=135,432 width=203) (actual time=8,553.320..144,785.540 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
99. 235.711 136,239.626 ↓ 1.1 144,484 1

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

100. 301.051 301.051 ↑ 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.104..301.051 rows=135,432 loops=1)

101. 7,042.464 135,702.864 ↑ 1.0 1 135,432

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

  • Hash Cond: (abc_1.creative_id = snapchat_ads_1_1.creative_id)
102. 15,710.112 127,983.240 ↓ 138.8 555 135,432

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

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

CTE asparam_assoc

104. 1.430 3.168 ↑ 2.4 131 1

HashAggregate (cost=143.77..150.75 rows=310 width=139) (actual time=3.138..3.168 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
105. 0.924 1.738 ↑ 2.1 149 1

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

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

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
108. 0.010 0.218 ↑ 9.0 5 1

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

  • Group Key: snapchat_creatives_2_1.headline
109. 0.208 0.208 ↑ 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.054..0.208 rows=30 loops=1)

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

CTE full_creatives

111. 1.102 1.102 ↑ 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.587..1.102 rows=762 loops=1)

112.          

CTE abc

113. 4.968 50.596 ↓ 148.2 593 1

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

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

115. 8.092 41.960 ↓ 126.8 507 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 189kB
116. 33.868 33.868 ↓ 126.8 507 1

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

  • Filter: (review_status = 'APPROVED'::text)
  • Rows Removed by Filter: 255
117. 105,636.960 112,273.128 ↓ 148.2 593 135,432

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

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

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

119. 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
120. 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)
121. 4.953 8,495.552 ↓ 755.0 65,688 1

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

122. 0.000 8,490.599 ↓ 43.9 3,822 1

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

  • Group Key: connatix_report_1.date, connatix_report_1.site, connatix_report_1.as_id
123. 19.618 8,491.451 ↓ 87.7 4,474 1

Gather Merge (cost=376,805.24..376,812.13 rows=51 width=35) (actual time=8,478.638..8,491.451 rows=4,474 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
124. 4.505 8,471.833 ↓ 43.9 2,237 2 / 2

Partial GroupAggregate (cost=375,805.23..375,806.38 rows=51 width=35) (actual time=8,465.919..8,471.833 rows=2,237 loops=2)

  • Group Key: connatix_report_1.date, connatix_report_1.site, connatix_report_1.as_id
125. 40.527 8,467.328 ↓ 286.7 14,620 2 / 2

Sort (cost=375,805.23..375,805.36 rows=51 width=35) (actual time=8,465.901..8,467.328 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,233kB
  • Worker 0: Sort Method: quicksort Memory: 2,205kB
126. 8,426.801 8,426.801 ↓ 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=16.619..8,426.801 rows=14,620 loops=2)

  • Filter: ((as_id ~~ '%%211%%'::text) AND (length(as_id) = 13))
  • Rows Removed by Filter: 3,833,038
127. 31.601 28,918.729 ↓ 380.1 264,933 1

Sort (cost=706,074.90..706,076.64 rows=697 width=44) (actual time=28,902.055..28,918.729 rows=264,933 loops=1)

  • Sort Key: zyx_1.date
  • Sort Method: quicksort Memory: 529kB
128. 0.421 28,887.128 ↓ 6.2 4,307 1

Subquery Scan on zyx_1 (cost=706,028.04..706,041.98 rows=697 width=44) (actual time=28,885.674..28,887.128 rows=4,307 loops=1)

129. 25.912 28,886.707 ↓ 6.2 4,307 1

HashAggregate (cost=706,028.04..706,035.01 rows=697 width=44) (actual time=28,885.673..28,886.707 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
130. 0.000 28,860.795 ↓ 6.2 4,307 1

Append (cost=1,000.00..706,022.82 rows=697 width=44) (actual time=9.172..28,860.795 rows=4,307 loops=1)

131. 0.000 5,125.585 ↓ 7.1 4,210 1

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

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

  • Filter: ((custom_criteria ~~ '%%as=211%%'::text) AND (date < '2020-08-18'::date))
  • Rows Removed by Filter: 3,116,886
133. 0.178 23,736.260 ↑ 1.1 97 1

GroupAggregate (cost=488,260.71..488,262.79 rows=104 width=31) (actual time=23,736.036..23,736.260 rows=97 loops=1)

  • Group Key: ga_gemini_tag_1.date, ga_gemini_tag_1.dimension21
134. 1.867 23,736.082 ↓ 12.8 1,326 1

Sort (cost=488,260.71..488,260.97 rows=104 width=31) (actual time=23,736.023..23,736.082 rows=1,326 loops=1)

  • Sort Key: ga_gemini_tag_1.date, ga_gemini_tag_1.dimension21
  • Sort Method: quicksort Memory: 151kB
135. 23,734.215 23,734.215 ↓ 12.8 1,326 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=68.550..23,734.215 rows=1,326 loops=1)

  • Index Cond: (date >= '2020-08-18'::date)
  • Filter: (dimension21 ~~ '211%%'::text)
  • Rows Removed by Filter: 1,157,659
136. 0.011 10,169.769 ↑ 12,782.4 5 1

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

  • Buckets: 65,536 Batches: 1 Memory Usage: 513kB
137. 0.011 10,169.758 ↑ 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,169.754..10,169.758 rows=5 loops=1)

138. 0.000 10,169.747 ↑ 12,782.4 5 1

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

  • Group Key: taboola_revenue_1.date, taboola_revenue_1.as_id
139. 14.493 10,171.504 ↑ 7,689.6 5 1

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

  • Workers Planned: 1
  • Workers Launched: 1
140. 0.019 10,157.011 ↑ 19,224.0 2 2 / 2

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

  • Group Key: taboola_revenue_1.date, taboola_revenue_1.as_id
141. 0.037 10,156.992 ↑ 9,612.0 4 2 / 2

Sort (cost=466,716.92..466,813.04 rows=38,448 width=25) (actual time=10,156.991..10,156.992 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
142. 10,156.955 10,156.955 ↑ 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,121.328..10,156.955 rows=4 loops=2)

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 32kB
144. 0.699 4.345 ↑ 1.0 223 1

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

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

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

146. 0.035 1.119 ↑ 1.5 172 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 20kB
147. 1.084 1.084 ↑ 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.380..1.084 rows=172 loops=1)

148. 0.665 745.357 ↑ 3.4 2,112 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 188kB
149. 0.288 744.692 ↑ 3.4 2,112 1

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

150. 0.000 744.404 ↑ 3.4 2,112 1

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

  • Group Key: avantis_subid_daily_report_1.date, avantis_subid_daily_report_1.sub_id
151. 5.436 745.400 ↑ 2.0 2,112 1

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

  • Workers Planned: 1
  • Workers Launched: 1
152. 0.458 739.964 ↑ 4.1 1,056 2 / 2

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

  • Group Key: avantis_subid_daily_report_1.date, avantis_subid_daily_report_1.sub_id
153. 2.814 739.506 ↑ 4.1 1,058 2 / 2

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

  • Sort Key: avantis_subid_daily_report_1.date, avantis_subid_daily_report_1.sub_id
  • Sort Method: quicksort Memory: 129kB
  • Worker 0: Sort Method: quicksort Memory: 133kB
154. 736.692 736.692 ↑ 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=2.442..736.692 rows=1,058 loops=2)

  • Filter: (""left""(sub_id, 3) = '211'::text)
  • Rows Removed by Filter: 730,170
155. 17,265.305 18,493.952 ↓ 2,689.0 2,689 144,484

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

156. 1.423 1,228.647 ↓ 2,689.0 2,689 1

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

  • Workers Planned: 1
  • Workers Launched: 1
157. 1,227.224 1,227.224 ↓ 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.023..1,227.224 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,451
158.          

CTE swipes_by_campaign_date

159. 0.423 0.525 ↑ 47.7 270 1

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

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

161.          

CTE xyz

162. 1.890 230,726.249 ↑ 158.5 812 1

Merge Left Join (cost=25,968.60..28,945.44 rows=128,697 width=360) (actual time=230,725.351..230,726.249 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))
163. 2.567 230,722.647 ↑ 158.5 812 1

Sort (cost=24,832.71..25,154.45 rows=128,697 width=328) (actual time=230,722.573..230,722.647 rows=812 loops=1)

  • Sort Key: all_data_3.date, all_data_3.campaign_id
  • Sort Method: quicksort Memory: 443kB
164. 230,720.080 230,720.080 ↑ 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=230,514.929..230,720.080 rows=812 loops=1)

165. 0.592 1.712 ↑ 16.1 799 1

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

  • Sort Key: swipes_by_campaign_date_1.date, swipes_by_campaign_date_1.campaign_id
  • Sort Method: quicksort Memory: 62kB
166. 1.120 1.120 ↑ 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=0.420..1.120 rows=270 loops=1)

167. 230,735.653 230,735.653 ↑ 299.9 427 1

CTE Scan on xyz (cost=0.00..4,496.36 rows=128,054 width=328) (actual time=230,725.407..230,735.653 rows=427 loops=1)

  • Filter: (impressions <> 0)
  • Rows Removed by Filter: 385
168. 3.383 209,766.316 ↑ 11.0 967 1

Sort (cost=923.81..950.39 rows=10,633 width=160) (actual time=209,766.272..209,766.316 rows=967 loops=1)

  • Sort Key: ga_pv.date, ga_pv.dimension21
  • Sort Method: quicksort Memory: 121kB
169. 209,762.933 209,762.933 ↑ 15.5 686 1

CTE Scan on ga_pv (cost=0.00..212.66 rows=10,633 width=160) (actual time=209,761.844..209,762.933 rows=686 loops=1)

Planning time : 166.128 ms