explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eMdM

Settings
# exclusive inclusive rows x rows loops node
1. 0.063 68,606.713 ↑ 1.0 50 1

Limit (cost=2,716,448.56..2,801,244.20 rows=50 width=608) (actual time=47,096.112..68,606.713 rows=50 loops=1)

2. 1.131 68,606.650 ↑ 126,482,360.4 50 1

Nested Loop Left Join (cost=2,716,448.56..10,725,155,580,276.48 rows=6,324,118,020 width=608) (actual time=47,096.110..68,606.650 rows=50 loops=1)

3. 0.202 68,603.569 ↑ 126,482,360.4 50 1

Hash Left Join (cost=2,716,448.13..10,721,131,330,147.38 rows=6,324,118,020 width=496) (actual time=47,096.033..68,603.569 rows=50 loops=1)

  • Hash Cond: (bs.source_spot_id = ssi.admiral_spot_id)
4. 0.709 68,577.336 ↑ 126,482,360.4 50 1

Hash Left Join (cost=2,715,814.63..10,711,154,957,448.09 rows=6,324,118,020 width=378) (actual time=47,069.910..68,577.336 rows=50 loops=1)

  • Hash Cond: (vw_spot_and_header.order_id = dmdo.source_order_id)
5. 0.139 68,206.022 ↑ 126,438,597.4 50 1

Merge Right Join (cost=2,708,534.93..10,710,359,253,476.80 rows=6,321,929,871 width=382) (actual time=46,698.724..68,206.022 rows=50 loops=1)

  • Merge Cond: (vw_spot_and_header.order_id = bs.order_id)
6. 0.003 43,734.650 ↑ 1,606,272.0 1 1

Nested Loop Left Join (cost=2,708,519.22..11,094,319.89 rows=1,606,272 width=12) (actual time=43,734.650..43,734.650 rows=1 loops=1)

  • Join Filter: ((vw_spot_and_header.division_cd)::text = (dmdv_1.division)::text)
  • Rows Removed by Join Filter: 5
7. 0.005 43,733.728 ↑ 683,520.0 1 1

Nested Loop Left Join (cost=2,708,519.22..6,275,488.01 rows=683,520 width=12) (actual time=43,733.728..43,733.728 rows=1 loops=1)

  • Join Filter: ((vw_spot_and_header.network_cd)::text = (dmdn_1.network_cd)::text)
8. 0.001 43,732.195 ↑ 683,520.0 1 1

Unique (cost=2,708,519.22..2,813,450.00 rows=683,520 width=12) (actual time=43,732.195..43,732.195 rows=1 loops=1)

9. 6,956.621 43,732.194 ↑ 10,493,078.0 1 1

Sort (cost=2,708,519.22..2,734,751.92 rows=10,493,078 width=12) (actual time=43,732.194..43,732.194 rows=1 loops=1)

  • Sort Key: vw_spot_and_header.order_id, vw_spot_and_header.division_cd, vw_spot_and_header.network_cd
  • Sort Method: external merge Disk: 242936kB
10. 36,775.573 36,775.573 ↓ 1.0 10,499,716 1

Seq Scan on vw_spot_and_header (cost=0.00..1,126,220.36 rows=10,493,078 width=12) (actual time=6.479..36,775.573 rows=10,499,716 loops=1)

  • Filter: (is_reveal_filtered = 1)
  • Rows Removed by Filter: 522611
11. 0.005 1.528 ↑ 337.0 1 1

Materialize (cost=0.00..10.06 rows=337 width=14) (actual time=1.528..1.528 rows=1 loops=1)

12. 1.523 1.523 ↑ 337.0 1 1

Seq Scan on dim_network dmdn_1 (cost=0.00..8.37 rows=337 width=14) (actual time=1.523..1.523 rows=1 loops=1)

13. 0.010 0.919 ↑ 78.3 6 1

Materialize (cost=0.00..17.05 rows=470 width=62) (actual time=0.917..0.919 rows=6 loops=1)

14. 0.909 0.909 ↑ 78.3 6 1

Seq Scan on dim_division dmdv_1 (cost=0.00..14.70 rows=470 width=62) (actual time=0.909..0.909 rows=6 loops=1)

15. 0.254 24,471.233 ↑ 3,569,986.7 50 1

Materialize (cost=15.71..10,710,253,772,441.50 rows=178,499,336 width=374) (actual time=2,964.067..24,471.233 rows=50 loops=1)

16. 0.212 24,470.979 ↑ 3,569,986.7 50 1

Nested Loop Left Join (cost=15.71..10,710,253,326,193.16 rows=178,499,336 width=374) (actual time=2,964.058..24,470.979 rows=50 loops=1)

17. 9,216.556 24,465.367 ↑ 4,360,761.2 36 1

Nested Loop Left Join (cost=0.86..10,703,676,024,996.72 rows=156,987,402 width=376) (actual time=2,962.937..24,465.367 rows=36 loops=1)

  • Join Filter: (bs.source_spot_id = sta.source_spot_id)
  • Rows Removed by Join Filter: 134604216
18. 1.195 1,881.795 ↑ 4,360,761.2 36 1

Nested Loop Left Join (cost=0.86..232,916,085,658.97 rows=156,987,402 width=376) (actual time=572.408..1,881.795 rows=36 loops=1)

  • Join Filter: (bs.secondary_product_conflict_id = dmpc.product_conflict_id)
  • Rows Removed by Join Filter: 14868
19. 0.907 1,876.172 ↑ 4,360,761.2 36 1

Nested Loop Left Join (cost=0.86..231,943,537,347.24 rows=156,987,402 width=376) (actual time=568.653..1,876.172 rows=36 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: 7494
20. 0.687 1,871.881 ↑ 4,360,761.2 36 1

Nested Loop Left Join (cost=0.86..231,355,619,522.08 rows=156,987,402 width=372) (actual time=567.304..1,871.881 rows=36 loops=1)

  • Join Filter: (bs.team_id = dmtm.team_id)
  • Rows Removed by Join Filter: 8244
21. 14.956 1,867.306 ↑ 4,360,761.2 36 1

Nested Loop Left Join (cost=0.86..230,733,931,696.35 rows=156,987,402 width=372) (actual time=563.777..1,867.306 rows=36 loops=1)

  • Join Filter: (bs.asr_user_id = dmasr.user_id)
  • Rows Removed by Join Filter: 203688
22. 4.776 1,756.122 ↑ 4,360,761.2 36 1

Nested Loop Left Join (cost=0.86..214,808,343,755.86 rows=156,987,402 width=372) (actual time=476.467..1,756.122 rows=36 loops=1)

  • Join Filter: (bs.ae_user_id = dmae.user_id)
  • Rows Removed by Join Filter: 65700
23. 5.764 1,708.362 ↑ 4,360,761.2 36 1

Nested Loop Left Join (cost=0.86..198,882,750,541.00 rows=156,987,402 width=372) (actual time=436.388..1,708.362 rows=36 loops=1)

  • Join Filter: (bs.selling_title_id = dmst.selling_title_id)
  • Rows Removed by Join Filter: 82845
24. 0.607 1,689.782 ↑ 4,360,761.2 36 1

Nested Loop Left Join (cost=0.86..99,052,890,564.40 rows=156,987,402 width=372) (actual time=426.804..1,689.782 rows=36 loops=1)

  • Join Filter: (lower(btrim(replace((bs.division_cd)::text, ' '::text, ''::text))) = (dmdv.division)::text)
  • Rows Removed by Join Filter: 432
25. 0.074 1,689.103 ↑ 1,855,643.1 36 1

Nested Loop Left Join (cost=0.86..98,346,447,237.28 rows=66,803,150 width=372) (actual time=426.769..1,689.103 rows=36 loops=1)

  • Join Filter: (bs.order_revenue_type_id = dmrt.revenue_type_id)
26. 0.118 1,688.993 ↑ 1,855,643.1 36 1

Nested Loop Left Join (cost=0.86..98,323,319,985.58 rows=66,803,150 width=372) (actual time=426.757..1,688.993 rows=36 loops=1)

  • Join Filter: (bs.spot_revenue_type_id = dmspt.spot_revenue_type_id)
  • Rows Removed by Join Filter: 36
27. 0.110 1,688.191 ↑ 1,855,643.1 36 1

Nested Loop Left Join (cost=0.86..98,083,822,362.45 rows=66,803,150 width=372) (actual time=426.078..1,688.191 rows=36 loops=1)

  • Join Filter: (bs.rate_card_type_id = dmrct.rate_card_type_id)
  • Rows Removed by Join Filter: 72
28. 0.065 1,687.289 ↑ 1,855,643.1 36 1

Nested Loop Left Join (cost=0.86..97,754,142,749.46 rows=66,803,150 width=334) (actual time=425.290..1,687.289 rows=36 loops=1)

  • Join Filter: (bs.property_id = dmdp.property_id)
29. 1.036 1,686.504 ↑ 1,855,643.1 36 1

Nested Loop Left Join (cost=0.86..97,334,280,175.39 rows=66,803,150 width=334) (actual time=424.585..1,686.504 rows=36 loops=1)

  • Join Filter: ((bs.network_cd)::text = (dmdn.network_cd)::text)
  • Rows Removed by Join Filter: 11760
30. 75.024 1,681.076 ↑ 1,855,643.1 36 1

Nested Loop Left Join (cost=0.86..96,996,590,242.93 rows=66,803,150 width=330) (actual time=424.569..1,681.076 rows=36 loops=1)

  • Join Filter: ((bs.lh_deal_cd)::text = (dmdd.source_deal_id)::text)
  • Rows Removed by Join Filter: 1111500
31. 0.091 1,270.784 ↑ 1,855,643.1 36 1

Nested Loop Left Join (cost=0.86..66,058,922,045.49 rows=66,803,150 width=336) (actual time=137.472..1,270.784 rows=36 loops=1)

  • Join Filter: (bs.order_type_id = dot.order_type_id)
  • Rows Removed by Join Filter: 105
32. 0.127 1,269.901 ↑ 700,242.7 36 1

Nested Loop Left Join (cost=0.86..65,858,512,577.66 rows=25,208,736 width=330) (actual time=136.733..1,269.901 rows=36 loops=1)

  • Join Filter: (bs.order_status_id = dos.order_status_id)
  • Rows Removed by Join Filter: 212
33. 409.263 1,268.406 ↑ 291,767.8 36 1

Nested Loop Left Join (cost=0.86..65,782,886,353.66 rows=10,503,640 width=330) (actual time=135.414..1,268.406 rows=36 loops=1)

  • Join Filter: (bs.order_id = dmo.source_order_id)
  • Rows Removed by Join Filter: 5968165
34. 23.396 294.735 ↑ 291,667.4 36 1

Nested Loop Left Join (cost=0.86..30,179,396,676.16 rows=10,500,025 width=322) (actual time=127.756..294.735 rows=36 loops=1)

  • Join Filter: (bs.brand_id = dmbd.brand_id)
  • Rows Removed by Join Filter: 319644
35. 19.289 244.087 ↑ 291,667.4 36 1

Nested Loop Left Join (cost=0.86..12,662,831,721.72 rows=10,500,025 width=322) (actual time=113.752..244.087 rows=36 loops=1)

  • Join Filter: (bs.agency_id = dmag.agency_id)
  • Rows Removed by Join Filter: 271512
36. 11.248 109.058 ↑ 291,667.4 36 1

Nested Loop Left Join (cost=0.86..9,915,079,787.36 rows=10,500,025 width=322) (actual time=9.558..109.058 rows=36 loops=1)

  • Join Filter: (bs.advertiser_id = dmad.advertiser_id)
  • Rows Removed by Join Filter: 156528
37. 0.300 84.130 ↑ 291,667.4 36 1

Nested Loop Left Join (cost=0.86..5,405,213,488.96 rows=10,500,025 width=322) (actual time=3.015..84.130 rows=36 loops=1)

38. 79.222 79.222 ↑ 291,667.4 36 1

Index Scan using idx_blnd_order_id on blended_spots bs (cost=0.43..2,312,899.40 rows=10,500,025 width=312) (actual time=0.017..79.222 rows=36 loops=1)

39. 4.608 4.608 ↓ 0.0 0 36

Index Scan using idx_stage_deal_to_spot_spot_id on stage_deal_to_spot sds (cost=0.43..327.61 rows=18,695 width=14) (actual time=0.128..0.128 rows=0 loops=36)

  • Index Cond: (bs.source_spot_id = source_spot_id)
40. 7.998 13.680 ↑ 6.6 4,349 36

Materialize (cost=0.00..756.53 rows=28,635 width=8) (actual time=0.143..0.380 rows=4,349 loops=36)

41. 5.682 5.682 ↑ 6.6 4,349 1

Seq Scan on dim_advertiser dmad (cost=0.00..613.35 rows=28,635 width=8) (actual time=5.117..5.682 rows=4,349 loops=1)

42. 13.259 115.740 ↑ 2.3 7,543 36

Materialize (cost=0.00..463.71 rows=17,447 width=8) (actual time=0.036..3.215 rows=7,543 loops=36)

43. 102.481 102.481 ↑ 2.3 7,543 1

Seq Scan on dim_agency dmag (cost=0.00..376.47 rows=17,447 width=8) (actual time=1.270..102.481 rows=7,543 loops=1)

44. 15.089 27.252 ↑ 12.5 8,880 36

Materialize (cost=0.00..2,839.26 rows=111,217 width=8) (actual time=0.304..0.757 rows=8,880 loops=36)

45. 12.163 12.163 ↑ 12.5 8,880 1

Seq Scan on dim_brand dmbd (cost=0.00..2,283.17 rows=111,217 width=8) (actual time=10.946..12.163 rows=8,880 loops=1)

46. 534.030 564.408 ↑ 1.0 165,783 36

Materialize (cost=0.00..6,166.80 rows=170,520 width=16) (actual time=0.219..15.678 rows=165,783 loops=36)

47. 30.378 30.378 ↑ 1.0 170,520 1

Seq Scan on dim_order dmo (cost=0.00..4,481.20 rows=170,520 width=16) (actual time=7.647..30.378 rows=170,520 loops=1)

48. 0.058 1.368 ↑ 68.6 7 36

Materialize (cost=0.00..17.20 rows=480 width=8) (actual time=0.037..0.038 rows=7 loops=36)

49. 1.310 1.310 ↑ 68.6 7 1

Seq Scan on dim_order_status dos (cost=0.00..14.80 rows=480 width=8) (actual time=1.307..1.310 rows=7 loops=1)

50. 0.059 0.792 ↑ 132.5 4 36

Materialize (cost=0.00..17.95 rows=530 width=12) (actual time=0.021..0.022 rows=4 loops=36)

51. 0.733 0.733 ↑ 132.5 4 1

Seq Scan on dim_order_type dot (cost=0.00..15.30 rows=530 width=12) (actual time=0.731..0.733 rows=4 loops=1)

52. 55.603 335.268 ↑ 1.0 30,875 36

Materialize (cost=0.00..1,715.12 rows=30,875 width=25) (actual time=0.297..9.313 rows=30,875 loops=36)

53. 279.665 279.665 ↑ 1.0 30,875 1

Seq Scan on dim_deal dmdd (cost=0.00..1,560.75 rows=30,875 width=25) (actual time=10.680..279.665 rows=30,875 loops=1)

54. 0.682 4.392 ↑ 1.0 328 36

Materialize (cost=0.00..10.06 rows=337 width=14) (actual time=0.001..0.122 rows=328 loops=36)

55. 3.710 3.710 ↑ 1.0 337 1

Seq Scan on dim_network dmdn (cost=0.00..8.37 rows=337 width=14) (actual time=0.007..3.710 rows=337 loops=1)

56. 0.024 0.720 ↑ 420.0 1 36

Materialize (cost=0.00..16.30 rows=420 width=8) (actual time=0.020..0.020 rows=1 loops=36)

57. 0.696 0.696 ↑ 420.0 1 1

Seq Scan on dim_property dmdp (cost=0.00..14.20 rows=420 width=8) (actual time=0.696..0.696 rows=1 loops=1)

58. 0.013 0.792 ↑ 110.0 3 36

Materialize (cost=0.00..14.95 rows=330 width=46) (actual time=0.022..0.022 rows=3 loops=36)

59. 0.779 0.779 ↑ 110.0 3 1

Seq Scan on dim_rate_card_type dmrct (cost=0.00..13.30 rows=330 width=46) (actual time=0.778..0.779 rows=3 loops=1)

60. 0.010 0.684 ↑ 120.0 2 36

Materialize (cost=0.00..13.60 rows=240 width=8) (actual time=0.019..0.019 rows=2 loops=36)

61. 0.674 0.674 ↑ 120.0 2 1

Seq Scan on dim_spot_revenue_type dmspt (cost=0.00..12.40 rows=240 width=8) (actual time=0.674..0.674 rows=2 loops=1)

62. 0.031 0.036 ↑ 24.0 1 36

Materialize (cost=0.00..1.36 rows=24 width=8) (actual time=0.001..0.001 rows=1 loops=36)

63. 0.005 0.005 ↑ 24.0 1 1

Seq Scan on dim_revenue_type dmrt (cost=0.00..1.24 rows=24 width=8) (actual time=0.005..0.005 rows=1 loops=1)

64. 0.067 0.072 ↑ 39.2 12 36

Materialize (cost=0.00..17.05 rows=470 width=62) (actual time=0.001..0.002 rows=12 loops=36)

65. 0.005 0.005 ↑ 39.2 12 1

Seq Scan on dim_division dmdv (cost=0.00..14.70 rows=470 width=62) (actual time=0.003..0.005 rows=12 loops=1)

66. 5.316 12.816 ↑ 18.4 2,302 36

Materialize (cost=0.00..1,184.92 rows=42,395 width=8) (actual time=0.140..0.356 rows=2,302 loops=36)

67. 7.500 7.500 ↑ 2.4 17,855 1

Seq Scan on dim_selling_title dmst (cost=0.00..972.95 rows=42,395 width=8) (actual time=5.024..7.500 rows=17,855 loops=1)

68. 3.357 42.984 ↑ 3.7 1,826 36

Materialize (cost=0.00..239.46 rows=6,764 width=8) (actual time=0.001..1.194 rows=1,826 loops=36)

69. 39.627 39.627 ↑ 3.7 1,826 1

Seq Scan on dim_user dmae (cost=0.00..205.64 rows=6,764 width=8) (actual time=0.008..39.627 rows=1,826 loops=1)

70. 10.225 96.228 ↑ 1.2 5,659 36

Materialize (cost=0.00..239.46 rows=6,764 width=8) (actual time=0.001..2.673 rows=5,659 loops=36)

71. 86.003 86.003 ↑ 1.2 5,659 1

Seq Scan on dim_user dmasr (cost=0.00..205.64 rows=6,764 width=8) (actual time=0.006..86.003 rows=5,659 loops=1)

72. 0.422 3.888 ↑ 1.2 230 36

Materialize (cost=0.00..7.98 rows=265 width=8) (actual time=0.023..0.108 rows=230 loops=36)

73. 3.466 3.466 ↑ 1.2 230 1

Seq Scan on dim_team dmtm (cost=0.00..6.65 rows=265 width=8) (actual time=0.805..3.466 rows=230 loops=1)

74. 0.479 3.384 ↑ 1.0 209 36

Materialize (cost=0.00..5.21 rows=214 width=10) (actual time=0.037..0.094 rows=209 loops=36)

75. 2.905 2.905 ↑ 1.0 214 1

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

76. 0.769 4.428 ↑ 1.0 414 36

Materialize (cost=0.00..11.21 rows=414 width=8) (actual time=0.023..0.123 rows=414 loops=36)

77. 3.659 3.659 ↑ 1.0 414 1

Seq Scan on dim_product_conflict dmpc (cost=0.00..9.14 rows=414 width=8) (actual time=0.808..3.659 rows=414 loops=1)

78. 11,906.126 13,367.016 ↓ 1.1 3,739,006 36

Materialize (cost=0.00..94,476.09 rows=3,527,806 width=4) (actual time=0.028..371.306 rows=3,739,006 loops=36)

79. 1,460.890 1,460.890 ↓ 1.1 3,739,006 1

Seq Scan on spot_tracking_audience sta (cost=0.00..63,056.06 rows=3,527,806 width=4) (actual time=0.715..1,460.890 rows=3,739,006 loops=1)

80. 0.365 5.400 ↑ 1.0 1 36

Hash Join (cost=14.84..56.73 rows=1 width=16) (actual time=0.148..0.150 rows=1 loops=36)

  • Hash Cond: (gnm.network_sk = dn.network_sk)
  • Join Filter: ((bs.network_cd)::text = (dn.network_cd)::text)
  • Rows Removed by Join Filter: 5
81. 0.560 4.932 ↑ 77.4 5 36

Hash Join (cost=2.26..43.12 rows=387 width=10) (actual time=0.136..0.137 rows=5 loops=36)

  • Hash Cond: (gnm.nielsen_network_id = lhga.nielsen_network_id)
82. 1.096 1.096 ↑ 158.8 8 8

Seq Scan on genesis_network_map gnm (cost=0.00..22.70 rows=1,270 width=8) (actual time=0.136..0.137 rows=8 loops=8)

83. 0.180 3.276 ↑ 12.2 5 36

Hash (cost=1.50..1.50 rows=61 width=14) (actual time=0.091..0.091 rows=5 loops=36)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
84. 3.096 3.096 ↑ 12.2 5 36

Index Scan using idx_genvail_genesis_join on genesis_availability lhga (cost=0.42..1.50 rows=61 width=14) (actual time=0.085..0.086 rows=5 loops=36)

  • Index Cond: (bs.broadcast_week_date_sk = broadcast_date_sk)
85. 0.049 0.103 ↑ 1.0 337 1

Hash (cost=8.37..8.37 rows=337 width=14) (actual time=0.103..0.103 rows=337 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
86. 0.054 0.054 ↑ 1.0 337 1

Seq Scan on dim_network dn (cost=0.00..8.37 rows=337 width=14) (actual time=0.005..0.054 rows=337 loops=1)

87. 35.342 370.605 ↑ 1.0 170,520 1

Hash (cost=4,481.20..4,481.20 rows=170,520 width=8) (actual time=370.605..370.605 rows=170,520 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 2697kB
88. 335.263 335.263 ↑ 1.0 170,520 1

Seq Scan on dim_order dmdo (cost=0.00..4,481.20 rows=170,520 width=8) (actual time=0.149..335.263 rows=170,520 loops=1)

89. 7.512 26.031 ↓ 3.5 44,311 1

Hash (cost=476.00..476.00 rows=12,600 width=122) (actual time=26.031..26.031 rows=44,311 loops=1)

  • Buckets: 65536 (originally 16384) Batches: 1 (originally 1) Memory Usage: 2248kB
90. 18.519 18.519 ↓ 3.5 44,311 1

Seq Scan on stage_spot_isci ssi (cost=0.00..476.00 rows=12,600 width=122) (actual time=0.699..18.519 rows=44,311 loops=1)

91. 1.950 1.950 ↓ 0.0 0 50

Index Scan using idx_stage_lighthouse_aired_spot_delivery_usn on aired_spot_delivery asdhh (cost=0.43..0.52 rows=1 width=4) (actual time=0.039..0.039 rows=0 loops=50)

  • Index Cond: (bs.source_spot_id = usn)
  • Filter: (upper((demo_cd)::text) = 'HH'::text)
Planning time : 256.146 ms
Execution time : 68,756.357 ms