explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LimS

Settings
# exclusive inclusive rows x rows loops node
1. 3.406 113,837.365 ↑ 374.0 1,982 1

Merge Left Join (cost=3,345,896.34..3,377,844.14 rows=741,205 width=276) (actual time=113,827.107..113,837.365 rows=1,982 loops=1)

  • Merge Cond: ((avantis.date = dfp.date) AND (avantis.site = (dfp.site)::text) AND (avantis.device = (dfp.device)::text) AND (avantis.country = dfp.country) AND (avantis.channel = dfp.channel) AND (avantis.sub_channel = dfp.sub_channel))
2.          

CTE avantis

3. 91.385 19,704.321 ↑ 374.0 1,982 1

GroupAggregate (cost=2,791,066.62..2,863,334.11 rows=741,205 width=155) (actual time=19,463.388..19,704.321 rows=1,982 loops=1)

  • Group Key: avantis_subid_device_daily_report.date, alls.site_id, (CASE WHEN (split_part(split_part(avantis_subid_device_daily_report.publisher_channel_name, '.com_'::text, 2), '_'::text, 1) = 'Desktop'::text) THEN 'D'::text WHEN (split_part(split_part(avantis_subid_device_daily_report.publisher_channel_name, '.com_'::text, 2), '_'::text, 1) = 'Mobile'::text) THEN 'M'::text WHEN (split_part(split_part(avantis_subid_device_daily_report.publisher_channel_name, '.com_'::text, 2), '_'::text, 1) = 'Tablet'::text) THEN 'T'::text ELSE NULL::text END), (CASE WHEN (avantis_subid_device_daily_report.sub_id ~~ '6%'::text) THEN 'fb'::text WHEN (avantis_subid_device_daily_report.sub_id ~~ '2%'::text) THEN 'contentrec'::text WHEN (avantis_subid_device_daily_report.sub_id ~~ '7%'::text) THEN 'aol'::text ELSE 'none'::text END), (CASE WHEN (avantis_subid_device_daily_report.sub_id ~~ '203%'::text) THEN 'outbrain'::text WHEN (avantis_subid_device_daily_report.sub_id ~~ '211%'::text) THEN 'snapchat'::text WHEN ((avantis_subid_device_daily_report.sub_id ~~ '210%'::text) OR (avantis_subid_device_daily_report.sub_id ~* '%yoe'::text)) THEN 'yahoo'::text WHEN (avantis_subid_device_daily_report.sub_id ~~ '202%'::text) THEN 'taboola'::text WHEN (avantis_subid_device_daily_report.sub_id ~~ '810%'::text) THEN 'adrizer_taboola'::text WHEN (avantis_subid_device_daily_report.sub_id ~~ '820%'::text) THEN 'adrizer_outbrain'::text WHEN (avantis_subid_device_daily_report.sub_id ~~ '204%'::text) THEN 'swagbucks'::text WHEN (avantis_subid_device_daily_report.sub_id ~~ '303%'::text) THEN 'PO outbrain'::text WHEN (avantis_subid_device_daily_report.sub_id ~~ '311%'::text) THEN 'PO snapchat'::text WHEN (avantis_subid_device_daily_report.sub_id ~~ '302%'::text) THEN 'PO taboola'::text WHEN (avantis_subid_device_daily_report.sub_id ~~ '310%'::text) THEN 'PO yahoo'::text ELSE 'none'::text END), avantis_subid_device_daily_report.country
4. 502.439 19,612.936 ↑ 2.3 316,106 1

Sort (cost=2,791,066.62..2,792,919.63 rows=741,205 width=131) (actual time=19,463.368..19,612.936 rows=316,106 loops=1)

  • Sort Key: avantis_subid_device_daily_report.date, alls.site_id, (CASE WHEN (split_part(split_part(avantis_subid_device_daily_report.publisher_channel_name, '.com_'::text, 2), '_'::text, 1) = 'Desktop'::text) THEN 'D'::text WHEN (split_part(split_part(avantis_subid_device_daily_report.publisher_channel_name, '.com_'::text, 2), '_'::text, 1) = 'Mobile'::text) THEN 'M'::text WHEN (split_part(split_part(avantis_subid_device_daily_report.publisher_channel_name, '.com_'::text, 2), '_'::text, 1) = 'Tablet'::text) THEN 'T'::text ELSE NULL::text END), (CASE WHEN (avantis_subid_device_daily_report.sub_id ~~ '6%'::text) THEN 'fb'::text WHEN (avantis_subid_device_daily_report.sub_id ~~ '2%'::text) THEN 'contentrec'::text WHEN (avantis_subid_device_daily_report.sub_id ~~ '7%'::text) THEN 'aol'::text ELSE 'none'::text END), (CASE WHEN (avantis_subid_device_daily_report.sub_id ~~ '203%'::text) THEN 'outbrain'::text WHEN (avantis_subid_device_daily_report.sub_id ~~ '211%'::text) THEN 'snapchat'::text WHEN ((avantis_subid_device_daily_report.sub_id ~~ '210%'::text) OR (avantis_subid_device_daily_report.sub_id ~* '%yoe'::text)) THEN 'yahoo'::text WHEN (avantis_subid_device_daily_report.sub_id ~~ '202%'::text) THEN 'taboola'::text WHEN (avantis_subid_device_daily_report.sub_id ~~ '810%'::text) THEN 'adrizer_taboola'::text WHEN (avantis_subid_device_daily_report.sub_id ~~ '820%'::text) THEN 'adrizer_outbrain'::text WHEN (avantis_subid_device_daily_report.sub_id ~~ '204%'::text) THEN 'swagbucks'::text WHEN (avantis_subid_device_daily_report.sub_id ~~ '303%'::text) THEN 'PO outbrain'::text WHEN (avantis_subid_device_daily_report.sub_id ~~ '311%'::text) THEN 'PO snapchat'::text WHEN (avantis_subid_device_daily_report.sub_id ~~ '302%'::text) THEN 'PO taboola'::text WHEN (avantis_subid_device_daily_report.sub_id ~~ '310%'::text) THEN 'PO yahoo'::text ELSE 'none'::text END), avantis_subid_device_daily_report.country
  • Sort Method: external merge Disk: 18,024kB
5. 9,131.610 19,110.497 ↑ 2.3 316,106 1

Nested Loop Left Join (cost=0.42..2,659,445.24 rows=741,205 width=131) (actual time=3.263..19,110.497 rows=316,106 loops=1)

  • Join Filter: (avantis_subid_device_daily_report.domain ~~* ('%'::text || alls.domain))
  • Rows Removed by Join Filter: 33,823,493
6. 8,398.357 8,398.357 ↓ 1.3 316,106 1

Index Scan using idx_avantis_subid_device_daily_report_date on avantis_subid_device_daily_report (cost=0.42..20,737.36 rows=251,256 width=161) (actual time=1.985..8,398.357 rows=316,106 loops=1)

  • Index Cond: (date >= '2020-09-06'::date)
7. 1,580.492 1,580.530 ↑ 5.5 108 316,106

Materialize (cost=0.00..18.85 rows=590 width=20) (actual time=0.000..0.005 rows=108 loops=316,106)

8. 0.038 0.038 ↑ 5.5 108 1

Seq Scan on all_sites alls (cost=0.00..15.90 rows=590 width=20) (actual time=0.012..0.038 rows=108 loops=1)

9.          

CTE ga

10. 20.416 26,618.981 ↑ 10.5 9,583 1

GroupAggregate (cost=25,544.04..33,628.92 rows=101,061 width=137) (actual time=26,591.551..26,618.981 rows=9,583 loops=1)

  • Group Key: ga_campaign_device_country.date, ga_campaign_device_country.site, (CASE WHEN (ga_campaign_device_country.dimension15 = 'desktopx'::text) THEN 'D'::text WHEN (ga_campaign_device_country.dimension15 = 'desktop'::text) THEN 'D'::text WHEN (ga_campaign_device_country.dimension15 = 'tablet'::text) THEN 'T'::text WHEN (ga_campaign_device_country.dimension15 = 'mobile'::text) THEN 'M'::text ELSE ga_campaign_device_country.dimension15 END), (CASE WHEN (ga_campaign_device_country.dimension21 ~~ '6%'::text) THEN 'fb'::text WHEN (ga_campaign_device_country.dimension21 ~~ '2%'::text) THEN 'contentrec'::text WHEN (ga_campaign_device_country.dimension21 ~~ '7%'::text) THEN 'aol'::text ELSE 'none'::text END), (CASE WHEN (ga_campaign_device_country.dimension21 ~~ '203%'::text) THEN 'outbrain'::text WHEN (ga_campaign_device_country.dimension21 ~~ '211%'::text) THEN 'snapchat'::text WHEN ((ga_campaign_device_country.dimension21 ~~ '210%'::text) OR (ga_campaign_device_country.dimension21 ~* '%yoe'::text)) THEN 'yahoo'::text WHEN (ga_campaign_device_country.dimension21 ~~ '202%'::text) THEN 'taboola'::text WHEN (ga_campaign_device_country.dimension21 ~~ '810%'::text) THEN 'adrizer_taboola'::text WHEN (ga_campaign_device_country.dimension21 ~~ '820%'::text) THEN 'adrizer_outbrain'::text WHEN (ga_campaign_device_country.dimension21 ~~ '204%'::text) THEN 'swagbucks'::text WHEN (ga_campaign_device_country.dimension21 ~~ '303%'::text) THEN 'PO outbrain'::text WHEN (ga_campaign_device_country.dimension21 ~~ '311%'::text) THEN 'PO snapchat'::text WHEN (ga_campaign_device_country.dimension21 ~~ '302%'::text) THEN 'PO taboola'::text WHEN (ga_campaign_device_country.dimension21 ~~ '310%'::text) THEN 'PO yahoo'::text ELSE 'none'::text END), country_code.code
11. 155.484 26,598.565 ↑ 1.4 72,484 1

Sort (cost=25,544.04..25,796.69 rows=101,061 width=113) (actual time=26,591.535..26,598.565 rows=72,484 loops=1)

  • Sort Key: ga_campaign_device_country.date, ga_campaign_device_country.site, (CASE WHEN (ga_campaign_device_country.dimension15 = 'desktopx'::text) THEN 'D'::text WHEN (ga_campaign_device_country.dimension15 = 'desktop'::text) THEN 'D'::text WHEN (ga_campaign_device_country.dimension15 = 'tablet'::text) THEN 'T'::text WHEN (ga_campaign_device_country.dimension15 = 'mobile'::text) THEN 'M'::text ELSE ga_campaign_device_country.dimension15 END), (CASE WHEN (ga_campaign_device_country.dimension21 ~~ '6%'::text) THEN 'fb'::text WHEN (ga_campaign_device_country.dimension21 ~~ '2%'::text) THEN 'contentrec'::text WHEN (ga_campaign_device_country.dimension21 ~~ '7%'::text) THEN 'aol'::text ELSE 'none'::text END), (CASE WHEN (ga_campaign_device_country.dimension21 ~~ '203%'::text) THEN 'outbrain'::text WHEN (ga_campaign_device_country.dimension21 ~~ '211%'::text) THEN 'snapchat'::text WHEN ((ga_campaign_device_country.dimension21 ~~ '210%'::text) OR (ga_campaign_device_country.dimension21 ~* '%yoe'::text)) THEN 'yahoo'::text WHEN (ga_campaign_device_country.dimension21 ~~ '202%'::text) THEN 'taboola'::text WHEN (ga_campaign_device_country.dimension21 ~~ '810%'::text) THEN 'adrizer_taboola'::text WHEN (ga_campaign_device_country.dimension21 ~~ '820%'::text) THEN 'adrizer_outbrain'::text WHEN (ga_campaign_device_country.dimension21 ~~ '204%'::text) THEN 'swagbucks'::text WHEN (ga_campaign_device_country.dimension21 ~~ '303%'::text) THEN 'PO outbrain'::text WHEN (ga_campaign_device_country.dimension21 ~~ '311%'::text) THEN 'PO snapchat'::text WHEN (ga_campaign_device_country.dimension21 ~~ '302%'::text) THEN 'PO taboola'::text WHEN (ga_campaign_device_country.dimension21 ~~ '310%'::text) THEN 'PO yahoo'::text ELSE 'none'::text END), country_code.code
  • Sort Method: external merge Disk: 3,088kB
12. 83.267 26,443.081 ↑ 1.4 72,484 1

Hash Left Join (cost=9.06..13,500.56 rows=101,061 width=113) (actual time=1.517..26,443.081 rows=72,484 loops=1)

  • Hash Cond: (ga_campaign_device_country.country = country_code.country_name_ga)
13. 26,358.717 26,358.717 ↑ 1.4 72,484 1

Index Scan using idx_684611_date on ga_campaign_device_country (cost=0.44..6,166.02 rows=100,994 width=51) (actual time=0.024..26,358.717 rows=72,484 loops=1)

  • Index Cond: (date >= '2020-09-06'::date)
14. 0.040 1.097 ↑ 1.0 250 1

Hash (cost=5.50..5.50 rows=250 width=11) (actual time=1.097..1.097 rows=250 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 19kB
15. 1.057 1.057 ↑ 1.0 250 1

Seq Scan on country_code (cost=0.00..5.50 rows=250 width=11) (actual time=1.018..1.057 rows=250 loops=1)

16.          

CTE dfp

17. 4.441 67,437.292 ↑ 36.2 8,059 1

GroupAggregate (cost=112,066.06..121,551.83 rows=291,870 width=136) (actual time=67,432.369..67,437.292 rows=8,059 loops=1)

  • Group Key: dfp_ad_unit_device_flaf.date, (CASE WHEN ((dfp_ad_unit_device_flaf.site)::text = 'OZ'::text) THEN 'CW'::character varying ELSE dfp_ad_unit_device_flaf.site END), dfp_ad_unit_device_flaf.device, (COALESCE(flaf.channel, 'none'::text)), (COALESCE(flaf.sub_channel, 'none'::text)), country_code_1.code
18. 27.360 67,432.851 ↑ 33.3 8,771 1

Sort (cost=112,066.06..112,795.73 rows=291,870 width=136) (actual time=67,432.351..67,432.851 rows=8,771 loops=1)

  • Sort Key: dfp_ad_unit_device_flaf.date, (CASE WHEN ((dfp_ad_unit_device_flaf.site)::text = 'OZ'::text) THEN 'CW'::character varying ELSE dfp_ad_unit_device_flaf.site END), dfp_ad_unit_device_flaf.device, (COALESCE(flaf.channel, 'none'::text)), (COALESCE(flaf.sub_channel, 'none'::text)), country_code_1.code
  • Sort Method: quicksort Memory: 1,070kB
19. 3.109 67,405.491 ↑ 33.3 8,771 1

Hash Left Join (cost=46,470.28..73,884.56 rows=291,870 width=136) (actual time=67,284.231..67,405.491 rows=8,771 loops=1)

  • Hash Cond: ((dfp_ad_unit_device_flaf.country_name)::text = country_code_1.country_name_dfp)
20. 3.171 67,401.440 ↑ 30.7 8,771 1

Hash Left Join (cost=46,461.66..63,540.85 rows=269,687 width=95) (actual time=67,282.977..67,401.440 rows=8,771 loops=1)

  • Hash Cond: (dfp_ad_unit_device_flaf.flaf_id = flaf.pid)
21. 52.223 67,397.438 ↑ 30.4 8,758 1

GroupAggregate (cost=46,428.26..55,154.49 rows=266,169 width=91) (actual time=67,280.996..67,397.438 rows=8,758 loops=1)

  • Group Key: dfp_ad_unit_device_flaf.date, dfp_ad_unit_device_flaf.site, dfp_ad_unit_device_flaf.device, dfp_ad_unit_device_flaf.flaf_id, dfp_ad_unit_device_flaf.country_name
22. 604.187 67,345.215 ↑ 1.0 259,513 1

Sort (cost=46,428.26..47,104.50 rows=270,497 width=35) (actual time=67,280.952..67,345.215 rows=259,513 loops=1)

  • Sort Key: dfp_ad_unit_device_flaf.date, dfp_ad_unit_device_flaf.site, dfp_ad_unit_device_flaf.device, dfp_ad_unit_device_flaf.flaf_id, dfp_ad_unit_device_flaf.country_name
  • Sort Method: external merge Disk: 13,632kB
23. 66,741.028 66,741.028 ↑ 1.0 259,513 1

Index Scan using idx_684476_date on dfp_ad_unit_device_flaf (cost=0.57..17,688.62 rows=270,497 width=35) (actual time=2.877..66,741.028 rows=259,513 loops=1)

  • Index Cond: (date >= '2020-09-06'::date)
24. 0.043 0.831 ↑ 3.5 298 1

Hash (cost=20.40..20.40 rows=1,040 width=23) (actual time=0.831..0.831 rows=298 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 32kB
25. 0.788 0.788 ↑ 3.5 299 1

Seq Scan on flaf (cost=0.00..20.40 rows=1,040 width=23) (actual time=0.600..0.788 rows=299 loops=1)

26. 0.044 0.942 ↑ 1.0 250 1

Hash (cost=5.50..5.50 rows=250 width=11) (actual time=0.942..0.942 rows=250 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 19kB
27. 0.898 0.898 ↑ 1.0 250 1

Seq Scan on country_code country_code_1 (cost=0.00..5.50 rows=250 width=11) (actual time=0.861..0.898 rows=250 loops=1)

28. 3.895 46,370.758 ↑ 374.0 1,982 1

Merge Left Join (cost=192,209.33..207,201.65 rows=741,205 width=244) (actual time=46,365.106..46,370.758 rows=1,982 loops=1)

  • Merge Cond: ((avantis.date = ga.date) AND (avantis.site = ga.site) AND (avantis.device = ga.device) AND (avantis.country = ga.country) AND (avantis.channel = ga.channel) AND (avantis.sub_channel = ga.sub_channel))
29. 5.009 19,714.480 ↑ 374.0 1,982 1

Sort (cost=176,121.28..177,974.30 rows=741,205 width=212) (actual time=19,714.329..19,714.480 rows=1,982 loops=1)

  • Sort Key: avantis.date, avantis.site, avantis.device, avantis.country, avantis.channel, avantis.sub_channel
  • Sort Method: quicksort Memory: 227kB
30. 19,709.471 19,709.471 ↑ 374.0 1,982 1

CTE Scan on avantis (cost=0.00..14,824.10 rows=741,205 width=212) (actual time=19,463.393..19,709.471 rows=1,982 loops=1)

31. 1.115 26,652.383 ↑ 10.6 9,574 1

Materialize (cost=16,088.05..16,593.35 rows=101,061 width=196) (actual time=26,650.763..26,652.383 rows=9,574 loops=1)

32. 27.458 26,651.268 ↑ 10.6 9,574 1

Sort (cost=16,088.05..16,340.70 rows=101,061 width=196) (actual time=26,650.759..26,651.268 rows=9,574 loops=1)

  • Sort Key: ga.date, ga.site, ga.device, ga.country, ga.channel, ga.sub_channel
  • Sort Method: quicksort Memory: 1,133kB
33. 26,623.810 26,623.810 ↑ 10.5 9,583 1

CTE Scan on ga (cost=0.00..2,021.22 rows=101,061 width=196) (actual time=26,591.555..26,623.810 rows=9,583 loops=1)

34. 0.807 67,463.201 ↑ 36.2 8,057 1

Materialize (cost=135,172.15..136,631.50 rows=291,870 width=680) (actual time=67,461.985..67,463.201 rows=8,057 loops=1)

35. 21.820 67,462.394 ↑ 36.2 8,057 1

Sort (cost=135,172.15..135,901.83 rows=291,870 width=680) (actual time=67,461.980..67,462.394 rows=8,057 loops=1)

  • Sort Key: dfp.date, dfp.site, dfp.device, dfp.country, dfp.channel, dfp.sub_channel
  • Sort Method: quicksort Memory: 822kB
36. 67,440.574 67,440.574 ↑ 36.2 8,059 1

CTE Scan on dfp (cost=0.00..5,837.40 rows=291,870 width=680) (actual time=67,432.374..67,440.574 rows=8,059 loops=1)