explain.depesz.com

PostgreSQL's explain analyze made readable

Result: b6qE

Settings
# exclusive inclusive rows x rows loops node
1. 7.477 109,717.127 ↑ 158.3 4,471 1

Merge Left Join (cost=2,382,731.29..2,420,462.29 rows=707,581 width=276) (actual time=109,695.792..109,717.127 rows=4,471 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. 204.127 45,715.714 ↑ 158.3 4,471 1

GroupAggregate (cost=1,543,856.14..1,611,076.34 rows=707,581 width=155) (actual time=45,183.731..45,715.714 rows=4,471 loops=1)

  • Group Key: avantis_subid_device_daily_report.date, alls.site_id, (CASE WHEN (""substring""(avantis_subid_device_daily_report.publisher_channel_name, 'Desktop|Tablet|Mobile'::text) = 'Desktop'::text) THEN 'D'::text WHEN (""substring""(avantis_subid_device_daily_report.publisher_channel_name, 'Desktop|Tablet|Mobile'::text) = 'Mobile'::text) THEN 'M'::text WHEN (""substring""(avantis_subid_device_daily_report.publisher_channel_name, 'Desktop|Tablet|Mobile'::text) = '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 ~~ '70%'::text) OR (avantis_subid_device_daily_report.sub_id ~~* '%yoe'::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 ~~ '6daq%'::text) THEN 'quiz'::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. 1,042.078 45,511.587 ↓ 1.0 710,716 1

Sort (cost=1,543,856.14..1,545,625.10 rows=707,581 width=131) (actual time=45,183.716..45,511.587 rows=710,716 loops=1)

  • Sort Key: avantis_subid_device_daily_report.date, alls.site_id, (CASE WHEN (""substring""(avantis_subid_device_daily_report.publisher_channel_name, 'Desktop|Tablet|Mobile'::text) = 'Desktop'::text) THEN 'D'::text WHEN (""substring""(avantis_subid_device_daily_report.publisher_channel_name, 'Desktop|Tablet|Mobile'::text) = 'Mobile'::text) THEN 'M'::text WHEN (""substring""(avantis_subid_device_daily_report.publisher_channel_name, 'Desktop|Tablet|Mobile'::text) = '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 ~~ '70%'::text) OR (avantis_subid_device_daily_report.sub_id ~~* '%yoe'::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 ~~ '6daq%'::text) THEN 'quiz'::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: 40,504kB
5. 23,887.129 44,469.509 ↓ 1.0 710,716 1

Nested Loop Left Join (cost=0.43..1,418,443.68 rows=707,581 width=131) (actual time=6.514..44,469.509 rows=710,716 loops=1)

  • Join Filter: (avantis_subid_device_daily_report.domain ~~* ('%'::text || alls.domain))
  • Rows Removed by Join Filter: 76,046,920
6. 17,028.800 17,028.800 ↓ 1.0 710,716 1

Index Scan using idx_avantis_subid_device_daily_report_date on avantis_subid_device_daily_report (cost=0.43..40,426.33 rows=707,581 width=162) (actual time=2.839..17,028.800 rows=710,716 loops=1)

  • Index Cond: (date >= '2020-09-06'::date)
7. 3,553.550 3,553.580 ↑ 1.0 108 710,716

Materialize (cost=0.00..3.62 rows=108 width=20) (actual time=0.000..0.005 rows=108 loops=710,716)

8. 0.030 0.030 ↑ 1.0 108 1

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

9.          

CTE ga

10. 41.999 22,107.411 ↑ 7.5 18,477 1

GroupAggregate (cost=35,843.26..47,640.84 rows=138,795 width=137) (actual time=22,046.269..22,107.411 rows=18,477 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 ~~ '70%'::text) OR (ga_campaign_device_country.dimension21 ~~* '%yoe'::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 ~~ '6daq%'::text) THEN 'quiz'::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. 259.858 22,065.412 ↓ 1.1 155,113 1

Sort (cost=35,843.26..36,190.25 rows=138,795 width=113) (actual time=22,046.257..22,065.412 rows=155,113 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 ~~ '70%'::text) OR (ga_campaign_device_country.dimension21 ~~* '%yoe'::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 ~~ '6daq%'::text) THEN 'quiz'::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: 6,592kB
12. 219.582 21,805.554 ↓ 1.1 155,113 1

Hash Left Join (cost=9.06..18,986.37 rows=138,795 width=113) (actual time=0.743..21,805.554 rows=155,113 loops=1)

  • Hash Cond: (ga_campaign_device_country.country = country_code.country_name_ga)
13. 21,585.272 21,585.272 ↓ 1.1 155,113 1

Index Scan using idx_684611_date on ga_campaign_device_country (cost=0.44..8,222.33 rows=138,715 width=51) (actual time=0.021..21,585.272 rows=155,113 loops=1)

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

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

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

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

16.          

CTE dfp

17. 9.193 41,745.944 ↑ 35.7 16,738 1

GroupAggregate (cost=234,261.49..253,694.38 rows=597,935 width=136) (actual time=41,735.717..41,745.944 rows=16,738 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. 53.057 41,736.751 ↑ 32.8 18,216 1

Sort (cost=234,261.49..235,756.33 rows=597,935 width=136) (actual time=41,735.701..41,736.751 rows=18,216 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: 2,192kB
19. 5.064 41,683.694 ↑ 32.8 18,216 1

Hash Left Join (cost=100,034.81..152,948.79 rows=597,935 width=136) (actual time=41,436.086..41,683.694 rows=18,216 loops=1)

  • Hash Cond: ((dfp_ad_unit_device_flaf.country_name)::text = country_code_1.country_name_dfp)
20. 4.086 41,678.543 ↑ 30.3 18,216 1

Hash Left Join (cost=100,026.19..131,767.28 rows=552,492 width=95) (actual time=41,435.990..41,678.543 rows=18,216 loops=1)

  • Hash Cond: (dfp_ad_unit_device_flaf.flaf_id = flaf.pid)
21. 105.077 41,673.687 ↑ 30.0 18,191 1

GroupAggregate (cost=100,016.46..118,056.49 rows=545,076 width=91) (actual time=41,435.207..41,673.687 rows=18,191 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. 1,320.618 41,568.610 ↑ 1.0 544,638 1

Sort (cost=100,016.46..101,425.59 rows=563,651 width=35) (actual time=41,435.166..41,568.610 rows=544,638 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: 28,600kB
23. 40,247.992 40,247.992 ↑ 1.0 544,638 1

Index Scan using idx_684476_date on dfp_ad_unit_device_flaf (cost=0.57..37,147.07 rows=563,651 width=35) (actual time=5.788..40,247.992 rows=544,638 loops=1)

  • Index Cond: (date >= '2020-09-06'::date)
24. 0.041 0.770 ↑ 1.0 298 1

Hash (cost=5.99..5.99 rows=299 width=23) (actual time=0.770..0.770 rows=298 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 24kB
25. 0.729 0.729 ↑ 1.0 299 1

Seq Scan on flaf (cost=0.00..5.99 rows=299 width=23) (actual time=0.583..0.729 rows=299 loops=1)

26. 0.041 0.087 ↑ 1.0 250 1

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

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

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

28. 8.004 67,902.775 ↑ 158.3 4,471 1

Merge Left Join (cost=190,307.68..205,466.26 rows=707,581 width=244) (actual time=67,891.473..67,902.775 rows=4,471 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. 9.149 45,727.253 ↑ 158.3 4,471 1

Sort (cost=167,895.09..169,664.04 rows=707,581 width=212) (actual time=45,726.970..45,727.253 rows=4,471 loops=1)

  • Sort Key: avantis.date, avantis.site, avantis.device, avantis.country, avantis.channel, avantis.sub_channel
  • Sort Method: quicksort Memory: 589kB
30. 45,718.104 45,718.104 ↑ 158.3 4,471 1

CTE Scan on avantis (cost=0.00..14,151.62 rows=707,581 width=212) (actual time=45,183.737..45,718.104 rows=4,471 loops=1)

31. 1.964 22,167.518 ↑ 7.5 18,471 1

Materialize (cost=22,412.59..23,106.57 rows=138,795 width=196) (actual time=22,164.492..22,167.518 rows=18,471 loops=1)

32. 50.637 22,165.554 ↑ 7.5 18,471 1

Sort (cost=22,412.59..22,759.58 rows=138,795 width=196) (actual time=22,164.486..22,165.554 rows=18,471 loops=1)

  • Sort Key: ga.date, ga.site, ga.device, ga.country, ga.channel, ga.sub_channel
  • Sort Method: quicksort Memory: 2,212kB
33. 22,114.917 22,114.917 ↑ 7.5 18,477 1

CTE Scan on ga (cost=0.00..2,775.90 rows=138,795 width=196) (actual time=22,046.273..22,114.917 rows=18,477 loops=1)

34. 1.697 41,806.875 ↑ 35.7 16,734 1

Materialize (cost=280,012.05..283,001.73 rows=597,935 width=680) (actual time=41,804.308..41,806.875 rows=16,734 loops=1)

35. 52.460 41,805.178 ↑ 35.7 16,734 1

Sort (cost=280,012.05..281,506.89 rows=597,935 width=680) (actual time=41,804.303..41,805.178 rows=16,734 loops=1)

  • Sort Key: dfp.date, dfp.site, dfp.device, dfp.country, dfp.channel, dfp.sub_channel
  • Sort Method: quicksort Memory: 2,076kB
36. 41,752.718 41,752.718 ↑ 35.7 16,738 1

CTE Scan on dfp (cost=0.00..11,958.70 rows=597,935 width=680) (actual time=41,735.721..41,752.718 rows=16,738 loops=1)