explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 43yGO

Settings
# exclusive inclusive rows x rows loops node
1. 4.167 228,598.943 ↓ 3.0 5,419 1

GroupAggregate (cost=3,535,056.56..3,535,339.61 rows=1,797 width=451) (actual time=228,594.471..228,598.943 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))
2. 13.078 228,594.776 ↓ 1.7 5,419 1

Sort (cost=3,535,056.56..3,535,064.64 rows=3,235 width=427) (actual time=228,594.455..228,594.776 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
3. 7.241 228,581.698 ↓ 1.7 5,419 1

Hash Left Join (cost=1,760,088.58..3,534,867.96 rows=3,235 width=427) (actual time=125,139.264..228,581.698 rows=5,419 loops=1)

  • Hash Cond: (snapchat_stats.date = dfp_all.date)
4. 1.489 199,550.469 ↓ 3.8 5,419 1

Merge Left Join (cost=1,707,168.81..3,481,928.19 rows=1,443 width=391) (actual time=96,115.248..199,550.469 rows=5,419 loops=1)

  • Merge Cond: (snapchat_stats.date = avantis_subid_daily_report_1.date)
5. 2.457 194,310.509 ↓ 4.9 5,419 1

Merge Left Join (cost=1,707,168.38..3,398,471.86 rows=1,112 width=383) (actual time=94,795.368..194,310.509 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))
6. 1.505 193,633.868 ↓ 4.9 5,419 1

Merge Left Join (cost=1,657,761.75..3,348,147.24 rows=1,112 width=375) (actual time=94,122.187..193,633.868 rows=5,419 loops=1)

  • Merge Cond: (snapchat_stats.date = ga_gemini_tag_1.date)
7. 1.568 94,728.930 ↓ 4.9 5,419 1

Nested Loop Left Join (cost=1,656,761.17..1,694,249.49 rows=1,112 width=367) (actual time=93,374.348..94,728.930 rows=5,419 loops=1)

8. 14.934 94,716.524 ↓ 4.9 5,419 1

Nested Loop Left Join (cost=1,656,760.90..1,693,867.70 rows=1,112 width=367) (actual time=93,373.982..94,716.524 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
9. 496.554 94,679.914 ↓ 4.9 5,419 1

Nested Loop Left Join (cost=1,656,760.63..1,693,472.17 rows=1,112 width=275) (actual time=93,372.578..94,679.914 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
10. 3.155 93,202.521 ↓ 4.9 5,419 1

Merge Left Join (cost=1,656,760.08..1,656,813.95 rows=1,112 width=171) (actual time=93,193.460..93,202.521 rows=5,419 loops=1)

  • Merge Cond: ((snapchat_stats.date = ga_gemini_tag.date) AND (snapchat_creatives.as_parameter = ga_gemini_tag.dimension21))
11. 15.854 285.378 ↓ 4.9 5,419 1

Sort (cost=2,906.42..2,909.20 rows=1,112 width=163) (actual time=284.554..285.378 rows=5,419 loops=1)

  • Sort Key: snapchat_stats.date, snapchat_creatives.as_parameter
  • Sort Method: quicksort Memory: 1,632kB
12. 1.900 269.524 ↓ 4.9 5,419 1

Hash Left Join (cost=452.19..2,850.16 rows=1,112 width=163) (actual time=35.555..269.524 rows=5,419 loops=1)

  • Hash Cond: (snapchat_stats.id = snapchat_ads_1.id)
13. 236.760 236.760 ↓ 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=4.683..236.760 rows=4,499 loops=1)

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

Hash (cost=440.97..440.97 rows=864 width=143) (actual time=30.864..30.864 rows=975 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 157kB
15. 0.276 30.675 ↓ 1.1 975 1

Hash Left Join (cost=396.94..440.97 rows=864 width=143) (actual time=29.224..30.675 rows=975 loops=1)

  • Hash Cond: (snapchat_ads_1.creative_id = snapchat_creatives.creative_id)
16. 1.522 1.522 ↑ 1.1 812 1

Seq Scan on snapchat_ads snapchat_ads_1 (cost=0.00..40.64 rows=864 width=111) (actual time=0.341..1.522 rows=812 loops=1)

17. 0.216 28.877 ↓ 118.4 1,302 1

Hash (cost=396.81..396.81 rows=11 width=64) (actual time=28.877..28.877 rows=1,302 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 121kB
18. 0.124 28.661 ↓ 118.4 1,302 1

Subquery Scan on snapchat_creatives (cost=396.59..396.81 rows=11 width=64) (actual time=27.668..28.661 rows=1,302 loops=1)

19. 0.641 28.537 ↓ 118.4 1,302 1

Group (cost=396.59..396.70 rows=11 width=96) (actual time=27.667..28.537 rows=1,302 loops=1)

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

CTE asparam_assoc

21. 0.922 2.233 ↑ 1.3 482 1

HashAggregate (cost=215.74..236.28 rows=632 width=274) (actual time=1.594..2.233 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
22. 0.802 1.311 ↑ 1.5 632 1

Hash Join (cost=100.14..201.67 rows=938 width=242) (actual time=0.327..1.311 rows=632 loops=1)

  • Hash Cond: (snapchat_creatives_1.headline = snapchat_creatives_2.headline)
23. 0.196 0.196 ↓ 1.0 1,018 1

Seq Scan on snapchat_creatives snapchat_creatives_1 (cost=0.00..94.12 rows=978 width=210) (actual time=0.004..0.196 rows=1,018 loops=1)

  • Filter: ((review_status = 'APPROVED'::text) OR (review_status = 'DISAPPROVED'::text))
  • Rows Removed by Filter: 198
24. 0.003 0.313 ↑ 7.9 9 1

Hash (cost=99.25..99.25 rows=71 width=18) (actual time=0.313..0.313 rows=9 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
25. 0.034 0.310 ↑ 7.9 9 1

HashAggregate (cost=97.83..98.54 rows=71 width=18) (actual time=0.309..0.310 rows=9 loops=1)

  • Group Key: snapchat_creatives_2.headline
26. 0.276 0.276 ↑ 1.1 197 1

Seq Scan on snapchat_creatives snapchat_creatives_2 (cost=0.00..97.31 rows=208 width=18) (actual time=0.027..0.276 rows=197 loops=1)

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

CTE full_creatives

28. 4.795 4.795 ↑ 1.0 1,216 1

Seq Scan on snapchat_creatives snapchat_creatives_1_1 (cost=0.00..110.06 rows=1,275 width=461) (actual time=0.448..4.795 rows=1,216 loops=1)

29.          

CTE abc

30. 11.968 20.999 ↓ 335.1 4,356 1

Hash Right Join (cost=32.07..49.73 rows=13 width=96) (actual time=8.149..20.999 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
31. 2.496 2.496 ↑ 1.3 482 1

CTE Scan on asparam_assoc (cost=0.00..12.64 rows=632 width=168) (actual time=1.596..2.496 rows=482 loops=1)

32. 0.393 6.535 ↓ 78.3 1,018 1

Hash (cost=31.88..31.88 rows=13 width=232) (actual time=6.535..6.535 rows=1,018 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 331kB
33. 6.142 6.142 ↓ 78.3 1,018 1

CTE Scan on full_creatives (cost=0.00..31.88 rows=13 width=232) (actual time=0.452..6.142 rows=1,018 loops=1)

  • Filter: ((review_status = 'APPROVED'::text) OR (review_status = 'DISAPPROVED'::text))
  • Rows Removed by Filter: 198
34. 5.493 27.896 ↓ 344.5 3,790 1

Sort (cost=0.52..0.54 rows=11 width=96) (actual time=27.665..27.896 rows=3,790 loops=1)

  • Sort Key: abc.creative_id, abc.as_parameter, abc.campaign_name
  • Sort Method: quicksort Memory: 677kB
35. 22.403 22.403 ↓ 344.5 3,790 1

CTE Scan on abc (cost=0.00..0.33 rows=11 width=96) (actual time=8.153..22.403 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
36. 0.818 92,913.988 ↓ 2.7 1,153 1

Finalize GroupAggregate (cost=1,653,853.66..1,653,892.76 rows=424 width=31) (actual time=92,908.891..92,913.988 rows=1,153 loops=1)

  • Group Key: ga_gemini_tag.date, ga_gemini_tag.dimension21
37. 11.526 92,913.170 ↓ 6.7 1,661 1

Gather Merge (cost=1,653,853.66..1,653,886.65 rows=249 width=31) (actual time=92,908.874..92,913.170 rows=1,661 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
38. 2.418 92,901.644 ↓ 3.3 830 2 / 2

Partial GroupAggregate (cost=1,652,853.65..1,652,858.63 rows=249 width=31) (actual time=92,898.235..92,901.644 rows=830 loops=2)

  • Group Key: ga_gemini_tag.date, ga_gemini_tag.dimension21
39. 17.444 92,899.226 ↓ 49.9 12,432 2 / 2

Sort (cost=1,652,853.65..1,652,854.27 rows=249 width=31) (actual time=92,898.222..92,899.226 rows=12,432 loops=2)

  • Sort Key: ga_gemini_tag.date, ga_gemini_tag.dimension21
  • Sort Method: quicksort Memory: 1,453kB
  • Worker 0: Sort Method: quicksort Memory: 1,257kB
40. 92,881.782 92,881.782 ↓ 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=205.735..92,881.782 rows=12,432 loops=2)

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

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

42. 896.782 896.782 ↓ 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.779..896.782 rows=341 loops=1)

  • Index Cond: ((channel)::text = 'Snapchat'::text)
43. 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)
44. 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
45. 0.106 98,903.433 ↑ 5.6 58 1

Finalize GroupAggregate (cost=1,000.58..1,653,880.00 rows=326 width=12) (actual time=747.834..98,903.433 rows=58 loops=1)

  • Group Key: ga_gemini_tag_1.date
46. 14,284.026 98,903.327 ↑ 3.2 78 1

Gather Merge (cost=1,000.58..1,653,875.50 rows=249 width=12) (actual time=415.445..98,903.327 rows=78 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
47. 2.280 84,619.301 ↑ 6.4 39 2 / 2

Partial GroupAggregate (cost=0.57..1,652,847.48 rows=249 width=12) (actual time=385.724..84,619.301 rows=39 loops=2)

  • Group Key: ga_gemini_tag_1.date
48. 84,617.021 84,617.021 ↓ 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=172.390..84,617.021 rows=12,432 loops=2)

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

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

  • Group Key: avantis_subid_daily_report.date, avantis_subid_daily_report.sub_id
50. 4.516 672.772 ↑ 1.6 3,081 1

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

  • Workers Planned: 1
  • Workers Launched: 1
51. 0.712 668.256 ↑ 3.3 1,540 2 / 2

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

  • Group Key: avantis_subid_daily_report.date, avantis_subid_daily_report.sub_id
52. 4.452 667.544 ↑ 3.2 1,572 2 / 2

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

  • Sort Key: avantis_subid_daily_report.date, avantis_subid_daily_report.sub_id
  • Sort Method: quicksort Memory: 166kB
  • Worker 0: Sort Method: quicksort Memory: 175kB
53. 663.092 663.092 ↑ 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.873..663.092 rows=1,572 loops=2)

  • Filter: (""left""(sub_id, 3) = '211'::text)
  • Rows Removed by Filter: 852,460
54. 0.795 5,238.471 ↑ 1.5 285 1

GroupAggregate (cost=0.43..83,433.71 rows=432 width=12) (actual time=33.072..5,238.471 rows=285 loops=1)

  • Group Key: avantis_subid_daily_report_1.date
55. 5,237.676 5,237.676 ↑ 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=27.964..5,237.676 rows=3,143 loops=1)

  • Filter: (""left""(sub_id, 3) = '211'::text)
  • Rows Removed by Filter: 1,704,919
56. 0.021 29,023.988 ↑ 12.9 58 1

Hash (cost=52,910.43..52,910.43 rows=747 width=12) (actual time=29,023.987..29,023.988 rows=58 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
57. 0.008 29,023.967 ↑ 12.9 58 1

Subquery Scan on dfp_all (cost=52,837.15..52,910.43 rows=747 width=12) (actual time=29,017.497..29,023.967 rows=58 loops=1)

58. 4.232 29,023.959 ↑ 12.9 58 1

GroupAggregate (cost=52,837.15..52,902.96 rows=747 width=12) (actual time=29,017.496..29,023.959 rows=58 loops=1)

  • Group Key: dfp_ad_unit_device_flaf.date
59. 9.602 29,019.727 ↓ 6.0 46,642 1

Sort (cost=52,837.15..52,856.60 rows=7,778 width=12) (actual time=29,017.472..29,019.727 rows=46,642 loops=1)

  • Sort Key: dfp_ad_unit_device_flaf.date
  • Sort Method: quicksort Memory: 3,723kB
60. 158.068 29,010.125 ↓ 6.0 46,642 1

Bitmap Heap Scan on dfp_ad_unit_device_flaf (cost=43,716.84..52,334.49 rows=7,778 width=12) (actual time=28,868.286..29,010.125 rows=46,642 loops=1)

  • Recheck Cond: ((flaf_id = 220,010) AND (date >= '2020-08-18'::date))
  • Rows Removed by Index Recheck: 59
  • Heap Blocks: exact=9,279
61. 1.141 28,852.057 ↓ 0.0 0 1

BitmapAnd (cost=43,716.84..43,716.84 rows=7,778 width=0) (actual time=28,852.056..28,852.057 rows=0 loops=1)

62. 535.409 535.409 ↓ 2.5 428,942 1

Bitmap Index Scan on idx_684476_flaf (cost=0.00..2,202.53 rows=173,475 width=0) (actual time=535.409..535.409 rows=428,942 loops=1)

  • Index Cond: (flaf_id = 220,010)
63. 28,315.507 28,315.507 ↓ 5.1 17,826,864 1

Bitmap Index Scan on idx_684476_date (cost=0.00..41,510.17 rows=3,482,601 width=0) (actual time=28,315.507..28,315.507 rows=17,826,864 loops=1)

  • Index Cond: (date >= '2020-08-18'::date)
Planning time : 12.374 ms