explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PkmC

Settings
# exclusive inclusive rows x rows loops node
1. 105.129 2,716.280 ↓ 43.3 44,779 1

Sort (cost=231,765.72..231,768.30 rows=1,034 width=1,462) (actual time=2,690.970..2,716.280 rows=44,779 loops=1)

  • Sort Key: inv.quantity DESC
  • Sort Method: external merge Disk: 20,824kB
2. 1,726.382 2,611.151 ↓ 43.3 44,779 1

Hash Right Join (cost=230,135.24..231,713.95 rows=1,034 width=1,462) (actual time=695.671..2,611.151 rows=44,779 loops=1)

  • Hash Cond: (scbi.stylecolor_id = sc.id)
3. 174.259 276.957 ↓ 1.1 18,274 1

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

  • Group Key: scbi.stylecolor_id
4. 72.705 102.698 ↓ 1.0 102,829 1

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

  • Sort Key: scbi.stylecolor_id
  • Sort Method: external merge Disk: 2,568kB
5. 23.341 29.993 ↓ 1.0 102,829 1

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

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

7. 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: 1,024 Batches: 1 Memory Usage: 10kB
8. 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)

9. 32.358 607.812 ↓ 43.3 44,779 1

Hash (cost=209,528.59..209,528.59 rows=1,034 width=1,854) (actual time=607.812..607.812 rows=44,779 loops=1)

  • Buckets: 16,384 (originally 2048) Batches: 4 (originally 1) Memory Usage: 3,969kB
10. 23.500 575.454 ↓ 43.3 44,779 1

Merge Right Join (cost=208,165.24..209,528.59 rows=1,034 width=1,854) (actual time=445.290..575.454 rows=44,779 loops=1)

  • Merge Cond: (ii.id = i.id)
11. 86.240 233.545 ↓ 1.2 49,603 1

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

  • Group Key: ii.id
12. 45.343 147.305 ↓ 1.6 65,021 1

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

  • Sort Key: ii.id
  • Sort Method: external merge Disk: 1,264kB
13. 20.274 101.962 ↓ 1.6 65,021 1

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

  • Hash Cond: (((bi.gender)::text = psm.gender) AND (((unnest(ii.size_map)))::text = psm.item_size))
14. 53.391 81.666 ↑ 127.0 65,553 1

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

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

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

16. 5.037 21.306 ↑ 1.0 32,941 1

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

  • Buckets: 65,536 Batches: 1 Memory Usage: 1,735kB
17. 9.879 16.269 ↑ 1.0 32,941 1

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

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

19. 1.626 3.646 ↓ 1.0 11,729 1

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

  • Buckets: 16,384 Batches: 1 Memory Usage: 587kB
20. 2.020 2.020 ↓ 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.020 rows=11,729 loops=1)

21. 0.011 0.022 ↑ 1.0 38 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
22. 0.011 0.011 ↑ 1.0 38 1

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

23. 70.193 318.409 ↓ 43.3 44,779 1

Sort (cost=12,182.48..12,185.06 rows=1,034 width=1,822) (actual time=305.842..318.409 rows=44,779 loops=1)

  • Sort Key: i.id
  • Sort Method: external sort Disk: 12,896kB
24. 30.690 248.216 ↓ 43.3 44,779 1

Hash Right Join (cost=11,848.53..12,130.71 rows=1,034 width=1,822) (actual time=215.105..248.216 rows=44,779 loops=1)

  • Hash Cond: (sciii.style_id = sc.id)
25. 14.820 41.351 ↑ 1.0 11,505 1

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

  • Group Key: sciii.style_id
26. 16.017 26.531 ↑ 1.0 42,890 1

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

  • Hash Cond: (iii.style_color_id = sciii.style_id)
27. 6.376 6.376 ↓ 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.376 rows=83,271 loops=1)

28. 1.765 4.138 ↑ 1.0 11,722 1

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

  • Buckets: 16,384 Batches: 1 Memory Usage: 1,422kB
29. 2.373 2.373 ↑ 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.004..2.373 rows=11,722 loops=1)

30. 31.159 176.175 ↓ 43.3 44,779 1

Hash (cost=8,249.02..8,249.02 rows=1,034 width=1,790) (actual time=176.175..176.175 rows=44,779 loops=1)

  • Buckets: 32,768 (originally 2048) Batches: 4 (originally 1) Memory Usage: 3,841kB
31. 12.693 145.016 ↓ 43.3 44,779 1

Hash Join (cost=5,064.84..8,249.02 rows=1,034 width=1,790) (actual time=44.934..145.016 rows=44,779 loops=1)

  • Hash Cond: (b.brand_id = br.id)
32. 10.926 132.247 ↓ 32.2 44,779 1

Hash Join (cost=5,056.80..8,225.42 rows=1,390 width=1,783) (actual time=44.848..132.247 rows=44,779 loops=1)

  • Hash Cond: (b.type = rbt.id)
33. 12.109 121.314 ↓ 21.6 44,779 1

Hash Join (cost=5,055.71..8,202.68 rows=2,069 width=1,271) (actual time=44.829..121.314 rows=44,779 loops=1)

  • Hash Cond: (b.catalog_id = c.id)
34. 16.582 109.167 ↓ 3.6 45,076 1

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

  • Hash Cond: (i.id = inv.item_id)
35. 12.659 76.605 ↓ 2.1 47,974 1

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

  • Hash Cond: (sc.color = color.id)
36. 27.284 63.938 ↓ 1.3 49,827 1

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

  • Hash Cond: (i.style_color_id = sc.id)
37. 8.190 8.190 ↓ 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..8.190 rows=83,271 loops=1)

38. 6.120 28.464 ↓ 1.0 15,256 1

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

  • Buckets: 16,384 Batches: 1 Memory Usage: 2,495kB
39. 9.600 22.344 ↓ 1.0 15,256 1

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

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

41. 2.382 9.935 ↓ 1.5 7,857 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 924kB
42. 1.917 7.553 ↓ 1.5 7,857 1

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

  • Hash Cond: (b.source_id = rbs.id)
43. 1.667 5.591 ↓ 1.5 7,857 1

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

  • Hash Cond: (b.category_id = rbc.id)
44. 2.799 3.907 ↓ 1.0 7,857 1

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

  • Hash Cond: (b.sub_category_id = rbsc.id)
45. 1.063 1.063 ↓ 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.063 rows=11,729 loops=1)

46. 0.027 0.045 ↑ 1.0 159 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
47. 0.018 0.018 ↑ 1.0 159 1

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

48. 0.009 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: 1,024 Batches: 1 Memory Usage: 11kB
49. 0.008 0.008 ↑ 1.0 49 1

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

50. 0.021 0.045 ↑ 1.0 148 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
51. 0.024 0.024 ↑ 1.0 148 1

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

52. 0.005 0.008 ↑ 1.0 12 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
53. 0.003 0.003 ↑ 1.0 12 1

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

54. 6.378 15.980 ↑ 1.0 45,093 1

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

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,274kB
55. 9.602 9.602 ↑ 1.0 45,093 1

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

  • Filter: (warehouse = 2)
  • Rows Removed by Filter: 7,885
56. 0.008 0.038 ↓ 5.4 27 1

Hash (cost=2.53..2.53 rows=5 width=522) (actual time=0.038..0.038 rows=27 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
57. 0.020 0.030 ↓ 5.4 27 1

Hash Join (cost=1.11..2.53 rows=5 width=522) (actual time=0.024..0.030 rows=27 loops=1)

  • Hash Cond: (c.season = rcs.id)
58. 0.004 0.004 ↓ 1.0 28 1

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

59. 0.004 0.006 ↑ 1.0 5 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
60. 0.002 0.002 ↑ 1.0 5 1

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

61. 0.004 0.007 ↑ 1.0 4 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
62. 0.003 0.003 ↑ 1.0 4 1

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

63. 0.046 0.076 ↓ 1.0 225 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 19kB
64. 0.030 0.030 ↓ 1.0 225 1

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

Planning time : 4.796 ms
Execution time : 2,742.404 ms