explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9CaP

Settings
# exclusive inclusive rows x rows loops node
1. 92.199 4,418.433 ↓ 116,167.0 116,167 1

Hash Join (cost=69,858.06..223,164.82 rows=1 width=147) (actual time=679.406..4,418.433 rows=116,167 loops=1)

  • Hash Cond: (mat.active = (split_4.value)::integer)
2. 202.396 4,326.215 ↓ 116,167.0 116,167 1

Hash Join (cost=69,855.81..223,162.54 rows=1 width=125) (actual time=679.365..4,326.215 rows=116,167 loops=1)

  • Hash Cond: (lc.lookupid = (split_3.value)::uuid)
3. 208.844 4,123.794 ↓ 116,167.0 116,167 1

Hash Join (cost=69,853.55..223,160.27 rows=1 width=173) (actual time=679.333..4,123.794 rows=116,167 loops=1)

  • Hash Cond: (ssy.seasonyearid = (split_1.value)::uuid)
4. 192.255 3,914.881 ↓ 116,927.0 116,927 1

Nested Loop (cost=69,851.30..223,158.01 rows=1 width=205) (actual time=679.256..3,914.881 rows=116,927 loops=1)

5. 228.126 3,486.738 ↓ 117,944.0 117,944 1

Nested Loop (cost=69,851.01..223,157.65 rows=1 width=565) (actual time=679.241..3,486.738 rows=117,944 loops=1)

6. 555.502 3,022.724 ↓ 117,944.0 117,944 1

Hash Join (cost=69,850.72..223,157.33 rows=1 width=590) (actual time=679.225..3,022.724 rows=117,944 loops=1)

  • Hash Cond: (((sh.eav ->> 'eavdepartment'::text))::uuid = (split_2.value)::uuid)
7. 103.782 2,467.129 ↓ 117,944.0 117,944 1

Nested Loop (cost=69,848.46..223,155.06 rows=1 width=558) (actual time=679.120..2,467.129 rows=117,944 loops=1)

8. 256.821 1,891.571 ↓ 117,944.0 117,944 1

Gather (cost=69,848.05..223,154.52 rows=1 width=573) (actual time=679.099..1,891.571 rows=117,944 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
9. 223.312 1,634.750 ↓ 58,972.0 58,972 2

Hash Join (cost=68,848.05..222,154.42 rows=1 width=573) (actual time=685.785..1,634.750 rows=58,972 loops=2)

  • Hash Cond: (((sh.eav ->> 'eavbrand'::text))::uuid = brand.lookupid)
10. 33.987 1,411.325 ↓ 1.4 58,972 2

Hash Join (cost=68,829.37..221,811.79 rows=43,188 width=561) (actual time=685.578..1,411.325 rows=58,972 loops=2)

  • Hash Cond: (ssy.seasonyearid = sy.seasonyearid)
11. 173.977 1,377.291 ↓ 1.4 58,972 2

Hash Join (cost=68,825.27..221,270.26 rows=43,188 width=535) (actual time=685.524..1,377.291 rows=58,972 loops=2)

  • Hash Cond: (ssy.styleid = sh.styleid)
12. 79.955 944.049 ↓ 1.4 58,972 2

Hash Join (cost=41,055.64..184,815.39 rows=43,188 width=152) (actual time=422.800..944.049 rows=58,972 loops=2)

  • Hash Cond: (wi.styleseasonyearid = ssy.styleseasonyearid)
13. 138.142 798.617 ↑ 1.5 58,972 2

Hash Join (cost=34,869.55..172,883.50 rows=89,726 width=136) (actual time=355.698..798.617 rows=58,972 loops=2)

  • Hash Cond: (sm.workflowitemid = wi.workflowitemid)
14. 256.915 305.948 ↑ 1.5 58,972 2

Nested Loop (cost=38.49..129,002.66 rows=89,726 width=115) (actual time=0.321..305.948 rows=58,972 loops=2)

15. 12.657 48.977 ↓ 1.7 4,204 2

Hash Join (cost=38.07..71,630.19 rows=2,459 width=115) (actual time=0.175..48.977 rows=4,204 loops=2)

  • Hash Cond: ((mlc.complianceid)::uuid = lc.lookupid)
16. 26.842 36.250 ↑ 501.9 4,204 2

Nested Loop (cost=0.01..44,428.00 rows=2,109,941 width=87) (actual time=0.084..36.250 rows=4,204 loops=2)

17. 9.407 9.407 ↑ 1.2 17,935 2

Parallel Seq Scan on pmaterial mat (cost=0.00..2,229.99 rows=21,099 width=217) (actual time=0.050..9.407 rows=17,935 loops=2)

18. 0.001 0.001 ↓ 0.0 0 35,869

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=35,869)

19. 0.009 0.070 ↑ 1.0 12 2

Hash (cost=37.91..37.91 rows=12 width=28) (actual time=0.070..0.070 rows=12 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.061 0.061 ↑ 1.0 12 2

Index Scan using ix_dlookup_lookuptypeid on dlookup lc (cost=0.29..37.91 rows=12 width=28) (actual time=0.021..0.061 rows=12 loops=2)

  • Index Cond: (lookuptypeid = '00000000-0000-0000-0000-000000000045'::uuid)
21. 0.056 0.056 ↑ 22.3 14 8,407

Index Scan using ix_pstylebom_materialid on pstylebom sm (cost=0.43..20.21 rows=312 width=32) (actual time=0.006..0.056 rows=14 loops=8,407)

  • Index Cond: (materialid = mat.materialid)
22. 144.441 354.527 ↑ 1.0 487,469 2

Hash (cost=23,976.69..23,976.69 rows=487,469 width=53) (actual time=354.527..354.527 rows=487,469 loops=2)

  • Buckets: 65536 Batches: 16 Memory Usage: 2829kB
23. 210.086 210.086 ↑ 1.0 487,469 2

Seq Scan on pworkflowitem wi (cost=0.00..23,976.69 rows=487,469 width=53) (actual time=0.024..210.086 rows=487,469 loops=2)

24. 34.459 65.477 ↑ 1.0 125,871 2

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

  • Buckets: 65536 Batches: 4 Memory Usage: 2976kB
25. 31.018 31.018 ↑ 1.0 125,871 2

Seq Scan on pstyleseasonyear ssy (cost=0.00..3,505.71 rows=125,871 width=48) (actual time=0.020..31.018 rows=125,871 loops=2)

26. 78.794 259.265 ↑ 1.0 126,161 2

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

  • Buckets: 16384 Batches: 16 Memory Usage: 3369kB
27. 180.471 180.471 ↑ 1.0 126,161 2

Seq Scan on pstyleheader sh (cost=0.00..19,908.61 rows=126,161 width=383) (actual time=0.019..180.471 rows=126,161 loops=2)

28. 0.013 0.047 ↑ 1.0 49 2

Hash (cost=3.49..3.49 rows=49 width=26) (actual time=0.047..0.047 rows=49 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
29. 0.034 0.034 ↑ 1.0 49 2

Seq Scan on pseasonyear sy (cost=0.00..3.49 rows=49 width=26) (actual time=0.017..0.034 rows=49 loops=2)

30. 0.004 0.113 ↓ 4.0 4 2

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
31. 0.030 0.109 ↓ 4.0 4 2

Hash Join (cost=2.54..18.67 rows=1 width=60) (actual time=0.089..0.109 rows=4 loops=2)

  • Hash Cond: (brand.lookupid = (split.value)::uuid)
32. 0.056 0.056 ↑ 1.0 4 2

Index Scan using ix_dlookup_lookuptypeid on dlookup brand (cost=0.29..16.38 rows=4 width=28) (actual time=0.042..0.056 rows=4 loops=2)

  • Index Cond: (lookuptypeid = '00000000-0000-0000-0000-000000000020'::uuid)
33. 0.011 0.023 ↑ 25.0 4 2

Hash (cost=1.00..1.00 rows=100 width=32) (actual time=0.023..0.023 rows=4 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
34. 0.012 0.012 ↑ 25.0 4 2

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

35. 471.776 471.776 ↑ 1.0 1 117,944

Index Scan using ix_pstyledevelopmentitem_styleid on pstyledevelopmentitem var (cost=0.42..0.53 rows=1 width=33) (actual time=0.004..0.004 rows=1 loops=117,944)

  • Index Cond: (styleid = ssy.styleid)
36. 0.078 0.093 ↑ 1.8 56 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
37. 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)

38. 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)
39. 235.888 235.888 ↑ 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.002..0.002 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
40. 0.058 0.069 ↑ 2.3 44 1

Hash (cost=1.00..1.00 rows=100 width=32) (actual time=0.069..0.069 rows=44 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
41. 0.011 0.011 ↑ 2.3 44 1

Function Scan on unnest split_1 (cost=0.00..1.00 rows=100 width=32) (actual time=0.006..0.011 rows=44 loops=1)

42. 0.020 0.025 ↑ 8.3 12 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
43. 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)

44. 0.007 0.019 ↑ 50.0 2 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
45. 0.012 0.012 ↑ 50.0 2 1

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