explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Yrl1

Settings
# exclusive inclusive rows x rows loops node
1. 140.794 27,910.337 ↓ 7.3 45,076 1

Sort (cost=1,139,815.07..1,139,830.60 rows=6,212 width=1,590) (actual time=27,882.880..27,910.337 rows=45,076 loops=1)

  • Sort Key: inv.quantity DESC
  • Sort Method: external merge Disk: 22912kB
2. 2,003.163 27,769.543 ↓ 7.3 45,076 1

Merge Right Join (cost=1,121,574.12..1,135,132.69 rows=6,212 width=1,590) (actual time=19,716.784..27,769.543 rows=45,076 loops=1)

  • Merge Cond: (oii.item_id = i.id)
3. 4,332.361 24,668.192 ↓ 1.7 39,455 1

GroupAggregate (cost=882,800.19..893,096.49 rows=23,648 width=36) (actual time=18,939.274..24,668.192 rows=39,455 loops=1)

  • Group Key: oii.item_id
4. 6,696.244 20,335.831 ↓ 13.2 6,660,053 1

Sort (cost=882,800.19..884,057.67 rows=502,991 width=529) (actual time=18,939.015..20,335.831 rows=6,660,053 loops=1)

  • Sort Key: oii.item_id
  • Sort Method: external merge Disk: 192264kB
5. 1,390.898 13,639.587 ↓ 13.2 6,660,053 1

Nested Loop (cost=128,557.15..594,471.51 rows=502,991 width=529) (actual time=892.051..13,639.587 rows=6,660,053 loops=1)

  • Join Filter: (oi.id = oii.order_id)
6. 245.461 6,241.663 ↓ 14.2 1,001,171 1

Hash Join (cost=128,556.72..502,858.74 rows=70,748 width=528) (actual time=892.040..6,241.663 rows=1,001,171 loops=1)

  • Hash Cond: (oi.keep_status = roks.id)
7. 174.034 5,996.191 ↓ 14.2 1,001,171 1

Nested Loop (cost=128,555.65..501,884.89 rows=70,748 width=14) (actual time=892.012..5,996.191 rows=1,001,171 loops=1)

  • Join Filter: (oi.id = p.order_id)
8. 835.321 2,548.263 ↓ 11.7 1,091,298 1

Hash Join (cost=128,555.22..445,981.04 rows=92,950 width=10) (actual time=891.997..2,548.263 rows=1,091,298 loops=1)

  • Hash Cond: (ti.order_id = oi.id)
9. 239.442 821.822 ↓ 3.6 1,113,864 1

Hash Join (cost=1.06..311,338.50 rows=309,835 width=4) (actual time=0.026..821.822 rows=1,113,864 loops=1)

  • Hash Cond: (ti.label_type = rlt.id)
10. 582.371 582.371 ↑ 1.0 1,539,259 1

Seq Scan on tracking_info ti (cost=0.00..302,429.69 rows=1,549,173 width=6) (actual time=0.006..582.371 rows=1,539,259 loops=1)

  • Filter: (time_sent IS NOT NULL)
  • Rows Removed by Filter: 719674
11. 0.003 0.009 ↑ 1.0 1 1

Hash (cost=1.05..1.05 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 0.006 0.006 ↑ 1.0 1 1

Seq Scan on tracking_info_label_type rlt (cost=0.00..1.05 rows=1 width=4) (actual time=0.005..0.006 rows=1 loops=1)

  • Filter: ((value)::text = 'regular'::text)
  • Rows Removed by Filter: 3
13. 217.417 891.120 ↓ 3.3 1,328,363 1

Hash (cost=121,941.36..121,941.36 rows=403,024 width=6) (actual time=891.120..891.120 rows=1,328,363 loops=1)

  • Buckets: 131072 (originally 131072) Batches: 32 (originally 8) Memory Usage: 3073kB
14. 514.080 673.703 ↓ 3.3 1,328,363 1

Hash Join (cost=1.18..121,941.36 rows=403,024 width=6) (actual time=0.024..673.703 rows=1,328,363 loops=1)

  • Hash Cond: (oi.order_type = rot.id)
15. 159.613 159.613 ↓ 1.0 1,353,699 1

Seq Scan on "order" oi (cost=0.00..112,872.14 rows=1,343,414 width=8) (actual time=0.003..159.613 rows=1,353,699 loops=1)

16. 0.003 0.010 ↑ 1.0 3 1

Hash (cost=1.14..1.14 rows=3 width=4) (actual time=0.010..0.010 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
17. 0.007 0.007 ↑ 1.0 3 1

Seq Scan on order_type rot (cost=0.00..1.14 rows=3 width=4) (actual time=0.005..0.007 rows=3 loops=1)

  • Filter: ((value)::text = ANY ('{registration,"on demand",periodic}'::text[]))
  • Rows Removed by Filter: 7
18. 3,273.894 3,273.894 ↑ 1.0 1 1,091,298

Index Scan using ix_payment_order_id on payment p (cost=0.43..0.59 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=1,091,298)

  • Index Cond: (order_id = ti.order_id)
  • Filter: (payment_time IS NOT NULL)
  • Rows Removed by Filter: 0
19. 0.006 0.011 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=520) (actual time=0.010..0.011 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.005 0.005 ↑ 1.0 3 1

Seq Scan on order_keep_status roks (cost=0.00..1.03 rows=3 width=520) (actual time=0.005..0.005 rows=3 loops=1)

21. 6,007.026 6,007.026 ↑ 2.0 7 1,001,171

Index Scan using order_item_order_id_idx on order_item oii (cost=0.43..1.12 rows=14 width=13) (actual time=0.003..0.006 rows=7 loops=1,001,171)

  • Index Cond: (order_id = ti.order_id)
22. 30.735 1,098.188 ↓ 7.3 45,076 1

Materialize (cost=238,773.93..240,775.63 rows=6,212 width=1,882) (actual time=777.376..1,098.188 rows=45,076 loops=1)

23. 56.238 1,067.453 ↓ 7.3 45,076 1

Nested Loop Left Join (cost=238,773.93..240,760.10 rows=6,212 width=1,882) (actual time=777.372..1,067.453 rows=45,076 loops=1)

  • Join Filter: (rcs.id = c.season)
  • Rows Removed by Join Filter: 180601
24. 42.420 1,011.215 ↓ 7.3 45,076 1

Merge Left Join (cost=238,773.93..240,293.14 rows=6,212 width=1,370) (actual time=777.358..1,011.215 rows=45,076 loops=1)

  • Merge Cond: (i.id = ii.id)
25. 73.095 657.265 ↓ 7.3 45,076 1

Sort (cost=42,791.17..42,806.70 rows=6,212 width=1,338) (actual time=633.479..657.265 rows=45,076 loops=1)

  • Sort Key: i.id
  • Sort Method: external merge Disk: 16752kB
26. 11.578 584.170 ↓ 7.3 45,076 1

Hash Left Join (cost=35,910.65..38,766.78 rows=6,212 width=1,338) (actual time=335.538..584.170 rows=45,076 loops=1)

  • Hash Cond: (b.catalog_id = c.id)
27. 10.928 572.576 ↓ 7.3 45,076 1

Hash Join (cost=35,909.05..38,686.02 rows=6,212 width=1,336) (actual time=335.511..572.576 rows=45,076 loops=1)

  • Hash Cond: (b.type = rbt.id)
28. 35.288 561.636 ↓ 4.9 45,076 1

Hash Right Join (cost=35,907.96..38,588.12 rows=9,251 width=824) (actual time=335.492..561.636 rows=45,076 loops=1)

  • Hash Cond: (x_box_season.style_color_id = sc.id)
29. 1.925 281.660 ↓ 1.1 18,274 1

Subquery Scan on x_box_season (cost=20,593.73..21,982.31 rows=16,252 width=36) (actual time=90.605..281.660 rows=18,274 loops=1)

30. 173.914 279.735 ↓ 1.1 18,274 1

GroupAggregate (cost=20,593.73..21,819.79 rows=16,252 width=36) (actual time=90.603..279.735 rows=18,274 loops=1)

  • Group Key: scbi.stylecolor_id
31. 75.116 105.821 ↓ 1.0 102,829 1

Sort (cost=20,593.73..20,849.45 rows=102,291 width=184) (actual time=90.533..105.821 rows=102,829 loops=1)

  • Sort Key: scbi.stylecolor_id
  • Sort Method: external merge Disk: 2568kB
32. 23.900 30.705 ↓ 1.0 102,829 1

Hash Join (cost=1.52..2,988.93 rows=102,291 width=184) (actual time=0.037..30.705 rows=102,829 loops=1)

  • Hash Cond: (scbi.box_id = box.id)
33. 6.790 6.790 ↓ 1.0 102,829 1

Seq Scan on style_color_boxes scbi (cost=0.00..1,580.91 rows=102,291 width=8) (actual time=0.008..6.790 rows=102,829 loops=1)

34. 0.005 0.015 ↓ 1.0 24 1

Hash (cost=1.23..1.23 rows=23 width=184) (actual time=0.015..0.015 rows=24 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
35. 0.010 0.010 ↓ 1.0 24 1

Seq Scan on box (cost=0.00..1.23 rows=23 width=184) (actual time=0.005..0.010 rows=24 loops=1)

36. 28.317 244.688 ↓ 4.9 45,076 1

Hash (cost=14,267.59..14,267.59 rows=9,251 width=796) (actual time=244.688..244.688 rows=45,076 loops=1)

  • Buckets: 8192 (originally 8192) Batches: 4 (originally 2) Memory Usage: 4033kB
37. 28.680 216.371 ↓ 4.9 45,076 1

Hash Right Join (cost=12,877.17..14,267.59 rows=9,251 width=796) (actual time=184.273..216.371 rows=45,076 loops=1)

  • Hash Cond: (x_image.image_style_color_id = sc.id)
38. 1.038 43.491 ↑ 1.0 11,505 1

Subquery Scan on x_image (cost=3,586.59..3,821.11 rows=11,726 width=36) (actual time=40.020..43.491 rows=11,505 loops=1)

39. 15.329 42.453 ↑ 1.0 11,505 1

HashAggregate (cost=3,586.59..3,703.85 rows=11,726 width=36) (actual time=40.019..42.453 rows=11,505 loops=1)

  • Group Key: sciii.style_id
40. 16.381 27.124 ↑ 1.0 42,890 1

Hash Join (cost=692.05..3,371.43 rows=43,032 width=81) (actual time=4.169..27.124 rows=42,890 loops=1)

  • Hash Cond: (iii.style_color_id = sciii.style_id)
41. 6.610 6.610 ↓ 1.0 83,271 1

Seq Scan on item iii (cost=0.00..1,728.65 rows=83,265 width=4) (actual time=0.005..6.610 rows=83,271 loops=1)

42. 1.787 4.133 ↑ 1.0 11,722 1

Hash (cost=544.80..544.80 rows=11,780 width=81) (actual time=4.133..4.133 rows=11,722 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1422kB
43. 2.346 2.346 ↑ 1.0 11,722 1

Seq Scan on style_color_image sciii (cost=0.00..544.80 rows=11,780 width=81) (actual time=0.003..2.346 rows=11,722 loops=1)

44. 28.673 144.200 ↓ 4.9 45,076 1

Hash (cost=8,279.94..8,279.94 rows=9,251 width=764) (actual time=144.200..144.200 rows=45,076 loops=1)

  • Buckets: 8192 (originally 8192) Batches: 4 (originally 2) Memory Usage: 4033kB
45. 11.633 115.527 ↓ 4.9 45,076 1

Hash Join (cost=5,061.16..8,279.94 rows=9,251 width=764) (actual time=44.547..115.527 rows=45,076 loops=1)

  • Hash Cond: (b.brand_id = br.id)
46. 15.000 103.827 ↓ 3.6 45,076 1

Hash Join (cost=5,053.12..8,132.79 rows=12,427 width=757) (actual time=44.458..103.827 rows=45,076 loops=1)

  • Hash Cond: (i.id = inv.item_id)
47. 11.972 72.829 ↓ 2.1 47,974 1

Hash Join (cost=2,163.08..4,946.43 rows=22,941 width=753) (actual time=28.222..72.829 rows=47,974 loops=1)

  • Hash Cond: (sc.color = color.id)
48. 25.386 60.851 ↓ 1.3 49,827 1

Hash Join (cost=2,161.81..4,575.83 rows=37,311 width=239) (actual time=28.201..60.851 rows=49,827 loops=1)

  • Hash Cond: (i.style_color_id = sc.id)
49. 7.330 7.330 ↓ 1.0 83,271 1

Seq Scan on item i (cost=0.00..1,728.65 rows=83,265 width=46) (actual time=0.004..7.330 rows=83,271 loops=1)

50. 6.420 28.135 ↓ 1.0 15,256 1

Hash (cost=1,977.28..1,977.28 rows=14,762 width=193) (actual time=28.135..28.135 rows=15,256 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 2495kB
51. 9.150 21.715 ↓ 1.0 15,256 1

Hash Join (cost=686.72..1,977.28 rows=14,762 width=193) (actual time=9.874..21.715 rows=15,256 loops=1)

  • Hash Cond: (sc.body_id = b.id)
52. 2.735 2.735 ↑ 1.0 32,942 1

Seq Scan on style_color sc (cost=0.00..1,019.42 rows=32,942 width=53) (actual time=0.005..2.735 rows=32,942 loops=1)

53. 2.574 9.830 ↓ 1.5 7,857 1

Hash (cost=621.42..621.42 rows=5,224 width=148) (actual time=9.830..9.830 rows=7,857 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 924kB
54. 1.637 7.256 ↓ 1.5 7,857 1

Hash Join (cost=11.01..621.42 rows=5,224 width=148) (actual time=0.147..7.256 rows=7,857 loops=1)

  • Hash Cond: (b.source_id = rbs.id)
55. 1.645 5.576 ↓ 1.5 7,857 1

Hash Join (cost=6.68..545.26 rows=5,224 width=140) (actual time=0.091..5.576 rows=7,857 loops=1)

  • Hash Cond: (b.category_id = rbc.id)
56. 2.723 3.914 ↓ 1.0 7,857 1

Hash Join (cost=4.58..461.72 rows=7,785 width=66) (actual time=0.057..3.914 rows=7,857 loops=1)

  • Hash Cond: (b.sub_category_id = rbsc.id)
57. 1.147 1.147 ↓ 1.0 11,729 1

Seq Scan on body b (cost=0.00..335.58 rows=11,658 width=60) (actual time=0.002..1.147 rows=11,729 loops=1)

58. 0.023 0.044 ↑ 1.0 159 1

Hash (cost=2.59..2.59 rows=159 width=14) (actual time=0.044..0.044 rows=159 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
59. 0.021 0.021 ↑ 1.0 159 1

Seq Scan on body_subcategory rbsc (cost=0.00..2.59 rows=159 width=14) (actual time=0.003..0.021 rows=159 loops=1)

60. 0.005 0.017 ↑ 1.0 49 1

Hash (cost=1.49..1.49 rows=49 width=82) (actual time=0.017..0.017 rows=49 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
61. 0.012 0.012 ↑ 1.0 49 1

Seq Scan on body_category rbc (cost=0.00..1.49 rows=49 width=82) (actual time=0.002..0.012 rows=49 loops=1)

62. 0.024 0.043 ↑ 1.0 148 1

Hash (cost=2.48..2.48 rows=148 width=16) (actual time=0.043..0.043 rows=148 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
63. 0.019 0.019 ↑ 1.0 148 1

Seq Scan on body_source rbs (cost=0.00..2.48 rows=148 width=16) (actual time=0.002..0.019 rows=148 loops=1)

64. 0.004 0.006 ↑ 1.0 12 1

Hash (cost=1.12..1.12 rows=12 width=520) (actual time=0.006..0.006 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
65. 0.002 0.002 ↑ 1.0 12 1

Seq Scan on color (cost=0.00..1.12 rows=12 width=520) (actual time=0.002..0.002 rows=12 loops=1)

66. 6.537 15.998 ↑ 1.0 45,093 1

Hash (cost=2,326.22..2,326.22 rows=45,105 width=8) (actual time=15.998..15.998 rows=45,093 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2274kB
67. 9.461 9.461 ↑ 1.0 45,093 1

Seq Scan on inventory inv (cost=0.00..2,326.22 rows=45,105 width=8) (actual time=0.010..9.461 rows=45,093 loops=1)

  • Filter: (warehouse = 2)
  • Rows Removed by Filter: 7885
68. 0.040 0.067 ↓ 1.0 225 1

Hash (cost=5.24..5.24 rows=224 width=15) (actual time=0.067..0.067 rows=225 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
69. 0.027 0.027 ↓ 1.0 225 1

Seq Scan on brand br (cost=0.00..5.24 rows=224 width=15) (actual time=0.004..0.027 rows=225 loops=1)

70. 0.004 0.012 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=520) (actual time=0.011..0.012 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
71. 0.008 0.008 ↑ 1.0 4 1

Seq Scan on body_type rbt (cost=0.00..1.04 rows=4 width=520) (actual time=0.007..0.008 rows=4 loops=1)

72. 0.008 0.016 ↓ 1.0 28 1

Hash (cost=1.27..1.27 rows=27 width=10) (actual time=0.016..0.016 rows=28 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
73. 0.008 0.008 ↓ 1.0 28 1

Seq Scan on catalog c (cost=0.00..1.27 rows=27 width=10) (actual time=0.006..0.008 rows=28 loops=1)

74. 11.586 311.530 ↓ 1.2 49,603 1

Materialize (cost=195,982.76..197,335.77 rows=41,631 width=36) (actual time=143.834..311.530 rows=49,603 loops=1)

75. 143.083 299.944 ↓ 1.2 49,603 1

GroupAggregate (cost=195,982.76..196,815.38 rows=41,631 width=36) (actual time=143.829..299.944 rows=49,603 loops=1)

  • Group Key: ii.id
76. 51.675 156.861 ↓ 1.6 65,021 1

Sort (cost=195,982.76..196,086.84 rows=41,631 width=36) (actual time=143.796..156.861 rows=65,021 loops=1)

  • Sort Key: ii.id
  • Sort Method: external merge Disk: 1264kB
77. 20.990 105.186 ↓ 1.6 65,021 1

Hash Join (cost=2,367.38..192,788.55 rows=41,631 width=36) (actual time=21.745..105.186 rows=65,021 loops=1)

  • Hash Cond: (((bi.gender)::text = psm.gender) AND (((unnest(ii.size_map)))::text = psm.item_size))
78. 54.892 84.171 ↑ 127.0 65,553 1

Hash Join (cost=2,365.43..46,661.79 rows=8,326,200 width=38) (actual time=21.705..84.171 rows=65,553 loops=1)

  • Hash Cond: (ii.style_color_id = sci.id)
79. 7.630 7.630 ↓ 1.0 83,271 1

Seq Scan on item ii (cost=0.00..1,728.65 rows=83,265 width=32) (actual time=0.004..7.630 rows=83,271 loops=1)

80. 4.707 21.649 ↑ 1.0 32,941 1

Hash (cost=1,953.67..1,953.67 rows=32,941 width=6) (actual time=21.649..21.649 rows=32,941 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 1735kB
81. 10.324 16.942 ↑ 1.0 32,941 1

Hash Join (cost=481.30..1,953.67 rows=32,941 width=6) (actual time=3.811..16.942 rows=32,941 loops=1)

  • Hash Cond: (sci.body_id = bi.id)
82. 2.888 2.888 ↑ 1.0 32,942 1

Seq Scan on style_color sci (cost=0.00..1,019.42 rows=32,942 width=8) (actual time=0.003..2.888 rows=32,942 loops=1)

83. 1.603 3.730 ↓ 1.0 11,729 1

Hash (cost=335.58..335.58 rows=11,658 width=6) (actual time=3.730..3.730 rows=11,729 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 587kB
84. 2.127 2.127 ↓ 1.0 11,729 1

Seq Scan on body bi (cost=0.00..335.58 rows=11,658 width=6) (actual time=0.002..2.127 rows=11,729 loops=1)

85. 0.012 0.025 ↑ 1.0 38 1

Hash (cost=1.38..1.38 rows=38 width=96) (actual time=0.025..0.025 rows=38 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
86. 0.013 0.013 ↑ 1.0 38 1

Seq Scan on size_mapping psm (cost=0.00..1.38 rows=38 width=96) (actual time=0.006..0.013 rows=38 loops=1)

87. 0.000 0.000 ↑ 1.0 5 45,076

Materialize (cost=0.00..1.07 rows=5 width=520) (actual time=0.000..0.000 rows=5 loops=45,076)

88. 0.006 0.006 ↑ 1.0 5 1

Seq Scan on catalog_season rcs (cost=0.00..1.05 rows=5 width=520) (actual time=0.005..0.006 rows=5 loops=1)

Planning time : 8.380 ms
Execution time : 27,970.306 ms