explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ubgp

Settings
# exclusive inclusive rows x rows loops node
1. 0.433 1,388.735 ↓ 0.0 0 1

Insert on fact_spot (cost=5.89..25,980.05 rows=5 width=712) (actual time=1,388.735..1,388.735 rows=0 loops=1)

2. 0.128 1,388.302 ↑ 1.0 5 1

Subquery Scan on *SELECT* (cost=5.89..25,980.05 rows=5 width=712) (actual time=67.218..1,388.302 rows=5 loops=1)

3. 0.020 1,388.174 ↑ 1.0 5 1

Limit (cost=5.89..25,979.53 rows=5 width=554) (actual time=67.183..1,388.174 rows=5 loops=1)

4. 0.007 1,388.154 ↑ 1,449,946.0 5 1

Subquery Scan on fn (cost=5.89..37,660,361,267.80 rows=7,249,730 width=554) (actual time=67.182..1,388.154 rows=5 loops=1)

5. 0.079 1,388.147 ↑ 1,449,946.0 5 1

Nested Loop Left Join (cost=5.89..37,660,270,646.17 rows=7,249,730 width=602) (actual time=67.180..1,388.147 rows=5 loops=1)

6. 0.018 1,388.043 ↑ 1,449,946.0 5 1

Nested Loop Left Join (cost=5.60..37,653,123,770.12 rows=7,249,730 width=492) (actual time=67.153..1,388.043 rows=5 loops=1)

7. 0.017 1,382.765 ↑ 1,449,946.0 5 1

Nested Loop Left Join (cost=5.31..37,650,797,964.52 rows=7,249,730 width=496) (actual time=67.134..1,382.765 rows=5 loops=1)

8. 0.014 1,382.708 ↑ 1,449,946.0 5 1

Merge Left Join (cost=4.87..37,646,963,992.40 rows=7,249,730 width=492) (actual time=67.117..1,382.708 rows=5 loops=1)

  • Merge Cond: (bs.source_spot_id = aired_spot_delivery.usn)
9. 1.060 1,382.626 ↑ 1,449,946.0 5 1

Nested Loop Left Join (cost=4.44..37,646,516,845.02 rows=7,249,730 width=484) (actual time=67.072..1,382.626 rows=5 loops=1)

  • Join Filter: (bs.source_spot_id = ssi.admiral_spot_id)
  • Rows Removed by Join Filter: 15630
10. 0.010 1,380.196 ↑ 1,449,946.0 5 1

Merge Left Join (cost=4.44..37,306,576,936.24 rows=7,249,730 width=472) (actual time=66.048..1,380.196 rows=5 loops=1)

  • Merge Cond: (bs.source_spot_id = sta.source_spot_id)
11. 0.144 1,380.141 ↑ 1,449,946.0 5 1

Nested Loop Left Join (cost=4.02..37,306,533,304.22 rows=7,249,730 width=353) (actual time=66.000..1,380.141 rows=5 loops=1)

  • Join Filter: (bs.secondary_product_conflict_id = dmpc.product_conflict_id)
  • Rows Removed by Join Filter: 2095
12. 60.599 1,379.822 ↑ 1,449,946.0 5 1

Nested Loop Left Join (cost=4.02..37,260,968,222.67 rows=7,249,730 width=353) (actual time=65.875..1,379.822 rows=5 loops=1)

  • Join Filter: (bs.parent_order_id = prnt.order_id)
  • Rows Removed by Join Filter: 909455
13. 0.019 1,206.173 ↑ 1,449,946.0 5 1

Nested Loop Left Join (cost=4.02..11,036,044,905.58 rows=7,249,730 width=345) (actual time=0.471..1,206.173 rows=5 loops=1)

14. 0.025 1,206.119 ↑ 1,449,946.0 5 1

Nested Loop Left Join (cost=3.73..11,033,813,360.67 rows=7,249,730 width=345) (actual time=0.454..1,206.119 rows=5 loops=1)

15. 0.098 1,205.269 ↑ 1,449,946.0 5 1

Nested Loop Left Join (cost=3.30..11,030,457,109.19 rows=7,249,730 width=351) (actual time=0.434..1,205.269 rows=5 loops=1)

  • Join Filter: ((CASE WHEN ((bs.audience_cd)::text = 'NG'::text) THEN 'No Audience Name'::character varying ELSE bs.audience_cd END)::text = (dmda.audience)::text)
  • Rows Removed by Join Filter: 886
16. 0.085 1,205.071 ↑ 1,449,946.0 5 1

Nested Loop Left Join (cost=3.30..11,003,306,865.67 rows=7,249,730 width=347) (actual time=0.416..1,205.071 rows=5 loops=1)

  • Join Filter: (bs.team_id = dmtm.team_id)
  • Rows Removed by Join Filter: 1120
17. 0.015 1,204.876 ↑ 1,449,946.0 5 1

Nested Loop Left Join (cost=3.30..10,974,488,344.74 rows=7,249,730 width=347) (actual time=0.340..1,204.876 rows=5 loops=1)

18. 0.019 1,204.836 ↑ 1,449,946.0 5 1

Nested Loop Left Join (cost=3.02..10,972,312,800.34 rows=7,249,730 width=347) (actual time=0.327..1,204.836 rows=5 loops=1)

19. 0.016 1,204.787 ↑ 1,449,946.0 5 1

Nested Loop Left Join (cost=2.74..10,970,137,255.94 rows=7,249,730 width=347) (actual time=0.312..1,204.787 rows=5 loops=1)

  • Join Filter: ((bs.broadcast_week_date >= sp.start_date) AND (bs.broadcast_week_date <= sp.end_date))
  • Rows Removed by Join Filter: 2
20. 0.016 1,202.516 ↑ 1,449,946.0 5 1

Nested Loop Left Join (cost=2.30..10,963,116,106.54 rows=7,249,730 width=349) (actual time=0.286..1,202.516 rows=5 loops=1)

21. 0.006 1,201.185 ↑ 1,449,946.0 5 1

Nested Loop Left Join (cost=2.01..10,960,884,101.57 rows=7,249,730 width=349) (actual time=0.272..1,201.185 rows=5 loops=1)

  • Join Filter: (bs.order_revenue_type_id = dmrt.revenue_type_id)
22. 0.007 1,201.174 ↑ 1,449,946.0 5 1

Nested Loop Left Join (cost=2.01..10,848,984,260.94 rows=7,249,730 width=349) (actual time=0.268..1,201.174 rows=5 loops=1)

  • Join Filter: (bs.spot_revenue_type_id = dmspt.spot_revenue_type_id)
23. 0.013 1,201.162 ↑ 1,449,946.0 5 1

Nested Loop Left Join (cost=2.01..10,722,947,479.73 rows=7,249,730 width=349) (actual time=0.264..1,201.162 rows=5 loops=1)

  • Join Filter: ((bs.spot_status_cd)::text = (dsst.spot_status_cd)::text)
  • Rows Removed by Join Filter: 64
24. 0.008 1,201.134 ↑ 1,449,946.0 5 1

Nested Loop Left Join (cost=2.01..10,596,802,153.23 rows=7,249,730 width=345) (actual time=0.256..1,201.134 rows=5 loops=1)

  • Join Filter: (bs.rate_card_type_id = dmrct.rate_card_type_id)
25. 0.012 1,201.121 ↑ 1,449,946.0 5 1

Nested Loop Left Join (cost=2.01..10,574,073,207.78 rows=7,249,730 width=336) (actual time=0.251..1,201.121 rows=5 loops=1)

  • Join Filter: (bs.property_id = dmdp.property_id)
  • Rows Removed by Join Filter: 70
26. 0.113 1,201.099 ↑ 1,449,946.0 5 1

Nested Loop Left Join (cost=2.01..10,461,085,935.45 rows=7,249,730 width=336) (actual time=0.244..1,201.099 rows=5 loops=1)

  • Join Filter: ((bs.network_cd)::text = (dmdn.network_cd)::text)
  • Rows Removed by Join Filter: 1409
27. 0.038 1,200.826 ↑ 1,449,946.0 5 1

Nested Loop Left Join (cost=2.01..10,423,894,811.27 rows=7,249,730 width=332) (actual time=0.223..1,200.826 rows=5 loops=1)

  • Join Filter: (btrim(replace((bs.division_cd)::text, ' '::text, ''::text)) = (dmdv.division)::text)
  • Rows Removed by Join Filter: 51
28. 0.017 1,200.768 ↑ 1,449,946.0 5 1

Nested Loop Left Join (cost=2.01..10,264,400,727.52 rows=7,249,730 width=332) (actual time=0.211..1,200.768 rows=5 loops=1)

29. 0.017 1,200.691 ↑ 1,449,946.0 5 1

Nested Loop Left Join (cost=1.72..10,262,179,930.59 rows=7,249,730 width=324) (actual time=0.191..1,200.691 rows=5 loops=1)

30. 0.020 1,200.639 ↑ 1,449,946.0 5 1

Nested Loop Left Join (cost=1.43..10,259,926,511.47 rows=7,249,730 width=324) (actual time=0.175..1,200.639 rows=5 loops=1)

31. 0.017 1,200.594 ↑ 1,449,946.0 5 1

Nested Loop Left Join (cost=1.14..10,257,714,331.07 rows=7,249,730 width=324) (actual time=0.165..1,200.594 rows=5 loops=1)

32. 0.008 1,200.542 ↑ 1,449,946.0 5 1

Nested Loop Left Join (cost=0.85..10,255,501,527.86 rows=7,249,730 width=324) (actual time=0.147..1,200.542 rows=5 loops=1)

  • Join Filter: (bs.order_type_id = dot.order_type_id)
  • Rows Removed by Join Filter: 12
33. 0.012 1,200.524 ↑ 1,449,946.0 5 1

Nested Loop Left Join (cost=0.85..10,135,880,959.11 rows=7,249,730 width=318) (actual time=0.143..1,200.524 rows=5 loops=1)

  • Join Filter: (bs.order_status_id = dos.order_status_id)
  • Rows Removed by Join Filter: 26
34. 0.021 1,200.497 ↑ 1,449,946.0 5 1

Nested Loop Left Join (cost=0.85..10,009,735,632.61 rows=7,249,730 width=318) (actual time=0.132..1,200.497 rows=5 loops=1)

35. 0.061 0.061 ↑ 1,449,946.0 5 1

Index Scan using idx_blnd_src_spot_id on blended_spots bs (cost=0.43..1,521,020.59 rows=7,249,730 width=314) (actual time=0.028..0.061 rows=5 loops=1)

  • Filter: ((source_spot_id_desc = ANY ('{"usn - aired spot","usn - booked spot"}'::text[])) OR (additional_admiral_spot = 1) OR (is_manual_adjustment = 1))
36. 1,200.415 1,200.415 ↑ 1.0 1 5

Index Scan using dim_order_order_source_source_order_id_key on dim_order dmo (cost=0.42..1,380.48 rows=1 width=16) (actual time=0.080..240.083 rows=1 loops=5)

  • Index Cond: (bs.order_id = source_order_id)
37. 0.009 0.015 ↑ 193.3 6 5

Materialize (cost=0.00..27.40 rows=1,160 width=8) (actual time=0.002..0.003 rows=6 loops=5)

38. 0.006 0.006 ↑ 165.7 7 1

Seq Scan on dim_order_status dos (cost=0.00..21.60 rows=1,160 width=8) (actual time=0.005..0.006 rows=7 loops=1)

39. 0.005 0.010 ↑ 366.7 3 5

Materialize (cost=0.00..26.50 rows=1,100 width=12) (actual time=0.001..0.002 rows=3 loops=5)

40. 0.005 0.005 ↑ 275.0 4 1

Seq Scan on dim_order_type dot (cost=0.00..21.00 rows=1,100 width=12) (actual time=0.003..0.005 rows=4 loops=1)

41. 0.035 0.035 ↑ 1.0 1 5

Index Scan using dim_advertiser_advertiser_id_key on dim_advertiser dmad (cost=0.29..0.31 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=5)

  • Index Cond: (bs.advertiser_id = advertiser_id)
42. 0.025 0.025 ↑ 1.0 1 5

Index Scan using dim_agency_agency_id_key on dim_agency dmag (cost=0.29..0.31 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=5)

  • Index Cond: (bs.agency_id = agency_id)
43. 0.035 0.035 ↑ 1.0 1 5

Index Scan using dim_brand_brand_id_key on dim_brand dmbd (cost=0.29..0.31 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=5)

  • Index Cond: (bs.brand_id = brand_id)
44. 0.060 0.060 ↑ 1.0 1 5

Index Scan using idx_dl_dlid on dim_deal dmdd (cost=0.29..0.31 rows=1 width=19) (actual time=0.012..0.012 rows=1 loops=5)

  • Index Cond: ((COALESCE(bs.deal_cd, bs.lh_deal_cd))::text = (source_deal_id)::text)
45. 0.012 0.020 ↑ 100.0 11 5

Materialize (cost=0.00..26.50 rows=1,100 width=17) (actual time=0.001..0.004 rows=11 loops=5)

46. 0.008 0.008 ↑ 84.6 13 1

Seq Scan on dim_division dmdv (cost=0.00..21.00 rows=1,100 width=17) (actual time=0.005..0.008 rows=13 loops=1)

47. 0.111 0.160 ↑ 1.2 283 5

Materialize (cost=0.00..10.13 rows=342 width=14) (actual time=0.001..0.032 rows=283 loops=5)

48. 0.049 0.049 ↑ 1.0 342 1

Seq Scan on dim_network dmdn (cost=0.00..8.42 rows=342 width=14) (actual time=0.004..0.049 rows=342 loops=1)

49. 0.007 0.010 ↑ 69.3 15 5

Materialize (cost=0.00..25.60 rows=1,040 width=8) (actual time=0.001..0.002 rows=15 loops=5)

50. 0.003 0.003 ↑ 69.3 15 1

Seq Scan on dim_property dmdp (cost=0.00..20.40 rows=1,040 width=8) (actual time=0.002..0.003 rows=15 loops=1)

51. 0.003 0.005 ↑ 210.0 1 5

Materialize (cost=0.00..13.15 rows=210 width=13) (actual time=0.001..0.001 rows=1 loops=5)

52. 0.002 0.002 ↑ 210.0 1 1

Seq Scan on dim_rate_card_type dmrct (cost=0.00..12.10 rows=210 width=13) (actual time=0.002..0.002 rows=1 loops=1)

53. 0.009 0.015 ↑ 82.9 14 5

Materialize (cost=0.00..27.40 rows=1,160 width=7) (actual time=0.001..0.003 rows=14 loops=5)

54. 0.006 0.006 ↑ 82.9 14 1

Seq Scan on dim_spot_status dsst (cost=0.00..21.60 rows=1,160 width=7) (actual time=0.004..0.006 rows=14 loops=1)

55. 0.003 0.005 ↑ 1,160.0 1 5

Materialize (cost=0.00..27.40 rows=1,160 width=8) (actual time=0.001..0.001 rows=1 loops=5)

56. 0.002 0.002 ↑ 1,160.0 1 1

Seq Scan on dim_spot_revenue_type dmspt (cost=0.00..21.60 rows=1,160 width=8) (actual time=0.001..0.002 rows=1 loops=1)

57. 0.003 0.005 ↑ 1,030.0 1 5

Materialize (cost=0.00..25.45 rows=1,030 width=8) (actual time=0.001..0.001 rows=1 loops=5)

58. 0.002 0.002 ↑ 1,030.0 1 1

Seq Scan on dim_revenue_type dmrt (cost=0.00..20.30 rows=1,030 width=8) (actual time=0.002..0.002 rows=1 loops=1)

59. 1.315 1.315 ↑ 1.0 1 5

Index Scan using dim_selling_title_selling_title_id_key on dim_selling_title dmst (cost=0.29..0.31 rows=1 width=8) (actual time=0.263..0.263 rows=1 loops=5)

  • Index Cond: (bs.selling_title_id = selling_title_id)
60. 0.027 2.255 ↑ 3.0 1 5

Nested Loop (cost=0.44..0.92 rows=3 width=18) (actual time=0.448..0.451 rows=1 loops=5)

61. 2.200 2.200 ↑ 3.0 1 5

Index Scan using dim_selling_title_selling_per_selling_title_sk_selling_peri_key on dim_selling_title_selling_period stsp1 (cost=0.29..0.44 rows=3 width=14) (actual time=0.440..0.440 rows=1 loops=5)

  • Index Cond: (dmst.selling_title_sk = selling_title_sk)
62. 0.028 0.028 ↑ 1.0 1 7

Index Scan using dim_selling_period_pkey on dim_selling_period sp (cost=0.14..0.16 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=7)

  • Index Cond: (selling_period_sk = stsp1.selling_period_sk)
63. 0.030 0.030 ↑ 1.0 1 5

Index Scan using dim_user_user_id_key on dim_user dmae (cost=0.28..0.30 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=5)

  • Index Cond: (bs.ae_user_id = user_id)
64. 0.025 0.025 ↑ 1.0 1 5

Index Scan using dim_user_user_id_key on dim_user dmasr (cost=0.28..0.30 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=5)

  • Index Cond: (bs.asr_user_id = user_id)
65. 0.080 0.110 ↑ 1.2 225 5

Materialize (cost=0.00..7.99 rows=266 width=8) (actual time=0.001..0.022 rows=225 loops=5)

66. 0.030 0.030 ↑ 1.2 225 1

Seq Scan on dim_team dmtm (cost=0.00..6.66 rows=266 width=8) (actual time=0.004..0.030 rows=225 loops=1)

67. 0.067 0.100 ↑ 1.2 178 5

Materialize (cost=0.00..5.21 rows=214 width=10) (actual time=0.001..0.020 rows=178 loops=5)

68. 0.033 0.033 ↑ 1.0 214 1

Seq Scan on dim_audience dmda (cost=0.00..4.14 rows=214 width=10) (actual time=0.003..0.033 rows=214 loops=1)

69. 0.825 0.825 ↑ 1.0 1 5

Index Scan using idx_blnd_genesis_join on genesis_avails ga (cost=0.42..0.45 rows=1 width=15) (actual time=0.165..0.165 rows=1 loops=5)

  • Index Cond: ((bs.rate_card_type_id = lighthouse_rate_card_type_id) AND (bs.broadcast_week_date_sk = broadcast_date_sk) AND ((bs.network_cd)::text = (network_cd)::text))
70. 0.035 0.035 ↑ 1.0 1 5

Index Scan using dim_sales_split_sales_split_id_key on dim_sales_split dssp (cost=0.29..0.31 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=5)

  • Index Cond: (bs.sales_split_id = sales_split_id)
71. 89.266 113.050 ↑ 1.0 181,891 5

Materialize (cost=0.00..5,099.36 rows=181,891 width=12) (actual time=0.011..22.610 rows=181,891 loops=5)

72. 23.784 23.784 ↑ 1.0 181,891 1

Seq Scan on order_info prnt (cost=0.00..3,300.91 rows=181,891 width=12) (actual time=0.004..23.784 rows=181,891 loops=1)

73. 0.123 0.175 ↑ 1.0 420 5

Materialize (cost=0.00..11.30 rows=420 width=8) (actual time=0.002..0.035 rows=420 loops=5)

74. 0.052 0.052 ↑ 1.0 420 1

Seq Scan on dim_product_conflict dmpc (cost=0.00..9.20 rows=420 width=8) (actual time=0.005..0.052 rows=420 loops=1)

75. 0.045 0.045 ↑ 441,592.0 1 1

Index Scan using idx_trck_spot_id on spot_tracking_audience sta (cost=0.42..20,832.76 rows=441,592 width=123) (actual time=0.044..0.045 rows=1 loops=1)

76. 0.962 1.370 ↑ 1.0 3,126 5

Materialize (cost=0.00..76.89 rows=3,126 width=16) (actual time=0.002..0.274 rows=3,126 loops=5)

77. 0.408 0.408 ↑ 1.0 3,126 1

Seq Scan on stage_spot_isci ssi (cost=0.00..61.26 rows=3,126 width=16) (actual time=0.004..0.408 rows=3,126 loops=1)

78. 0.025 0.068 ↑ 707,148.6 5 1

GroupAggregate (cost=0.43..356,101.73 rows=3,535,743 width=12) (actual time=0.042..0.068 rows=5 loops=1)

  • Group Key: aired_spot_delivery.usn
79. 0.043 0.043 ↑ 646,591.6 11 1

Index Scan using idx_stage_lighthouse_aired_spot_delivery_usn on aired_spot_delivery (cost=0.43..285,181.76 rows=7,112,508 width=12) (actual time=0.033..0.043 rows=11 loops=1)

80. 0.040 0.040 ↓ 0.0 0 5

Index Scan using idx_lh_inv_usn on invoice_data inv (cost=0.43..0.52 rows=1 width=8) (actual time=0.008..0.008 rows=0 loops=5)

  • Index Cond: (bs.source_spot_id = usn)
81. 5.260 5.260 ↑ 1.0 1 5

Index Scan using idx_dt_time_element on dim_time dtt (cost=0.29..0.31 rows=1 width=12) (actual time=1.052..1.052 rows=1 loops=5)

  • Index Cond: (bs.broadcast_air_time = time_element)
82. 0.025 0.025 ↑ 1.0 1 5

Index Scan using idx_dt_time_element on dim_time gt (cost=0.29..0.31 rows=1 width=12) (actual time=0.004..0.005 rows=1 loops=5)

  • Index Cond: ((bs.gregorian_air_date_time)::time without time zone = time_element)
Planning time : 64.461 ms
Execution time : 1,390.805 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
check_adjustment 0.122 ms 5 0.024 ms