explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3J1D

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

WindowAgg (cost=14,511,242.91..14,511,350.43 rows=6,144 width=52) (actual rows= loops=)

2.          

CTE replacement_mappings

3. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.26..14,786.63 rows=214 width=8) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.84..14,603.74 rows=214 width=8) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Index Only Scan using massoccece_uk01 on massoccece m (cost=0.42..365.80 rows=10,319 width=8) (actual rows= loops=)

  • Index Cond: ((store_id = 10) AND (massoctype_id = 'REPLACEMENT'::text))
6. 0.000 0.000 ↓ 0.0

Index Scan using catentry_pk on catentry ce1 (cost=0.42..1.37 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = m.catentry_id_from)
  • Filter: (discontinuedate >= (('now'::cstring)::date - 60))
7. 0.000 0.000 ↓ 0.0

Index Only Scan using catentry_pk on catentry ce2 (cost=0.42..0.84 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = m.catentry_id_to)
8.          

CTE replacement_mappings_mirrored

9. 0.000 0.000 ↓ 0.0

HashAggregate (cost=14.98..19.26 rows=428 width=8) (actual rows= loops=)

  • Group Key: replacement_mappings.a, replacement_mappings.b
10. 0.000 0.000 ↓ 0.0

Append (cost=0.00..12.84 rows=428 width=8) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

CTE Scan on replacement_mappings (cost=0.00..4.28 rows=214 width=8) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

CTE Scan on replacement_mappings replacement_mappings_1 (cost=0.00..4.28 rows=214 width=8) (actual rows= loops=)

13.          

CTE ordersa

14. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=3,947,326.00..3,948,388.99 rows=200 width=12) (actual rows= loops=)

  • Group Key: (COALESCE(oi.catentry_id, replacement_mappings_mirrored.a))
15. 0.000 0.000 ↓ 0.0

Sort (cost=3,947,326.00..3,947,679.66 rows=141,465 width=4) (actual rows= loops=)

  • Sort Key: (COALESCE(oi.catentry_id, replacement_mappings_mirrored.a))
16. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..3,935,223.61 rows=141,465 width=4) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

CTE Scan on replacement_mappings_mirrored (cost=0.00..8.56 rows=428 width=4) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Index Scan using orderitem_idx02 on orderitem oi (cost=0.57..9,191.12 rows=331 width=4) (actual rows= loops=)

  • Index Cond: (catentry_id = replacement_mappings_mirrored.a)
  • Filter: ((status <> ALL ('{P,X,J}'::bpchar[])) AND (timecreated >= (now() - '30 days'::interval)))
19.          

CTE ordersb

20. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=3,947,326.00..3,948,388.99 rows=200 width=12) (actual rows= loops=)

  • Group Key: (COALESCE(oi_1.catentry_id, replacement_mappings_mirrored_1.b))
21. 0.000 0.000 ↓ 0.0

Sort (cost=3,947,326.00..3,947,679.66 rows=141,465 width=4) (actual rows= loops=)

  • Sort Key: (COALESCE(oi_1.catentry_id, replacement_mappings_mirrored_1.b))
22. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..3,935,223.61 rows=141,465 width=4) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

CTE Scan on replacement_mappings_mirrored replacement_mappings_mirrored_1 (cost=0.00..8.56 rows=428 width=4) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Index Scan using orderitem_idx02 on orderitem oi_1 (cost=0.57..9,191.12 rows=331 width=4) (actual rows= loops=)

  • Index Cond: (catentry_id = replacement_mappings_mirrored_1.b)
  • Filter: ((status <> ALL ('{P,X,J}'::bpchar[])) AND (timecreated >= (now() - '30 days'::interval)))
25.          

CTE replacement_amounts

26. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=13.00..54.73 rows=428 width=20) (actual rows= loops=)

  • Hash Cond: (replacement_mappings_mirrored_2.b = ordersb.ceid)
27. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=6.50..31.11 rows=428 width=16) (actual rows= loops=)

  • Hash Cond: (replacement_mappings_mirrored_2.a = ordersa.ceid)
28. 0.000 0.000 ↓ 0.0

CTE Scan on replacement_mappings_mirrored replacement_mappings_mirrored_2 (cost=0.00..8.56 rows=428 width=8) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Hash (cost=4.00..4.00 rows=200 width=12) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

CTE Scan on ordersa (cost=0.00..4.00 rows=200 width=12) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Hash (cost=4.00..4.00 rows=200 width=12) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

CTE Scan on ordersb (cost=0.00..4.00 rows=200 width=12) (actual rows= loops=)

33.          

CTE straight_amounts

34. 0.000 0.000 ↓ 0.0

HashAggregate (cost=6,587,648.17..6,588,258.25 rows=61,008 width=20) (actual rows= loops=)

  • Group Key: oi_2.store_id, oi_2.catentry_id
35. 0.000 0.000 ↓ 0.0

Index Scan using orderitem_idx15 on orderitem oi_2 (cost=10.21..6,545,208.40 rows=5,658,636 width=8) (actual rows= loops=)

  • Index Cond: (timereleased >= (now() - '30 days'::interval))
  • Filter: ((bundle_id IS NULL) AND (NOT (hashed SubPlan 6)) AND (status <> ALL ('{P,X,J}'::bpchar[])))
36.          

SubPlan (for Index Scan)

37. 0.000 0.000 ↓ 0.0

CTE Scan on replacement_amounts (cost=0.00..8.56 rows=428 width=4) (actual rows= loops=)

38.          

CTE old_bundles

39. 0.000 0.000 ↓ 0.0

CTE Scan on first_sale_of_bundle f (cost=12.90..13.62 rows=9 width=4) (actual rows= loops=)

  • Filter: (first_sale <= (now() - '60 days'::interval))
40.          

CTE first_sale_of_bundle

41. 0.000 0.000 ↓ 0.0

HashAggregate (cost=12.64..12.90 rows=26 width=12) (actual rows= loops=)

  • Group Key: orderitembundle.catentry_id
42. 0.000 0.000 ↓ 0.0

Seq Scan on orderitembundle (cost=0.00..10.43 rows=443 width=12) (actual rows= loops=)

43.          

CTE simple_bundles

44. 0.000 0.000 ↓ 0.0

Unique (cost=1,547.09..1,547.10 rows=1 width=8) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Sort (cost=1,547.09..1,547.10 rows=1 width=8) (actual rows= loops=)

  • Sort Key: m_1.catentry_id_from, ce1_1.new_catenttype
46. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.26..1,547.08 rows=1 width=8) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.84..1,544.74 rows=1 width=12) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Index Scan using catentry_idx07 on catentry ce1_1 (cost=0.42..367.74 rows=456 width=8) (actual rows= loops=)

  • Index Cond: (new_catenttype = ANY ('{MultiSKUBundle,SameSKUBundle}'::catentrytype[]))
49. 0.000 0.000 ↓ 0.0

Index Scan using massoccece_idx01 on massoccece m_1 (cost=0.42..2.57 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (catentry_id_from = ce1_1.id)
  • Filter: (massoctype_id = 'BUNDLE_EQUIVALENT'::text)
50. 0.000 0.000 ↓ 0.0

Index Scan using catentry_pk on catentry ce2_1 (cost=0.42..2.33 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = m_1.catentry_id_to)
  • Filter: (new_catenttype = 'Item'::catentrytype)
51.          

CTE bundles

52. 0.000 0.000 ↓ 0.0

Unique (cost=1,547.11..1,547.13 rows=1 width=248) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

Sort (cost=1,547.11..1,547.11 rows=1 width=248) (actual rows= loops=)

  • Sort Key: m_2.catentry_id_from, m_2.catentry_id_to, ((ce1_2.new_catenttype)::character varying(20)), ((ce2_2.new_catenttype)::character varying(20)), ce1_2.name, ce2_2.name
54. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.26..1,547.10 rows=1 width=248) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.84..1,544.74 rows=1 width=74) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

Index Scan using catentry_idx07 on catentry ce1_2 (cost=0.42..367.74 rows=456 width=70) (actual rows= loops=)

  • Index Cond: (new_catenttype = ANY ('{MultiSKUBundle,SameSKUBundle}'::catentrytype[]))
57. 0.000 0.000 ↓ 0.0

Index Scan using massoccece_idx01 on massoccece m_2 (cost=0.42..2.57 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (catentry_id_from = ce1_2.id)
  • Filter: (massoctype_id = 'BUNDLE_EQUIVALENT'::text)
58. 0.000 0.000 ↓ 0.0

Index Scan using catentry_pk on catentry ce2_2 (cost=0.42..2.33 rows=1 width=70) (actual rows= loops=)

  • Index Cond: (id = m_2.catentry_id_to)
  • Filter: (new_catenttype = 'Item'::catentrytype)
59.          

CTE new_bundles

60. 0.000 0.000 ↓ 0.0

HashAggregate (cost=0.23..0.24 rows=1 width=4) (actual rows= loops=)

  • Group Key: bundles.bundle_catentry_id
61. 0.000 0.000 ↓ 0.0

CTE Scan on bundles (cost=0.20..0.22 rows=1 width=4) (actual rows= loops=)

  • Filter: (NOT (hashed SubPlan 12))
62.          

SubPlan (for CTE Scan)

63. 0.000 0.000 ↓ 0.0

CTE Scan on old_bundles (cost=0.00..0.18 rows=9 width=4) (actual rows= loops=)

64.          

CTE raw_bundle_amounts

65. 0.000 0.000 ↓ 0.0

HashAggregate (cost=15.97..16.23 rows=26 width=12) (actual rows= loops=)

  • Group Key: oib.catentry_id
66. 0.000 0.000 ↓ 0.0

Seq Scan on orderitembundle oib (cost=0.00..13.75 rows=443 width=4) (actual rows= loops=)

  • Filter: (timecreated >= (now() - '30 days'::interval))
67.          

CTE intermediate_table

68. 0.000 0.000 ↓ 0.0

Unique (cost=2,996.09..2,996.13 rows=2 width=256) (actual rows= loops=)

69. 0.000 0.000 ↓ 0.0

Sort (cost=2,996.09..2,996.10 rows=2 width=256) (actual rows= loops=)

  • Sort Key: m_3.catentry_id_from, m_3.catentry_id_to, ((ce1_3.new_catenttype)::character varying(20)), ((ce2_3.new_catenttype)::character varying(20)), ce1_3.name, ce2_3.name, se.amount
70. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=1,547.09..2,996.08 rows=2 width=256) (actual rows= loops=)

  • Hash Cond: (se.catentry_id = m_3.catentry_id_to)
71. 0.000 0.000 ↓ 0.0

CTE Scan on straight_amounts se (cost=0.00..1,220.16 rows=61,008 width=12) (actual rows= loops=)

72. 0.000 0.000 ↓ 0.0

Hash (cost=1,547.08..1,547.08 rows=1 width=140) (actual rows= loops=)

73. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.26..1,547.08 rows=1 width=140) (actual rows= loops=)

74. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.84..1,544.74 rows=1 width=74) (actual rows= loops=)

75. 0.000 0.000 ↓ 0.0

Index Scan using catentry_idx07 on catentry ce1_3 (cost=0.42..367.74 rows=456 width=70) (actual rows= loops=)

  • Index Cond: (new_catenttype = ANY ('{MultiSKUBundle,SameSKUBundle}'::catentrytype[]))
76. 0.000 0.000 ↓ 0.0

Index Scan using massoccece_idx01 on massoccece m_3 (cost=0.42..2.57 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (catentry_id_from = ce1_3.id)
  • Filter: (massoctype_id = 'BUNDLE_EQUIVALENT'::text)
77. 0.000 0.000 ↓ 0.0

Index Scan using catentry_pk on catentry ce2_3 (cost=0.42..2.33 rows=1 width=70) (actual rows= loops=)

  • Index Cond: (id = m_3.catentry_id_to)
  • Filter: (new_catenttype = 'Item'::catentrytype)
78.          

CTE bundle_bonus

79. 0.000 0.000 ↓ 0.0

HashAggregate (cost=0.05..0.08 rows=2 width=8) (actual rows= loops=)

  • Group Key: intermediate_table.bundle_catentry_id
80. 0.000 0.000 ↓ 0.0

CTE Scan on intermediate_table (cost=0.00..0.04 rows=2 width=12) (actual rows= loops=)

81.          

CTE bundle_amounts_plus_bonuses_applied

82. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.03..0.76 rows=1 width=32) (actual rows= loops=)

  • Join Filter: (b1.bundle_catentry_id = b.bundle_catentry_id)
83. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.03..0.69 rows=1 width=20) (actual rows= loops=)

  • Join Filter: (n.bundle_catentry_id = b1.bundle_catentry_id)
84. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=0.03..0.66 rows=1 width=16) (actual rows= loops=)

  • Hash Cond: (r.bundle_catentry_id = b1.bundle_catentry_id)
85. 0.000 0.000 ↓ 0.0

CTE Scan on raw_bundle_amounts r (cost=0.00..0.52 rows=26 width=12) (actual rows= loops=)

86. 0.000 0.000 ↓ 0.0

Hash (cost=0.02..0.02 rows=1 width=8) (actual rows= loops=)

87. 0.000 0.000 ↓ 0.0

CTE Scan on simple_bundles b1 (cost=0.00..0.02 rows=1 width=8) (actual rows= loops=)

88. 0.000 0.000 ↓ 0.0

CTE Scan on new_bundles n (cost=0.00..0.02 rows=1 width=4) (actual rows= loops=)

89. 0.000 0.000 ↓ 0.0

CTE Scan on bundle_bonus b (cost=0.00..0.04 rows=2 width=8) (actual rows= loops=)

90.          

CTE all_amounts

91. 0.000 0.000 ↓ 0.0

HashAggregate (cost=2,457.48..3,071.85 rows=61,437 width=20) (actual rows= loops=)

  • Group Key: replacement_amounts_1.store_id, replacement_amounts_1.catentry_id, replacement_amounts_1.rankingtype, replacement_amounts_1.amount
92. 0.000 0.000 ↓ 0.0

Append (cost=0.00..1,843.11 rows=61,437 width=20) (actual rows= loops=)

93. 0.000 0.000 ↓ 0.0

CTE Scan on replacement_amounts replacement_amounts_1 (cost=0.00..8.56 rows=428 width=20) (actual rows= loops=)

94. 0.000 0.000 ↓ 0.0

CTE Scan on straight_amounts (cost=0.00..1,220.16 rows=61,008 width=20) (actual rows= loops=)

95. 0.000 0.000 ↓ 0.0

CTE Scan on bundle_amounts_plus_bonuses_applied (cost=0.00..0.02 rows=1 width=20) (actual rows= loops=)

96. 0.000 0.000 ↓ 0.0

Sort (cost=2,152.93..2,168.29 rows=6,144 width=40) (actual rows= loops=)

  • Sort Key: (sum(all_amounts.amount)) DESC
97. 0.000 0.000 ↓ 0.0

HashAggregate (cost=1,689.52..1,766.32 rows=6,144 width=40) (actual rows= loops=)

  • Group Key: all_amounts.store_id, all_amounts.catentry_id
98. 0.000 0.000 ↓ 0.0

CTE Scan on all_amounts (cost=0.00..1,228.74 rows=61,437 width=16) (actual rows= loops=)