explain.depesz.com

PostgreSQL's explain analyze made readable

Result: k6Od

Settings
# exclusive inclusive rows x rows loops node
1. 816.591 11,980.991 ↓ 116,927.0 116,927 1

Sort (cost=20,897.06..20,897.06 rows=1 width=175) (actual time=11,848.276..11,980.991 rows=116,927 loops=1)

  • Sort Key: brand.itemkey, sh.styleno, var.styledevelopmentname, lc.itemkey, sm.materialno
  • Sort Method: external merge Disk: 23400kB
2. 110.624 11,164.400 ↓ 116,927.0 116,927 1

Nested Loop Left Join (cost=1,019.30..20,897.05 rows=1 width=175) (actual time=2.781..11,164.400 rows=116,927 loops=1)

3. 250.406 10,819.922 ↓ 116,927.0 116,927 1

Nested Loop (cost=1,019.01..20,896.74 rows=1 width=143) (actual time=2.765..10,819.922 rows=116,927 loops=1)

4. 176.069 10,335.662 ↓ 116,927.0 116,927 1

Nested Loop (cost=1,018.72..20,890.84 rows=1 width=163) (actual time=2.753..10,335.662 rows=116,927 loops=1)

5. 0.000 8,863.433 ↓ 1,296,160.0 1,296,160 1

Gather (cost=1,018.72..20,888.84 rows=1 width=293) (actual time=2.596..8,863.433 rows=1,296,160 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 1,644.647 10,124.838 ↓ 432,053.0 432,053 3

Nested Loop (cost=18.72..19,888.74 rows=1 width=293) (actual time=1.798..10,124.838 rows=432,053 loops=3)

7. 1,741.243 8,480.189 ↓ 87,258.4 436,292 3

Nested Loop (cost=18.43..19,887.09 rows=5 width=652) (actual time=1.770..8,480.189 rows=436,292 loops=3)

8. 4,937.139 6,738.943 ↓ 87,258.4 436,292 3

Nested Loop (cost=18.14..19,885.52 rows=5 width=456) (actual time=1.496..6,738.943 rows=436,292 loops=3)

9. 106.235 1,801.699 ↓ 41,956.0 41,956 3

Nested Loop (cost=17.71..19,880.34 rows=1 width=460) (actual time=1.191..1,801.699 rows=41,956 loops=3)

10. 578.464 1,695.462 ↓ 41,956.0 41,956 3

Nested Loop (cost=17.57..19,880.18 rows=1 width=466) (actual time=0.897..1,695.462 rows=41,956 loops=3)

11. 605.620 1,116.985 ↓ 42,033.0 42,033 3

Nested Loop (cost=17.15..19,879.64 rows=1 width=434) (actual time=0.556..1,116.985 rows=42,033 loops=3)

12. 214.758 511.352 ↓ 42,038.0 42,038 3

Nested Loop (cost=16.73..19,879.10 rows=1 width=401) (actual time=0.335..511.352 rows=42,038 loops=3)

13. 222.048 296.591 ↓ 2,101.9 42,038 3

Hash Join (cost=16.44..19,872.71 rows=20 width=394) (actual time=0.267..296.591 rows=42,038 loops=3)

  • Hash Cond: (((sh.eav ->> 'eavbrand'::text))::uuid = brand.lookupid)
14. 74.466 74.466 ↑ 1.3 42,054 3

Parallel Seq Scan on pstyleheader sh (cost=0.00..19,172.78 rows=52,578 width=382) (actual time=0.054..74.466 rows=42,054 loops=3)

15. 0.007 0.077 ↑ 1.0 4 3

Hash (cost=16.39..16.39 rows=4 width=28) (actual time=0.077..0.077 rows=4 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 0.070 0.070 ↑ 1.0 4 3

Index Scan using ix_dlookup_lookuptypeid on dlookup brand (cost=0.29..16.39 rows=4 width=28) (actual time=0.057..0.070 rows=4 loops=3)

  • Index Cond: (lookuptypeid = '00000000-0000-0000-0000-000000000020'::uuid)
17. 0.003 0.003 ↑ 1.0 1 126,115

Index Scan using pk_dlookup on dlookup dept (cost=0.29..0.32 rows=1 width=23) (actual time=0.003..0.003 rows=1 loops=126,115)

  • Index Cond: (lookupid = ((sh.eav ->> 'eavdepartment'::text))::uuid)
  • Filter: (lookuptypeid = '00000000-0000-0000-0000-000000000019'::uuid)
18. 0.013 0.013 ↑ 1.0 1 126,115

Index Scan using ix_pstyledevelopmentitem_styleid on pstyledevelopmentitem var (cost=0.42..0.53 rows=1 width=33) (actual time=0.013..0.013 rows=1 loops=126,115)

  • Index Cond: (styleid = sh.styleid)
19. 0.013 0.013 ↑ 1.0 1 126,098

Index Only Scan using ix_pstyleseasonyear_styleid_seasonyearid on pstyleseasonyear ssy (cost=0.42..0.53 rows=1 width=32) (actual time=0.012..0.013 rows=1 loops=126,098)

  • Index Cond: (styleid = sh.styleid)
  • Heap Fetches: 31896
20. 0.002 0.002 ↑ 1.0 1 125,869

Index Scan using pk_pseasonyear on pseasonyear sy (cost=0.14..0.16 rows=1 width=26) (actual time=0.002..0.002 rows=1 loops=125,869)

  • Index Cond: (seasonyearid = ssy.seasonyearid)
21. 0.105 0.105 ↑ 1.9 10 125,869

Index Scan using ix_pstylebom_styleid_styleset_workflowitemid_materialtype on pstylebom sm (cost=0.43..4.99 rows=19 width=60) (actual time=0.021..0.105 rows=10 loops=125,869)

  • Index Cond: (styleid = ssy.styleid)
22. 0.003 0.003 ↑ 1.0 1 1,308,877

Index Scan using pk__pmaterial__53f76c67 on pmaterial mat (cost=0.29..0.31 rows=1 width=228) (actual time=0.003..0.003 rows=1 loops=1,308,877)

  • Index Cond: (materialid = sm.materialid)
23. 0.002 0.002 ↑ 1.0 1 1,308,877

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=1,308,877)

  • 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
24. 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)

25. 233.854 233.854 ↑ 1.0 1 116,927

Index Scan using pk_dlookup on dlookup lc (cost=0.29..5.15 rows=1 width=28) (actual time=0.002..0.002 rows=1 loops=116,927)

  • Index Cond: (lookupid = (mlc.complianceid)::uuid)
  • Filter: (lookuptypeid = '00000000-0000-0000-0000-000000000045'::uuid)
26. 233.854 233.854 ↑ 1.0 1 116,927

Index Scan using pk_utradepartnervendor on utradepartnervendor tpv (cost=0.29..0.30 rows=1 width=42) (actual time=0.002..0.002 rows=1 loops=116,927)

  • Index Cond: (tradepartnervendorid = sm.tradepartnervendorid)
Planning time : 6.109 ms
Execution time : 12,006.187 ms