explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ujkg

Settings
# exclusive inclusive rows x rows loops node
1. 0.034 57,802.990 ↑ 1.0 1 1

Limit (cost=3.29..37,135.15 rows=1 width=40) (actual time=57,802.956..57,802.990 rows=1 loops=1)

2. 0.094 57,802.956 ↑ 18,805.0 1 1

Subquery Scan on dp (cost=3.29..698,264,702.07 rows=18,805 width=40) (actual time=57,802.956..57,802.956 rows=1 loops=1)

3. 0.180 4.721 ↑ 18,805.0 1 1

GroupAggregate (cost=3.29..10,016,707.46 rows=18,805 width=2,256) (actual time=4.721..4.721 rows=1 loops=1)

  • Group Key: dd.deal_sk
4. 0.052 4.541 ↑ 17,180.1 14 1

Nested Loop (cost=3.29..9,992,766.72 rows=240,522 width=1,864) (actual time=2.854..4.541 rows=14 loops=1)

  • Join Filter: (fdl.guaranteed_audience_sk = gau.audience_sk)
  • Rows Removed by Join Filter: 468
5. 0.056 4.447 ↑ 17,180.1 14 1

Nested Loop (cost=3.29..9,242,299.66 rows=240,522 width=1,857) (actual time=2.832..4.447 rows=14 loops=1)

  • Join Filter: (fdl.team_sk = ddt.team_sk)
  • Rows Removed by Join Filter: 577
6. 0.013 4.335 ↑ 17,180.1 14 1

Nested Loop (cost=3.29..8,289,800.56 rows=240,522 width=1,838) (actual time=2.800..4.335 rows=14 loops=1)

7. 0.010 3.496 ↑ 17,180.1 14 1

Nested Loop (cost=3.01..8,217,020.68 rows=240,522 width=1,827) (actual time=2.320..3.496 rows=14 loops=1)

8. 0.011 2.632 ↑ 17,180.1 14 1

Nested Loop (cost=2.73..8,144,240.81 rows=240,522 width=1,812) (actual time=1.803..2.632 rows=14 loops=1)

  • Join Filter: (fdl.rate_card_type_sk = rct.rate_card_type_sk)
  • Rows Removed by Join Filter: 28
9. 0.002 2.607 ↑ 17,180.1 14 1

Nested Loop (cost=2.73..7,426,235.40 rows=240,522 width=1,694) (actual time=1.787..2.607 rows=14 loops=1)

  • Join Filter: (fdl.property_sk = ddp.property_sk)
10. 0.016 2.577 ↑ 17,180.1 14 1

Nested Loop (cost=2.73..5,878,441.94 rows=240,522 width=1,698) (actual time=1.764..2.577 rows=14 loops=1)

  • Join Filter: (fdl.network_sk = ddn.network_sk)
  • Rows Removed by Join Filter: 25
11. 0.010 2.533 ↑ 17,180.1 14 1

Nested Loop (cost=2.73..5,012,526.85 rows=240,522 width=1,702) (actual time=1.732..2.533 rows=14 loops=1)

  • Join Filter: (fdl.division_sk = ddv.division_sk)
  • Rows Removed by Join Filter: 52
12. 0.019 2.509 ↑ 17,180.1 14 1

Nested Loop (cost=2.73..3,284,344.65 rows=240,522 width=1,644) (actual time=1.724..2.509 rows=14 loops=1)

  • Join Filter: (fdl.calendar_type_sk = dct.calendar_type_sk)
  • Rows Removed by Join Filter: 28
13. 0.006 2.476 ↑ 17,180.1 14 1

Nested Loop (cost=2.73..2,385,952.78 rows=240,522 width=1,372) (actual time=1.703..2.476 rows=14 loops=1)

14. 0.012 1.798 ↑ 17,180.1 14 1

Nested Loop (cost=2.43..2,305,799.04 rows=240,522 width=1,376) (actual time=1.057..1.798 rows=14 loops=1)

15. 0.010 1.772 ↑ 17,180.1 14 1

Nested Loop (cost=2.15..2,231,464.14 rows=240,522 width=1,361) (actual time=1.054..1.772 rows=14 loops=1)

16. 0.023 1.734 ↑ 17,180.1 14 1

Nested Loop (cost=1.86..2,157,129.23 rows=240,522 width=1,346) (actual time=1.039..1.734 rows=14 loops=1)

17. 0.011 1.123 ↑ 17,180.1 14 1

Nested Loop (cost=1.57..2,082,794.32 rows=240,522 width=1,327) (actual time=0.666..1.123 rows=14 loops=1)

18. 0.012 0.216 ↑ 17,180.1 14 1

Nested Loop (cost=1.28..2,007,871.32 rows=240,522 width=1,309) (actual time=0.107..0.216 rows=14 loops=1)

19. 0.006 0.176 ↑ 17,180.1 14 1

Nested Loop (cost=0.99..1,931,060.46 rows=240,522 width=1,305) (actual time=0.090..0.176 rows=14 loops=1)

20. 0.015 0.100 ↑ 17,180.1 14 1

Nested Loop (cost=0.71..1,854,249.61 rows=240,522 width=1,301) (actual time=0.050..0.100 rows=14 loops=1)

  • Join Filter: (fdl.proposal_status_sk = dps.proposal_status_sk)
  • Rows Removed by Join Filter: 81
21. 0.007 0.071 ↑ 17,180.1 14 1

Nested Loop (cost=0.71..955,857.74 rows=240,522 width=1,305) (actual time=0.040..0.071 rows=14 loops=1)

  • Join Filter: (fdl.deal_status_sk = dds.deal_status_sk)
  • Rows Removed by Join Filter: 14
22. 0.010 0.050 ↑ 17,180.1 14 1

Nested Loop (cost=0.71..57,465.87 rows=240,522 width=1,163) (actual time=0.027..0.050 rows=14 loops=1)

23. 0.014 0.014 ↑ 9,402.5 2 1

Index Scan using dim_deal_pkey on dim_deal dd (cost=0.29..6,960.77 rows=18,805 width=1,085) (actual time=0.013..0.014 rows=2 loops=1)

24. 0.026 0.026 ↑ 2.9 7 2

Index Scan using idx_fact_deal_line_deal_sk on fact_deal_line fdl (cost=0.42..2.49 rows=20 width=86) (actual time=0.009..0.013 rows=7 loops=2)

  • Index Cond: (deal_sk = dd.deal_sk)
  • Filter: (is_active_record = 1)
25. 0.007 0.014 ↑ 125.0 2 14

Materialize (cost=0.00..13.75 rows=250 width=150) (actual time=0.001..0.001 rows=2 loops=14)

26. 0.007 0.007 ↑ 125.0 2 1

Seq Scan on dim_deal_status dds (cost=0.00..12.50 rows=250 width=150) (actual time=0.006..0.007 rows=2 loops=1)

27. 0.010 0.014 ↑ 35.7 7 14

Materialize (cost=0.00..13.75 rows=250 width=4) (actual time=0.001..0.001 rows=7 loops=14)

28. 0.004 0.004 ↑ 35.7 7 1

Seq Scan on dim_proposal_status dps (cost=0.00..12.50 rows=250 width=4) (actual time=0.004..0.004 rows=7 loops=1)

29. 0.070 0.070 ↑ 1.0 1 14

Index Scan using dim_dates_pkey on dim_dates psd (cost=0.29..0.32 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=14)

  • Index Cond: (date_sk = fdl.proposal_start_date_sk)
30. 0.028 0.028 ↑ 1.0 1 14

Index Scan using dim_dates_pkey on dim_dates ped (cost=0.29..0.32 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=14)

  • Index Cond: (date_sk = fdl.proposal_end_date_sk)
31. 0.896 0.896 ↑ 1.0 1 14

Index Scan using dim_advertiser_pkey on dim_advertiser dad (cost=0.29..0.31 rows=1 width=26) (actual time=0.064..0.064 rows=1 loops=14)

  • Index Cond: (advertiser_sk = fdl.advertiser_sk)
32. 0.588 0.588 ↑ 1.0 1 14

Index Scan using dim_agency_pkey on dim_agency dag (cost=0.29..0.31 rows=1 width=27) (actual time=0.042..0.042 rows=1 loops=14)

  • Index Cond: (agency_sk = fdl.agency_sk)
33. 0.028 0.028 ↑ 1.0 1 14

Index Scan using dim_agency_pkey on dim_agency prag (cost=0.29..0.31 rows=1 width=23) (actual time=0.002..0.002 rows=1 loops=14)

  • Index Cond: (agency_sk = fdl.parent_agency_sk)
34. 0.014 0.014 ↑ 1.0 1 14

Index Scan using dim_agency_pkey on dim_agency psag (cost=0.29..0.31 rows=1 width=23) (actual time=0.001..0.001 rows=1 loops=14)

  • Index Cond: (agency_sk = fdl.posting_agency_sk)
35. 0.672 0.672 ↑ 1.0 1 14

Index Only Scan using dim_brand_pkey on dim_brand dbr (cost=0.29..0.33 rows=1 width=4) (actual time=0.048..0.048 rows=1 loops=14)

  • Index Cond: (brand_sk = fdl.brand_sk)
  • Heap Fetches: 14
36. 0.000 0.014 ↑ 83.3 3 14

Materialize (cost=0.00..13.75 rows=250 width=280) (actual time=0.001..0.001 rows=3 loops=14)

37. 0.014 0.014 ↑ 83.3 3 1

Seq Scan on dim_calendar_type dct (cost=0.00..12.50 rows=250 width=280) (actual time=0.013..0.014 rows=3 loops=1)

38. 0.009 0.014 ↑ 96.0 5 14

Materialize (cost=0.00..17.20 rows=480 width=66) (actual time=0.000..0.001 rows=5 loops=14)

39. 0.005 0.005 ↑ 96.0 5 1

Seq Scan on dim_division ddv (cost=0.00..14.80 rows=480 width=66) (actual time=0.004..0.005 rows=5 loops=1)

40. 0.006 0.028 ↑ 80.3 3 14

Materialize (cost=0.00..6.62 rows=241 width=4) (actual time=0.002..0.002 rows=3 loops=14)

41. 0.022 0.022 ↑ 18.5 13 1

Seq Scan on dim_network ddn (cost=0.00..5.41 rows=241 width=4) (actual time=0.020..0.022 rows=13 loops=1)

42. 0.008 0.028 ↑ 430.0 1 14

Materialize (cost=0.00..16.45 rows=430 width=4) (actual time=0.002..0.002 rows=1 loops=14)

43. 0.020 0.020 ↑ 430.0 1 1

Seq Scan on dim_property ddp (cost=0.00..14.30 rows=430 width=4) (actual time=0.020..0.020 rows=1 loops=1)

44. 0.009 0.014 ↑ 66.7 3 14

Materialize (cost=0.00..13.00 rows=200 width=126) (actual time=0.001..0.001 rows=3 loops=14)

45. 0.005 0.005 ↑ 66.7 3 1

Seq Scan on dim_rate_card_type rct (cost=0.00..12.00 rows=200 width=126) (actual time=0.005..0.005 rows=3 loops=1)

46. 0.854 0.854 ↑ 1.0 1 14

Index Scan using dim_user_pkey on dim_user adu (cost=0.28..0.30 rows=1 width=23) (actual time=0.061..0.061 rows=1 loops=14)

  • Index Cond: (user_sk = fdl.ae_user_sk)
47. 0.826 0.826 ↑ 1.0 1 14

Index Scan using dim_user_pkey on dim_user sdu (cost=0.28..0.30 rows=1 width=19) (actual time=0.059..0.059 rows=1 loops=14)

  • Index Cond: (user_sk = fdl.sales_planner_user_sk)
48. 0.038 0.056 ↑ 6.3 42 14

Materialize (cost=0.00..6.98 rows=265 width=27) (actual time=0.001..0.004 rows=42 loops=14)

49. 0.018 0.018 ↑ 5.9 45 1

Seq Scan on dim_team ddt (cost=0.00..5.65 rows=265 width=27) (actual time=0.009..0.018 rows=45 loops=1)

50. 0.029 0.042 ↑ 6.1 34 14

Materialize (cost=0.00..5.13 rows=209 width=15) (actual time=0.001..0.003 rows=34 loops=14)

51. 0.013 0.013 ↑ 5.2 40 1

Seq Scan on dim_audience gau (cost=0.00..4.09 rows=209 width=15) (actual time=0.010..0.013 rows=40 loops=1)

52.          

SubPlan (forSubquery Scan)

53. 0.005 5,940.175 ↑ 1.0 1 1

Subquery Scan on od (cost=7,384.57..7,520.62 rows=1 width=8) (actual time=5,940.174..5,940.175 rows=1 loops=1)

54. 273.419 5,940.170 ↑ 1.0 1 1

GroupAggregate (cost=7,384.57..7,520.60 rows=1 width=16) (actual time=5,940.169..5,940.170 rows=1 loops=1)

  • Group Key: di.deal_sk
55. 357.603 5,666.751 ↓ 2,900,352.0 2,900,352 1

Nested Loop Left Join (cost=7,384.57..7,520.59 rows=1 width=16) (actual time=4,551.796..5,666.751 rows=2,900,352 loops=1)

  • Join Filter: (di.deal_sk = f.deal_sk)
56. 0.007 0.007 ↑ 1.0 1 1

Index Only Scan using dim_deal_pkey on dim_deal di (cost=0.29..4.30 rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=1)

  • Index Cond: (deal_sk = dp.deal_sk)
  • Heap Fetches: 0
57. 443.009 5,309.141 ↓ 1,672.6 2,900,352 1

Merge Join (cost=7,384.28..7,494.61 rows=1,734 width=16) (actual time=4,551.787..5,309.141 rows=2,900,352 loops=1)

  • Merge Cond: (d.date_sk = f.order_start_date_sk)
58. 3.841 3.841 ↑ 2.0 10,888 1

Index Scan using dim_dates_pkey on dim_dates d (cost=0.29..1,382.34 rows=21,916 width=12) (actual time=0.017..3.841 rows=10,888 loops=1)

59. 1,680.123 4,862.291 ↓ 1,672.6 2,900,352 1

Sort (cost=6,752.44..6,756.78 rows=1,734 width=12) (actual time=4,548.280..4,862.291 rows=2,900,352 loops=1)

  • Sort Key: f.order_start_date_sk
  • Sort Method: external sort Disk: 85176kB
60. 2,992.421 3,182.168 ↓ 1,672.6 2,900,352 1

Bitmap Heap Scan on fact_spot f (cost=33.87..6,659.16 rows=1,734 width=12) (actual time=199.901..3,182.168 rows=2,900,352 loops=1)

  • Recheck Cond: (deal_sk = dp.deal_sk)
  • Rows Removed by Index Recheck: 2063239
  • Heap Blocks: exact=48814 lossy=231080
61. 189.747 189.747 ↓ 1,672.6 2,900,352 1

Bitmap Index Scan on idx_fact_spot_replace_deal_sk (cost=0.00..33.44 rows=1,734 width=0) (actual time=189.747..189.747 rows=2,900,352 loops=1)

  • Index Cond: (deal_sk = dp.deal_sk)
62. 0.003 5,989.911 ↑ 1.0 1 1

Subquery Scan on od_1 (cost=7,384.57..7,520.61 rows=1 width=8) (actual time=5,989.911..5,989.911 rows=1 loops=1)

63. 277.715 5,989.908 ↑ 1.0 1 1

GroupAggregate (cost=7,384.57..7,520.60 rows=1 width=16) (actual time=5,989.908..5,989.908 rows=1 loops=1)

  • Group Key: di_1.deal_sk
64. 352.118 5,712.193 ↓ 2,900,352.0 2,900,352 1

Nested Loop Left Join (cost=7,384.57..7,520.59 rows=1 width=16) (actual time=4,622.616..5,712.193 rows=2,900,352 loops=1)

  • Join Filter: (di_1.deal_sk = f_1.deal_sk)
65. 0.020 0.020 ↑ 1.0 1 1

Index Only Scan using dim_deal_pkey on dim_deal di_1 (cost=0.29..4.30 rows=1 width=8) (actual time=0.018..0.020 rows=1 loops=1)

  • Index Cond: (deal_sk = dp.deal_sk)
  • Heap Fetches: 0
66. 430.288 5,360.055 ↓ 1,672.6 2,900,352 1

Merge Join (cost=7,384.28..7,494.61 rows=1,734 width=16) (actual time=4,622.595..5,360.055 rows=2,900,352 loops=1)

  • Merge Cond: (d_1.date_sk = f_1.order_start_date_sk)
67. 4.049 4.049 ↑ 2.0 10,888 1

Index Scan using dim_dates_pkey on dim_dates d_1 (cost=0.29..1,382.34 rows=21,916 width=12) (actual time=0.017..4.049 rows=10,888 loops=1)

68. 1,704.009 4,925.718 ↓ 1,672.6 2,900,352 1

Sort (cost=6,752.44..6,756.78 rows=1,734 width=12) (actual time=4,618.862..4,925.718 rows=2,900,352 loops=1)

  • Sort Key: f_1.order_start_date_sk
  • Sort Method: external sort Disk: 85176kB
69. 3,006.848 3,221.709 ↓ 1,672.6 2,900,352 1

Bitmap Heap Scan on fact_spot f_1 (cost=33.87..6,659.16 rows=1,734 width=12) (actual time=225.500..3,221.709 rows=2,900,352 loops=1)

  • Recheck Cond: (deal_sk = dp.deal_sk)
  • Rows Removed by Index Recheck: 2063239
  • Heap Blocks: exact=48814 lossy=231080
70. 214.861 214.861 ↓ 1,672.6 2,900,352 1

Bitmap Index Scan on idx_fact_spot_replace_deal_sk (cost=0.00..33.44 rows=1,734 width=0) (actual time=214.861..214.861 rows=2,900,352 loops=1)

  • Index Cond: (deal_sk = dp.deal_sk)
71. 0.002 5,836.476 ↑ 1.0 1 1

Subquery Scan on od_2 (cost=7,384.57..7,523.11 rows=1 width=8) (actual time=5,836.475..5,836.476 rows=1 loops=1)

72. 264.192 5,836.474 ↑ 1.0 1 1

GroupAggregate (cost=7,384.57..7,523.09 rows=1 width=16) (actual time=5,836.473..5,836.474 rows=1 loops=1)

  • Group Key: di_2.deal_sk
73. 351.919 5,572.282 ↓ 2,900,352.0 2,900,352 1

Nested Loop Left Join (cost=7,384.57..7,523.08 rows=1 width=16) (actual time=4,488.527..5,572.282 rows=2,900,352 loops=1)

  • Join Filter: (di_2.deal_sk = f_2.deal_sk)
74. 0.020 0.020 ↑ 1.0 1 1

Index Only Scan using dim_deal_pkey on dim_deal di_2 (cost=0.29..4.30 rows=1 width=8) (actual time=0.018..0.020 rows=1 loops=1)

  • Index Cond: (deal_sk = dp.deal_sk)
  • Heap Fetches: 0
75. 430.012 5,220.343 ↓ 1,672.6 2,900,352 1

Merge Join (cost=7,384.28..7,497.10 rows=1,734 width=16) (actual time=4,488.505..5,220.343 rows=2,900,352 loops=1)

  • Merge Cond: (d_2.date_sk = f_2.order_end_date_sk)
76. 5.765 5.765 ↑ 2.0 10,957 1

Index Scan using dim_dates_pkey on dim_dates d_2 (cost=0.29..1,382.34 rows=21,916 width=12) (actual time=0.017..5.765 rows=10,957 loops=1)

77. 1,672.926 4,784.566 ↓ 1,672.6 2,900,352 1

Sort (cost=6,752.44..6,756.78 rows=1,734 width=12) (actual time=4,484.777..4,784.566 rows=2,900,352 loops=1)

  • Sort Key: f_2.order_end_date_sk
  • Sort Method: external sort Disk: 85176kB
78. 2,917.605 3,111.640 ↓ 1,672.6 2,900,352 1

Bitmap Heap Scan on fact_spot f_2 (cost=33.87..6,659.16 rows=1,734 width=12) (actual time=203.958..3,111.640 rows=2,900,352 loops=1)

  • Recheck Cond: (deal_sk = dp.deal_sk)
  • Rows Removed by Index Recheck: 2063239
  • Heap Blocks: exact=48814 lossy=231080
79. 194.035 194.035 ↓ 1,672.6 2,900,352 1

Bitmap Index Scan on idx_fact_spot_replace_deal_sk (cost=0.00..33.44 rows=1,734 width=0) (actual time=194.035..194.035 rows=2,900,352 loops=1)

  • Index Cond: (deal_sk = dp.deal_sk)
80. 0.002 8,969.440 ↑ 1.0 1 1

Subquery Scan on od_3 (cost=34.46..6,689.13 rows=1 width=8) (actual time=8,969.439..8,969.440 rows=1 loops=1)

81. 375.254 8,969.438 ↑ 1.0 1 1

GroupAggregate (cost=34.46..6,689.11 rows=1 width=16) (actual time=8,969.437..8,969.438 rows=1 loops=1)

  • Group Key: di_3.deal_sk
82. 2,093.496 8,594.184 ↓ 2,900,352.0 2,900,352 1

Nested Loop Left Join (cost=34.46..6,689.10 rows=1 width=16) (actual time=206.193..8,594.184 rows=2,900,352 loops=1)

83. 513.484 3,600.336 ↓ 2,900,352.0 2,900,352 1

Nested Loop Left Join (cost=34.16..6,685.14 rows=1 width=16) (actual time=204.244..3,600.336 rows=2,900,352 loops=1)

  • Join Filter: (di_3.deal_sk = s.deal_sk)
84. 0.023 0.023 ↑ 1.0 1 1

Index Only Scan using dim_deal_pkey on dim_deal di_3 (cost=0.29..4.30 rows=1 width=8) (actual time=0.022..0.023 rows=1 loops=1)

  • Index Cond: (deal_sk = dp.deal_sk)
  • Heap Fetches: 0
85. 2,892.644 3,086.829 ↓ 1,672.6 2,900,352 1

Bitmap Heap Scan on fact_spot s (cost=33.87..6,659.16 rows=1,734 width=16) (actual time=204.214..3,086.829 rows=2,900,352 loops=1)

  • Recheck Cond: (deal_sk = dp.deal_sk)
  • Rows Removed by Index Recheck: 2063239
  • Heap Blocks: exact=48814 lossy=231080
86. 194.185 194.185 ↓ 1,672.6 2,900,352 1

Bitmap Index Scan on idx_fact_spot_replace_deal_sk (cost=0.00..33.44 rows=1,734 width=0) (actual time=194.185..194.185 rows=2,900,352 loops=1)

  • Index Cond: (deal_sk = dp.deal_sk)
87. 2,900.352 2,900.352 ↑ 1.0 1 2,900,352

Index Scan using dim_order_pkey on dim_order dmo (cost=0.29..3.96 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=2,900,352)

  • Index Cond: (s.order_sk = order_sk)
88. 0.022 1.832 ↑ 1.0 1 1

Aggregate (cost=91.02..91.03 rows=1 width=32) (actual time=1.831..1.832 rows=1 loops=1)

89. 0.004 1.810 ↑ 20.0 1 1

Group (cost=90.56..90.71 rows=20 width=42) (actual time=1.807..1.810 rows=1 loops=1)

  • Group Key: fdl_1.deal_sk, dmn.network_sk
90. 0.015 1.806 ↑ 1.5 13 1

Sort (cost=90.56..90.61 rows=20 width=42) (actual time=1.806..1.806 rows=13 loops=1)

  • Sort Key: dmn.network_sk
  • Sort Method: quicksort Memory: 26kB
91. 0.025 1.791 ↑ 1.5 13 1

Hash Join (cost=13.00..90.13 rows=20 width=42) (actual time=1.782..1.791 rows=13 loops=1)

  • Hash Cond: (fdl_1.network_sk = dmn.network_sk)
92. 0.008 0.028 ↑ 1.5 13 1

Bitmap Heap Scan on fact_deal_line fdl_1 (cost=4.58..81.66 rows=20 width=12) (actual time=0.028..0.028 rows=13 loops=1)

  • Recheck Cond: (deal_sk = dp.deal_sk)
  • Heap Blocks: exact=1
93. 0.020 0.020 ↑ 1.5 13 1

Bitmap Index Scan on idx_fact_deal_line_deal_sk (cost=0.00..4.57 rows=20 width=0) (actual time=0.020..0.020 rows=13 loops=1)

  • Index Cond: (deal_sk = dp.deal_sk)
94. 0.051 1.738 ↑ 1.0 241 1

Hash (cost=5.41..5.41 rows=241 width=34) (actual time=1.738..1.738 rows=241 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
95. 1.687 1.687 ↑ 1.0 241 1

Seq Scan on dim_network dmn (cost=0.00..5.41 rows=241 width=34) (actual time=0.009..1.687 rows=241 loops=1)

96. 0.009 0.068 ↑ 1.0 1 1

Aggregate (cost=102.27..102.28 rows=1 width=32) (actual time=0.068..0.068 rows=1 loops=1)

97. 0.002 0.059 ↑ 20.0 1 1

Group (cost=101.82..101.97 rows=20 width=84) (actual time=0.057..0.059 rows=1 loops=1)

  • Group Key: fdl_2.deal_sk, dmp.property_sk
98. 0.010 0.057 ↑ 1.5 13 1

Sort (cost=101.82..101.87 rows=20 width=84) (actual time=0.056..0.057 rows=13 loops=1)

  • Sort Key: dmp.property_sk
  • Sort Method: quicksort Memory: 26kB
99. 0.019 0.047 ↑ 1.5 13 1

Hash Join (cost=24.25..101.38 rows=20 width=84) (actual time=0.041..0.047 rows=13 loops=1)

  • Hash Cond: (fdl_2.property_sk = dmp.property_sk)
100. 0.005 0.014 ↑ 1.5 13 1

Bitmap Heap Scan on fact_deal_line fdl_2 (cost=4.58..81.66 rows=20 width=12) (actual time=0.014..0.014 rows=13 loops=1)

  • Recheck Cond: (deal_sk = dp.deal_sk)
  • Heap Blocks: exact=1
101. 0.009 0.009 ↑ 1.5 13 1

Bitmap Index Scan on idx_fact_deal_line_deal_sk (cost=0.00..4.57 rows=20 width=0) (actual time=0.009..0.009 rows=13 loops=1)

  • Index Cond: (deal_sk = dp.deal_sk)
102. 0.004 0.014 ↑ 26.9 16 1

Hash (cost=14.30..14.30 rows=430 width=76) (actual time=0.014..0.014 rows=16 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
103. 0.010 0.010 ↑ 26.9 16 1

Seq Scan on dim_property dmp (cost=0.00..14.30 rows=430 width=76) (actual time=0.007..0.010 rows=16 loops=1)

104. 776.814 31,060.239 ↑ 1.0 1 1

Aggregate (cost=7,152.37..7,152.38 rows=1 width=32) (actual time=31,060.239..31,060.239 rows=1 loops=1)

105. 30,084.038 30,283.425 ↓ 4,886.2 1,504,946 1

Bitmap Heap Scan on fact_spot fs (cost=33.52..7,150.82 rows=308 width=14) (actual time=206.445..30,283.425 rows=1,504,946 loops=1)

  • Recheck Cond: (deal_sk = dp.deal_sk)
  • Rows Removed by Index Recheck: 2063239
  • Filter: ((is_active_record = 1) AND ((stewardship_status_sk = ANY ('{1,2,3}'::integer[])) OR (is_manual_adjustment = 1)) AND (broadcast_week_date_sk <= (to_char((((reveal.get_broadcast_date_from_calendar((now())::date) ->> 'broadcast_quarter_end_date'::text))::date)::timestamp with time zone, 'YYYYMMDD'::text))::bigint))
  • Rows Removed by Filter: 1395406
  • Heap Blocks: exact=48814 lossy=231080
106. 199.387 199.387 ↓ 1,672.6 2,900,352 1

Bitmap Index Scan on idx_fact_spot_replace_deal_sk (cost=0.00..33.44 rows=1,734 width=0) (actual time=199.386..199.387 rows=2,900,352 loops=1)

  • Index Cond: (deal_sk = dp.deal_sk)