explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RumN

Settings
# exclusive inclusive rows x rows loops node
1. 171,523.382 171,523.382 ↓ 2.5 4,499 1

CTE Scan on synthesis (cost=3,535,751.98..3,535,837.18 rows=1,788 width=836) (actual time=171,520.544..171,523.382 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. 4.428 171,476.535 ↓ 3.0 5,419 1

GroupAggregate (cost=3,535,029.37..3,535,312.42 rows=1,797 width=451) (actual time=171,471.746..171,476.535 rows=5,419 loops=1)

  • Group Key: (to_char((snapchat_stats.date)::timestamp with time zone, 'YYYY-MM-DD'::text)), snapchat_creatives.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))
4. 13.381 171,472.107 ↓ 1.7 5,419 1

Sort (cost=3,535,029.37..3,535,037.45 rows=3,235 width=427) (actual time=171,471.726..171,472.107 rows=5,419 loops=1)

  • Sort Key: (to_char((snapchat_stats.date)::timestamp with time zone, 'YYYY-MM-DD'::text)), snapchat_creatives.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: quicksort Memory: 3,094kB
5. 7.388 171,458.726 ↓ 1.7 5,419 1

Hash Left Join (cost=1,760,062.73..3,534,840.77 rows=3,235 width=427) (actual time=111,572.748..171,458.726 rows=5,419 loops=1)

  • Hash Cond: (snapchat_stats.date = dfp_all.date)
6. 1.541 144,032.556 ↓ 3.8 5,419 1

Merge Left Join (cost=1,707,159.28..3,481,917.32 rows=1,443 width=391) (actual time=84,153.926..144,032.556 rows=5,419 loops=1)

  • Merge Cond: (snapchat_stats.date = avantis_subid_daily_report_1.date)
7. 2.491 137,875.975 ↓ 4.9 5,419 1

Merge Left Join (cost=1,707,158.85..3,398,460.99 rows=1,112 width=383) (actual time=82,669.103..137,875.975 rows=5,419 loops=1)

  • Merge Cond: ((snapchat_stats.date = avantis_subid_daily_report.date) AND (snapchat_creatives.as_parameter = avantis_subid_daily_report.sub_id))
8. 1.476 137,254.090 ↓ 4.9 5,419 1

Merge Left Join (cost=1,657,752.22..3,348,136.37 rows=1,112 width=375) (actual time=82,050.877..137,254.090 rows=5,419 loops=1)

  • Merge Cond: (snapchat_stats.date = ga_gemini_tag_1.date)
9. 3.622 83,291.905 ↓ 4.9 5,419 1

Nested Loop Left Join (cost=1,656,751.64..1,694,238.62 rows=1,112 width=367) (actual time=81,995.445..83,291.905 rows=5,419 loops=1)

10. 5.998 83,277.445 ↓ 4.9 5,419 1

Nested Loop Left Join (cost=1,656,751.37..1,693,860.89 rows=1,112 width=367) (actual time=81,994.333..83,277.445 rows=5,419 loops=1)

  • Join Filter: ((length(snapchat_creatives.as_parameter) <> 13) OR ((length(snapchat_creatives.as_parameter) = 13) AND (replace(snapchat_adsquads_1.name, ' '::text, ''::text) = replace((as_parameters_1.campaign)::text, ' '::text, ''::text))))
  • Rows Removed by Join Filter: 920
11. 492.139 83,249.771 ↓ 4.9 5,419 1

Nested Loop Left Join (cost=1,656,751.10..1,693,462.64 rows=1,112 width=275) (actual time=81,993.197..83,249.771 rows=5,419 loops=1)

  • Join Filter: (split_part((as_parameters_1.asparam)::text, '='::text, 2) = snapchat_creatives.as_parameter)
  • Rows Removed by Join Filter: 1,842,460
12. 3.103 81,825.564 ↓ 4.9 5,419 1

Merge Left Join (cost=1,656,750.55..1,656,804.42 rows=1,112 width=171) (actual time=81,816.419..81,825.564 rows=5,419 loops=1)

  • Merge Cond: ((snapchat_stats.date = ga_gemini_tag.date) AND (snapchat_creatives.as_parameter = ga_gemini_tag.dimension21))
13. 9.857 221.066 ↓ 4.9 5,419 1

Sort (cost=2,896.89..2,899.67 rows=1,112 width=163) (actual time=220.256..221.066 rows=5,419 loops=1)

  • Sort Key: snapchat_stats.date, snapchat_creatives.as_parameter
  • Sort Method: quicksort Memory: 1,632kB
14. 1.564 211.209 ↓ 4.9 5,419 1

Hash Left Join (cost=442.66..2,840.63 rows=1,112 width=163) (actual time=33.977..211.209 rows=5,419 loops=1)

  • Hash Cond: (snapchat_stats.id = snapchat_ads_1.id)
15. 179.027 179.027 ↓ 4.0 4,499 1

Index Scan Backward using idx_snapchat_stats_date on snapchat_stats (cost=0.42..2,383.09 rows=1,112 width=57) (actual time=3.350..179.027 rows=4,499 loops=1)

  • Index Cond: (date >= '2020-08-18'::date)
  • Filter: (impressions > 0)
  • Rows Removed by Filter: 33,584
16. 0.189 30.618 ↓ 1.2 975 1

Hash (cost=432.09..432.09 rows=812 width=143) (actual time=30.618..30.618 rows=975 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 157kB
17. 0.252 30.429 ↓ 1.2 975 1

Hash Left Join (cost=388.80..432.09 rows=812 width=143) (actual time=28.158..30.429 rows=975 loops=1)

  • Hash Cond: (snapchat_ads_1.creative_id = snapchat_creatives.creative_id)
18. 2.376 2.376 ↑ 1.0 812 1

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

19. 0.216 27.801 ↓ 130.2 1,302 1

Hash (cost=388.67..388.67 rows=10 width=64) (actual time=27.801..27.801 rows=1,302 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 121kB
20. 0.124 27.585 ↓ 130.2 1,302 1

Subquery Scan on snapchat_creatives (cost=388.47..388.67 rows=10 width=64) (actual time=26.671..27.585 rows=1,302 loops=1)

21. 0.613 27.461 ↓ 130.2 1,302 1

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

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

CTE asparam_assoc

23. 0.961 2.300 ↑ 1.3 482 1

HashAggregate (cost=212.55..232.14 rows=603 width=274) (actual time=1.651..2.300 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
24. 0.829 1.339 ↑ 1.4 632 1

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

  • Hash Cond: (snapchat_creatives_1.headline = snapchat_creatives_2.headline)
25. 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.004..0.202 rows=1,018 loops=1)

  • Filter: ((review_status = 'APPROVED'::text) OR (review_status = 'DISAPPROVED'::text))
  • Rows Removed by Filter: 198
26. 0.002 0.308 ↑ 7.8 9 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
27. 0.037 0.306 ↑ 7.8 9 1

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

  • Group Key: snapchat_creatives_2.headline
28. 0.269 0.269 ↑ 1.0 197 1

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

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

CTE full_creatives

30. 3.397 3.397 ↑ 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.450..3.397 rows=1,216 loops=1)

31.          

CTE abc

32. 12.277 20.029 ↓ 363.0 4,356 1

Hash Right Join (cost=30.58..47.43 rows=12 width=96) (actual time=6.861..20.029 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
33. 2.564 2.564 ↑ 1.3 482 1

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

34. 0.400 5.188 ↓ 84.8 1,018 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 331kB
35. 4.788 4.788 ↓ 84.8 1,018 1

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

  • Filter: ((review_status = 'APPROVED'::text) OR (review_status = 'DISAPPROVED'::text))
  • Rows Removed by Filter: 198
36. 5.387 26.848 ↓ 379.0 3,790 1

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

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

CTE Scan on abc (cost=0.00..0.30 rows=10 width=96) (actual time=6.863..21.461 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
38. 0.830 81,601.395 ↓ 2.7 1,153 1

Finalize GroupAggregate (cost=1,653,853.66..1,653,892.76 rows=424 width=31) (actual time=81,596.151..81,601.395 rows=1,153 loops=1)

  • Group Key: ga_gemini_tag.date, ga_gemini_tag.dimension21
39. 11.892 81,600.565 ↓ 7.1 1,760 1

Gather Merge (cost=1,653,853.66..1,653,886.65 rows=249 width=31) (actual time=81,596.137..81,600.565 rows=1,760 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
40. 2.562 81,588.673 ↓ 3.5 880 2 / 2

Partial GroupAggregate (cost=1,652,853.65..1,652,858.63 rows=249 width=31) (actual time=81,585.068..81,588.673 rows=880 loops=2)

  • Group Key: ga_gemini_tag.date, ga_gemini_tag.dimension21
41. 14.981 81,586.111 ↓ 49.9 12,432 2 / 2

Sort (cost=1,652,853.65..1,652,854.27 rows=249 width=31) (actual time=81,585.051..81,586.111 rows=12,432 loops=2)

  • Sort Key: ga_gemini_tag.date, ga_gemini_tag.dimension21
  • Sort Method: quicksort Memory: 1,439kB
  • Worker 0: Sort Method: quicksort Memory: 1,271kB
42. 81,571.130 81,571.130 ↓ 49.9 12,432 2 / 2

Parallel Index Scan using idx_17910449_date on ga_gemini_tag (cost=0.57..1,652,843.74 rows=249 width=31) (actual time=2.735..81,571.130 rows=12,432 loops=2)

  • Index Cond: (date >= '2020-08-18'::date)
  • Filter: (dimension21 ~~ '211%%'::text)
  • Rows Removed by Filter: 2,248,398
43. 82.228 932.068 ↓ 2.1 341 5,419

Materialize (cost=0.55..33,545.02 rows=160 width=120) (actual time=0.002..0.172 rows=341 loops=5,419)

44. 849.840 849.840 ↓ 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.852..849.840 rows=341 loops=1)

  • Index Cond: ((channel)::text = 'Snapchat'::text)
45. 21.676 21.676 ↑ 1.0 1 5,419

Index Scan using idx_snapchat_adsquads_id on snapchat_adsquads snapchat_adsquads_1 (cost=0.27..0.33 rows=1 width=129) (actual time=0.004..0.004 rows=1 loops=5,419)

  • Index Cond: (id = snapchat_ads_1.ad_squad_id)
46. 10.838 10.838 ↑ 1.0 1 5,419

Index Only Scan using idx_snapchat_campaigns_id on snapchat_campaigns snapchat_campaigns_1 (cost=0.27..0.33 rows=1 width=37) (actual time=0.002..0.002 rows=1 loops=5,419)

  • Index Cond: (id = snapchat_adsquads_1.campaign_id)
  • Heap Fetches: 4,499
47. 0.113 53,960.709 ↑ 5.6 58 1

Finalize GroupAggregate (cost=1,000.58..1,653,880.00 rows=326 width=12) (actual time=55.426..53,960.709 rows=58 loops=1)

  • Group Key: ga_gemini_tag_1.date
48. 0.000 53,960.596 ↑ 3.6 70 1

Gather Merge (cost=1,000.58..1,653,875.50 rows=249 width=12) (actual time=48.647..53,960.596 rows=70 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
49. 1.950 54,779.906 ↑ 7.1 35 2 / 2

Partial GroupAggregate (cost=0.57..1,652,847.48 rows=249 width=12) (actual time=27.396..54,779.906 rows=35 loops=2)

  • Group Key: ga_gemini_tag_1.date
50. 54,777.956 54,777.956 ↓ 49.9 12,432 2 / 2

Parallel Index Scan using idx_17910449_date on ga_gemini_tag ga_gemini_tag_1 (cost=0.57..1,652,843.74 rows=249 width=12) (actual time=2.617..54,777.956 rows=12,432 loops=2)

  • Index Cond: (date >= '2020-08-18'::date)
  • Filter: (dimension21 ~~ '211%%'::text)
  • Rows Removed by Filter: 2,248,398
51. 1.340 619.394 ↑ 2.7 3,081 1

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

  • Group Key: avantis_subid_daily_report.date, avantis_subid_daily_report.sub_id
52. 6.874 618.054 ↑ 1.6 3,081 1

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

  • Workers Planned: 1
  • Workers Launched: 1
53. 0.715 611.180 ↑ 3.3 1,540 2 / 2

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

  • Group Key: avantis_subid_daily_report.date, avantis_subid_daily_report.sub_id
54. 4.656 610.465 ↑ 3.2 1,572 2 / 2

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

  • Sort Key: avantis_subid_daily_report.date, avantis_subid_daily_report.sub_id
  • Sort Method: quicksort Memory: 180kB
  • Worker 0: Sort Method: quicksort Memory: 162kB
55. 605.809 605.809 ↑ 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=0.301..605.809 rows=1,572 loops=2)

  • Filter: (""left""(sub_id, 3) = '211'::text)
  • Rows Removed by Filter: 852,426
56. 0.906 6,155.040 ↑ 1.5 285 1

GroupAggregate (cost=0.43..83,433.71 rows=432 width=12) (actual time=30.908..6,155.040 rows=285 loops=1)

  • Group Key: avantis_subid_daily_report_1.date
57. 6,154.134 6,154.134 ↑ 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=23.352..6,154.134 rows=3,143 loops=1)

  • Filter: (""left""(sub_id, 3) = '211'::text)
  • Rows Removed by Filter: 1,704,852
58. 0.024 27,418.782 ↑ 12.9 58 1

Hash (cost=52,894.11..52,894.11 rows=747 width=12) (actual time=27,418.782..27,418.782 rows=58 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
59. 0.011 27,418.758 ↑ 12.9 58 1

Subquery Scan on dfp_all (cost=52,820.86..52,894.11 rows=747 width=12) (actual time=27,409.622..27,418.758 rows=58 loops=1)

60. 6.782 27,418.747 ↑ 12.9 58 1

GroupAggregate (cost=52,820.86..52,886.64 rows=747 width=12) (actual time=27,409.621..27,418.747 rows=58 loops=1)

  • Group Key: dfp_ad_unit_device_flaf.date
61. 9.732 27,411.965 ↓ 6.0 46,630 1

Sort (cost=52,820.86..52,840.29 rows=7,775 width=12) (actual time=27,409.591..27,411.965 rows=46,630 loops=1)

  • Sort Key: dfp_ad_unit_device_flaf.date
  • Sort Method: quicksort Memory: 3,722kB
62. 152.648 27,402.233 ↓ 6.0 46,630 1

Bitmap Heap Scan on dfp_ad_unit_device_flaf (cost=43,704.09..52,318.41 rows=7,775 width=12) (actual time=27,264.978..27,402.233 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,277
63. 1.205 27,249.585 ↓ 0.0 0 1

BitmapAnd (cost=43,704.09..43,704.09 rows=7,775 width=0) (actual time=27,249.585..27,249.585 rows=0 loops=1)

64. 605.010 605.010 ↓ 2.5 428,919 1

Bitmap Index Scan on idx_684476_flaf (cost=0.00..2,202.13 rows=173,422 width=0) (actual time=605.010..605.010 rows=428,919 loops=1)

  • Index Cond: (flaf_id = 220,010)
65. 26,643.370 26,643.370 ↓ 5.1 17,799,681 1

Bitmap Index Scan on idx_684476_date (cost=0.00..41,497.82 rows=3,481,540 width=0) (actual time=26,643.370..26,643.370 rows=17,799,681 loops=1)

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

CTE swipes_by_campaign_date

67. 2.190 2.819 ↓ 6.5 1,300 1

HashAggregate (cost=53.91..56.41 rows=200 width=128) (actual time=2.544..2.819 rows=1,300 loops=1)

  • Group Key: all_data.date, all_data.campaign_id, all_data.as_parameter
68. 0.629 0.629 ↓ 3.0 5,419 1

CTE Scan on all_data (cost=0.00..35.94 rows=1,797 width=104) (actual time=0.001..0.629 rows=5,419 loops=1)

69.          

CTE xyz

70. 5.282 171,501.491 ↓ 3.0 5,419 1

Merge Left Join (cost=144.72..182.67 rows=1,797 width=852) (actual time=171,495.537..171,501.491 rows=5,419 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))
71. 9.910 171,489.510 ↓ 3.0 5,419 1

Sort (cost=133.08..137.57 rows=1,797 width=820) (actual time=171,489.098..171,489.510 rows=5,419 loops=1)

  • Sort Key: all_data_1.date, all_data_1.campaign_id, all_data_1.as_parameter
  • Sort Method: quicksort Memory: 3,094kB
72. 171,479.600 171,479.600 ↓ 3.0 5,419 1

CTE Scan on all_data all_data_1 (cost=0.00..35.94 rows=1,797 width=820) (actual time=171,471.749..171,479.600 rows=5,419 loops=1)

73. 3.470 6.699 ↓ 23.8 4,750 1

Sort (cost=11.64..12.14 rows=200 width=128) (actual time=6.425..6.699 rows=4,750 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: 214kB
74. 3.229 3.229 ↓ 6.5 1,300 1

CTE Scan on swipes_by_campaign_date (cost=0.00..4.00 rows=200 width=128) (actual time=2.546..3.229 rows=1,300 loops=1)

75.          

CTE synthesis

76. 10.158 171,520.850 ↓ 3.0 5,419 1

Sort (cost=195.98..200.47 rows=1,797 width=844) (actual time=171,520.534..171,520.850 rows=5,419 loops=1)

  • Sort Key: xyz.date DESC, xyz.as_parameter
  • Sort Method: quicksort Memory: 3,183kB
77. 171,510.692 171,510.692 ↓ 3.0 5,419 1

CTE Scan on xyz (cost=0.00..98.84 rows=1,797 width=844) (actual time=171,495.552..171,510.692 rows=5,419 loops=1)

Planning time : 15.027 ms