explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HeKo

Settings
# exclusive inclusive rows x rows loops node
1. 11.451 1,100,219.693 ↓ 10.0 61,464 1

WindowAgg (cost=14,853,121.62..14,853,229.14 rows=6,144 width=52) (actual time=1,100,201.993..1,100,219.693 rows=61,464 loops=1)

  • Buffers: shared hit=5,803,244 read=3,563,842 dirtied=1,717 written=2,490
2.          

CTE replacement_mappings

3. 0.024 45.669 ↑ 13.4 16 1

Nested Loop Left Join (cost=1.26..14,786.63 rows=214 width=8) (actual time=16.272..45.669 rows=16 loops=1)

  • Buffers: shared hit=14,972 read=35
4. 1.582 45.469 ↑ 13.4 16 1

Nested Loop (cost=0.84..14,603.74 rows=214 width=8) (actual time=16.237..45.469 rows=16 loops=1)

  • Buffers: shared hit=14,907 read=35
5. 18.834 18.834 ↑ 2.9 3,579 1

Index Only Scan using massoccece_uk01 on massoccece m (cost=0.42..365.80 rows=10,319 width=8) (actual time=2.053..18.834 rows=3,579 loops=1)

  • Index Cond: ((store_id = 10) AND (massoctype_id = 'REPLACEMENT'::text))
  • Heap Fetches: 957
  • Buffers: shared hit=571 read=35
6. 25.053 25.053 ↓ 0.0 0 3,579

Index Scan using catentry_pk on catentry ce1 (cost=0.42..1.37 rows=1 width=4) (actual time=0.007..0.007 rows=0 loops=3,579)

  • Index Cond: (id = m.catentry_id_from)
  • Filter: (discontinuedate >= (('now'::cstring)::date - 60))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=14,336
7. 0.176 0.176 ↑ 1.0 1 16

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

  • Index Cond: (id = m.catentry_id_to)
  • Heap Fetches: 16
  • Buffers: shared hit=65
8.          

CTE replacement_mappings_mirrored

9. 0.088 45.774 ↑ 13.4 32 1

HashAggregate (cost=14.98..19.26 rows=428 width=8) (actual time=45.713..45.774 rows=32 loops=1)

  • Group Key: replacement_mappings.a, replacement_mappings.b
  • Buffers: shared hit=14,972 read=35
10. 0.004 45.686 ↑ 13.4 32 1

Append (cost=0.00..12.84 rows=428 width=8) (actual time=16.274..45.686 rows=32 loops=1)

  • Buffers: shared hit=14,972 read=35
11. 45.679 45.679 ↑ 13.4 16 1

CTE Scan on replacement_mappings (cost=0.00..4.28 rows=214 width=8) (actual time=16.273..45.679 rows=16 loops=1)

  • Buffers: shared hit=14,972 read=35
12. 0.003 0.003 ↑ 13.4 16 1

CTE Scan on replacement_mappings replacement_mappings_1 (cost=0.00..4.28 rows=214 width=8) (actual time=0.001..0.003 rows=16 loops=1)

13.          

CTE ordersa

14. 0.314 170,897.049 ↑ 8.3 24 1

GroupAggregate (cost=3,947,300.20..3,948,361.45 rows=200 width=12) (actual time=170,896.459..170,897.049 rows=24 loops=1)

  • Group Key: (COALESCE(oi.catentry_id, replacement_mappings_mirrored.a))
  • Buffers: shared hit=11,876 read=273,916 dirtied=6
15. 4.081 170,896.735 ↑ 28.8 4,899 1

Sort (cost=3,947,300.20..3,947,653.28 rows=141,233 width=4) (actual time=170,896.454..170,896.735 rows=4,899 loops=1)

  • Sort Key: (COALESCE(oi.catentry_id, replacement_mappings_mirrored.a))
  • Sort Method: quicksort Memory: 422kB
  • Buffers: shared hit=11,876 read=273,916 dirtied=6
16. 2.396 170,892.654 ↑ 28.8 4,899 1

Nested Loop (cost=0.57..3,935,219.33 rows=141,233 width=4) (actual time=0.668..170,892.654 rows=4,899 loops=1)

  • Buffers: shared hit=11,873 read=273,916 dirtied=6
17. 0.114 0.114 ↑ 13.4 32 1

CTE Scan on replacement_mappings_mirrored (cost=0.00..8.56 rows=428 width=4) (actual time=0.000..0.114 rows=32 loops=1)

18. 170,890.144 170,890.144 ↑ 2.2 153 32

Index Scan using orderitem_idx02 on orderitem oi (cost=0.57..9,191.12 rows=330 width=4) (actual time=978.340..5,340.317 rows=153 loops=32)

  • Index Cond: (catentry_id = replacement_mappings_mirrored.a)
  • Filter: ((status <> ALL ('{P,X,J}'::bpchar[])) AND (timecreated >= (now() - '30 days'::interval)))
  • Rows Removed by Filter: 8,778
  • Buffers: shared hit=11,873 read=273,916 dirtied=6
19.          

CTE ordersb

20. 0.346 630.651 ↑ 8.3 24 1

GroupAggregate (cost=3,947,300.20..3,948,361.45 rows=200 width=12) (actual time=630.066..630.651 rows=24 loops=1)

  • Group Key: (COALESCE(oi_1.catentry_id, replacement_mappings_mirrored_1.b))
  • Buffers: shared hit=285,736
21. 1.257 630.305 ↑ 28.8 4,899 1

Sort (cost=3,947,300.20..3,947,653.28 rows=141,233 width=4) (actual time=630.063..630.305 rows=4,899 loops=1)

  • Sort Key: (COALESCE(oi_1.catentry_id, replacement_mappings_mirrored_1.b))
  • Sort Method: quicksort Memory: 422kB
  • Buffers: shared hit=285,736
22. 2.085 629.048 ↑ 28.8 4,899 1

Nested Loop (cost=0.57..3,935,219.33 rows=141,233 width=4) (actual time=7.811..629.048 rows=4,899 loops=1)

  • Buffers: shared hit=285,736
23. 0.019 0.019 ↑ 13.4 32 1

CTE Scan on replacement_mappings_mirrored replacement_mappings_mirrored_1 (cost=0.00..8.56 rows=428 width=4) (actual time=0.001..0.019 rows=32 loops=1)

24. 626.944 626.944 ↑ 2.2 153 32

Index Scan using orderitem_idx02 on orderitem oi_1 (cost=0.57..9,191.12 rows=330 width=4) (actual time=3.333..19.592 rows=153 loops=32)

  • Index Cond: (catentry_id = replacement_mappings_mirrored_1.b)
  • Filter: ((status <> ALL ('{P,X,J}'::bpchar[])) AND (timecreated >= (now() - '30 days'::interval)))
  • Rows Removed by Filter: 8,778
  • Buffers: shared hit=285,736
25.          

CTE replacement_amounts

26. 0.017 171,573.504 ↑ 13.4 32 1

Hash Left Join (cost=13.00..54.73 rows=428 width=20) (actual time=171,573.483..171,573.504 rows=32 loops=1)

  • Hash Cond: (replacement_mappings_mirrored_2.b = ordersb.ceid)
  • Buffers: shared hit=312,584 read=273,951 dirtied=6
27. 0.034 170,942.817 ↑ 13.4 32 1

Hash Left Join (cost=6.50..31.11 rows=428 width=16) (actual time=170,942.802..170,942.817 rows=32 loops=1)

  • Hash Cond: (replacement_mappings_mirrored_2.a = ordersa.ceid)
  • Buffers: shared hit=26,848 read=273,951 dirtied=6
28. 45.718 45.718 ↑ 13.4 32 1

CTE Scan on replacement_mappings_mirrored replacement_mappings_mirrored_2 (cost=0.00..8.56 rows=428 width=8) (actual time=45.714..45.718 rows=32 loops=1)

  • Buffers: shared hit=14,972 read=35
29. 0.007 170,897.065 ↑ 8.3 24 1

Hash (cost=4.00..4.00 rows=200 width=12) (actual time=170,897.065..170,897.065 rows=24 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=11,876 read=273,916 dirtied=6
30. 170,897.058 170,897.058 ↑ 8.3 24 1

CTE Scan on ordersa (cost=0.00..4.00 rows=200 width=12) (actual time=170,896.462..170,897.058 rows=24 loops=1)

  • Buffers: shared hit=11,876 read=273,916 dirtied=6
31. 0.010 630.670 ↑ 8.3 24 1

Hash (cost=4.00..4.00 rows=200 width=12) (actual time=630.670..630.670 rows=24 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=285,736
32. 630.660 630.660 ↑ 8.3 24 1

CTE Scan on ordersb (cost=0.00..4.00 rows=200 width=12) (actual time=630.070..630.660 rows=24 loops=1)

  • Buffers: shared hit=285,736
33.          

CTE straight_amounts

34. 7,951.773 928,419.283 ↓ 1.0 61,012 1

HashAggregate (cost=6,929,581.97..6,930,192.05 rows=61,008 width=20) (actual time=928,400.865..928,419.283 rows=61,012 loops=1)

  • Group Key: oi_2.store_id, oi_2.catentry_id
  • Buffers: shared hit=5,478,528 read=3,289,881 dirtied=1,711 written=2,490
35. 2,318.915 920,467.510 ↓ 2.4 13,805,249 1

Hash Anti Join (cost=14.49..6,887,216.65 rows=5,648,709 width=8) (actual time=0.947..920,467.510 rows=13,805,249 loops=1)

  • Hash Cond: (oi_2.catentry_id = ra.catentry_id)
  • Buffers: shared hit=5,478,528 read=3,289,881 dirtied=1,711 written=2,490
36. 918,148.585 918,148.585 ↓ 1.2 13,808,740 1

Index Scan using orderitem_idx15 on orderitem oi_2 (cost=0.58..6,498,147.91 rows=11,297,418 width=8) (actual time=0.927..918,148.585 rows=13,808,740 loops=1)

  • Index Cond: (timereleased >= (now() - '30 days'::interval))
  • Filter: ((bundle_id IS NULL) AND (status <> ALL ('{P,X,J}'::bpchar[])))
  • Rows Removed by Filter: 118,930
  • Buffers: shared hit=5,478,528 read=3,289,881 dirtied=1,711 written=2,490
37. 0.002 0.010 ↑ 13.4 32 1

Hash (cost=8.56..8.56 rows=428 width=4) (actual time=0.010..0.010 rows=32 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
38. 0.008 0.008 ↑ 13.4 32 1

CTE Scan on replacement_amounts ra (cost=0.00..8.56 rows=428 width=4) (actual time=0.002..0.008 rows=32 loops=1)

39.          

CTE old_bundles

40. 0.167 0.167 ↓ 0.0 0 1

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

  • Filter: (first_sale <= (now() - '60 days'::interval))
  • Rows Removed by Filter: 27
  • Buffers: shared hit=6
41.          

CTE first_sale_of_bundle

42. 0.092 0.153 ↓ 1.0 27 1

HashAggregate (cost=12.64..12.90 rows=26 width=12) (actual time=0.150..0.153 rows=27 loops=1)

  • Group Key: orderitembundle.catentry_id
  • Buffers: shared hit=6
43. 0.061 0.061 ↓ 1.0 454 1

Seq Scan on orderitembundle (cost=0.00..10.43 rows=443 width=12) (actual time=0.009..0.061 rows=454 loops=1)

  • Buffers: shared hit=6
44.          

CTE simple_bundles

45. 0.047 10.690 ↓ 420.0 420 1

Unique (cost=1,547.09..1,547.10 rows=1 width=8) (actual time=10.614..10.690 rows=420 loops=1)

  • Buffers: shared hit=4,026 read=9
46. 0.216 10.643 ↓ 438.0 438 1

Sort (cost=1,547.09..1,547.10 rows=1 width=8) (actual time=10.612..10.643 rows=438 loops=1)

  • Sort Key: m_1.catentry_id_from, ce1_1.new_catenttype
  • Sort Method: quicksort Memory: 45kB
  • Buffers: shared hit=4,026 read=9
47. 0.000 10.427 ↓ 438.0 438 1

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

  • Buffers: shared hit=4,024 read=9
48. 0.337 7.875 ↓ 438.0 438 1

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

  • Buffers: shared hit=2,270 read=9
49. 6.062 6.062 ↓ 1.1 492 1

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

  • Index Cond: (new_catenttype = ANY ('{MultiSKUBundle,SameSKUBundle}'::catentrytype[]))
  • Buffers: shared hit=371 read=7
50. 1.476 1.476 ↑ 1.0 1 492

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

  • Index Cond: (catentry_id_from = ce1_1.id)
  • Filter: (massoctype_id = 'BUNDLE_EQUIVALENT'::text)
  • Buffers: shared hit=1,899 read=2
51. 2.628 2.628 ↑ 1.0 1 438

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

  • Index Cond: (id = m_1.catentry_id_to)
  • Filter: (new_catenttype = 'Item'::catentrytype)
  • Buffers: shared hit=1,754
52.          

CTE bundles

53. 0.077 3.960 ↓ 438.0 438 1

Unique (cost=1,547.11..1,547.13 rows=1 width=248) (actual time=3.847..3.960 rows=438 loops=1)

  • Buffers: shared hit=4,043
54. 0.263 3.883 ↓ 438.0 438 1

Sort (cost=1,547.11..1,547.11 rows=1 width=248) (actual time=3.845..3.883 rows=438 loops=1)

  • 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
  • Sort Method: quicksort Memory: 140kB
  • Buffers: shared hit=4,043
55. 0.135 3.620 ↓ 438.0 438 1

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

  • Buffers: shared hit=4,040
56. 0.000 1.733 ↓ 438.0 438 1

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

  • Buffers: shared hit=2,279
57. 0.795 0.795 ↓ 1.1 492 1

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

  • Index Cond: (new_catenttype = ANY ('{MultiSKUBundle,SameSKUBundle}'::catentrytype[]))
  • Buffers: shared hit=378
58. 0.984 0.984 ↑ 1.0 1 492

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

  • Index Cond: (catentry_id_from = ce1_2.id)
  • Filter: (massoctype_id = 'BUNDLE_EQUIVALENT'::text)
  • Buffers: shared hit=1,901
59. 1.752 1.752 ↑ 1.0 1 438

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

  • Index Cond: (id = m_2.catentry_id_to)
  • Filter: (new_catenttype = 'Item'::catentrytype)
  • Buffers: shared hit=1,754
60.          

CTE new_bundles

61. 0.123 4.386 ↓ 420.0 420 1

HashAggregate (cost=0.23..0.24 rows=1 width=4) (actual time=4.343..4.386 rows=420 loops=1)

  • Group Key: bundles.bundle_catentry_id
  • Buffers: shared hit=4,049
62. 4.096 4.263 ↓ 438.0 438 1

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

  • Filter: (NOT (hashed SubPlan 11))
  • Buffers: shared hit=4,049
63.          

SubPlan (for CTE Scan)

64. 0.167 0.167 ↓ 0.0 0 1

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

  • Buffers: shared hit=6
65.          

CTE raw_bundle_amounts

66. 0.076 1.077 ↓ 1.0 27 1

HashAggregate (cost=15.97..16.23 rows=26 width=12) (actual time=1.072..1.077 rows=27 loops=1)

  • Group Key: oib.catentry_id
  • Buffers: shared hit=18 read=1
67. 1.001 1.001 ↓ 1.0 454 1

Seq Scan on orderitembundle oib (cost=0.00..13.75 rows=443 width=4) (actual time=0.829..1.001 rows=454 loops=1)

  • Filter: (timecreated >= (now() - '30 days'::interval))
  • Buffers: shared hit=18 read=1
68.          

CTE intermediate_table

69. 0.096 12.123 ↓ 219.0 438 1

Unique (cost=2,996.09..2,996.13 rows=2 width=256) (actual time=11.929..12.123 rows=438 loops=1)

  • Buffers: shared hit=4,036
70. 0.345 12.027 ↓ 219.0 438 1

Sort (cost=2,996.09..2,996.10 rows=2 width=256) (actual time=11.927..12.027 rows=438 loops=1)

  • 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
  • Sort Method: quicksort Memory: 144kB
  • Buffers: shared hit=4,036
71. 4.341 11.682 ↓ 219.0 438 1

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

  • Hash Cond: (se.catentry_id = m_3.catentry_id_to)
  • Buffers: shared hit=4,033
72. 4.116 4.116 ↓ 1.0 61,012 1

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

73. 0.134 3.225 ↓ 438.0 438 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 87kB
  • Buffers: shared hit=4,033
74. 0.142 3.091 ↓ 438.0 438 1

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

  • Buffers: shared hit=4,033
75. 0.000 1.635 ↓ 438.0 438 1

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

  • Buffers: shared hit=2,279
76. 0.666 0.666 ↓ 1.1 492 1

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

  • Index Cond: (new_catenttype = ANY ('{MultiSKUBundle,SameSKUBundle}'::catentrytype[]))
  • Buffers: shared hit=378
77. 0.984 0.984 ↑ 1.0 1 492

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

  • Index Cond: (catentry_id_from = ce1_3.id)
  • Filter: (massoctype_id = 'BUNDLE_EQUIVALENT'::text)
  • Buffers: shared hit=1,901
78. 1.314 1.314 ↑ 1.0 1 438

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

  • Index Cond: (id = m_3.catentry_id_to)
  • Filter: (new_catenttype = 'Item'::catentrytype)
  • Buffers: shared hit=1,754
79.          

CTE bundle_bonus

80. 0.366 12.599 ↓ 210.0 420 1

HashAggregate (cost=0.05..0.08 rows=2 width=8) (actual time=12.415..12.599 rows=420 loops=1)

  • Group Key: intermediate_table.bundle_catentry_id
  • Buffers: shared hit=4,036
81. 12.233 12.233 ↓ 219.0 438 1

CTE Scan on intermediate_table (cost=0.00..0.04 rows=2 width=12) (actual time=11.930..12.233 rows=438 loops=1)

  • Buffers: shared hit=4,036
82.          

CTE bundle_amounts_plus_bonuses_applied

83. 12.077 74.005 ↓ 420.0 420 1

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

  • Join Filter: (b1.bundle_catentry_id = b.bundle_catentry_id)
  • Rows Removed by Join Filter: 175,980
  • Buffers: shared hit=12,129 read=10
84. 11.299 38.408 ↓ 420.0 420 1

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

  • Join Filter: (n.bundle_catentry_id = b1.bundle_catentry_id)
  • Rows Removed by Join Filter: 175,980
  • Buffers: shared hit=8,093 read=10
85. 0.100 11.989 ↓ 420.0 420 1

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

  • Hash Cond: (r.bundle_catentry_id = b1.bundle_catentry_id)
  • Buffers: shared hit=4,044 read=10
86. 1.087 1.087 ↓ 1.0 27 1

CTE Scan on raw_bundle_amounts r (cost=0.00..0.52 rows=26 width=12) (actual time=1.075..1.087 rows=27 loops=1)

  • Buffers: shared hit=18 read=1
87. 0.052 10.802 ↓ 420.0 420 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 25kB
  • Buffers: shared hit=4,026 read=9
88. 10.750 10.750 ↓ 420.0 420 1

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

  • Buffers: shared hit=4,026 read=9
89. 15.120 15.120 ↓ 420.0 420 420

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

  • Buffers: shared hit=4,049
90. 23.520 23.520 ↓ 210.0 420 420

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

  • Buffers: shared hit=4,036
91.          

CTE all_amounts

92. 38.419 1,100,122.068 ↓ 1.0 61,464 1

HashAggregate (cost=2,457.48..3,071.85 rows=61,437 width=20) (actual time=1,100,103.191..1,100,122.068 rows=61,464 loops=1)

  • Group Key: replacement_amounts.store_id, replacement_amounts.catentry_id, replacement_amounts.rankingtype, replacement_amounts.amount
  • Buffers: shared hit=5,803,241 read=3,563,842 dirtied=1,717 written=2,490
93. 3.976 1,100,083.649 ↓ 1.0 61,464 1

Append (cost=0.00..1,843.11 rows=61,437 width=20) (actual time=171,573.485..1,100,083.649 rows=61,464 loops=1)

  • Buffers: shared hit=5,803,241 read=3,563,842 dirtied=1,717 written=2,490
94. 171,573.514 171,573.514 ↑ 13.4 32 1

CTE Scan on replacement_amounts (cost=0.00..8.56 rows=428 width=20) (actual time=171,573.485..171,573.514 rows=32 loops=1)

  • Buffers: shared hit=312,584 read=273,951 dirtied=6
95. 928,431.965 928,431.965 ↓ 1.0 61,012 1

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

  • Buffers: shared hit=5,478,528 read=3,289,881 dirtied=1,711 written=2,490
96. 74.194 74.194 ↓ 420.0 420 1

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

  • Buffers: shared hit=12,129 read=10
97. 26.113 1,100,208.242 ↓ 10.0 61,464 1

Sort (cost=2,152.93..2,168.29 rows=6,144 width=40) (actual time=1,100,201.978..1,100,208.242 rows=61,464 loops=1)

  • Sort Key: (sum(all_amounts.amount)) DESC
  • Sort Method: quicksort Memory: 4,418kB
  • Buffers: shared hit=5,803,244 read=3,563,842 dirtied=1,717 written=2,490
98. 47.183 1,100,182.129 ↓ 10.0 61,464 1

HashAggregate (cost=1,689.52..1,766.32 rows=6,144 width=40) (actual time=1,100,160.570..1,100,182.129 rows=61,464 loops=1)

  • Group Key: all_amounts.store_id, all_amounts.catentry_id
  • Buffers: shared hit=5,803,241 read=3,563,842 dirtied=1,717 written=2,490
99. 1,100,134.946 1,100,134.946 ↓ 1.0 61,464 1

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

  • Buffers: shared hit=5,803,241 read=3,563,842 dirtied=1,717 written=2,490
Planning time : 6.069 ms
Execution time : 1,100,228.416 ms