explain.depesz.com

PostgreSQL's explain analyze made readable

Result: U6BM

Settings
# exclusive inclusive rows x rows loops node
1. 36.592 4,418.133 ↑ 1.0 1 1

Aggregate (cost=1,609.00..1,609.01 rows=1 width=8) (actual time=4,418.133..4,418.133 rows=1 loops=1)

2.          

CTE perm_workflowbystyletype

3. 9.843 9.843 ↑ 16.7 60 1

Function Scan on fnx_permissions_getintproducttype_workflowpermissions p (cost=0.28..10.28 rows=1,000 width=32) (actual time=9.794..9.843 rows=60 loops=1)

4.          

CTE locking

5. 0.093 0.093 ↑ 1.0 1 1

Result (cost=0.00..0.26 rows=1 width=4) (actual time=0.092..0.093 rows=1 loops=1)

6. 143.803 4,381.541 ↓ 5,171.3 165,481 1

Nested Loop Left Join (cost=131.67..1,598.38 rows=32 width=0) (actual time=41.706..4,381.541 rows=165,481 loops=1)

7. 195.588 4,237.738 ↓ 5,171.3 165,481 1

Nested Loop (cost=131.67..1,597.73 rows=32 width=0) (actual time=41.611..4,237.738 rows=165,481 loops=1)

  • Join Filter: ((m.isseasonal = 0) OR ((m.isseasonal = 1) AND (sw.styleseasonyearid = ssy.styleseasonyearid)))
  • Rows Removed by Join Filter: 4524
8. 93.555 3,577.177 ↓ 5,166.4 154,991 1

Hash Left Join (cost=131.26..1,584.12 rows=30 width=52) (actual time=41.589..3,577.177 rows=154,991 loops=1)

  • Hash Cond: ((sh.customfield3)::text = (s.statusname)::text)
9. 104.935 3,483.602 ↓ 5,166.4 154,991 1

Nested Loop (cost=130.10..1,582.55 rows=30 width=63) (actual time=41.556..3,483.602 rows=154,991 loops=1)

  • Join Filter: (sh.styletype = st.styletypeid)
10. 575.921 3,378.667 ↓ 5,166.4 154,991 1

Nested Loop (cost=130.10..1,578.10 rows=30 width=71) (actual time=41.533..3,378.667 rows=154,991 loops=1)

  • Join Filter: (m.map = sw.workflowid)
  • Rows Removed by Join Filter: 2511079
11. 204.606 662.258 ↓ 4,633.1 152,892 1

Nested Loop (cost=129.68..1,465.63 rows=33 width=87) (actual time=41.468..662.258 rows=152,892 loops=1)

  • Join Filter: (wti.workflowtemplateid = sh.styleworkflowid)
  • Rows Removed by Join Filter: 586086
12. 0.219 10.588 ↓ 58.0 58 1

Nested Loop (cost=1.79..27.46 rows=1 width=72) (actual time=9.838..10.588 rows=58 loops=1)

13. 0.098 10.079 ↓ 58.0 58 1

Hash Join (cost=1.65..26.73 rows=1 width=52) (actual time=9.830..10.079 rows=58 loops=1)

  • Hash Cond: (perm.workflowid = wti.workflowid)
14. 9.956 9.956 ↓ 6.0 60 1

CTE Scan on perm_workflowbystyletype perm (cost=0.00..25.00 rows=10 width=20) (actual time=9.798..9.956 rows=60 loops=1)

  • Filter: ((permissionroleid = 3) OR (permissionview = 1))
15. 0.008 0.025 ↑ 1.0 29 1

Hash (cost=1.29..1.29 rows=29 width=32) (actual time=0.025..0.025 rows=29 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
16. 0.017 0.017 ↑ 1.0 29 1

Seq Scan on pworkflowtemplateitem wti (cost=0.00..1.29 rows=29 width=32) (actual time=0.012..0.017 rows=29 loops=1)

17. 0.290 0.290 ↑ 1.0 1 58

Index Scan using pk__mmapping__7b9b496d on mapping m (cost=0.14..0.71 rows=1 width=20) (actual time=0.005..0.005 rows=1 loops=58)

  • Index Cond: (map = wti.workflowid)
  • Filter: ((isseasonal = 0) OR (isseasonal = 1))
18. 403.854 447.064 ↑ 1.0 12,741 58

Bitmap Heap Scan on pstyleheader sh (cost=127.88..1,278.82 rows=12,748 width=47) (actual time=0.945..7.708 rows=12,741 loops=58)

  • Recheck Cond: (styletype = perm.producttypeid)
  • Heap Blocks: exact=94192
19. 43.210 43.210 ↑ 1.0 12,745 58

Bitmap Index Scan on ix_pstyleheader_styletype (cost=0.00..124.70 rows=12,748 width=0) (actual time=0.745..0.745 rows=12,745 loops=58)

  • Index Cond: (styletype = perm.producttypeid)
20. 2,140.488 2,140.488 ↑ 1.1 17 152,892

Index Scan using ix_pstyleworkflow_styleid on pstyleworkflow sw (cost=0.42..3.17 rows=19 width=48) (actual time=0.005..0.014 rows=17 loops=152,892)

  • Index Cond: (styleid = sh.styleid)
21. 0.000 0.000 ↑ 6.0 1 154,991

Materialize (cost=0.00..2.09 rows=6 width=4) (actual time=0.000..0.000 rows=1 loops=154,991)

22. 0.014 0.014 ↑ 6.0 1 1

Seq Scan on pstyletype st (cost=0.00..2.06 rows=6 width=4) (actual time=0.014..0.014 rows=1 loops=1)

23. 0.005 0.020 ↑ 1.0 7 1

Hash (cost=1.07..1.07 rows=7 width=13) (actual time=0.020..0.020 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
24. 0.015 0.015 ↑ 1.0 7 1

Seq Scan on pstatus s (cost=0.00..1.07 rows=7 width=13) (actual time=0.014..0.015 rows=7 loops=1)

25. 464.973 464.973 ↑ 1.0 1 154,991

Index Scan using ix_pstyleseasonyear_styleid_seasonyearid on pstyleseasonyear ssy (cost=0.41..0.44 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=154,991)

  • Index Cond: (styleid = sw.styleid)
26. 0.000 0.000 ↑ 1.0 1 165,481

CTE Scan on locking (cost=0.00..0.02 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=165,481)