explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yXwR

Settings
# exclusive inclusive rows x rows loops node
1. 109.633 2,717.901 ↓ 7.3 45,076 1

Sort (cost=243,974.07..243,989.60 rows=6,212 width=1,462) (actual time=2,692.911..2,717.901 rows=45,076 loops=1)

  • Sort Key: inv.quantity DESC
  • Sort Method: external merge Disk: 20960kB
2. 1,714.101 2,608.268 ↓ 7.3 45,076 1

Hash Left Join (cost=235,648.48..239,631.19 rows=6,212 width=1,462) (actual time=718.939..2,608.268 rows=45,076 loops=1)

  • Hash Cond: (b.catalog_id = c.id)
3. 13.087 894.130 ↓ 7.3 45,076 1

Hash Join (cost=235,645.61..238,803.72 rows=6,212 width=1,368) (actual time=718.770..894.130 rows=45,076 loops=1)

  • Hash Cond: (b.type = rbt.id)
4. 60.919 881.032 ↓ 4.9 45,076 1

Hash Right Join (cost=235,644.52..238,705.82 rows=9,251 width=856) (actual time=718.752..881.032 rows=45,076 loops=1)

  • Hash Cond: (x_age_group.item_id = i.id)
5. 5.058 237.652 ↓ 1.2 49,603 1

Subquery Scan on x_age_group (cost=195,982.76..197,231.69 rows=41,631 width=36) (actual time=136.115..237.652 rows=49,603 loops=1)

6. 87.985 232.594 ↓ 1.2 49,603 1

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

  • Group Key: ii.id
7. 45.348 144.609 ↓ 1.6 65,021 1

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

  • Sort Key: ii.id
  • Sort Method: external merge Disk: 1264kB
8. 19.833 99.261 ↓ 1.6 65,021 1

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

  • Hash Cond: (((bi.gender)::text = psm.gender) AND (((unnest(ii.size_map)))::text = psm.item_size))
9. 51.842 79.407 ↑ 127.0 65,553 1

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

  • Hash Cond: (ii.style_color_id = sci.id)
10. 6.918 6.918 ↓ 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..6.918 rows=83,271 loops=1)

11. 4.491 20.647 ↑ 1.0 32,941 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 1735kB
12. 9.906 16.156 ↑ 1.0 32,941 1

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

  • Hash Cond: (sci.body_id = bi.id)
13. 2.806 2.806 ↑ 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.004..2.806 rows=32,942 loops=1)

14. 1.473 3.444 ↓ 1.0 11,729 1

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

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

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

16. 0.007 0.021 ↑ 1.0 38 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
17. 0.014 0.014 ↑ 1.0 38 1

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

18. 34.655 582.461 ↓ 4.9 45,076 1

Hash (cost=38,588.12..38,588.12 rows=9,251 width=824) (actual time=582.461..582.461 rows=45,076 loops=1)

  • Buckets: 8192 (originally 8192) Batches: 8 (originally 2) Memory Usage: 4033kB
19. 31.523 547.806 ↓ 4.9 45,076 1

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

  • Hash Cond: (x_box_season.style_color_id = sc.id)
20. 2.063 272.022 ↓ 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=88.206..272.022 rows=18,274 loops=1)

21. 167.599 269.959 ↓ 1.1 18,274 1

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

  • Group Key: scbi.stylecolor_id
22. 71.267 102.360 ↓ 1.0 102,829 1

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

  • Sort Key: scbi.stylecolor_id
  • Sort Method: external merge Disk: 2568kB
23. 24.727 31.093 ↓ 1.0 102,829 1

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

  • Hash Cond: (scbi.box_id = box.id)
24. 6.353 6.353 ↓ 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.009..6.353 rows=102,829 loops=1)

25. 0.006 0.013 ↓ 1.0 24 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
26. 0.007 0.007 ↓ 1.0 24 1

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

27. 27.313 244.261 ↓ 4.9 45,076 1

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

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

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

  • Hash Cond: (x_image.image_style_color_id = sc.id)
29. 1.031 42.736 ↑ 1.0 11,505 1

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

30. 14.667 41.705 ↑ 1.0 11,505 1

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

  • Group Key: sciii.style_id
31. 15.972 27.038 ↑ 1.0 42,890 1

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

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

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

33. 2.183 4.485 ↑ 1.0 11,722 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 1422kB
34. 2.302 2.302 ↑ 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.302 rows=11,722 loops=1)

35. 28.756 144.612 ↓ 4.9 45,076 1

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

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

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

  • Hash Cond: (b.brand_id = br.id)
37. 14.258 103.949 ↓ 3.6 45,076 1

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

  • Hash Cond: (i.id = inv.item_id)
38. 12.140 73.699 ↓ 2.1 47,974 1

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

  • Hash Cond: (sc.color = color.id)
39. 25.776 61.550 ↓ 1.3 49,827 1

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

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

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

41. 6.433 28.004 ↓ 1.0 15,256 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 2495kB
42. 9.221 21.571 ↓ 1.0 15,256 1

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

  • Hash Cond: (sc.body_id = b.id)
43. 2.778 2.778 ↑ 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.002..2.778 rows=32,942 loops=1)

44. 2.595 9.572 ↓ 1.5 7,857 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 924kB
45. 1.644 6.977 ↓ 1.5 7,857 1

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

  • Hash Cond: (b.source_id = rbs.id)
46. 1.577 5.291 ↓ 1.5 7,857 1

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

  • Hash Cond: (b.category_id = rbc.id)
47. 2.605 3.699 ↓ 1.0 7,857 1

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

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

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

49. 0.029 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
50. 0.015 0.015 ↑ 1.0 159 1

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

51. 0.008 0.015 ↑ 1.0 49 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
52. 0.007 0.007 ↑ 1.0 49 1

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

53. 0.027 0.042 ↑ 1.0 148 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
54. 0.015 0.015 ↑ 1.0 148 1

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

55. 0.005 0.009 ↑ 1.0 12 1

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

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

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

57. 6.556 15.992 ↑ 1.0 45,093 1

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

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

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

  • Filter: (warehouse = 2)
  • Rows Removed by Filter: 7885
59. 0.034 0.070 ↓ 1.0 225 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
60. 0.036 0.036 ↓ 1.0 225 1

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

61. 0.004 0.011 ↑ 1.0 4 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
62. 0.007 0.007 ↑ 1.0 4 1

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

63. 0.008 0.037 ↓ 1.0 28 1

Hash (cost=2.53..2.53 rows=27 width=522) (actual time=0.037..0.037 rows=28 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
64. 0.014 0.029 ↓ 1.0 28 1

Hash Left Join (cost=1.11..2.53 rows=27 width=522) (actual time=0.022..0.029 rows=28 loops=1)

  • Hash Cond: (c.season = rcs.id)
65. 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)

66. 0.004 0.007 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=520) (actual time=0.007..0.007 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
67. 0.003 0.003 ↑ 1.0 5 1

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

Planning time : 4.882 ms
Execution time : 2,742.282 ms