explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yApO

Settings
# exclusive inclusive rows x rows loops node
1. 91.260 243,552.392 ↓ 0.0 0 1

Insert on engage_deal_lines (cost=1,657,289.86..1,680,602.60 rows=1 width=198) (actual time=243,552.392..243,552.392 rows=0 loops=1)

2.          

CTE engage_deal_line_dollars

3. 4,056.246 8,562.381 ↑ 1.3 248,607 1

GroupAggregate (cost=166,686.39..268,263.28 rows=333,039 width=264) (actual time=4,234.246..8,562.381 rows=248,607 loops=1)

  • Group Key: wd.deal_id, wd.proposal_id, ((COALESCE(to_char(dt.broadcast_week_start_date, 'YYYYMMDD'::text), to_char((hsp.start_date)::timestamp with time zone, 'YYYYMMDD'::text)))::integer), dn.network_sk, dp.property_sk, da.audience_sk
4. 1,192.454 4,506.135 ↓ 3.0 999,543 1

Sort (cost=166,686.39..167,518.99 rows=333,039 width=38) (actual time=4,233.858..4,506.135 rows=999,543 loops=1)

  • Sort Key: wd.deal_id, wd.proposal_id, ((COALESCE(to_char(dt.broadcast_week_start_date, 'YYYYMMDD'::text), to_char((hsp.start_date)::timestamp with time zone, 'YYYYMMDD'::text)))::integer), dn.network_sk, dp.property_sk, da.audience_sk
  • Sort Method: external merge Disk: 48968kB
5. 876.951 3,313.681 ↓ 3.0 999,543 1

Hash Left Join (cost=109,681.49..127,030.83 rows=333,039 width=38) (actual time=1,337.725..3,313.681 rows=999,543 loops=1)

  • Hash Cond: (wd.selling_period_id = hsp.selling_period_id)
6. 200.395 2,436.689 ↓ 3.0 999,543 1

Hash Left Join (cost=109,675.41..118,282.48 rows=333,039 width=46) (actual time=1,337.664..2,436.689 rows=999,543 loops=1)

  • Hash Cond: (wd.property_id = dp.property_id)
7. 191.514 2,236.278 ↓ 3.0 999,543 1

Hash Left Join (cost=109,641.56..117,370.91 rows=333,039 width=46) (actual time=1,337.639..2,236.278 rows=999,543 loops=1)

  • Hash Cond: (wd.network_id = dn.network_id)
8. 200.722 2,044.675 ↓ 3.0 999,543 1

Hash Left Join (cost=109,628.84..116,473.16 rows=333,039 width=46) (actual time=1,337.543..2,044.675 rows=999,543 loops=1)

  • Hash Cond: (wd.primary_demo_id = da.audience_id)
9. 224.655 1,843.879 ↓ 3.0 999,543 1

Merge Right Join (cost=109,620.96..115,573.97 rows=333,039 width=46) (actual time=1,337.451..1,843.879 rows=999,543 loops=1)

  • Merge Cond: (dt.calendar_date = wd.broadcast_week)
10. 3.134 3.134 ↑ 2.0 11,224 1

Index Scan using idx_dd_calendar_date on dim_dates dt (cost=0.29..1,382.34 rows=21,916 width=16) (actual time=0.010..3.134 rows=11,224 loops=1)

11. 104.537 1,616.090 ↓ 3.0 999,543 1

Materialize (cost=109,011.08..110,676.28 rows=333,039 width=42) (actual time=1,334.798..1,616.090 rows=999,543 loops=1)

12. 646.770 1,511.553 ↓ 3.0 999,543 1

Sort (cost=109,011.08..109,843.68 rows=333,039 width=42) (actual time=1,334.796..1,511.553 rows=999,543 loops=1)

  • Sort Key: wd.broadcast_week
  • Sort Method: external merge Disk: 52904kB
13. 864.783 864.783 ↓ 3.0 999,543 1

Seq Scan on vw_deal_and_proposals wd (cost=0.00..68,214.53 rows=333,039 width=42) (actual time=0.143..864.783 rows=999,543 loops=1)

  • Filter: ((end_date)::date >= '2016-01-01'::date)
14. 0.041 0.074 ↑ 1.0 217 1

Hash (cost=5.17..5.17 rows=217 width=8) (actual time=0.074..0.074 rows=217 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
15. 0.033 0.033 ↑ 1.0 217 1

Seq Scan on dim_audience da (cost=0.00..5.17 rows=217 width=8) (actual time=0.008..0.033 rows=217 loops=1)

16. 0.040 0.089 ↑ 1.0 343 1

Hash (cost=8.43..8.43 rows=343 width=8) (actual time=0.089..0.089 rows=343 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
17. 0.049 0.049 ↑ 1.0 343 1

Seq Scan on dim_network dn (cost=0.00..8.43 rows=343 width=8) (actual time=0.006..0.049 rows=343 loops=1)

18. 0.008 0.016 ↑ 25.2 42 1

Hash (cost=20.60..20.60 rows=1,060 width=8) (actual time=0.016..0.016 rows=42 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 18kB
19. 0.008 0.008 ↑ 25.2 42 1

Seq Scan on dim_property dp (cost=0.00..20.60 rows=1,060 width=8) (actual time=0.004..0.008 rows=42 loops=1)

20. 0.021 0.041 ↑ 1.0 137 1

Hash (cost=4.37..4.37 rows=137 width=8) (actual time=0.041..0.041 rows=137 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
21. 0.020 0.020 ↑ 1.0 137 1

Seq Scan on selling_period hsp (cost=0.00..4.37 rows=137 width=8) (actual time=0.003..0.020 rows=137 loops=1)

22.          

CTE engage_deal_line_imps

23. 1,090.395 27,835.521 ↑ 1.3 248,607 1

GroupAggregate (cost=607,415.25..624,067.20 rows=333,039 width=252) (actual time=25,882.098..27,835.521 rows=248,607 loops=1)

  • Group Key: wd_1.deal_id, wd_1.proposal_id, dn_1.network_sk, dp_1.property_sk, ((COALESCE(to_char(dt_1.broadcast_week_start_date, 'YYYYMMDD'::text), to_char((hsp_1.start_date)::timestamp with time zone, 'YYYYMMDD'::text)))::integer)
24. 7,061.500 26,745.126 ↓ 12.9 4,309,050 1

Sort (cost=607,415.25..608,247.84 rows=333,039 width=42) (actual time=25,882.080..26,745.126 rows=4,309,050 loops=1)

  • Sort Key: wd_1.deal_id, wd_1.proposal_id, dn_1.network_sk, dp_1.property_sk, ((COALESCE(to_char(dt_1.broadcast_week_start_date, 'YYYYMMDD'::text), to_char((hsp_1.start_date)::timestamp with time zone, 'YYYYMMDD'::text)))::integer)
  • Sort Method: external merge Disk: 244576kB
25. 3,716.260 19,683.626 ↓ 12.9 4,309,050 1

Hash Left Join (cost=162,111.24..566,618.69 rows=333,039 width=42) (actual time=9,110.176..19,683.626 rows=4,309,050 loops=1)

  • Hash Cond: (wd_1.selling_period_id = hsp_1.selling_period_id)
26. 881.081 15,967.325 ↓ 12.9 4,309,050 1

Hash Left Join (cost=162,105.15..557,870.33 rows=333,039 width=50) (actual time=9,110.107..15,967.325 rows=4,309,050 loops=1)

  • Hash Cond: (wd_1.property_id = dp_1.property_id)
27. 934.607 15,086.215 ↓ 12.9 4,309,050 1

Hash Left Join (cost=162,071.30..556,958.76 rows=333,039 width=50) (actual time=9,110.057..15,086.215 rows=4,309,050 loops=1)

  • Hash Cond: (wd_1.network_id = dn_1.network_id)
28. 2,566.269 14,151.477 ↓ 12.9 4,309,050 1

Merge Right Join (cost=162,058.59..556,061.02 rows=333,039 width=50) (actual time=9,109.904..14,151.477 rows=4,309,050 loops=1)

  • Merge Cond: ((sd.proposal_line_object_id = wd_1.proposal_line_object_id) AND (sd.selling_title_id = wd_1.selling_title_id) AND (sd.broadcast_week = wd_1.broadcast_week))
29. 913.834 913.834 ↑ 1.0 4,309,050 1

Index Scan using idx_pl_demo_cover on proposal_line_selling_title_week_demo sd (cost=0.56..357,513.05 rows=4,309,050 width=45) (actual time=0.021..913.834 rows=4,309,050 loops=1)

30. 290.261 10,671.374 ↓ 12.9 4,309,048 1

Materialize (cost=162,058.03..163,723.22 rows=333,039 width=71) (actual time=9,109.874..10,671.374 rows=4,309,048 loops=1)

31. 8,474.200 10,381.113 ↓ 3.0 999,543 1

Sort (cost=162,058.03..162,890.63 rows=333,039 width=71) (actual time=9,109.868..10,381.113 rows=999,543 loops=1)

  • Sort Key: wd_1.proposal_line_object_id, wd_1.selling_title_id, wd_1.broadcast_week
  • Sort Method: external merge Disk: 88088kB
32. 208.277 1,906.913 ↓ 3.0 999,543 1

Merge Right Join (cost=111,895.96..117,848.97 rows=333,039 width=71) (actual time=1,422.862..1,906.913 rows=999,543 loops=1)

  • Merge Cond: (dt_1.calendar_date = wd_1.broadcast_week)
33. 3.011 3.011 ↑ 2.0 11,224 1

Index Scan using idx_dd_calendar_date on dim_dates dt_1 (cost=0.29..1,382.34 rows=21,916 width=16) (actual time=0.009..3.011 rows=11,224 loops=1)

34. 100.714 1,695.625 ↓ 3.0 999,543 1

Materialize (cost=111,286.08..112,951.28 rows=333,039 width=63) (actual time=1,420.183..1,695.625 rows=999,543 loops=1)

35. 726.240 1,594.911 ↓ 3.0 999,543 1

Sort (cost=111,286.08..112,118.68 rows=333,039 width=63) (actual time=1,420.178..1,594.911 rows=999,543 loops=1)

  • Sort Key: wd_1.broadcast_week
  • Sort Method: external merge Disk: 76344kB
36. 868.671 868.671 ↓ 3.0 999,543 1

Seq Scan on vw_deal_and_proposals wd_1 (cost=0.00..68,214.53 rows=333,039 width=63) (actual time=0.141..868.671 rows=999,543 loops=1)

  • Filter: ((end_date)::date >= '2016-01-01'::date)
37. 0.061 0.131 ↑ 1.0 343 1

Hash (cost=8.43..8.43 rows=343 width=8) (actual time=0.131..0.131 rows=343 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
38. 0.070 0.070 ↑ 1.0 343 1

Seq Scan on dim_network dn_1 (cost=0.00..8.43 rows=343 width=8) (actual time=0.018..0.070 rows=343 loops=1)

39. 0.015 0.029 ↑ 25.2 42 1

Hash (cost=20.60..20.60 rows=1,060 width=8) (actual time=0.029..0.029 rows=42 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 18kB
40. 0.014 0.014 ↑ 25.2 42 1

Seq Scan on dim_property dp_1 (cost=0.00..20.60 rows=1,060 width=8) (actual time=0.007..0.014 rows=42 loops=1)

41. 0.018 0.041 ↑ 1.0 137 1

Hash (cost=4.37..4.37 rows=137 width=8) (actual time=0.041..0.041 rows=137 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
42. 0.023 0.023 ↑ 1.0 137 1

Seq Scan on selling_period hsp_1 (cost=0.00..4.37 rows=137 width=8) (actual time=0.004..0.023 rows=137 loops=1)

43.          

CTE engage_deal_data

44. 85.781 2,784.391 ↑ 98.5 3,381 1

Unique (cost=223,250.31..241,567.45 rows=333,039 width=316) (actual time=2,570.531..2,784.391 rows=3,381 loops=1)

45. 1,329.591 2,698.610 ↑ 2.1 161,745 1

Sort (cost=223,250.31..224,082.91 rows=333,039 width=316) (actual time=2,570.528..2,698.610 rows=161,745 loops=1)

  • Sort Key: ((pd.deal_id)::character varying(100)), pd.is_equivalized, pd.proposal_number, pd.proposal_id, pd.proposal_status_code, ((to_char((pd.start_date)::timestamp without time zone, 'YYYYMMDD'::text))::integer), ((to_char((pd.end_date)::timestamp without time zone, 'YYYYMMDD'::text))::integer), pd.advertiser_id, pd.agency_id, pa.agency_id, pd.brand_id, pd.calendar_type_code, pd.division_id, pd.network_id, pd.property_id, pd.ratecard_type_id, pd.primary_ae_user_id, pd.sales_planner_user_id, pd.team_id, pd.primary_demo_id, pd.product_conflict_id
  • Sort Method: external merge Disk: 19128kB
46. 384.224 1,369.019 ↑ 2.1 161,745 1

Hash Join (cost=1,607.30..94,799.76 rows=333,039 width=316) (actual time=23.250..1,369.019 rows=161,745 loops=1)

  • Hash Cond: ((pd.proposal_status_code)::text = (ps.proposal_status_code)::text)
47. 57.339 984.776 ↑ 2.1 161,745 1

Hash Left Join (cost=1,606.06..79,395.45 rows=333,039 width=130) (actual time=23.190..984.776 rows=161,745 loops=1)

  • Hash Cond: (pd.agency_id = ha.agency_id)
48. 914.394 914.394 ↑ 2.1 161,745 1

Seq Scan on vw_deal_and_proposals pd (cost=0.00..73,210.11 rows=333,039 width=126) (actual time=10.008..914.394 rows=161,745 loops=1)

  • Filter: ((proposal_modified_utc)::timestamp without time zone > (CURRENT_DATE - '180 days'::interval))
  • Rows Removed by Filter: 837798
49. 2.605 13.043 ↑ 1.0 17,424 1

Hash (cost=1,388.26..1,388.26 rows=17,424 width=8) (actual time=13.043..13.043 rows=17,424 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 937kB
50. 5.088 10.438 ↑ 1.0 17,424 1

Hash Left Join (cost=679.45..1,388.26 rows=17,424 width=8) (actual time=4.349..10.438 rows=17,424 loops=1)

  • Hash Cond: (ha.parent_id = pa.agency_id)
51. 1.231 1.231 ↑ 1.0 17,424 1

Seq Scan on agency ha (cost=0.00..469.24 rows=17,424 width=8) (actual time=0.097..1.231 rows=17,424 loops=1)

52. 2.199 4.119 ↑ 1.0 17,424 1

Hash (cost=461.65..461.65 rows=17,424 width=4) (actual time=4.119..4.119 rows=17,424 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 869kB
53. 1.920 1.920 ↑ 1.0 17,424 1

Index Only Scan using idx_stage_harbor_agency_agency_id on agency pa (cost=0.29..461.65 rows=17,424 width=4) (actual time=0.036..1.920 rows=17,424 loops=1)

  • Heap Fetches: 0
54. 0.006 0.019 ↑ 1.0 11 1

Hash (cost=1.11..1.11 rows=11 width=7) (actual time=0.019..0.019 rows=11 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
55. 0.013 0.013 ↑ 1.0 11 1

Seq Scan on proposal_status ps (cost=0.00..1.11 rows=11 width=7) (actual time=0.010..0.013 rows=11 loops=1)

56.          

CTE engage_deal_keys

57. 99.476 3,927.160 ↑ 98.5 3,381 1

Hash Left Join (cost=35,120.07..271,383.26 rows=333,039 width=372) (actual time=3,567.356..3,927.160 rows=3,381 loops=1)

  • Hash Cond: (dd.product_conflict_id = pc.product_conflict_id)
58.          

Initplan (forHash Left Join)

59. 928.093 928.093 ↑ 432.0 1 1

Seq Scan on dim_time (cost=0.00..25,211.28 rows=432 width=4) (actual time=474.140..928.093 rows=1 loops=1)

  • Filter: ((((fn_get_correct_time())::time without time zone)::character varying(8))::text = (COALESCE((time_element)::character varying, military_time_display))::text)
  • Rows Removed by Filter: 86400
60. 1.634 2,899.479 ↑ 98.5 3,381 1

Hash Left Join (cost=9,894.41..75,424.59 rows=333,039 width=354) (actual time=2,638.621..2,899.479 rows=3,381 loops=1)

  • Hash Cond: (dd.primary_audience_id = da_1.audience_id)
61. 1.652 2,897.782 ↑ 98.5 3,381 1

Hash Left Join (cost=9,886.53..74,525.41 rows=333,039 width=354) (actual time=2,638.546..2,897.782 rows=3,381 loops=1)

  • Hash Cond: (dd.team_id = dt_2.team_id)
62. 1.878 2,896.056 ↑ 98.5 3,381 1

Hash Left Join (cost=9,876.50..73,627.32 rows=333,039 width=354) (actual time=2,638.456..2,896.056 rows=3,381 loops=1)

  • Hash Cond: (dd.sales_planner_user_id = spu.user_id)
63. 1.894 2,892.444 ↑ 98.5 3,381 1

Hash Left Join (cost=9,586.71..72,462.76 rows=333,039 width=354) (actual time=2,636.676..2,892.444 rows=3,381 loops=1)

  • Hash Cond: (dd.ae_user_id = dau.user_id)
64. 1.706 2,888.672 ↑ 98.5 3,381 1

Hash Left Join (cost=9,296.93..71,298.21 rows=333,039 width=354) (actual time=2,634.753..2,888.672 rows=3,381 loops=1)

  • Hash Cond: (dd.rate_card_type_id = rct.rate_card_type_id)
65. 1.833 2,886.953 ↑ 98.5 3,381 1

Hash Left Join (cost=9,280.63..70,394.45 rows=333,039 width=354) (actual time=2,634.728..2,886.953 rows=3,381 loops=1)

  • Hash Cond: (dd.property_id = pt.property_id)
66. 1.766 2,885.104 ↑ 98.5 3,381 1

Hash Left Join (cost=9,246.78..69,482.88 rows=333,039 width=354) (actual time=2,634.695..2,885.104 rows=3,381 loops=1)

  • Hash Cond: (dd.network_id = dn_2.network_id)
67. 1.657 2,883.241 ↑ 98.5 3,381 1

Hash Left Join (cost=9,234.06..68,585.13 rows=333,039 width=354) (actual time=2,634.589..2,883.241 rows=3,381 loops=1)

  • Hash Cond: (dd.division_id = dv.division_id)
68. 4.075 2,881.572 ↑ 98.5 3,381 1

Hash Left Join (cost=9,199.08..67,672.59 rows=333,039 width=354) (actual time=2,634.554..2,881.572 rows=3,381 loops=1)

  • Hash Cond: (lower(btrim(replace((dd.calendar_type_code)::text, ' '::text, ''::text))) = (ct.calendar_type_code)::text)
69. 2.202 2,877.487 ↑ 98.5 3,381 1

Hash Left Join (cost=9,162.08..61,807.41 rows=333,039 width=408) (actual time=2,634.508..2,877.487 rows=3,381 loops=1)

  • Hash Cond: (dd.posting_agency_id = pst.agency_id)
70. 2.025 2,870.634 ↑ 98.5 3,381 1

Hash Left Join (cost=8,568.02..60,338.91 rows=333,039 width=408) (actual time=2,629.706..2,870.634 rows=3,381 loops=1)

  • Hash Cond: (dd.parent_agency_id = pa_1.agency_id)
71. 1.982 2,863.981 ↑ 98.5 3,381 1

Hash Left Join (cost=7,973.96..58,870.41 rows=333,039 width=408) (actual time=2,624.914..2,863.981 rows=3,381 loops=1)

  • Hash Cond: (dd.agency_id = a.agency_id)
72. 2.555 2,857.283 ↑ 98.5 3,381 1

Hash Left Join (cost=7,379.89..57,401.91 rows=333,039 width=408) (actual time=2,620.050..2,857.283 rows=3,381 loops=1)

  • Hash Cond: (dd.advertiser_id = ad.advertiser_id)
73. 3.070 2,846.848 ↑ 98.5 3,381 1

Hash Left Join (cost=6,410.71..55,558.36 rows=333,039 width=408) (actual time=2,612.018..2,846.848 rows=3,381 loops=1)

  • Hash Cond: ((dd.deal_id)::text = (dl_1.source_deal_id)::text)
74. 10.162 2,836.448 ↑ 98.5 3,381 1

Hash Left Join (cost=4,136.49..52,409.71 rows=333,039 width=400) (actual time=2,604.628..2,836.448 rows=3,381 loops=1)

  • Hash Cond: (dd.brand_id = b.brand_id)
75. 7.368 2,793.031 ↑ 98.5 3,381 1

Hash Left Join (cost=37.00..12,525.96 rows=333,039 width=400) (actual time=2,570.596..2,793.031 rows=3,381 loops=1)

  • Hash Cond: (lower(btrim(replace((dd.proposal_status_code)::text, ' '::text, ''::text))) = (ps_1.proposal_status_code)::text)
76. 2,785.640 2,785.640 ↑ 98.5 3,381 1

CTE Scan on engage_deal_data dd (cost=0.00..6,660.78 rows=333,039 width=454) (actual time=2,570.534..2,785.640 rows=3,381 loops=1)

77. 0.008 0.023 ↑ 100.0 12 1

Hash (cost=22.00..22.00 rows=1,200 width=11) (actual time=0.023..0.023 rows=12 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
78. 0.015 0.015 ↑ 100.0 12 1

Seq Scan on dim_proposal_status ps_1 (cost=0.00..22.00 rows=1,200 width=11) (actual time=0.013..0.015 rows=12 loops=1)

79. 18.394 33.255 ↑ 1.0 110,955 1

Hash (cost=2,278.55..2,278.55 rows=110,955 width=8) (actual time=33.255..33.255 rows=110,955 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 3189kB
80. 14.861 14.861 ↑ 1.0 110,955 1

Seq Scan on dim_brand b (cost=0.00..2,278.55 rows=110,955 width=8) (actual time=0.112..14.861 rows=110,955 loops=1)

81. 2.965 7.330 ↑ 1.0 18,321 1

Hash (cost=2,045.21..2,045.21 rows=18,321 width=20) (actual time=7.330..7.330 rows=18,321 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1192kB
82. 4.365 4.365 ↑ 1.0 18,321 1

Seq Scan on dim_deal dl_1 (cost=0.00..2,045.21 rows=18,321 width=20) (actual time=0.122..4.365 rows=18,321 loops=1)

83. 4.184 7.880 ↑ 1.0 28,586 1

Hash (cost=611.86..611.86 rows=28,586 width=8) (actual time=7.880..7.880 rows=28,586 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1373kB
84. 3.696 3.696 ↑ 1.0 28,586 1

Seq Scan on dim_advertiser ad (cost=0.00..611.86 rows=28,586 width=8) (actual time=0.106..3.696 rows=28,586 loops=1)

85. 2.526 4.716 ↑ 1.0 17,425 1

Hash (cost=376.25..376.25 rows=17,425 width=8) (actual time=4.716..4.716 rows=17,425 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 937kB
86. 2.190 2.190 ↑ 1.0 17,425 1

Seq Scan on dim_agency a (cost=0.00..376.25 rows=17,425 width=8) (actual time=0.006..2.190 rows=17,425 loops=1)

87. 2.572 4.628 ↑ 1.0 17,425 1

Hash (cost=376.25..376.25 rows=17,425 width=8) (actual time=4.628..4.628 rows=17,425 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 937kB
88. 2.056 2.056 ↑ 1.0 17,425 1

Seq Scan on dim_agency pa_1 (cost=0.00..376.25 rows=17,425 width=8) (actual time=0.004..2.056 rows=17,425 loops=1)

89. 2.540 4.651 ↑ 1.0 17,425 1

Hash (cost=376.25..376.25 rows=17,425 width=8) (actual time=4.651..4.651 rows=17,425 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 937kB
90. 2.111 2.111 ↑ 1.0 17,425 1

Seq Scan on dim_agency pst (cost=0.00..376.25 rows=17,425 width=8) (actual time=0.006..2.111 rows=17,425 loops=1)

91. 0.006 0.010 ↑ 400.0 3 1

Hash (cost=22.00..22.00 rows=1,200 width=12) (actual time=0.009..0.010 rows=3 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
92. 0.004 0.004 ↑ 400.0 3 1

Seq Scan on dim_calendar_type ct (cost=0.00..22.00 rows=1,200 width=12) (actual time=0.004..0.004 rows=3 loops=1)

93. 0.006 0.012 ↑ 58.4 19 1

Hash (cost=21.10..21.10 rows=1,110 width=8) (actual time=0.012..0.012 rows=19 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
94. 0.006 0.006 ↑ 58.4 19 1

Seq Scan on dim_division dv (cost=0.00..21.10 rows=1,110 width=8) (actual time=0.004..0.006 rows=19 loops=1)

95. 0.048 0.097 ↑ 1.0 343 1

Hash (cost=8.43..8.43 rows=343 width=8) (actual time=0.097..0.097 rows=343 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
96. 0.049 0.049 ↑ 1.0 343 1

Seq Scan on dim_network dn_2 (cost=0.00..8.43 rows=343 width=8) (actual time=0.008..0.049 rows=343 loops=1)

97. 0.008 0.016 ↑ 25.2 42 1

Hash (cost=20.60..20.60 rows=1,060 width=8) (actual time=0.016..0.016 rows=42 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 18kB
98. 0.008 0.008 ↑ 25.2 42 1

Seq Scan on dim_property pt (cost=0.00..20.60 rows=1,060 width=8) (actual time=0.004..0.008 rows=42 loops=1)

99. 0.006 0.013 ↑ 11.2 25 1

Hash (cost=12.80..12.80 rows=280 width=8) (actual time=0.013..0.013 rows=25 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
100. 0.007 0.007 ↑ 11.2 25 1

Seq Scan on dim_rate_card_type rct (cost=0.00..12.80 rows=280 width=8) (actual time=0.004..0.007 rows=25 loops=1)

101. 0.933 1.878 ↓ 1.0 6,747 1

Hash (cost=205.46..205.46 rows=6,746 width=8) (actual time=1.878..1.878 rows=6,747 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 328kB
102. 0.945 0.945 ↓ 1.0 6,747 1

Seq Scan on dim_user dau (cost=0.00..205.46 rows=6,746 width=8) (actual time=0.004..0.945 rows=6,747 loops=1)

103. 0.957 1.734 ↓ 1.0 6,747 1

Hash (cost=205.46..205.46 rows=6,746 width=8) (actual time=1.734..1.734 rows=6,747 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 328kB
104. 0.777 0.777 ↓ 1.0 6,747 1

Seq Scan on dim_user spu (cost=0.00..205.46 rows=6,746 width=8) (actual time=0.002..0.777 rows=6,747 loops=1)

105. 0.037 0.074 ↑ 1.0 268 1

Hash (cost=6.68..6.68 rows=268 width=8) (actual time=0.074..0.074 rows=268 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
106. 0.037 0.037 ↑ 1.0 268 1

Seq Scan on dim_team dt_2 (cost=0.00..6.68 rows=268 width=8) (actual time=0.006..0.037 rows=268 loops=1)

107. 0.033 0.063 ↑ 1.0 217 1

Hash (cost=5.17..5.17 rows=217 width=8) (actual time=0.063..0.063 rows=217 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
108. 0.030 0.030 ↑ 1.0 217 1

Seq Scan on dim_audience da_1 (cost=0.00..5.17 rows=217 width=8) (actual time=0.006..0.030 rows=217 loops=1)

109. 0.057 0.112 ↑ 1.0 417 1

Hash (cost=9.17..9.17 rows=417 width=8) (actual time=0.112..0.112 rows=417 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
110. 0.055 0.055 ↑ 1.0 417 1

Seq Scan on dim_product_conflict pc (cost=0.00..9.17 rows=417 width=8) (actual time=0.006..0.055 rows=417 loops=1)

111. 203,198.511 243,461.132 ↓ 40,568.0 40,568 1

Nested Loop (cost=252,008.67..275,321.41 rows=1 width=198) (actual time=52,231.530..243,461.132 rows=40,568 loops=1)

  • Join Filter: (((dl.deal_id)::text = (dk.source_deal_id)::text) AND (dl.network_sk = dk.network_sk) AND (dl.property_sk = dk.property_sk))
  • Rows Removed by Join Filter: 840499699
112. 293.840 40,262.621 ↓ 248,607.0 248,607 1

Merge Join (cost=252,008.67..262,832.45 rows=1 width=472) (actual time=39,805.862..40,262.621 rows=248,607 loops=1)

  • Merge Cond: (((dl.deal_id)::text = (dli.deal_id)::text) AND (dl.network_sk = dli.network_sk) AND (dl.property_sk = dli.property_sk) AND ((dl.proposal_id)::text = (dli.proposal_id)::text) AND (dl.broadcast_week_start_date_sk = dli.broadcast_week_start_date_sk))
113. 1,677.806 10,439.215 ↑ 1.3 248,607 1

Sort (cost=126,004.33..126,836.93 rows=333,039 width=288) (actual time=10,385.124..10,439.215 rows=248,607 loops=1)

  • Sort Key: dl.deal_id, dl.network_sk, dl.property_sk, dl.proposal_id, dl.broadcast_week_start_date_sk
  • Sort Method: external sort Disk: 13136kB
114. 8,761.409 8,761.409 ↑ 1.3 248,607 1

CTE Scan on engage_deal_line_dollars dl (cost=0.00..6,660.78 rows=333,039 width=288) (actual time=4,234.251..8,761.409 rows=248,607 loops=1)

115. 47.353 29,529.566 ↑ 1.3 248,607 1

Materialize (cost=126,004.33..127,669.53 rows=333,039 width=288) (actual time=29,420.727..29,529.566 rows=248,607 loops=1)

116. 29,482.213 29,482.213 ↑ 1.3 248,607 1

Sort (cost=126,004.33..126,836.93 rows=333,039 width=288) (actual time=29,420.718..29,482.213 rows=248,607 loops=1)

  • Sort Key: dli.deal_id, dli.network_sk, dli.property_sk, dli.proposal_id, dli.broadcast_week_start_date_sk