explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hmYg

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

XN Hash Left Join DS_DIST_OUTER (cost=2,752,601,156,036.75..2,785,852,613,083.81 rows=2,696,079 width=9,846) (actual rows= loops=)

  • Outer Dist Key: "outer".sailthru_email
  • Hash Cond: (("outer".sailthru_email)::text = ("inner".tailored_email)::text)
2. 0.000 0.000 ↓ 0.0

XN Hash Right Join DS_DIST_INNER (cost=2,752,123,357,504.77..2,752,126,746,842.02 rows=11,391 width=9,330) (actual rows= loops=)

  • Inner Dist Key: distinct_subs.user_email
  • Hash Cond: (("outer".sailthru_email)::text = ("inner".user_email)::text)
3. 0.000 0.000 ↓ 0.0

XN Subquery Scan atb_received (cost=0.00..566,996.93 rows=2,278,113 width=400) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

XN Unique (cost=0.00..544,215.80 rows=2,278,113 width=25) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

XN Seq Scan on profile (cost=0.00..435,372.64 rows=43,537,264 width=25) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

XN Hash (cost=2,752,123,357,504.76..2,752,123,357,504.76 rows=1 width=8,930) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

XN Subquery Scan distinct_subs (cost=2,752,123,275,847.99..2,752,123,357,504.76 rows=1 width=8,930) (actual rows= loops=)

  • Filter: (((email)::text = 'lisa@barkbox.com'::text) AND (subs_rank = 1) AND (((city)::text = 'APO'::text) OR ((city)::text = 'DPO'::text) OR ((city)::text = 'FPO'::text) OR ((country)::text = 'CA'::text) OR ((country)::text = 'US'::text)) AND ((email)::text <> 'emma@barkbox.com'::text) AND ((email)::text <> 'lisa@barkbox.com'::text) AND ((email)::text <> 'showdogs@barkbox.com'::text))
8. 0.000 0.000 ↓ 0.0

XN Window (cost=2,752,123,275,847.99..2,752,123,337,448.71 rows=573,030 width=2,101) (actual rows= loops=)

  • Partition: s.subscription_id
  • Order: s.id
9. 0.000 0.000 ↓ 0.0

XN Sort (cost=2,752,123,275,847.99..2,752,123,277,280.56 rows=573,030 width=2,101) (actual rows= loops=)

  • Sort Key: s.subscription_id, s.id
10. 0.000 0.000 ↓ 0.0

XN Network (cost=1,000,930,970,371.37..1,752,123,221,042.68 rows=573,030 width=2,101) (actual rows= loops=)

  • Distribute
11. 0.000 0.000 ↓ 0.0

XN Hash Left Join DS_BCAST_INNER (cost=1,000,930,970,371.37..1,752,123,221,042.68 rows=573,030 width=2,101) (actual rows= loops=)

  • Hash Cond: ("outer".subscription_id = "inner".subscription_id)
12. 0.000 0.000 ↓ 0.0

XN Hash Left Join DS_DIST_OUTER (cost=67,508.61..725,784,023,800.24 rows=573,030 width=2,069) (actual rows= loops=)

  • Outer Dist Key: "outer".user_id
  • Hash Cond: ("outer".user_id = "inner".id)
13. 0.000 0.000 ↓ 0.0

XN Hash Left Join DS_BCAST_INNER (cost=32,405.92..361,909,902,883.17 rows=573,030 width=2,024) (actual rows= loops=)

  • Hash Cond: ("outer".current_plan_id = "inner".id)
14. 0.000 0.000 ↓ 0.0

XN Hash Left Join DS_DIST_OUTER (cost=32,391.50..361,725,249,975.58 rows=573,030 width=2,024) (actual rows= loops=)

  • Outer Dist Key: s.subscription_id
  • Hash Cond: ("outer".subscription_id = "inner".id)
15. 0.000 0.000 ↓ 0.0

XN Seq Scan on atb_worksheets s (cost=0.00..5,730.30 rows=573,030 width=2,010) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

XN Hash (cost=25,913.20..25,913.20 rows=2,591,320 width=18) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

XN Seq Scan on subscriptions ss (cost=0.00..25,913.20 rows=2,591,320 width=18) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

XN Hash (cost=11.54..11.54 rows=1,154 width=8) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

XN Seq Scan on plans (cost=0.00..11.54 rows=1,154 width=8) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

XN Hash (cost=28,082.15..28,082.15 rows=2,808,215 width=49) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

XN Seq Scan on users u (cost=0.00..28,082.15 rows=2,808,215 width=49) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

XN Hash (cost=1,000,930,902,718.58..1,000,930,902,718.58 rows=57,671 width=36) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

XN Subquery Scan udd (cost=1,000,930,892,428.72..1,000,930,902,718.58 rows=57,671 width=36) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

XN Hash Left Join DS_DIST_INNER (cost=1,000,930,892,428.72..1,000,930,902,141.87 rows=57,671 width=20) (actual rows= loops=)

  • Inner Dist Key: p.subscription_id
  • Hash Cond: ("outer".subscription_id = "inner".subscription_id)
25. 0.000 0.000 ↓ 0.0

XN Subquery Scan o (cost=644,817.29..645,538.18 rows=57,671 width=12) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

XN HashAggregate (cost=644,817.29..644,961.47 rows=57,671 width=4) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

XN Seq Scan on monthly_addons box (cost=0.00..626,428.45 rows=3,677,768 width=4) (actual rows= loops=)

  • Filter: (item_id = 12367)
28. 0.000 0.000 ↓ 0.0

XN Hash (cost=1,000,930,247,611.43..1,000,930,247,611.43 rows=1 width=12) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

XN Subquery Scan p (cost=1,000,930,247,611.42..1,000,930,247,611.43 rows=1 width=12) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

XN HashAggregate (cost=1,000,930,247,611.42..1,000,930,247,611.42 rows=1 width=524) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

XN Subquery Scan order_details (cost=1,000,930,247,611.40..1,000,930,247,611.41 rows=1 width=524) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

XN HashAggregate (cost=1,000,930,247,611.40..1,000,930,247,611.40 rows=1 width=142) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

XN Hash Right Join DS_DIST_INNER (cost=1,000,930,156,806.65..1,000,930,247,611.37 rows=1 width=142) (actual rows= loops=)

  • Inner Dist Key: o.orderable_id
  • Hash Cond: ("outer".id = "inner".orderable_id)
34. 0.000 0.000 ↓ 0.0

XN Seq Scan on subscriptions s (cost=0.00..25,913.20 rows=2,591,320 width=53) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

XN Hash (cost=1,000,930,156,806.65..1,000,930,156,806.65 rows=1 width=93) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

XN Hash Right Join DS_DIST_INNER (cost=1,000,930,134,213.12..1,000,930,156,806.65 rows=1 width=93) (actual rows= loops=)

  • Inner Dist Key: o.id
  • Hash Cond: ("outer".order_id = "inner".id)
37. 0.000 0.000 ↓ 0.0

XN Subquery Scan o (cost=1,000,928,649,740.42..1,000,928,649,831.44 rows=200 width=36) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

XN GroupAggregate (cost=1,000,928,649,740.42..1,000,928,649,829.44 rows=200 width=19) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

XN Sort (cost=1,000,928,649,740.42..1,000,928,649,769.09 rows=11,470 width=19) (actual rows= loops=)

  • Sort Key: x.order_id, (i.sku)::text
40. 0.000 0.000 ↓ 0.0

XN Result (cost=856,943,422.36..928,648,967.02 rows=11,470 width=19) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

XN Network (cost=856,943,422.36..928,648,967.02 rows=11,470 width=19) (actual rows= loops=)

  • Distribute
42. 0.000 0.000 ↓ 0.0

XN Hash Join DS_DIST_INNER (cost=856,943,422.36..928,648,967.02 rows=11,470 width=19) (actual rows= loops=)

  • Inner Dist Key: li.source_id
  • Hash Cond: ("outer".id = "inner".source_id)
43. 0.000 0.000 ↓ 0.0

XN Seq Scan on items i (cost=0.00..162.63 rows=16,263 width=15) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

XN Hash (cost=856,943,393.69..856,943,393.69 rows=11,470 width=12) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

XN Hash Join DS_BCAST_INNER (cost=74,778,819.25..856,943,393.69 rows=11,470 width=12) (actual rows= loops=)

  • Hash Cond: ("outer".order_id = "inner".order_id)
46. 0.000 0.000 ↓ 0.0

XN Seq Scan on line_items li (cost=0.00..447,119.28 rows=29,191,672 width=12) (actual rows= loops=)

  • Filter: (((source_type)::text <> 'ShippingMethod'::text) AND ((source_type)::text <> 'Plan'::text))
47. 0.000 0.000 ↓ 0.0

XN Hash (cost=74,778,806.15..74,778,806.15 rows=5,239 width=4) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

XN Subquery Scan x (cost=48,352,010.53..74,778,806.15 rows=5,239 width=4) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

XN Unique (cost=48,352,010.53..74,778,753.76 rows=5,239 width=4) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

XN Hash Join DS_DIST_INNER (cost=48,352,010.53..74,778,740.66 rows=5,239 width=4) (actual rows= loops=)

  • Inner Dist Key: o.user_id
  • Hash Cond: ("outer".id = "inner".user_id)
51. 0.000 0.000 ↓ 0.0

XN Seq Scan on users u (cost=0.00..28,082.15 rows=2,808,215 width=4) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

XN Hash (cost=48,351,997.44..48,351,997.44 rows=5,239 width=8) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

XN Hash Join DS_DIST_INNER (cost=27,643,459.48..48,351,997.44 rows=5,239 width=8) (actual rows= loops=)

  • Inner Dist Key: li.order_id
  • Hash Cond: ("outer".id = "inner".order_id)
54. 0.000 0.000 ↓ 0.0

XN Seq Scan on orders o (cost=0.00..249,937.78 rows=24,993,778 width=8) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

XN Hash (cost=27,643,446.39..27,643,446.39 rows=5,239 width=4) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

XN Hash Join DS_DIST_INNER (cost=596,172.14..27,643,446.39 rows=5,239 width=4) (actual rows= loops=)

  • Inner Dist Key: li.source_id
  • Hash Cond: ("outer".id = "inner".source_id)
57. 0.000 0.000 ↓ 0.0

XN Seq Scan on items i (cost=0.00..162.63 rows=16,263 width=4) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

XN Hash (cost=596,159.04..596,159.04 rows=5,239 width=8) (actual rows= loops=)

59. 0.000 0.000 ↓ 0.0

XN Seq Scan on line_items li (cost=0.00..596,159.04 rows=5,239 width=8) (actual rows= loops=)

  • Filter: (((origin)::text = 'Add On Order'::text) AND (sale_price > 0.00) AND ((source_type)::text <> 'ShippingMethod'::text) AND ((source_type)::text <> 'Plan'::text))
60. 0.000 0.000 ↓ 0.0

XN Hash (cost=1,484,472.70..1,484,472.70 rows=1 width=61) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

XN Hash Join DS_DIST_INNER (cost=1,407,537.85..1,484,472.70 rows=1 width=61) (actual rows= loops=)

  • Inner Dist Key: o.user_id
  • Hash Cond: ("outer".id = "inner".user_id)
62. 0.000 0.000 ↓ 0.0

XN Seq Scan on users u (cost=0.00..28,082.15 rows=2,808,215 width=30) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

XN Hash (cost=1,407,537.85..1,407,537.85 rows=1 width=35) (actual rows= loops=)

64. 0.000 0.000 ↓ 0.0

XN Hash Join DS_DIST_INNER (cost=836,427.83..1,407,537.85 rows=1 width=35) (actual rows= loops=)

  • Inner Dist Key: li.order_id
  • Hash Cond: ("outer".id = "inner".order_id)
65. 0.000 0.000 ↓ 0.0

XN Seq Scan on orders o (cost=0.00..249,937.78 rows=24,993,778 width=20) (actual rows= loops=)

66. 0.000 0.000 ↓ 0.0

XN Hash (cost=836,427.83..836,427.83 rows=1 width=19) (actual rows= loops=)

67. 0.000 0.000 ↓ 0.0

XN Hash Join DS_BCAST_INNER (cost=203.29..836,427.83 rows=1 width=19) (actual rows= loops=)

  • Hash Cond: ("outer".source_id = "inner".id)
68. 0.000 0.000 ↓ 0.0

XN Seq Scan on line_items li (cost=0.00..596,159.04 rows=5,239 width=12) (actual rows= loops=)

  • Filter: (((origin)::text = 'Add On Order'::text) AND (sale_price > 0.00) AND ((source_type)::text <> 'ShippingMethod'::text) AND ((source_type)::text <> 'Plan'::text))
69. 0.000 0.000 ↓ 0.0

XN Hash (cost=203.29..203.29 rows=1 width=15) (actual rows= loops=)

70. 0.000 0.000 ↓ 0.0

XN Seq Scan on items i (cost=0.00..203.29 rows=1 width=15) (actual rows= loops=)

  • Filter: ((sku)::text = 'U-DD'::text)
71. 0.000 0.000 ↓ 0.0

XN Hash (cost=477,798,413.64..477,798,413.64 rows=47,337 width=516) (actual rows= loops=)

72. 0.000 0.000 ↓ 0.0

XN Subquery Scan tailored_subs (cost=300,220,413.72..477,798,413.64 rows=47,337 width=516) (actual rows= loops=)

73. 0.000 0.000 ↓ 0.0

XN Unique (cost=300,220,413.72..477,797,940.27 rows=47,337 width=26) (actual rows= loops=)

74. 0.000 0.000 ↓ 0.0

XN Hash Join DS_DIST_INNER (cost=300,220,413.72..477,797,821.93 rows=47,337 width=26) (actual rows= loops=)

  • Inner Dist Key: s.user_id
  • Hash Cond: ("outer".id = "inner".user_id)
75. 0.000 0.000 ↓ 0.0

XN Seq Scan on users u (cost=0.00..28,082.15 rows=2,808,215 width=30) (actual rows= loops=)

76. 0.000 0.000 ↓ 0.0

XN Hash (cost=300,220,295.38..300,220,295.38 rows=47,337 width=4) (actual rows= loops=)

77. 0.000 0.000 ↓ 0.0

XN Hash Join DS_DIST_INNER (cost=591.71..300,220,295.38 rows=47,337 width=4) (actual rows= loops=)

  • Inner Dist Key: t.subscription_id
  • Hash Cond: ("outer".id = "inner".subscription_id)
78. 0.000 0.000 ↓ 0.0

XN Seq Scan on subscriptions s (cost=0.00..25,913.20 rows=2,591,320 width=8) (actual rows= loops=)

79. 0.000 0.000 ↓ 0.0

XN Hash (cost=473.37..473.37 rows=47,337 width=4) (actual rows= loops=)

80. 0.000 0.000 ↓ 0.0

XN Seq Scan on current_tailored_subs t (cost=0.00..473.37 rows=47,337 width=4) (actual rows= loops=)