explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sQjh

Settings
# exclusive inclusive rows x rows loops node
1. 0.344 190.992 ↓ 3.5 1,000 1

Limit (cost=164.31..2,709.39 rows=283 width=196) (actual time=51.535..190.992 rows=1,000 loops=1)

2. 4.571 190.648 ↓ 3.5 1,000 1

Nested Loop Left Join (cost=164.31..2,706.56 rows=283 width=196) (actual time=51.534..190.648 rows=1,000 loops=1)

3.          

CTE styletypebyworkflowperm

4. 0.038 16.849 ↓ 2.0 60 1

Hash Join (cost=2.41..15.09 rows=30 width=80) (actual time=16.817..16.849 rows=60 loops=1)

  • Hash Cond: (perm_1.producttypeid = st_1.styletypeid)
5. 16.790 16.790 ↑ 16.7 60 1

Function Scan on fnx_permissions_getintproducttype_workflowpermissions perm_1 (cost=0.28..10.28 rows=1,000 width=80) (actual time=16.783..16.790 rows=60 loops=1)

6. 0.004 0.021 ↑ 1.0 6 1

Hash (cost=2.06..2.06 rows=6 width=4) (actual time=0.021..0.021 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
7. 0.017 0.017 ↑ 1.0 6 1

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

8.          

CTE locking

9. 0.002 0.284 ↑ 1.0 1 1

Limit (cost=0.25..0.26 rows=1 width=4) (actual time=0.283..0.284 rows=1 loops=1)

10. 0.282 0.282 ↑ 1.0 1 1

Function Scan on fnx_getappsetting_boolean2num config (cost=0.25..0.26 rows=1 width=4) (actual time=0.282..0.282 rows=1 loops=1)

11. 1.477 185.077 ↓ 3.5 1,000 1

Nested Loop (cost=148.96..2,681.30 rows=283 width=204) (actual time=51.234..185.077 rows=1,000 loops=1)

  • Join Filter: ((m.isseasonal = 0) OR ((m.isseasonal = 1) AND (sw.styleseasonyearid = ssy.styleseasonyearid)))
12. 4.671 178.930 ↓ 3.5 934 1

Nested Loop (cost=148.54..2,559.73 rows=268 width=218) (actual time=51.197..178.930 rows=934 loops=1)

  • Join Filter: (m.map = sw.workflowid)
  • Rows Removed by Join Filter: 13088
13. 1.789 54.707 ↓ 5.7 934 1

Nested Loop Left Join (cost=148.12..2,004.19 rows=163 width=139) (actual time=50.329..54.707 rows=934 loops=1)

  • Join Filter: ((s.statusname)::text = (sh.customfield3)::text)
  • Rows Removed by Join Filter: 5603
14. 5.383 51.984 ↓ 5.7 934 1

Nested Loop (cost=148.12..1,985.99 rows=163 width=146) (actual time=50.273..51.984 rows=934 loops=1)

  • Join Filter: (wti.workflowtemplateid = sh.styleworkflowid)
  • Rows Removed by Join Filter: 25482
15. 0.007 16.988 ↓ 3.0 3 1

Nested Loop (cost=1.04..16.43 rows=1 width=108) (actual time=16.956..16.988 rows=3 loops=1)

16. 0.005 16.966 ↓ 3.0 3 1

Nested Loop (cost=0.90..15.69 rows=1 width=77) (actual time=16.947..16.966 rows=3 loops=1)

  • Join Filter: (perm.producttypeid = st.styletypeid)
  • Rows Removed by Join Filter: 2
17. 0.008 16.946 ↓ 1.5 3 1

Merge Join (cost=0.90..13.43 rows=2 width=60) (actual time=16.938..16.946 rows=3 loops=1)

  • Merge Cond: (wti.workflowid = perm.workflowid)
18. 0.015 0.015 ↑ 14.5 2 1

Index Scan using ix_pworkflowtemplateitem_workflowid on pworkflowtemplateitem wti (cost=0.14..12.57 rows=29 width=32) (actual time=0.013..0.015 rows=2 loops=1)

19. 0.034 16.923 ↓ 1.5 3 1

Sort (cost=0.76..0.77 rows=2 width=28) (actual time=16.921..16.923 rows=3 loops=1)

  • Sort Key: perm.workflowid
  • Sort Method: quicksort Memory: 29kB
20. 16.889 16.889 ↓ 30.0 60 1

CTE Scan on styletypebyworkflowperm perm (cost=0.00..0.75 rows=2 width=28) (actual time=16.820..16.889 rows=60 loops=1)

  • Filter: ((permissionroleid = 3) OR (permissionview = 1))
21. 0.006 0.015 ↑ 3.0 2 3

Materialize (cost=0.00..2.09 rows=6 width=17) (actual time=0.003..0.005 rows=2 loops=3)

22. 0.009 0.009 ↑ 2.0 3 1

Seq Scan on pstyletype st (cost=0.00..2.06 rows=6 width=17) (actual time=0.005..0.009 rows=3 loops=1)

23. 0.015 0.015 ↑ 1.0 1 3

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

  • Index Cond: (map = wti.workflowid)
  • Filter: ((isseasonal = 0) OR (isseasonal = 1))
24. 26.589 29.613 ↑ 1.4 8,805 3

Bitmap Heap Scan on pstyleheader sh (cost=147.08..1,810.21 rows=12,748 width=78) (actual time=1.277..9.871 rows=8,805 loops=3)

  • Recheck Cond: (styletype = st.styletypeid)
  • Heap Blocks: exact=3398
25. 3.024 3.024 ↓ 1.3 16,993 3

Bitmap Index Scan on ix_pstyleheader_styletype (cost=0.00..143.90 rows=12,748 width=0) (actual time=1.008..1.008 rows=16,993 loops=3)

  • Index Cond: (styletype = st.styletypeid)
26. 0.884 0.934 ↑ 1.0 7 934

Materialize (cost=0.00..1.11 rows=7 width=17) (actual time=0.000..0.001 rows=7 loops=934)

27. 0.050 0.050 ↑ 1.0 7 1

Seq Scan on pstatus s (cost=0.00..1.07 rows=7 width=17) (actual time=0.046..0.050 rows=7 loops=1)

28. 119.552 119.552 ↑ 1.3 15 934

Index Scan using ix_pstyleworkflow_styleid on pstyleworkflow sw (cost=0.42..3.17 rows=19 width=111) (actual time=0.055..0.128 rows=15 loops=934)

  • Index Cond: (styleid = sh.styleid)
29. 4.670 4.670 ↑ 1.0 1 934

Index Scan using ix_pstyleseasonyear_styleid_seasonyearid on pstyleseasonyear ssy (cost=0.41..0.44 rows=1 width=54) (actual time=0.005..0.005 rows=1 loops=934)

  • Index Cond: (styleid = sw.styleid)
30. 1.000 1.000 ↑ 1.0 1 1,000

CTE Scan on locking (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1,000)

Planning time : 41.600 ms
Execution time : 193.811 ms