explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Hofv

Settings
# exclusive inclusive rows x rows loops node
1. 678.594 14,721.307 ↓ 116,167.0 116,167 1

Sort (cost=2,158,755.39..2,158,755.39 rows=1 width=159) (actual time=14,579.594..14,721.307 rows=116,167 loops=1)

  • Sort Key: brand.itemkey, sh.styleno, var.styledevelopmentname, lc.itemkey, sm.materialno
  • Sort Method: external merge Disk: 22800kB
2. 75.238 14,042.713 ↓ 116,167.0 116,167 1

Hash Join (cost=670,764.78..2,158,755.38 rows=1 width=159) (actual time=4,262.499..14,042.713 rows=116,167 loops=1)

  • Hash Cond: (mat.active = (split_4.value)::integer)
3. 183.147 13,967.458 ↓ 116,167.0 116,167 1

Hash Join (cost=670,762.53..2,158,753.10 rows=1 width=137) (actual time=4,262.473..13,967.458 rows=116,167 loops=1)

  • Hash Cond: (lc.lookupid = (split_3.value)::uuid)
4. 6,449.373 13,784.287 ↓ 116,167.0 116,167 1

Nested Loop (cost=670,760.28..2,158,750.84 rows=1 width=169) (actual time=4,262.443..13,784.287 rows=116,167 loops=1)

  • Join Filter: (ssy.seasonyearid = (split_1.value)::uuid)
  • Rows Removed by Join Filter: 5028621
5. 239.115 6,750.279 ↓ 116,927.0 116,927 1

Nested Loop (cost=670,760.28..2,158,748.08 rows=1 width=201) (actual time=4,262.424..6,750.279 rows=116,927 loops=1)

6. 174.384 6,393.220 ↓ 117,944.0 117,944 1

Nested Loop (cost=670,759.98..2,158,747.73 rows=1 width=561) (actual time=4,262.407..6,393.220 rows=117,944 loops=1)

7. 260.645 5,982.948 ↓ 117,944.0 117,944 1

Nested Loop (cost=670,759.69..2,158,747.41 rows=1 width=586) (actual time=4,262.362..5,982.948 rows=117,944 loops=1)

  • Join Filter: ((((jsonb_array_elements_text((pmaterial.eav -> 'eavcompliancemc'::text))))::uuid) = lc.lookupid)
  • Rows Removed by Join Filter: 1297384
8. 4.975 4.975 ↑ 1.0 12 1

Index Scan using pk_dlookup on dlookup lc (cost=0.29..1,072.48 rows=12 width=28) (actual time=0.159..4.975 rows=12 loops=1)

  • Filter: (lookuptypeid = '00000000-0000-0000-0000-000000000045'::uuid)
  • Rows Removed by Filter: 10286
9. 605.333 5,717.328 ↓ 991.1 117,944 12

Materialize (cost=670,759.41..2,157,653.80 rows=119 width=558) (actual time=127.188..476.444 rows=117,944 loops=12)

10. 478.708 5,111.995 ↓ 991.1 117,944 1

Hash Join (cost=670,759.41..2,157,653.21 rows=119 width=558) (actual time=1,526.118..5,111.995 rows=117,944 loops=1)

  • Hash Cond: (((sh.eav ->> 'eavdepartment'::text))::uuid = (split_2.value)::uuid)
11. 352.971 4,633.200 ↓ 495.6 117,944 1

Hash Join (cost=670,757.15..2,157,647.38 rows=238 width=526) (actual time=1,526.019..4,633.200 rows=117,944 loops=1)

  • Hash Cond: (((sh.eav ->> 'eavbrand'::text))::uuid = brand.lookupid)
12. 828.632 4,280.193 ↑ 534.2 117,944 1

Hash Join (cost=670,738.47..1,685,051.03 rows=63,002,200 width=514) (actual time=1,525.971..4,280.193 rows=117,944 loops=1)

  • Hash Cond: (sm.materialid = mat.materialid)
13. 1,119.508 3,321.113 ↓ 2.1 1,308,877 1

Hash Join (cost=121,649.96..306,054.70 rows=630,022 width=475) (actual time=1,394.248..3,321.113 rows=1,308,877 loops=1)

  • Hash Cond: (sm.workflowitemid = wi.workflowitemid)
14. 808.217 808.217 ↑ 1.0 1,308,877 1

Seq Scan on pstylebom sm (cost=0.00..136,208.11 rows=1,308,911 width=44) (actual time=0.056..808.217 rows=1,308,877 loops=1)

15. 179.475 1,393.388 ↓ 1.0 235,657 1

Hash (cost=104,739.02..104,739.02 rows=234,635 width=463) (actual time=1,393.388..1,393.388 rows=235,657 loops=1)

  • Buckets: 8192 Batches: 32 Memory Usage: 3649kB
16. 72.989 1,213.913 ↓ 1.0 235,657 1

Hash Join (cost=45,844.95..104,739.02 rows=234,635 width=463) (actual time=481.855..1,213.913 rows=235,657 loops=1)

  • Hash Cond: (ssy.seasonyearid = sy.seasonyearid)
17. 258.190 1,140.895 ↓ 1.0 235,657 1

Hash Join (cost=45,840.84..101,815.14 rows=234,635 width=437) (actual time=481.819..1,140.895 rows=235,657 loops=1)

  • Hash Cond: (ssy.styleid = sh.styleid)
18. 219.766 632.402 ↓ 1.0 235,657 1

Hash Join (cost=18,071.22..57,465.27 rows=234,635 width=102) (actual time=230.321..632.402 rows=235,657 loops=1)

  • Hash Cond: (wi.styleseasonyearid = ssy.styleseasonyearid)
19. 182.415 182.415 ↑ 1.0 487,469 1

Seq Scan on pworkflowitem wi (cost=0.00..23,976.69 rows=487,469 width=53) (actual time=0.016..182.415 rows=487,469 loops=1)

20. 42.754 230.221 ↑ 1.0 125,869 1

Hash (cost=14,776.83..14,776.83 rows=125,871 width=81) (actual time=230.221..230.221 rows=125,869 loops=1)

  • Buckets: 32768 Batches: 4 Memory Usage: 3742kB
21. 102.308 187.467 ↑ 1.0 125,869 1

Hash Join (cost=6,186.10..14,776.83 rows=125,871 width=81) (actual time=56.750..187.467 rows=125,869 loops=1)

  • Hash Cond: (var.styleid = ssy.styleid)
22. 29.037 29.037 ↑ 1.0 126,111 1

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

23. 32.556 56.122 ↑ 1.0 125,871 1

Hash (cost=3,505.71..3,505.71 rows=125,871 width=48) (actual time=56.122..56.122 rows=125,871 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 2977kB
24. 23.566 23.566 ↑ 1.0 125,871 1

Seq Scan on pstyleseasonyear ssy (cost=0.00..3,505.71 rows=125,871 width=48) (actual time=0.007..23.566 rows=125,871 loops=1)

25. 71.405 250.303 ↑ 1.0 126,161 1

Hash (cost=19,908.61..19,908.61 rows=126,161 width=383) (actual time=250.303..250.303 rows=126,161 loops=1)

  • Buckets: 16384 Batches: 16 Memory Usage: 3369kB
26. 178.898 178.898 ↑ 1.0 126,161 1

Seq Scan on pstyleheader sh (cost=0.00..19,908.61 rows=126,161 width=383) (actual time=0.038..178.898 rows=126,161 loops=1)

27. 0.011 0.029 ↑ 1.0 49 1

Hash (cost=3.49..3.49 rows=49 width=26) (actual time=0.029..0.029 rows=49 loops=1)

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

Seq Scan on pseasonyear sy (cost=0.00..3.49 rows=49 width=26) (actual time=0.007..0.018 rows=49 loops=1)

29. 9.467 130.448 ↑ 426.7 8,407 1

Hash (cost=462,218.27..462,218.27 rows=3,586,900 width=71) (actual time=130.448..130.448 rows=8,407 loops=1)

  • Buckets: 65536 Batches: 128 Memory Usage: 527kB
30. 8.213 120.981 ↑ 426.7 8,407 1

Nested Loop (cost=0.29..462,218.27 rows=3,586,900 width=71) (actual time=0.063..120.981 rows=8,407 loops=1)

31. 5.161 5.161 ↑ 1.0 35,869 1

Seq Scan on pmaterial mat (cost=0.00..2,377.69 rows=35,869 width=55) (actual time=0.011..5.161 rows=35,869 loops=1)

32. 35.869 107.607 ↓ 0.0 0 35,869

Result (cost=0.29..10.82 rows=100 width=16) (actual time=0.003..0.003 rows=0 loops=35,869)

33. 0.000 71.738 ↓ 0.0 0 35,869

ProjectSet (cost=0.29..8.82 rows=100 width=32) (actual time=0.002..0.002 rows=0 loops=35,869)

34. 71.738 71.738 ↓ 0.0 0 35,869

Index Scan using pk__pmaterial__53f76c67 on pmaterial (cost=0.29..8.31 rows=1 width=162) (actual time=0.002..0.002 rows=0 loops=35,869)

  • Index Cond: (materialid = mat.materialid)
  • Filter: ((eav -> 'eavcompliancemc'::text) IS NOT NULL)
  • Rows Removed by Filter: 1
35. 0.003 0.036 ↓ 4.0 4 1

Hash (cost=18.67..18.67 rows=1 width=60) (actual time=0.036..0.036 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
36. 0.012 0.033 ↓ 4.0 4 1

Hash Join (cost=2.54..18.67 rows=1 width=60) (actual time=0.025..0.033 rows=4 loops=1)

  • Hash Cond: (brand.lookupid = (split.value)::uuid)
37. 0.011 0.011 ↑ 1.0 4 1

Index Scan using ix_dlookup_lookuptypeid on dlookup brand (cost=0.29..16.38 rows=4 width=28) (actual time=0.009..0.011 rows=4 loops=1)

  • Index Cond: (lookuptypeid = '00000000-0000-0000-0000-000000000020'::uuid)
38. 0.008 0.010 ↑ 25.0 4 1

Hash (cost=1.00..1.00 rows=100 width=32) (actual time=0.010..0.010 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
39. 0.002 0.002 ↑ 25.0 4 1

Function Scan on unnest split (cost=0.00..1.00 rows=100 width=32) (actual time=0.002..0.002 rows=4 loops=1)

40. 0.072 0.087 ↑ 1.8 56 1

Hash (cost=1.00..1.00 rows=100 width=32) (actual time=0.087..0.087 rows=56 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
41. 0.015 0.015 ↑ 1.8 56 1

Function Scan on unnest split_2 (cost=0.00..1.00 rows=100 width=32) (actual time=0.010..0.015 rows=56 loops=1)

42. 235.888 235.888 ↑ 1.0 1 117,944

Index Scan using pk_dlookup on dlookup dept (cost=0.29..0.32 rows=1 width=23) (actual time=0.002..0.002 rows=1 loops=117,944)

  • Index Cond: (lookupid = ((sh.eav ->> 'eavdepartment'::text))::uuid)
  • Filter: (lookuptypeid = '00000000-0000-0000-0000-000000000019'::uuid)
43. 117.944 117.944 ↑ 1.0 1 117,944

Index Scan using pk_dlookup on dlookup devstage (cost=0.29..0.32 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=117,944)

  • Index Cond: (lookupid = ((sh.eav ->> 'eavdevstage'::text))::uuid)
  • Filter: ((lookuptypeid = '00000000-0000-0000-0000-000000000005'::uuid) AND ((itemkey)::text = ANY ('{001,002,003}'::text[])))
  • Rows Removed by Filter: 0
44. 584.635 584.635 ↑ 2.3 44 116,927

Function Scan on unnest split_1 (cost=0.00..1.00 rows=100 width=32) (actual time=0.000..0.005 rows=44 loops=116,927)

45. 0.019 0.024 ↑ 8.3 12 1

Hash (cost=1.00..1.00 rows=100 width=32) (actual time=0.024..0.024 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
46. 0.005 0.005 ↑ 8.3 12 1

Function Scan on unnest split_3 (cost=0.00..1.00 rows=100 width=32) (actual time=0.003..0.005 rows=12 loops=1)

47. 0.009 0.017 ↑ 50.0 2 1

Hash (cost=1.00..1.00 rows=100 width=32) (actual time=0.017..0.017 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
48. 0.008 0.008 ↑ 50.0 2 1

Function Scan on unnest split_4 (cost=0.00..1.00 rows=100 width=32) (actual time=0.008..0.008 rows=2 loops=1)