explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1hYN

Settings
# exclusive inclusive rows x rows loops node
1. 275,451.859 275,451.859 ↑ 13.6 4,499 1

CTE Scan on synthesis (cost=3,617,443.78..3,620,352.19 rows=61,037 width=836) (actual time=275,447.208..275,451.859 rows=4,499 loops=1)

  • Filter: ((length(as_parameter) <> 13) OR ((length(as_parameter) = 13) AND (campaign_id IS NOT NULL)))
  • Rows Removed by Filter: 920
2.          

CTE all_data

3. 41.151 275,367.370 ↑ 8.0 7,693 1

GroupAggregate (cost=3,562,827.87..3,572,537.74 rows=61,650 width=451) (actual time=275,298.105..275,367.370 rows=7,693 loops=1)

  • Group Key: (to_char((snapchat_stats.date)::timestamp with time zone, 'YYYY-MM-DD'::text)), abc.as_parameter, as_parameters_1.campaign, snapchat_ads_1.creative_id, snapchat_ads_1.id, snapchat_ads_1.ad_squad_id, snapchat_adsquads_1.campaign_id, snapchat_adsquads_1.name
  • Filter: ((sum(snapchat_stats.spend) > '0'::double precision) OR (sum((COALESCE((sum(ga_gemini_tag.dfp_revenue)), '0'::double precision) + COALESCE((sum(avantis_subid_daily_report.revenue)), '0'::double precision))) > '0'::double precision))
  • Rows Removed by Filter: 32,298
4. 262.318 275,326.219 ↑ 2.6 42,089 1

Sort (cost=3,562,827.87..3,563,105.29 rows=110,970 width=427) (actual time=275,298.079..275,326.219 rows=42,089 loops=1)

  • Sort Key: (to_char((snapchat_stats.date)::timestamp with time zone, 'YYYY-MM-DD'::text)), abc.as_parameter, as_parameters_1.campaign, snapchat_ads_1.creative_id, snapchat_ads_1.id, snapchat_ads_1.ad_squad_id, snapchat_adsquads_1.campaign_id, snapchat_adsquads_1.name
  • Sort Method: external merge Disk: 13,688kB
5. 63.196 275,063.901 ↑ 2.6 42,089 1

Hash Left Join (cost=1,801,547.70..3,540,863.79 rows=110,970 width=427) (actual time=143,754.046..275,063.901 rows=42,089 loops=1)

  • Hash Cond: (snapchat_stats.date = dfp_all.date)
6. 10.991 238,529.199 ↑ 1.2 42,089 1

Merge Left Join (cost=1,748,668.42..3,487,298.15 rows=49,469 width=391) (actual time=107,282.385..238,529.199 rows=42,089 loops=1)

  • Merge Cond: (snapchat_stats.date = avantis_subid_daily_report_1.date)
7. 14.386 227,414.815 ↓ 1.1 42,089 1

Merge Left Join (cost=1,748,668.00..3,403,269.01 rows=38,132 width=383) (actual time=103,753.931..227,414.815 rows=42,089 loops=1)

  • Merge Cond: ((snapchat_stats.date = avantis_subid_daily_report.date) AND (abc.as_parameter = avantis_subid_daily_report.sub_id))
8. 10.827 226,444.790 ↓ 1.1 42,089 1

Merge Left Join (cost=1,699,261.37..3,352,712.92 rows=38,132 width=375) (actual time=102,799.488..226,444.790 rows=42,089 loops=1)

  • Merge Cond: (snapchat_stats.date = ga_gemini_tag_1.date)
9. 22.495 101,897.531 ↓ 1.1 42,089 1

Merge Left Join (cost=1,698,260.79..1,698,594.67 rows=38,132 width=367) (actual time=101,848.892..101,897.531 rows=42,089 loops=1)

  • Merge Cond: ((snapchat_stats.date = ga_gemini_tag.date) AND (abc.as_parameter = ga_gemini_tag.dimension21))
10. 187.528 1,711.582 ↓ 1.1 42,089 1

Sort (cost=44,641.58..44,736.91 rows=38,132 width=359) (actual time=1,691.886..1,711.582 rows=42,089 loops=1)

  • Sort Key: snapchat_stats.date, abc.as_parameter
  • Sort Method: external merge Disk: 12,120kB
11. 61.733 1,524.054 ↓ 1.1 42,089 1

Hash Left Join (cost=34,071.30..38,074.58 rows=38,132 width=359) (actual time=1,220.658..1,524.054 rows=42,089 loops=1)

  • Hash Cond: (snapchat_ads_1.ad_squad_id = snapchat_adsquads_1.id)
  • Join Filter: ((length(abc.as_parameter) <> 13) OR ((length(abc.as_parameter) = 13) AND (replace(snapchat_adsquads_1.name, ' '::text, ''::text) = replace((as_parameters_1.campaign)::text, ' '::text, ''::text))))
  • Rows Removed by Join Filter: 12,962
12. 15.251 1,459.376 ↓ 1.1 42,089 1

Hash Left Join (cost=34,022.38..36,834.03 rows=38,132 width=267) (actual time=1,217.690..1,459.376 rows=42,089 loops=1)

  • Hash Cond: (snapchat_stats.id = snapchat_ads_1.id)
13. 228.314 228.314 ↑ 1.0 38,083 1

Index Scan Backward using idx_snapchat_stats_date on snapchat_stats (cost=0.42..2,287.76 rows=38,132 width=57) (actual time=1.840..228.314 rows=38,083 loops=1)

  • Index Cond: (date >= '2020-08-18'::date)
14. 0.590 1,215.811 ↓ 1.2 975 1

Hash (cost=34,011.81..34,011.81 rows=812 width=247) (actual time=1,215.811..1,215.811 rows=975 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 231kB
15. 0.752 1,215.221 ↓ 1.2 975 1

Hash Right Join (cost=439.30..34,011.81 rows=812 width=247) (actual time=1,047.847..1,215.221 rows=975 loops=1)

  • Hash Cond: (abc.creative_id = snapchat_ads_1.creative_id)
16. 143.606 1,212.291 ↓ 130.2 1,302 1

Nested Loop Left Join (cost=389.03..33,961.30 rows=10 width=168) (actual time=65.140..1,212.291 rows=1,302 loops=1)

  • Join Filter: (split_part((as_parameters_1.asparam)::text, '='::text, 2) = abc.as_parameter)
  • Rows Removed by Join Filter: 442,686
17. 1.148 42.709 ↓ 130.2 1,302 1

Group (cost=388.47..388.57 rows=10 width=96) (actual time=40.967..42.709 rows=1,302 loops=1)

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

CTE asparam_assoc

19. 1.447 2.818 ↑ 1.3 482 1

HashAggregate (cost=212.55..232.14 rows=603 width=274) (actual time=1.770..2.818 rows=482 loops=1)

  • Group Key: snapchat_creatives_1.ad_account_id, snapchat_creatives_1.headline, snapchat_creatives_1.url, split_part(split_part(snapchat_creatives_1.url, 'as='::text, 2), '&'::text, 1), snapchat_creatives_1.type, snapchat_creatives_1.created_at
20. 0.842 1.371 ↑ 1.4 632 1

Hash Join (cost=99.05..199.30 rows=883 width=242) (actual time=0.356..1.371 rows=632 loops=1)

  • Hash Cond: (snapchat_creatives_1.headline = snapchat_creatives_2.headline)
21. 0.202 0.202 ↓ 1.1 1,018 1

Seq Scan on snapchat_creatives snapchat_creatives_1 (cost=0.00..93.24 rows=933 width=210) (actual time=0.005..0.202 rows=1,018 loops=1)

  • Filter: ((review_status = 'APPROVED'::text) OR (review_status = 'DISAPPROVED'::text))
  • Rows Removed by Filter: 198
22. 0.004 0.327 ↑ 7.8 9 1

Hash (cost=98.18..98.18 rows=70 width=18) (actual time=0.327..0.327 rows=9 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
23. 0.036 0.323 ↑ 7.8 9 1

HashAggregate (cost=96.78..97.48 rows=70 width=18) (actual time=0.321..0.323 rows=9 loops=1)

  • Group Key: snapchat_creatives_2.headline
24. 0.287 0.287 ↑ 1.0 197 1

Seq Scan on snapchat_creatives snapchat_creatives_2 (cost=0.00..96.28 rows=199 width=18) (actual time=0.029..0.287 rows=197 loops=1)

  • Filter: ((url = 'nan'::text) AND ((review_status = 'APPROVED'::text) OR (review_status = 'DISAPPROVED'::text)))
  • Rows Removed by Filter: 1,019
25.          

CTE full_creatives

26. 3.800 3.800 ↑ 1.0 1,216 1

Seq Scan on snapchat_creatives snapchat_creatives_1_1 (cost=0.00..108.44 rows=1,216 width=461) (actual time=0.621..3.800 rows=1,216 loops=1)

27.          

CTE abc

28. 18.728 28.397 ↓ 363.0 4,356 1

Hash Right Join (cost=30.58..47.43 rows=12 width=96) (actual time=8.151..28.397 rows=4,356 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: 55,429
29. 3.321 3.321 ↑ 1.3 482 1

CTE Scan on asparam_assoc (cost=0.00..12.06 rows=603 width=168) (actual time=1.772..3.321 rows=482 loops=1)

30. 0.622 6.348 ↓ 84.8 1,018 1

Hash (cost=30.40..30.40 rows=12 width=232) (actual time=6.348..6.348 rows=1,018 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 331kB
31. 5.726 5.726 ↓ 84.8 1,018 1

CTE Scan on full_creatives (cost=0.00..30.40 rows=12 width=232) (actual time=0.629..5.726 rows=1,018 loops=1)

  • Filter: ((review_status = 'APPROVED'::text) OR (review_status = 'DISAPPROVED'::text))
  • Rows Removed by Filter: 198
32. 10.528 41.561 ↓ 379.0 3,790 1

Sort (cost=0.47..0.49 rows=10 width=96) (actual time=40.962..41.561 rows=3,790 loops=1)

  • Sort Key: abc.creative_id, abc.as_parameter, abc.campaign_name
  • Sort Method: quicksort Memory: 677kB
33. 31.033 31.033 ↓ 379.0 3,790 1

CTE Scan on abc (cost=0.00..0.30 rows=10 width=96) (actual time=8.154..31.033 rows=3,790 loops=1)

  • Filter: ((as_parameter IS NOT NULL) AND (campaign_name IS NOT NULL) AND (as_parameter <> ''::text) AND (campaign_name <> '//'::text))
  • Rows Removed by Filter: 566
34. 22.705 1,025.976 ↓ 2.1 341 1,302

Materialize (cost=0.55..33,545.02 rows=160 width=120) (actual time=0.010..0.788 rows=341 loops=1,302)

35. 1,003.271 1,003.271 ↓ 2.1 341 1

Index Scan using idx_1495027_inx on as_parameters as_parameters_1 (cost=0.55..33,544.22 rows=160 width=120) (actual time=12.515..1,003.271 rows=341 loops=1)

  • Index Cond: ((channel)::text = 'Snapchat'::text)
36. 0.208 2.178 ↑ 1.0 812 1

Hash (cost=40.12..40.12 rows=812 width=111) (actual time=2.178..2.178 rows=812 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 122kB
37. 1.970 1.970 ↑ 1.0 812 1

Seq Scan on snapchat_ads snapchat_ads_1 (cost=0.00..40.12 rows=812 width=111) (actual time=0.275..1.970 rows=812 loops=1)

38. 0.121 2.945 ↑ 1.0 339 1

Hash (cost=44.69..44.69 rows=339 width=129) (actual time=2.945..2.945 rows=339 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 62kB
39. 0.183 2.824 ↑ 1.0 339 1

Hash Left Join (cost=12.64..44.69 rows=339 width=129) (actual time=1.430..2.824 rows=339 loops=1)

  • Hash Cond: (snapchat_adsquads_1.campaign_id = snapchat_campaigns_1.id)
40. 1.719 1.719 ↑ 1.0 339 1

Seq Scan on snapchat_adsquads snapchat_adsquads_1 (cost=0.00..27.39 rows=339 width=129) (actual time=0.442..1.719 rows=339 loops=1)

41. 0.091 0.922 ↑ 1.0 206 1

Hash (cost=10.06..10.06 rows=206 width=37) (actual time=0.922..0.922 rows=206 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 22kB
42. 0.831 0.831 ↑ 1.0 206 1

Seq Scan on snapchat_campaigns snapchat_campaigns_1 (cost=0.00..10.06 rows=206 width=37) (actual time=0.009..0.831 rows=206 loops=1)

43. 1.215 100,163.454 ↓ 2.7 1,153 1

Finalize GroupAggregate (cost=1,653,619.20..1,653,658.30 rows=424 width=31) (actual time=100,156.995..100,163.454 rows=1,153 loops=1)

  • Group Key: ga_gemini_tag.date, ga_gemini_tag.dimension21
44. 9.473 100,162.239 ↓ 6.9 1,726 1

Gather Merge (cost=1,653,619.20..1,653,652.20 rows=249 width=31) (actual time=100,156.989..100,162.239 rows=1,726 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
45. 3.036 100,152.766 ↓ 3.5 863 2 / 2

Partial GroupAggregate (cost=1,652,619.19..1,652,624.17 rows=249 width=31) (actual time=100,148.599..100,152.766 rows=863 loops=2)

  • Group Key: ga_gemini_tag.date, ga_gemini_tag.dimension21
46. 16.063 100,149.730 ↓ 49.7 12,383 2 / 2

Sort (cost=1,652,619.19..1,652,619.82 rows=249 width=31) (actual time=100,148.573..100,149.730 rows=12,383 loops=2)

  • Sort Key: ga_gemini_tag.date, ga_gemini_tag.dimension21
  • Sort Method: quicksort Memory: 1,232kB
  • Worker 0: Sort Method: quicksort Memory: 1,471kB
47. 100,133.667 100,133.667 ↓ 49.7 12,383 2 / 2

Parallel Index Scan using idx_17910449_date on ga_gemini_tag (cost=0.57..1,652,609.28 rows=249 width=31) (actual time=42.158..100,133.667 rows=12,383 loops=2)

  • Index Cond: (date >= '2020-08-18'::date)
  • Filter: (dimension21 ~~ '211%%'::text)
  • Rows Removed by Filter: 2,247,754
48. 0.155 124,536.432 ↑ 5.6 58 1

Finalize GroupAggregate (cost=1,000.58..1,653,645.55 rows=326 width=12) (actual time=950.590..124,536.432 rows=58 loops=1)

  • Group Key: ga_gemini_tag_1.date
49. 14,152.276 124,536.277 ↑ 3.2 77 1

Gather Merge (cost=1,000.58..1,653,641.04 rows=249 width=12) (actual time=470.336..124,536.277 rows=77 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
50. 3.291 110,384.001 ↑ 6.6 38 2 / 2

Partial GroupAggregate (cost=0.57..1,652,613.02 rows=249 width=12) (actual time=444.641..110,384.001 rows=38 loops=2)

  • Group Key: ga_gemini_tag_1.date
51. 110,380.710 110,380.710 ↓ 49.7 12,383 2 / 2

Parallel Index Scan using idx_17910449_date on ga_gemini_tag ga_gemini_tag_1 (cost=0.57..1,652,609.28 rows=249 width=12) (actual time=42.278..110,380.710 rows=12,383 loops=2)

  • Index Cond: (date >= '2020-08-18'::date)
  • Filter: (dimension21 ~~ '211%%'::text)
  • Rows Removed by Filter: 2,247,754
52. 2.336 955.639 ↑ 2.7 3,081 1

Finalize GroupAggregate (cost=49,406.63..50,192.56 rows=8,341 width=33) (actual time=951.496..955.639 rows=3,081 loops=1)

  • Group Key: avantis_subid_daily_report.date, avantis_subid_daily_report.sub_id
53. 4.761 953.303 ↑ 1.6 3,081 1

Gather Merge (cost=49,406.63..50,071.51 rows=5,018 width=33) (actual time=951.486..953.303 rows=3,081 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
54. 0.851 948.542 ↑ 3.3 1,540 2 / 2

Partial GroupAggregate (cost=48,406.62..48,506.98 rows=5,018 width=33) (actual time=947.514..948.542 rows=1,540 loops=2)

  • Group Key: avantis_subid_daily_report.date, avantis_subid_daily_report.sub_id
55. 5.315 947.691 ↑ 3.2 1,572 2 / 2

Sort (cost=48,406.62..48,419.16 rows=5,018 width=33) (actual time=947.504..947.691 rows=1,572 loops=2)

  • Sort Key: avantis_subid_daily_report.date, avantis_subid_daily_report.sub_id
  • Sort Method: quicksort Memory: 169kB
  • Worker 0: Sort Method: quicksort Memory: 172kB
56. 942.376 942.376 ↑ 3.2 1,572 2 / 2

Parallel Seq Scan on avantis_subid_daily_report (cost=0.00..48,098.19 rows=5,018 width=33) (actual time=2.044..942.376 rows=1,572 loops=2)

  • Filter: (""left""(sub_id, 3) = '211'::text)
  • Rows Removed by Filter: 852,410
57. 1.183 11,103.393 ↑ 1.5 285 1

GroupAggregate (cost=0.43..83,433.71 rows=432 width=12) (actual time=39.536..11,103.393 rows=285 loops=1)

  • Group Key: avantis_subid_daily_report_1.date
58. 11,102.210 11,102.210 ↑ 2.7 3,143 1

Index Scan using idx_17243_date on avantis_subid_daily_report avantis_subid_daily_report_1 (cost=0.43..83,386.74 rows=8,530 width=12) (actual time=32.860..11,102.210 rows=3,143 loops=1)

  • Filter: (""left""(sub_id, 3) = '211'::text)
  • Rows Removed by Filter: 1,704,819
59. 0.027 36,471.506 ↑ 12.9 58 1

Hash (cost=52,869.94..52,869.94 rows=747 width=12) (actual time=36,471.506..36,471.506 rows=58 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
60. 0.014 36,471.479 ↑ 12.9 58 1

Subquery Scan on dfp_all (cost=52,796.71..52,869.94 rows=747 width=12) (actual time=36,460.190..36,471.479 rows=58 loops=1)

61. 7.699 36,471.465 ↑ 12.9 58 1

GroupAggregate (cost=52,796.71..52,862.47 rows=747 width=12) (actual time=36,460.189..36,471.465 rows=58 loops=1)

  • Group Key: dfp_ad_unit_device_flaf.date
62. 14.070 36,463.766 ↓ 6.0 46,630 1

Sort (cost=52,796.71..52,816.14 rows=7,772 width=12) (actual time=36,460.147..36,463.766 rows=46,630 loops=1)

  • Sort Key: dfp_ad_unit_device_flaf.date
  • Sort Method: quicksort Memory: 3,722kB
63. 225.312 36,449.696 ↓ 6.0 46,630 1

Bitmap Heap Scan on dfp_ad_unit_device_flaf (cost=43,683.49..52,294.48 rows=7,772 width=12) (actual time=36,241.120..36,449.696 rows=46,630 loops=1)

  • Recheck Cond: ((flaf_id = 220,010) AND (date >= '2020-08-18'::date))
  • Rows Removed by Index Recheck: 59
  • Heap Blocks: exact=9,274
64. 2.647 36,224.384 ↓ 0.0 0 1

BitmapAnd (cost=43,683.49..43,683.49 rows=7,772 width=0) (actual time=36,224.384..36,224.384 rows=0 loops=1)

65. 687.458 687.458 ↓ 2.5 428,859 1

Bitmap Index Scan on idx_684476_flaf (cost=0.00..2,201.53 rows=173,341 width=0) (actual time=687.458..687.458 rows=428,859 loops=1)

  • Index Cond: (flaf_id = 220,010)
66. 35,534.279 35,534.279 ↓ 5.1 17,757,720 1

Bitmap Index Scan on idx_684476_date (cost=0.00..41,477.82 rows=3,479,901 width=0) (actual time=35,534.279..35,534.279 rows=17,757,720 loops=1)

  • Index Cond: (date >= '2020-08-18'::date)
67.          

CTE swipes_by_campaign_date

68. 4.346 5.448 ↑ 4.4 1,415 1

HashAggregate (cost=1,849.50..1,926.56 rows=6,165 width=128) (actual time=4.915..5.448 rows=1,415 loops=1)

  • Group Key: all_data.date, all_data.campaign_id, all_data.as_parameter
69. 1.102 1.102 ↑ 8.0 7,693 1

CTE Scan on all_data (cost=0.00..1,233.00 rows=61,650 width=104) (actual time=0.001..1.102 rows=7,693 loops=1)

70.          

CTE xyz

71. 9.497 275,417.238 ↑ 8.0 7,693 1

Merge Left Join (cost=19,732.30..21,027.43 rows=61,650 width=852) (actual time=275,406.608..275,417.238 rows=7,693 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) AND (all_data_1.as_parameter = swipes_by_campaign_date.as_parameter))
72. 21.624 275,396.450 ↑ 8.0 7,693 1

Sort (cost=19,220.92..19,375.04 rows=61,650 width=820) (actual time=275,395.766..275,396.450 rows=7,693 loops=1)

  • Sort Key: all_data_1.date, all_data_1.campaign_id, all_data_1.as_parameter
  • Sort Method: quicksort Memory: 4,271kB
73. 275,374.826 275,374.826 ↑ 8.0 7,693 1

CTE Scan on all_data all_data_1 (cost=0.00..1,233.00 rows=61,650 width=820) (actual time=275,298.109..275,374.826 rows=7,693 loops=1)

74. 5.248 11.291 ↓ 1.1 6,761 1

Sort (cost=511.38..526.80 rows=6,165 width=128) (actual time=10.831..11.291 rows=6,761 loops=1)

  • Sort Key: swipes_by_campaign_date.date, swipes_by_campaign_date.campaign_id, swipes_by_campaign_date.as_parameter
  • Sort Method: quicksort Memory: 229kB
75. 6.043 6.043 ↑ 4.4 1,415 1

CTE Scan on swipes_by_campaign_date (cost=0.00..123.30 rows=6,165 width=128) (actual time=4.918..6.043 rows=1,415 loops=1)

76.          

CTE synthesis

77. 14.703 275,447.564 ↑ 11.3 5,419 1

Sort (cost=21,798.69..21,952.05 rows=61,342 width=844) (actual time=275,447.198..275,447.564 rows=5,419 loops=1)

  • Sort Key: xyz.date DESC, xyz.as_parameter
  • Sort Method: quicksort Memory: 3,183kB
78. 275,432.861 275,432.861 ↑ 11.3 5,419 1

CTE Scan on xyz (cost=0.00..3,534.10 rows=61,342 width=844) (actual time=275,406.630..275,432.861 rows=5,419 loops=1)

  • Filter: (impressions <> 0)
  • Rows Removed by Filter: 2,274
Planning time : 75.725 ms