explain.depesz.com

PostgreSQL's explain analyze made readable

Result: x8CQ

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.000 9,584.493 ↑ 8.2 116,927 1

Gather Merge (cost=429,624.86..541,712.02 rows=960,680 width=175) (actual time=9,398.492..9,584.493 rows=116,927 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
2. 921.975 28,019.403 ↑ 12.3 38,976 3

Sort (cost=428,624.84..429,825.69 rows=480,340 width=175) (actual time=9,298.798..9,339.801 rows=38,976 loops=3)

  • Sort Key: brand.itemkey, sh.styleno, var.styledevelopmentname, lc.itemkey, sm.materialno
  • Sort Method: external merge Disk: 7736kB
3. 408.597 27,097.428 ↑ 12.3 38,976 3

Hash Join (cost=72,894.04..301,199.88 rows=480,340 width=175) (actual time=2,551.466..9,032.476 rows=38,976 loops=3)

  • Hash Cond: ((mlc.complianceid)::uuid = lc.lookupid)
4. 152.861 26,673.036 ↑ 12.3 38,976 3

Nested Loop (cost=72,481.18..292,201.25 rows=480,340 width=173) (actual time=2,545.003..8,891.012 rows=38,976 loops=3)

5. 578.748 25,224.015 ↓ 90.0 432,053 3

Hash Left Join (cost=72,481.17..282,595.25 rows=4,803 width=303) (actual time=2,377.306..8,408.005 rows=432,053 loops=3)

  • Hash Cond: (sm.tradepartnervendorid = tpv.tradepartnervendorid)
6. 4,276.035 24,630.762 ↓ 90.0 432,053 3

Hash Join (cost=72,039.70..282,093.15 rows=4,803 width=293) (actual time=2,372.415..8,210.254 rows=432,053 loops=3)

  • Hash Cond: (((sh.eav ->> 'eavdevstage'::text))::uuid = devstage.lookupid)
7. 2,637.882 20,350.422 ↑ 1.3 436,292 3

Hash Join (cost=71,715.91..274,457.30 rows=562,489 width=652) (actual time=2,370.948..6,783.474 rows=436,292 loops=3)

  • Hash Cond: (sm.styleid = ssy.styleid)
8. 1,000.590 10,757.547 ↑ 1.3 436,292 3

Hash Join (cost=3,953.84..153,010.01 rows=562,489 width=256) (actual time=47.094..3,585.849 rows=436,292 loops=3)

  • Hash Cond: (sm.materialid = mat.materialid)
9. 9,624.717 9,624.717 ↑ 1.3 436,292 3

Parallel Seq Scan on pstylebom sm (cost=0.00..128,743.89 rows=562,489 width=60) (actual time=0.210..3,208.239 rows=436,292 loops=3)

10. 64.878 132.240 ↑ 1.0 35,869 3

Hash (cost=2,378.93..2,378.93 rows=35,993 width=228) (actual time=44.080..44.080 rows=35,869 loops=3)

  • Buckets: 16384 Batches: 4 Memory Usage: 2363kB
11. 67.362 67.362 ↑ 1.0 35,869 3

Seq Scan on pmaterial mat (cost=0.00..2,378.93 rows=35,993 width=228) (actual time=0.031..22.454 rows=35,869 loops=3)

12. 935.421 6,954.993 ↑ 1.0 125,869 3

Hash (cost=58,689.68..58,689.68 rows=125,871 width=460) (actual time=2,318.331..2,318.331 rows=125,869 loops=3)

  • Buckets: 8192 (originally 8192) Batches: 32 (originally 16) Memory Usage: 4033kB
13. 193.329 6,019.572 ↑ 1.0 125,869 3

Hash Join (cost=18,164.05..58,689.68 rows=125,871 width=460) (actual time=280.049..2,006.524 rows=125,869 loops=3)

  • Hash Cond: (ssy.seasonyearid = sy.seasonyearid)
14. 1,373.427 5,826.063 ↑ 1.0 125,869 3

Hash Join (cost=18,159.95..57,119.26 rows=125,871 width=466) (actual time=279.982..1,942.021 rows=125,869 loops=3)

  • Hash Cond: (((sh.eav ->> 'eavdepartment'::text))::uuid = dept.lookupid)
15. 1,366.461 4,438.635 ↑ 1.0 125,869 3

Hash Join (cost=17,747.08..55,070.14 rows=125,871 width=459) (actual time=275.240..1,479.545 rows=125,869 loops=3)

  • Hash Cond: (((sh.eav ->> 'eavbrand'::text))::uuid = brand.lookupid)
16. 1,348.173 3,057.516 ↑ 1.0 125,869 3

Hash Join (cost=17,334.22..53,021.02 rows=125,871 width=447) (actual time=270.204..1,019.172 rows=125,869 loops=3)

  • Hash Cond: (sh.styleid = ssy.styleid)
17. 905.694 905.694 ↑ 1.0 126,161 3

Seq Scan on pstyleheader sh (cost=0.00..19,908.88 rows=126,188 width=382) (actual time=0.052..301.898 rows=126,161 loops=3)

18. 157.170 803.649 ↑ 1.0 125,869 3

Hash (cost=14,284.83..14,284.83 rows=125,871 width=65) (actual time=267.883..267.883 rows=125,869 loops=3)

  • Buckets: 65536 Batches: 4 Memory Usage: 3513kB
19. 349.821 646.479 ↑ 1.0 125,869 3

Hash Join (cost=5,940.10..14,284.83 rows=125,871 width=65) (actual time=64.734..215.493 rows=125,869 loops=3)

  • Hash Cond: (var.styleid = ssy.styleid)
20. 104.478 104.478 ↑ 1.0 126,111 3

Seq Scan on pstyledevelopmentitem var (cost=0.00..3,780.11 rows=126,111 width=33) (actual time=0.033..34.826 rows=126,111 loops=3)

21. 109.095 192.180 ↑ 1.0 125,871 3

Hash (cost=3,505.71..3,505.71 rows=125,871 width=32) (actual time=64.060..64.060 rows=125,871 loops=3)

  • Buckets: 65536 Batches: 4 Memory Usage: 2484kB
22. 83.085 83.085 ↑ 1.0 125,871 3

Seq Scan on pstyleseasonyear ssy (cost=0.00..3,505.71 rows=125,871 width=32) (actual time=0.026..27.695 rows=125,871 loops=3)

23. 7.833 14.658 ↑ 1.0 10,298 3

Hash (cost=284.05..284.05 rows=10,305 width=28) (actual time=4.886..4.886 rows=10,298 loops=3)

  • Buckets: 16384 Batches: 1 Memory Usage: 744kB
24. 6.825 6.825 ↑ 1.0 10,298 3

Seq Scan on dlookup brand (cost=0.00..284.05 rows=10,305 width=28) (actual time=0.010..2.275 rows=10,298 loops=3)

25. 7.212 14.001 ↑ 1.0 10,298 3

Hash (cost=284.05..284.05 rows=10,305 width=23) (actual time=4.667..4.667 rows=10,298 loops=3)

  • Buckets: 16384 Batches: 1 Memory Usage: 684kB
26. 6.789 6.789 ↑ 1.0 10,298 3

Seq Scan on dlookup dept (cost=0.00..284.05 rows=10,305 width=23) (actual time=0.008..2.263 rows=10,298 loops=3)

27. 0.057 0.180 ↑ 1.0 49 3

Hash (cost=3.49..3.49 rows=49 width=26) (actual time=0.060..0.060 rows=49 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
28. 0.123 0.123 ↑ 1.0 49 3

Seq Scan on pseasonyear sy (cost=0.00..3.49 rows=49 width=26) (actual time=0.025..0.041 rows=49 loops=3)

29. 0.075 4.305 ↑ 1.0 88 3

Hash (cost=322.69..322.69 rows=88 width=16) (actual time=1.435..1.435 rows=88 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
30. 4.230 4.230 ↑ 1.0 88 3

Seq Scan on dlookup devstage (cost=0.00..322.69 rows=88 width=16) (actual time=0.029..1.410 rows=88 loops=3)

  • Filter: ((itemkey)::text = ANY ('{001,002,003}'::text[]))
  • Rows Removed by Filter: 10210
31. 7.365 14.505 ↑ 1.0 8,732 3

Hash (cost=332.32..332.32 rows=8,732 width=42) (actual time=4.835..4.835 rows=8,732 loops=3)

  • Buckets: 16384 Batches: 1 Memory Usage: 757kB
32. 7.140 7.140 ↑ 1.0 8,732 3

Seq Scan on utradepartnervendor tpv (cost=0.00..332.32 rows=8,732 width=42) (actual time=0.029..2.380 rows=8,732 loops=3)

33. 1,296.160 1,296.160 ↓ 0.0 0 1,296,160

Function Scan on jsonb_array_elements_text mlc (cost=0.01..1.00 rows=100 width=32) (actual time=0.001..0.001 rows=0 loops=1,296,160)

34. 7.638 15.795 ↑ 1.0 10,298 3

Hash (cost=284.05..284.05 rows=10,305 width=28) (actual time=5.265..5.265 rows=10,298 loops=3)

  • Buckets: 16384 Batches: 1 Memory Usage: 744kB
35. 8.157 8.157 ↑ 1.0 10,298 3

Seq Scan on dlookup lc (cost=0.00..284.05 rows=10,305 width=28) (actual time=0.038..2.719 rows=10,298 loops=3)