explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lYIp

Settings
# exclusive inclusive rows x rows loops node
1. 271.996 3,710.435 ↓ 116,927.0 116,927 1

Nested Loop (cost=33,390.51..667,386.81 rows=1 width=135) (actual time=217.692..3,710.435 rows=116,927 loops=1)

2. 50.851 3,320.495 ↓ 117,944.0 117,944 1

Nested Loop (cost=33,390.22..667,386.45 rows=1 width=473) (actual time=217.679..3,320.495 rows=117,944 loops=1)

3. 283.488 2,797.868 ↓ 117,944.0 117,944 1

Nested Loop (cost=33,389.80..667,385.91 rows=1 width=488) (actual time=217.669..2,797.868 rows=117,944 loops=1)

4. 370.638 2,396.436 ↓ 16,849.1 117,944 1

Hash Join (cost=33,389.51..667,383.67 rows=7 width=481) (actual time=217.658..2,396.436 rows=117,944 loops=1)

  • Hash Cond: (((sh.eav ->> 'eavbrand'::text))::uuid = brand.lookupid)
5. 56.834 2,025.757 ↓ 6.2 117,944 1

Hash Join (cost=33,373.08..667,121.11 rows=18,934 width=469) (actual time=217.606..2,025.757 rows=117,944 loops=1)

  • Hash Cond: (ssy.seasonyearid = sy.seasonyearid)
6. 122.941 1,968.891 ↓ 6.2 117,944 1

Nested Loop (cost=33,368.98..666,881.39 rows=18,934 width=475) (actual time=217.565..1,968.891 rows=117,944 loops=1)

7. 139.577 1,138.286 ↓ 6.2 117,944 1

Hash Join (cost=33,368.56..647,131.06 rows=18,934 width=92) (actual time=217.543..1,138.286 rows=117,944 loops=1)

  • Hash Cond: (wi.styleseasonyearid = ssy.styleseasonyearid)
8. 134.414 935.756 ↓ 3.0 117,944 1

Hash Join (cost=27,182.47..638,341.35 rows=39,336 width=76) (actual time=154.546..935.756 rows=117,944 loops=1)

  • Hash Cond: (sm.workflowitemid = wi.workflowitemid)
9. 37.434 647.558 ↑ 1.3 117,944 1

Nested Loop (cost=38.78..605,064.04 rows=152,524 width=55) (actual time=0.362..647.558 rows=117,944 loops=1)

10. 4.125 156.146 ↓ 2.0 8,407 1

Hash Join (cost=38.35..507,537.86 rows=4,180 width=55) (actual time=0.148..156.146 rows=8,407 loops=1)

  • Hash Cond: ((((jsonb_array_elements_text((pmaterial.eav -> 'eavcompliancemc'::text))))::uuid) = lc.lookupid)
11. 27.247 151.956 ↑ 426.7 8,407 1

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

12. 17.102 17.102 ↑ 1.0 35,869 1

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

13. 0.000 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)

14. 35.869 107.607 ↓ 0.0 0 35,869

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

15. 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
16. 0.005 0.065 ↑ 1.0 12 1

Hash (cost=37.91..37.91 rows=12 width=16) (actual time=0.065..0.065 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
17. 0.060 0.060 ↑ 1.0 12 1

Index Scan using ix_dlookup_lookuptypeid on dlookup lc (cost=0.29..37.91 rows=12 width=16) (actual time=0.021..0.060 rows=12 loops=1)

  • Index Cond: (lookuptypeid = '00000000-0000-0000-0000-000000000045'::uuid)
18. 453.978 453.978 ↑ 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.054 rows=14 loops=8,407)

  • Index Cond: (materialid = mat.materialid)
19. 36.534 153.784 ↓ 1.0 125,871 1

Hash (cost=24,344.21..24,344.21 rows=125,718 width=53) (actual time=153.784..153.784 rows=125,871 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 2890kB
20. 109.128 117.250 ↓ 1.0 125,871 1

Bitmap Heap Scan on pworkflowitem wi (cost=3,670.74..24,344.21 rows=125,718 width=53) (actual time=9.929..117.250 rows=125,871 loops=1)

  • Recheck Cond: (workflowid = '40000000-0000-0000-0000-000000000080'::uuid)
  • Heap Blocks: exact=14389
21. 8.122 8.122 ↓ 1.0 125,871 1

Bitmap Index Scan on ix_pworkflowitem_workflowid (cost=0.00..3,639.31 rows=125,718 width=0) (actual time=8.122..8.122 rows=125,871 loops=1)

  • Index Cond: (workflowid = '40000000-0000-0000-0000-000000000080'::uuid)
22. 30.921 62.953 ↑ 1.0 125,871 1

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

  • Buckets: 65536 Batches: 4 Memory Usage: 2976kB
23. 32.032 32.032 ↑ 1.0 125,871 1

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

24. 707.664 707.664 ↑ 1.0 1 117,944

Index Scan using pk__pstyleheader__371114f6 on pstyleheader sh (cost=0.42..1.04 rows=1 width=383) (actual time=0.006..0.006 rows=1 loops=117,944)

  • Index Cond: (styleid = ssy.styleid)
25. 0.011 0.032 ↑ 1.0 49 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
26. 0.021 0.021 ↑ 1.0 49 1

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

27. 0.004 0.041 ↑ 1.0 4 1

Hash (cost=16.38..16.38 rows=4 width=28) (actual time=0.041..0.041 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
28. 0.037 0.037 ↑ 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.028..0.037 rows=4 loops=1)

  • Index Cond: (lookuptypeid = '00000000-0000-0000-0000-000000000020'::uuid)
29. 117.944 117.944 ↑ 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.001..0.001 rows=1 loops=117,944)

  • Index Cond: (lookupid = ((sh.eav ->> 'eavdepartment'::text))::uuid)
  • Filter: (lookuptypeid = '00000000-0000-0000-0000-000000000019'::uuid)
30. 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.003..0.004 rows=1 loops=117,944)

  • Index Cond: (styleid = ssy.styleid)
31. 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