explain.depesz.com

PostgreSQL's explain analyze made readable

Result: A4e7

Settings
# exclusive inclusive rows x rows loops node
1. 0.033 10,275.999 ↑ 1.0 50 1

Limit (cost=120,949.91..238,570.20 rows=50 width=608) (actual time=3,933.318..10,275.999 rows=50 loops=1)

2. 0.403 10,275.966 ↑ 14,545,848.0 50 1

Nested Loop Left Join (cost=120,949.91..1,710,886,981,368.03 rows=727,292,402 width=608) (actual time=3,933.315..10,275.966 rows=50 loops=1)

3. 109.207 10,243.713 ↑ 63,964,240.5 10 1

Nested Loop Left Join (cost=120,935.07..1,684,007,881,346.68 rows=639,642,405 width=498) (actual time=3,929.111..10,243.713 rows=10 loops=1)

  • Join Filter: (bs.order_id = prnt.order_id)
  • Rows Removed by Join Filter: 1553003
4. 0.065 9,963.496 ↑ 15,698,740.2 10 1

Nested Loop Left Join (cost=120,935.07..1,107,102,723,710.11 rows=156,987,402 width=494) (actual time=3,864.320..9,963.496 rows=10 loops=1)

5. 29.367 9,941.401 ↑ 15,698,740.2 10 1

Nested Loop Left Join (cost=120,934.64..1,107,020,095,932.33 rows=156,987,402 width=494) (actual time=3,862.392..9,941.401 rows=10 loops=1)

  • Join Filter: (bs.source_spot_id = ssi.admiral_spot_id)
  • Rows Removed by Join Filter: 443110
6. 0.586 9,883.414 ↑ 15,698,740.2 10 1

Hash Left Join (cost=120,934.64..1,077,349,476,446.83 rows=156,987,402 width=376) (actual time=3,848.418..9,883.414 rows=10 loops=1)

  • Hash Cond: (bs.source_spot_id = sta.source_spot_id)
7. 0.284 8,839.112 ↑ 15,698,740.2 10 1

Nested Loop Left Join (cost=0.00..1,008,105,375,182.08 rows=156,987,402 width=376) (actual time=2,804.155..8,839.112 rows=10 loops=1)

  • Join Filter: (bs.secondary_product_conflict_id = dmpc.product_conflict_id)
  • Rows Removed by Join Filter: 4130
8. 0.223 8,838.558 ↑ 15,698,740.2 10 1

Nested Loop Left Join (cost=0.00..1,007,132,826,870.36 rows=156,987,402 width=376) (actual time=2,804.028..8,838.558 rows=10 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: 1951
9. 0.086 8,838.175 ↑ 15,698,740.2 10 1

Nested Loop Left Join (cost=0.00..1,006,544,909,045.19 rows=156,987,402 width=372) (actual time=2,804.002..8,838.175 rows=10 loops=1)

  • Join Filter: (bs.team_id = dmtm.team_id)
  • Rows Removed by Join Filter: 990
10. 3.972 8,837.989 ↑ 15,698,740.2 10 1

Nested Loop Left Join (cost=0.00..1,005,923,221,219.47 rows=156,987,402 width=372) (actual time=2,803.921..8,837.989 rows=10 loops=1)

  • Join Filter: (bs.asr_user_id = dmasr.user_id)
  • Rows Removed by Join Filter: 55424
11. 2.986 8,805.357 ↑ 15,698,740.2 10 1

Nested Loop Left Join (cost=0.00..989,997,633,278.97 rows=156,987,402 width=372) (actual time=2,778.067..8,805.357 rows=10 loops=1)

  • Join Filter: (bs.ae_user_id = dmae.user_id)
  • Rows Removed by Join Filter: 43164
12. 7.944 8,799.141 ↑ 15,698,740.2 10 1

Nested Loop Left Join (cost=0.00..974,072,040,064.11 rows=156,987,402 width=372) (actual time=2,776.470..8,799.141 rows=10 loops=1)

  • Join Filter: (bs.selling_title_id = dmst.selling_title_id)
  • Rows Removed by Join Filter: 100982
13. 0.157 8,772.547 ↑ 15,698,740.2 10 1

Nested Loop Left Join (cost=0.00..874,242,180,087.51 rows=156,987,402 width=372) (actual time=2,776.323..8,772.547 rows=10 loops=1)

  • Join Filter: (lower(btrim(replace((bs.division_cd)::text, ' '::text, ''::text))) = (dmdv.division)::text)
  • Rows Removed by Join Filter: 120
14. 0.022 8,772.370 ↑ 6,680,315.0 10 1

Nested Loop Left Join (cost=0.00..873,535,736,760.39 rows=66,803,150 width=372) (actual time=2,776.299..8,772.370 rows=10 loops=1)

  • Join Filter: (bs.order_revenue_type_id = dmrt.revenue_type_id)
15. 0.023 8,772.338 ↑ 6,680,315.0 10 1

Nested Loop Left Join (cost=0.00..873,512,609,508.69 rows=66,803,150 width=372) (actual time=2,776.293..8,772.338 rows=10 loops=1)

  • Join Filter: (bs.spot_revenue_type_id = dmspt.spot_revenue_type_id)
  • Rows Removed by Join Filter: 1
16. 0.034 8,772.305 ↑ 6,680,315.0 10 1

Nested Loop Left Join (cost=0.00..873,273,111,885.56 rows=66,803,150 width=372) (actual time=2,776.288..8,772.305 rows=10 loops=1)

  • Join Filter: (bs.rate_card_type_id = dmrct.rate_card_type_id)
  • Rows Removed by Join Filter: 132
17. 0.019 8,772.251 ↑ 6,680,315.0 10 1

Nested Loop Left Join (cost=0.00..872,943,432,272.57 rows=66,803,150 width=334) (actual time=2,776.275..8,772.251 rows=10 loops=1)

  • Join Filter: (bs.property_id = dmdp.property_id)
18. 0.260 8,772.222 ↑ 6,680,315.0 10 1

Nested Loop Left Join (cost=0.00..872,523,569,698.50 rows=66,803,150 width=334) (actual time=2,776.268..8,772.222 rows=10 loops=1)

  • Join Filter: ((bs.network_cd)::text = (dmdn.network_cd)::text)
  • Rows Removed by Join Filter: 3025
19. 15.601 8,771.722 ↑ 6,680,315.0 10 1

Nested Loop Left Join (cost=0.00..872,185,879,766.04 rows=66,803,150 width=330) (actual time=2,776.259..8,771.722 rows=10 loops=1)

  • Join Filter: ((bs.lh_deal_cd)::text = (dmdd.source_deal_id)::text)
  • Rows Removed by Join Filter: 228466
20. 0.035 8,738.301 ↑ 6,680,315.0 10 1

Nested Loop Left Join (cost=0.00..841,248,211,568.60 rows=66,803,150 width=336) (actual time=2,765.437..8,738.301 rows=10 loops=1)

  • Join Filter: (bs.order_type_id = dot.order_type_id)
  • Rows Removed by Join Filter: 28
21. 0.036 8,738.256 ↑ 2,520,873.6 10 1

Nested Loop Left Join (cost=0.00..841,047,802,100.77 rows=25,208,736 width=330) (actual time=2,765.431..8,738.256 rows=10 loops=1)

  • Join Filter: (bs.order_status_id = dos.order_status_id)
  • Rows Removed by Join Filter: 56
22. 103.589 8,738.200 ↑ 1,050,364.0 10 1

Nested Loop Left Join (cost=0.00..840,972,175,876.77 rows=10,503,640 width=330) (actual time=2,765.419..8,738.200 rows=10 loops=1)

  • Join Filter: (bs.order_id = dmo.source_order_id)
  • Rows Removed by Join Filter: 1608218
23. 46.042 8,457.451 ↑ 1,050,002.5 10 1

Nested Loop Left Join (cost=0.00..805,368,686,199.27 rows=10,500,025 width=322) (actual time=2,706.166..8,457.451 rows=10 loops=1)

  • Join Filter: (bs.brand_id = dmbd.brand_id)
  • Rows Removed by Join Filter: 710449
24. 4.073 8,165.969 ↑ 1,050,002.5 10 1

Nested Loop Left Join (cost=0.00..787,852,121,244.83 rows=10,500,025 width=322) (actual time=2,503.371..8,165.969 rows=10 loops=1)

  • Join Filter: (bs.agency_id = dmag.agency_id)
  • Rows Removed by Join Filter: 64220
25. 5.515 8,110.916 ↑ 1,050,002.5 10 1

Nested Loop Left Join (cost=0.00..785,104,369,310.47 rows=10,500,025 width=322) (actual time=2,469.310..8,110.916 rows=10 loops=1)

  • Join Filter: (bs.advertiser_id = dmad.advertiser_id)
  • Rows Removed by Join Filter: 86183
26. 2,457.342 8,021.831 ↑ 1,050,002.5 10 1

Nested Loop Left Join (cost=0.00..780,594,503,012.07 rows=10,500,025 width=322) (actual time=2,386.606..8,021.831 rows=10 loops=1)

  • Join Filter: (bs.source_spot_id = sds.source_spot_id)
  • Rows Removed by Join Filter: 34563427
27. 6.099 6.099 ↑ 1,050,002.5 10 1

Seq Scan on blended_spots bs (cost=0.00..615,107.25 rows=10,500,025 width=312) (actual time=6.087..6.099 rows=10 loops=1)

28. 3,344.402 5,558.390 ↑ 1.1 3,456,343 10

Materialize (cost=0.00..111,957.09 rows=3,739,006 width=14) (actual time=0.069..555.839 rows=3,456,343 loops=10)

29. 2,213.988 2,213.988 ↑ 1.0 3,739,006 1

Seq Scan on stage_deal_to_spot sds (cost=0.00..75,005.06 rows=3,739,006 width=14) (actual time=0.618..2,213.988 rows=3,739,006 loops=1)

30. 6.573 83.570 ↑ 3.3 8,619 10

Materialize (cost=0.00..756.53 rows=28,635 width=8) (actual time=0.015..8.357 rows=8,619 loops=10)

31. 76.997 76.997 ↑ 1.0 28,497 1

Seq Scan on dim_advertiser dmad (cost=0.00..613.35 rows=28,635 width=8) (actual time=0.148..76.997 rows=28,497 loops=1)

32. 3.980 50.980 ↑ 2.7 6,423 10

Materialize (cost=0.00..463.71 rows=17,447 width=8) (actual time=0.002..5.098 rows=6,423 loops=10)

33. 47.000 47.000 ↑ 1.5 11,438 1

Seq Scan on dim_agency dmag (cost=0.00..376.47 rows=17,447 width=8) (actual time=0.009..47.000 rows=11,438 loops=1)

34. 68.521 245.440 ↑ 1.6 71,046 10

Materialize (cost=0.00..2,839.26 rows=111,217 width=8) (actual time=0.027..24.544 rows=71,046 loops=10)

35. 176.919 176.919 ↑ 1.0 110,403 1

Seq Scan on dim_brand dmbd (cost=0.00..2,283.17 rows=111,217 width=8) (actual time=0.175..176.919 rows=110,403 loops=1)

36. 154.651 177.160 ↑ 1.1 160,823 10

Materialize (cost=0.00..6,166.80 rows=170,520 width=16) (actual time=0.021..17.716 rows=160,823 loops=10)

37. 22.509 22.509 ↑ 1.0 170,520 1

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

38. 0.015 0.020 ↑ 68.6 7 10

Materialize (cost=0.00..17.20 rows=480 width=8) (actual time=0.001..0.002 rows=7 loops=10)

39. 0.005 0.005 ↑ 68.6 7 1

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

40. 0.006 0.010 ↑ 132.5 4 10

Materialize (cost=0.00..17.95 rows=530 width=12) (actual time=0.000..0.001 rows=4 loops=10)

41. 0.004 0.004 ↑ 132.5 4 1

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

42. 12.886 17.820 ↑ 1.4 22,847 10

Materialize (cost=0.00..1,715.12 rows=30,875 width=25) (actual time=0.014..1.782 rows=22,847 loops=10)

43. 4.934 4.934 ↑ 1.0 30,875 1

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

44. 0.198 0.240 ↑ 1.1 304 10

Materialize (cost=0.00..10.06 rows=337 width=14) (actual time=0.001..0.024 rows=304 loops=10)

45. 0.042 0.042 ↑ 1.0 337 1

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

46. 0.006 0.010 ↑ 420.0 1 10

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

47. 0.004 0.004 ↑ 420.0 1 1

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

48. 0.015 0.020 ↑ 23.6 14 10

Materialize (cost=0.00..14.95 rows=330 width=46) (actual time=0.001..0.002 rows=14 loops=10)

49. 0.005 0.005 ↑ 19.4 17 1

Seq Scan on dim_rate_card_type dmrct (cost=0.00..13.30 rows=330 width=46) (actual time=0.003..0.005 rows=17 loops=1)

50. 0.006 0.010 ↑ 240.0 1 10

Materialize (cost=0.00..13.60 rows=240 width=8) (actual time=0.000..0.001 rows=1 loops=10)

51. 0.004 0.004 ↑ 120.0 2 1

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

52. 0.006 0.010 ↑ 24.0 1 10

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

53. 0.004 0.004 ↑ 24.0 1 1

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

54. 0.016 0.020 ↑ 39.2 12 10

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

55. 0.004 0.004 ↑ 39.2 12 1

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

56. 8.594 18.650 ↑ 4.2 10,099 10

Materialize (cost=0.00..1,184.92 rows=42,395 width=8) (actual time=0.014..1.865 rows=10,099 loops=10)

57. 10.056 10.056 ↑ 1.2 36,234 1

Seq Scan on dim_selling_title dmst (cost=0.00..972.95 rows=42,395 width=8) (actual time=0.132..10.056 rows=36,234 loops=1)

58. 2.414 3.230 ↑ 1.6 4,317 10

Materialize (cost=0.00..239.46 rows=6,764 width=8) (actual time=0.001..0.323 rows=4,317 loops=10)

59. 0.816 0.816 ↑ 1.2 5,809 1

Seq Scan on dim_user dmae (cost=0.00..205.64 rows=6,764 width=8) (actual time=0.005..0.816 rows=5,809 loops=1)

60. 3.099 28.660 ↑ 1.2 5,543 10

Materialize (cost=0.00..239.46 rows=6,764 width=8) (actual time=0.001..2.866 rows=5,543 loops=10)

61. 25.561 25.561 ↑ 1.0 6,694 1

Seq Scan on dim_user dmasr (cost=0.00..205.64 rows=6,764 width=8) (actual time=0.002..25.561 rows=6,694 loops=1)

62. 0.066 0.100 ↑ 2.6 100 10

Materialize (cost=0.00..7.98 rows=265 width=8) (actual time=0.001..0.010 rows=100 loops=10)

63. 0.034 0.034 ↑ 1.1 247 1

Seq Scan on dim_team dmtm (cost=0.00..6.65 rows=265 width=8) (actual time=0.007..0.034 rows=247 loops=1)

64. 0.127 0.160 ↑ 1.1 196 10

Materialize (cost=0.00..5.21 rows=214 width=10) (actual time=0.001..0.016 rows=196 loops=10)

65. 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.005..0.033 rows=214 loops=1)

66. 0.221 0.270 ↑ 1.0 414 10

Materialize (cost=0.00..11.21 rows=414 width=8) (actual time=0.001..0.027 rows=414 loops=10)

67. 0.049 0.049 ↑ 1.0 414 1

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

68. 567.832 1,043.716 ↓ 1.1 3,739,006 1

Hash (cost=63,056.06..63,056.06 rows=3,527,806 width=4) (actual time=1,043.716..1,043.716 rows=3,739,006 loops=1)

  • Buckets: 131072 Batches: 64 Memory Usage: 3084kB
69. 475.884 475.884 ↓ 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.024..475.884 rows=3,739,006 loops=1)

70. 22.507 28.620 ↓ 3.5 44,311 10

Materialize (cost=0.00..539.00 rows=12,600 width=122) (actual time=0.002..2.862 rows=44,311 loops=10)

71. 6.113 6.113 ↓ 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.011..6.113 rows=44,311 loops=1)

72. 22.030 22.030 ↓ 0.0 0 10

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=2.203..2.203 rows=0 loops=10)

  • Index Cond: (bs.source_spot_id = usn)
  • Filter: (upper((demo_cd)::text) = 'HH'::text)
  • Rows Removed by Filter: 0
73. 145.521 171.010 ↑ 1.2 155,301 10

Materialize (cost=0.00..4,761.85 rows=184,790 width=12) (actual time=0.013..17.101 rows=155,301 loops=10)

74. 25.489 25.489 ↑ 1.1 170,518 1

Seq Scan on parents prnt (cost=0.00..2,934.90 rows=184,790 width=12) (actual time=0.016..25.489 rows=170,518 loops=1)

75. 0.238 31.850 ↓ 5.0 5 10

Hash Join (cost=14.84..56.73 rows=1 width=16) (actual time=3.169..3.185 rows=5 loops=10)

  • Hash Cond: (gnm.network_sk = dn.network_sk)
  • Join Filter: ((bs.network_cd)::text = (dn.network_cd)::text)
  • Rows Removed by Join Filter: 37
76. 0.240 31.400 ↑ 9.2 42 10

Hash Join (cost=2.26..43.12 rows=387 width=10) (actual time=3.132..3.140 rows=42 loops=10)

  • Hash Cond: (gnm.nielsen_network_id = lhga.nielsen_network_id)
77. 0.050 0.050 ↑ 158.8 8 10

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

78. 0.150 31.110 ↑ 1.3 46 10

Hash (cost=1.50..1.50 rows=61 width=14) (actual time=3.111..3.111 rows=46 loops=10)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
79. 30.960 30.960 ↑ 1.3 46 10

Index Scan using idx_genvail_genesis_join on genesis_availability lhga (cost=0.42..1.50 rows=61 width=14) (actual time=2.379..3.096 rows=46 loops=10)

  • Index Cond: (bs.broadcast_week_date_sk = broadcast_date_sk)
80. 0.132 0.212 ↑ 1.0 337 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
81. 0.080 0.080 ↑ 1.0 337 1

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

Planning time : 65.986 ms
Execution time : 10,336.626 ms