explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GBXJ

Settings
# exclusive inclusive rows x rows loops node
1. 1.374 379,144.296 ↓ 2,029.0 2,029 1

Unique (cost=157,806.32..157,806.36 rows=1 width=280) (actual time=379,142.542..379,144.296 rows=2,029 loops=1)

2.          

CTE af_devtimedeptgroupings

3. 0.004 0.015 ↑ 1.0 11 1

Append (cost=0.00..0.22 rows=11 width=128) (actual time=0.002..0.015 rows=11 loops=1)

4. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.001..0.001 rows=1 loops=1)

5. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.001..0.001 rows=1 loops=1)

6. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.001..0.001 rows=1 loops=1)

7. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.001..0.001 rows=1 loops=1)

8. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.001..0.001 rows=1 loops=1)

9. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.001..0.001 rows=1 loops=1)

10. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.001..0.001 rows=1 loops=1)

11. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.001..0.001 rows=1 loops=1)

12. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.001..0.001 rows=1 loops=1)

13. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.001..0.001 rows=1 loops=1)

14. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.001..0.001 rows=1 loops=1)

15.          

CTE af_devtimedeptgroupings_depts

16. 0.013 0.058 ↑ 1.0 49 1

Append (cost=0.00..0.98 rows=49 width=128) (actual time=0.002..0.058 rows=49 loops=1)

17. 0.002 0.002 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.002..0.002 rows=1 loops=1)

18. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.001..0.001 rows=1 loops=1)

19. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.001..0.001 rows=1 loops=1)

20. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.001..0.001 rows=1 loops=1)

21. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.000..0.001 rows=1 loops=1)

22. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.001..0.001 rows=1 loops=1)

23. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.001..0.001 rows=1 loops=1)

24. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.001..0.001 rows=1 loops=1)

25. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.000..0.001 rows=1 loops=1)

26. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.001..0.001 rows=1 loops=1)

27. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.001..0.001 rows=1 loops=1)

28. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.001..0.001 rows=1 loops=1)

29. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.001..0.001 rows=1 loops=1)

30. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.001..0.001 rows=1 loops=1)

31. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.000..0.000 rows=1 loops=1)

32. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.000..0.001 rows=1 loops=1)

33. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.001..0.001 rows=1 loops=1)

34. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.000..0.000 rows=1 loops=1)

35. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.001..0.001 rows=1 loops=1)

36. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.001..0.001 rows=1 loops=1)

37. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.001..0.001 rows=1 loops=1)

38. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.001..0.001 rows=1 loops=1)

39. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.001..0.001 rows=1 loops=1)

40. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.000..0.001 rows=1 loops=1)

41. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.001..0.001 rows=1 loops=1)

42. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.001..0.001 rows=1 loops=1)

43. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.001..0.001 rows=1 loops=1)

44. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.001..0.001 rows=1 loops=1)

45. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.001..0.001 rows=1 loops=1)

46. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.001..0.001 rows=1 loops=1)

47. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.001..0.001 rows=1 loops=1)

48. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.000..0.001 rows=1 loops=1)

49. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.001..0.001 rows=1 loops=1)

50. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.001..0.001 rows=1 loops=1)

51. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.001..0.001 rows=1 loops=1)

52. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.000..0.001 rows=1 loops=1)

53. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.000..0.000 rows=1 loops=1)

54. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.001..0.001 rows=1 loops=1)

55. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.001..0.001 rows=1 loops=1)

56. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.001..0.001 rows=1 loops=1)

57. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.001..0.001 rows=1 loops=1)

58. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.001..0.001 rows=1 loops=1)

59. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.000..0.000 rows=1 loops=1)

60. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.001..0.001 rows=1 loops=1)

61. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.000..0.001 rows=1 loops=1)

62. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.000..0.000 rows=1 loops=1)

63. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.001..0.001 rows=1 loops=1)

64. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.001..0.001 rows=1 loops=1)

65. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.001..0.001 rows=1 loops=1)

66.          

CTE mintbasubmit

67. 102.796 456.357 ↓ 4.8 41,763 1

HashAggregate (cost=39,965.06..40,052.76 rows=8,770 width=28) (actual time=442.128..456.357 rows=41,763 loops=1)

  • Group Key: srt_mintba.samplerequesttradeid, ss_mintba.statusid
68. 46.924 353.561 ↓ 4.8 42,005 1

Hash Join (cost=36,234.35..39,833.51 rows=8,770 width=261) (actual time=290.610..353.561 rows=42,005 loops=1)

  • Hash Cond: (srt_mintba.samplerequesttradeid = srw_mintba.samplerequesttradeid)
69. 16.103 16.103 ↑ 1.0 87,379 1

Seq Scan on psamplerequesttrade srt_mintba (cost=0.00..3,183.79 rows=87,379 width=16) (actual time=0.016..16.103 rows=87,379 loops=1)

70. 29.333 290.534 ↓ 4.8 42,005 1

Hash (cost=36,124.73..36,124.73 rows=8,770 width=261) (actual time=290.534..290.534 rows=42,005 loops=1)

  • Buckets: 65536 (originally 16384) Batches: 1 (originally 1) Memory Usage: 16744kB
71. 14.225 261.201 ↓ 4.8 42,005 1

Hash Join (cost=26,010.51..36,124.73 rows=8,770 width=261) (actual time=136.065..261.201 rows=42,005 loops=1)

  • Hash Cond: ((srw_mintba.sampleworkflowid)::text = (sw_mintba.sampleworkflowid)::text)
72. 74.992 246.947 ↓ 1.9 42,486 1

Hash Join (cost=26,008.35..35,843.41 rows=22,551 width=267) (actual time=136.014..246.947 rows=42,486 loops=1)

  • Hash Cond: (srw_mintba.samplerequestworkflowid = srs_mintba.samplerequestworkflowid)
73. 36.096 36.096 ↑ 1.0 177,437 1

Seq Scan on psamplerequestworkflow srw_mintba (cost=0.00..8,722.37 rows=177,437 width=38) (actual time=0.007..36.096 rows=177,437 loops=1)

74. 28.860 135.859 ↓ 1.9 42,486 1

Hash (cost=25,726.46..25,726.46 rows=22,551 width=261) (actual time=135.859..135.859 rows=42,486 loops=1)

  • Buckets: 65536 (originally 32768) Batches: 1 (originally 1) Memory Usage: 16891kB
75. 53.775 106.999 ↓ 1.9 42,486 1

Hash Join (cost=1.25..25,726.46 rows=22,551 width=261) (actual time=0.077..106.999 rows=42,486 loops=1)

  • Hash Cond: (srs_mintba.status = ss_mintba.statusid)
76. 53.192 53.192 ↑ 1.0 202,960 1

Seq Scan on psamplerequestsubmit srs_mintba (cost=0.00..24,738.60 rows=202,960 width=261) (actual time=0.020..53.192 rows=202,960 loops=1)

77. 0.014 0.032 ↑ 1.0 2 1

Hash (cost=1.23..1.23 rows=2 width=4) (actual time=0.032..0.032 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
78. 0.018 0.018 ↑ 1.0 2 1

Seq Scan on psamplerequestsubmitstatus ss_mintba (cost=0.00..1.23 rows=2 width=4) (actual time=0.016..0.018 rows=2 loops=1)

  • Filter: (statusid = ANY ('{6,14}'::integer[]))
  • Rows Removed by Filter: 16
79. 0.008 0.029 ↑ 1.0 14 1

Hash (cost=1.99..1.99 rows=14 width=24) (actual time=0.029..0.029 rows=14 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
80. 0.021 0.021 ↑ 1.0 14 1

Seq Scan on psampleworkflow sw_mintba (cost=0.00..1.99 rows=14 width=24) (actual time=0.012..0.021 rows=14 loops=1)

  • Filter: ((sampleworkflowid)::text = ANY ('{4_50A,4_52A,4_53A,4_54A,4_55A,4_56A,4_91A,4_94A,4_96A,4_96B,4_96C,4_96D,91A,94A}'::text[]))
  • Rows Removed by Filter: 22
81.          

CTE a_ssr_rt

82. 151.653 645.109 ↑ 1.0 84,083 1

HashAggregate (cost=49,072.75..49,946.54 rows=87,379 width=48) (actual time=594.207..645.109 rows=84,083 loops=1)

  • Group Key: srt_ff2.samplerequesttradeid
83. 52.416 493.456 ↑ 1.2 166,155 1

Hash Join (cost=15,218.17..48,063.02 rows=201,945 width=40) (actual time=150.856..493.456 rows=166,155 loops=1)

  • Hash Cond: ((srw_ff2.sampleworkflowid)::text = (sw_ff2.sampleworkflowid)::text)
84. 90.388 440.997 ↑ 1.2 166,155 1

Hash Join (cost=15,216.36..45,561.44 rows=201,945 width=22) (actual time=150.805..440.997 rows=166,155 loops=1)

  • Hash Cond: (srw_ff2.samplerequesttradeid = srt_ff2.samplerequesttradeid)
85. 103.887 312.368 ↑ 1.2 166,155 1

Hash Join (cost=10,940.33..38,735.88 rows=201,945 width=22) (actual time=112.073..312.368 rows=166,155 loops=1)

  • Hash Cond: (srs_ff2.samplerequestworkflowid = srw_ff2.samplerequestworkflowid)
86. 97.356 97.356 ↑ 1.2 166,155 1

Seq Scan on psamplerequestsubmit srs_ff2 (cost=0.00..25,246.00 rows=201,945 width=16) (actual time=0.023..97.356 rows=166,155 loops=1)

  • Filter: (cdate <> mdate)
  • Rows Removed by Filter: 36805
87. 58.547 111.125 ↑ 1.0 177,437 1

Hash (cost=8,722.37..8,722.37 rows=177,437 width=38) (actual time=111.125..111.125 rows=177,437 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 14178kB
88. 52.578 52.578 ↑ 1.0 177,437 1

Seq Scan on psamplerequestworkflow srw_ff2 (cost=0.00..8,722.37 rows=177,437 width=38) (actual time=0.021..52.578 rows=177,437 loops=1)

89. 21.093 38.241 ↑ 1.0 87,379 1

Hash (cost=3,183.79..3,183.79 rows=87,379 width=16) (actual time=38.241..38.241 rows=87,379 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 5120kB
90. 17.148 17.148 ↑ 1.0 87,379 1

Seq Scan on psamplerequesttrade srt_ff2 (cost=0.00..3,183.79 rows=87,379 width=16) (actual time=0.018..17.148 rows=87,379 loops=1)

91. 0.015 0.043 ↑ 1.0 36 1

Hash (cost=1.36..1.36 rows=36 width=48) (actual time=0.043..0.043 rows=36 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
92. 0.028 0.028 ↑ 1.0 36 1

Seq Scan on psampleworkflow sw_ff2 (cost=0.00..1.36 rows=36 width=48) (actual time=0.021..0.028 rows=36 loops=1)

93.          

CTE firstfit

94. 36.257 1,917.401 ↓ 192.6 81,283 1

GroupAggregate (cost=7,856.12..7,864.56 rows=422 width=24) (actual time=1,861.523..1,917.401 rows=81,283 loops=1)

  • Group Key: srt_ff.samplerequesttradeid
95. 62.020 1,881.144 ↓ 192.6 81,283 1

Sort (cost=7,856.12..7,857.18 rows=422 width=32) (actual time=1,861.511..1,881.144 rows=81,283 loops=1)

  • Sort Key: srt_ff.samplerequesttradeid
  • Sort Method: quicksort Memory: 9423kB
96. 41.666 1,819.124 ↓ 192.6 81,283 1

Nested Loop (cost=3.42..7,837.72 rows=422 width=32) (actual time=594.321..1,819.124 rows=81,283 loops=1)

97. 40.539 1,533.609 ↓ 192.6 81,283 1

Nested Loop (cost=3.00..7,628.95 rows=422 width=48) (actual time=594.308..1,533.609 rows=81,283 loops=1)

98. 49.044 1,167.938 ↓ 192.2 81,283 1

Nested Loop (cost=2.59..7,212.43 rows=423 width=48) (actual time=594.296..1,167.938 rows=81,283 loops=1)

  • Join Filter: ((sw_ff.sampleworkflowid)::text = (srw_ff.sampleworkflowid)::text)
  • Rows Removed by Join Filter: 85896
99. 30.860 712.479 ↓ 13.3 81,283 1

Hash Join (cost=2.17..2,138.59 rows=6,117 width=40) (actual time=594.270..712.479 rows=81,283 loops=1)

  • Hash Cond: (a_ssr_rt.minsort = (sw_ff.sampleworkflowsort)::text)
100. 681.575 681.575 ↑ 1.0 84,083 1

CTE Scan on a_ssr_rt (cost=0.00..1,747.58 rows=87,379 width=48) (actual time=594.210..681.575 rows=84,083 loops=1)

101. 0.013 0.044 ↑ 1.0 14 1

Hash (cost=1.99..1.99 rows=14 width=48) (actual time=0.044..0.044 rows=14 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
102. 0.031 0.031 ↑ 1.0 14 1

Seq Scan on psampleworkflow sw_ff (cost=0.00..1.99 rows=14 width=48) (actual time=0.021..0.031 rows=14 loops=1)

  • Filter: ((sampleworkflowid)::text = ANY ('{4_50A,4_52A,4_53A,4_54A,4_55A,4_56A,4_91A,4_94A,4_96A,4_96B,4_96C,4_96D,91A,94A}'::text[]))
  • Rows Removed by Filter: 22
103. 406.415 406.415 ↑ 1.0 2 81,283

Index Scan using ix_psamplerequestworkflow_samplerequesttradeid on psamplerequestworkflow srw_ff (cost=0.42..0.80 rows=2 width=38) (actual time=0.003..0.005 rows=2 loops=81,283)

  • Index Cond: (samplerequesttradeid = a_ssr_rt.samplerequesttradeid)
104. 325.132 325.132 ↑ 1.0 1 81,283

Index Scan using ix_psamplerequestsubmit_samplerequestworkflowid_submit on psamplerequestsubmit srs_ff (cost=0.42..0.97 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=81,283)

  • Index Cond: ((samplerequestworkflowid = srw_ff.samplerequestworkflowid) AND (submit = 1))
105. 243.849 243.849 ↑ 1.0 1 81,283

Index Only Scan using pk__psamplerequestte__4b62f1bd on psamplerequesttrade srt_ff (cost=0.42..0.49 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=81,283)

  • Index Cond: (samplerequesttradeid = srw_ff.samplerequesttradeid)
  • Heap Fetches: 81283
106.          

CTE samplecount

107. 131.946 621.037 ↓ 1.7 136,070 1

Hash Join (cost=16,059.50..43,839.61 rows=78,534 width=56) (actual time=160.194..621.037 rows=136,070 loops=1)

  • Hash Cond: (srw_sc.samplerequesttradeid = srt_sc.samplerequesttradeid)
108. 165.870 451.957 ↓ 1.7 136,070 1

Hash Join (cost=11,783.47..38,572.10 rows=78,534 width=56) (actual time=122.727..451.957 rows=136,070 loops=1)

  • Hash Cond: (srs_sc.samplerequestworkflowid = srw_sc.samplerequestworkflowid)
109. 163.766 163.766 ↑ 1.2 166,155 1

Seq Scan on psamplerequestsubmit srs_sc (cost=0.00..25,246.00 rows=201,945 width=40) (actual time=0.021..163.766 rows=166,155 loops=1)

  • Filter: (cdate <> mdate)
  • Rows Removed by Filter: 36805
110. 38.156 122.321 ↓ 1.9 131,082 1

Hash (cost=10,920.93..10,920.93 rows=69,003 width=32) (actual time=122.321..122.321 rows=131,082 loops=1)

  • Buckets: 262144 (originally 131072) Batches: 1 (originally 1) Memory Usage: 10241kB
111. 51.867 84.165 ↓ 1.9 131,082 1

Hash Join (cost=2.17..10,920.93 rows=69,003 width=32) (actual time=0.047..84.165 rows=131,082 loops=1)

  • Hash Cond: ((srw_sc.sampleworkflowid)::text = (sw_mintba_1.sampleworkflowid)::text)
112. 32.271 32.271 ↑ 1.0 177,437 1

Seq Scan on psamplerequestworkflow srw_sc (cost=0.00..8,722.37 rows=177,437 width=38) (actual time=0.009..32.271 rows=177,437 loops=1)

113. 0.007 0.027 ↑ 1.0 14 1

Hash (cost=1.99..1.99 rows=14 width=24) (actual time=0.027..0.027 rows=14 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
114. 0.020 0.020 ↑ 1.0 14 1

Seq Scan on psampleworkflow sw_mintba_1 (cost=0.00..1.99 rows=14 width=24) (actual time=0.011..0.020 rows=14 loops=1)

  • Filter: ((sampleworkflowid)::text = ANY ('{4_50A,4_52A,4_53A,4_54A,4_55A,4_56A,4_91A,4_94A,4_96A,4_96B,4_96C,4_96D,91A,94A}'::text[]))
  • Rows Removed by Filter: 22
115. 20.000 37.134 ↑ 1.0 87,379 1

Hash (cost=3,183.79..3,183.79 rows=87,379 width=16) (actual time=37.134..37.134 rows=87,379 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 5120kB
116. 17.134 17.134 ↑ 1.0 87,379 1

Seq Scan on psamplerequesttrade srt_sc (cost=0.00..3,183.79 rows=87,379 width=16) (actual time=0.016..17.134 rows=87,379 loops=1)

117. 4.981 379,142.922 ↓ 3,783.0 3,783 1

Sort (cost=16,101.64..16,101.65 rows=1 width=280) (actual time=379,142.540..379,142.922 rows=3,783 loops=1)

  • Sort Key: srt.samplerequesttradeid, srs.samplerequestsubmitid, ((CASE brand.itemkey WHEN '5'::text THEN 'Hollister'::character varying ELSE brand.itemvalue END)::text), ((dept.itemkey)::text), firstfit.recdate, mintbasubmit.techbulkapproved, (count(samplecount.samplerequestsubmitid) OVER (?)), dg.groupname, dg.gendercustomkey, dg.sortorder, dgd.sortorder, ((CASE WHEN (firstfit.recdate < mintbasubmit.techbulkapproved) THEN date_part('day'::text, (mintbasubmit.techbulkapproved - firstfit.recdate)) ELSE '0'::double precision END)::bigint), ((CASE WHEN (firstfit.recdate < mintbasubmit.techbulkapproved) THEN 1 ELSE 0 END)::bigint)
  • Sort Method: quicksort Memory: 1101kB
118. 3.729 379,137.941 ↓ 3,783.0 3,783 1

WindowAgg (cost=16,101.59..16,101.63 rows=1 width=280) (actual time=379,133.664..379,137.941 rows=3,783 loops=1)

119. 16.357 379,134.212 ↓ 3,783.0 3,783 1

Sort (cost=16,101.59..16,101.60 rows=1 width=225) (actual time=379,133.643..379,134.212 rows=3,783 loops=1)

  • Sort Key: srw.samplerequestworkflowid, srt.samplerequesttradeid
  • Sort Method: quicksort Memory: 999kB
120. 158.743 379,117.855 ↓ 3,783.0 3,783 1

Nested Loop Left Join (cost=13,997.52..16,101.58 rows=1 width=225) (actual time=2,872.995..379,117.855 rows=3,783 loops=1)

121. 76,133.770 378,909.933 ↓ 3,783.0 3,783 1

Nested Loop (cost=13,996.99..16,101.02 rows=1 width=573) (actual time=2,872.912..378,909.933 rows=3,783 loops=1)

  • Join Filter: ((samplecount.cdate <= mintbasubmit.techbulkapproved) AND (mintbasubmit.samplerequesttradeid = srt.samplerequesttradeid) AND (mintbasubmit.statusid = srs.status) AND (mintbasubmit.techbulkapproved = ((srs.eav ->> 'eavfitcommentdate'::text))::timestamp without time zone))
  • Rows Removed by Join Filter: 697354791
122. 68.873 3,497.909 ↓ 16,698.0 16,698 1

Nested Loop (cost=13,995.58..15,861.74 rows=1 width=866) (actual time=2,353.721..3,497.909 rows=16,698 loops=1)

123. 36.634 3,187.449 ↓ 14,211.0 14,211 1

Nested Loop (cost=13,995.16..15,860.76 rows=1 width=605) (actual time=2,353.709..3,187.449 rows=14,211 loops=1)

124. 83.514 2,988.211 ↓ 6,254.0 6,254 1

Hash Join (cost=13,994.74..15,859.93 rows=1 width=573) (actual time=2,353.697..2,988.211 rows=6,254 loops=1)

  • Hash Cond: (samplecount.samplerequesttradeid = srt.samplerequesttradeid)
125. 711.306 711.306 ↓ 1.7 136,070 1

CTE Scan on samplecount (cost=0.00..1,570.68 rows=78,534 width=40) (actual time=160.199..711.306 rows=136,070 loops=1)

126. 4.778 2,193.391 ↓ 4,000.0 4,000 1

Hash (cost=13,994.73..13,994.73 rows=1 width=533) (actual time=2,193.391..2,193.391 rows=4,000 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1915kB
127. 4.819 2,188.613 ↓ 4,000.0 4,000 1

Hash Join (cost=3,096.64..13,994.73 rows=1 width=533) (actual time=1,980.572..2,188.613 rows=4,000 loops=1)

  • Hash Cond: (srt.samplerequesttradeid = firstfit.samplerequesttradeid)
128. 1.746 217.488 ↓ 1,078.0 4,312 1

Nested Loop (cost=3,082.92..13,980.78 rows=4 width=509) (actual time=14.255..217.488 rows=4,312 loops=1)

129. 1.029 198.002 ↓ 887.0 3,548 1

Nested Loop (cost=3,082.50..13,978.46 rows=4 width=509) (actual time=14.234..198.002 rows=3,548 loops=1)

130. 0.041 0.041 ↑ 1.0 1 1

Seq Scan on pseasonyear sy (cost=0.00..2.61 rows=1 width=16) (actual time=0.037..0.041 rows=1 loops=1)

  • Filter: (seasonyearid = 'dc409f7e-4c0e-4f41-83f3-59e9fc7ed5d9'::uuid)
  • Rows Removed by Filter: 48
131. 4.162 196.932 ↓ 887.0 3,548 1

Nested Loop (cost=3,082.50..13,975.81 rows=4 width=525) (actual time=14.193..196.932 rows=3,548 loops=1)

132. 3.797 169.066 ↓ 846.6 5,926 1

Hash Join (cost=3,082.09..13,964.90 rows=7 width=525) (actual time=14.164..169.066 rows=5,926 loops=1)

  • Hash Cond: ((dept.itemkey)::text = dgd.deptcustomkey)
133. 54.684 164.998 ↑ 1.0 5,934 1

Hash Join (cost=3,080.32..13,940.76 rows=5,949 width=397) (actual time=13.844..164.998 rows=5,934 loops=1)

  • Hash Cond: (fnx_try_cast_to_uuid((sh.eav ->> 'eavdepartment'::text)) = dept.lookupid)
134. 13.782 104.785 ↑ 1.0 5,934 1

Nested Loop (cost=2,653.67..13,363.79 rows=5,949 width=392) (actual time=8.072..104.785 rows=5,934 loops=1)

  • Join Filter: (ssy.styleid = sh.styleid)
135. 35.490 67.267 ↑ 1.0 5,934 1

Hash Join (cost=2,653.25..7,172.90 rows=5,949 width=64) (actual time=8.040..67.267 rows=5,934 loops=1)

  • Hash Cond: (var.styleid = ssy.styleid)
136. 23.804 23.804 ↑ 1.0 132,157 1

Seq Scan on pstyledevelopmentitem var (cost=0.00..3,964.57 rows=132,157 width=16) (actual time=0.015..23.804 rows=132,157 loops=1)

137. 2.084 7.973 ↑ 1.0 5,934 1

Hash (cost=2,578.89..2,578.89 rows=5,949 width=48) (actual time=7.973..7.973 rows=5,934 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 528kB
138. 5.345 5.889 ↑ 1.0 5,934 1

Bitmap Heap Scan on pstyleseasonyear ssy (cost=142.52..2,578.89 rows=5,949 width=48) (actual time=0.779..5.889 rows=5,934 loops=1)

  • Recheck Cond: (seasonyearid = 'dc409f7e-4c0e-4f41-83f3-59e9fc7ed5d9'::uuid)
  • Heap Blocks: exact=2185
139. 0.544 0.544 ↑ 1.0 5,934 1

Bitmap Index Scan on ix_pstyleseasonyear_seasonyearid (cost=0.00..141.04 rows=5,949 width=0) (actual time=0.544..0.544 rows=5,934 loops=1)

  • Index Cond: (seasonyearid = 'dc409f7e-4c0e-4f41-83f3-59e9fc7ed5d9'::uuid)
140. 23.736 23.736 ↑ 1.0 1 5,934

Index Scan using pk__pstyleheader__371114f6 on pstyleheader sh (cost=0.42..1.03 rows=1 width=376) (actual time=0.004..0.004 rows=1 loops=5,934)

  • Index Cond: (styleid = var.styleid)
141. 2.757 5.529 ↑ 1.0 10,651 1

Hash (cost=293.51..293.51 rows=10,651 width=21) (actual time=5.529..5.529 rows=10,651 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 690kB
142. 2.772 2.772 ↑ 1.0 10,651 1

Seq Scan on dlookup dept (cost=0.00..293.51 rows=10,651 width=21) (actual time=0.016..2.772 rows=10,651 loops=1)

143. 0.054 0.271 ↓ 4.5 49 1

Hash (cost=1.63..1.63 rows=11 width=160) (actual time=0.271..0.271 rows=49 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
144. 0.053 0.217 ↓ 4.5 49 1

Hash Join (cost=0.36..1.63 rows=11 width=160) (actual time=0.065..0.217 rows=49 loops=1)

  • Hash Cond: (dgd.groupingid = dg.groupid)
145. 0.113 0.113 ↑ 1.0 49 1

CTE Scan on af_devtimedeptgroupings_depts dgd (cost=0.00..0.98 rows=49 width=96) (actual time=0.006..0.113 rows=49 loops=1)

146. 0.025 0.051 ↑ 1.0 11 1

Hash (cost=0.22..0.22 rows=11 width=128) (actual time=0.051..0.051 rows=11 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
147. 0.026 0.026 ↑ 1.0 11 1

CTE Scan on af_devtimedeptgroupings dg (cost=0.00..0.22 rows=11 width=128) (actual time=0.004..0.026 rows=11 loops=1)

148. 23.704 23.704 ↑ 1.0 1 5,926

Index Scan using ix_pstylesamplerequesttrade_styleseasonyearid on pstylesamplerequesttrade ssrt (cost=0.42..1.55 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=5,926)

  • Index Cond: (styleseasonyearid = ssy.styleseasonyearid)
149. 17.740 17.740 ↑ 1.0 1 3,548

Index Scan using ix_psamplerequesttrade_stylesamplerequesttradeid on psamplerequesttrade srt (cost=0.42..0.57 rows=1 width=32) (actual time=0.004..0.005 rows=1 loops=3,548)

  • Index Cond: (stylesamplerequesttradeid = ssrt.stylesamplerequesttradeid)
150. 18.266 1,966.306 ↓ 192.6 81,283 1

Hash (cost=8.44..8.44 rows=422 width=24) (actual time=1,966.306..1,966.306 rows=81,283 loops=1)

  • Buckets: 131072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 5470kB
151. 1,948.040 1,948.040 ↓ 192.6 81,283 1

CTE Scan on firstfit (cost=0.00..8.44 rows=422 width=24) (actual time=1,861.527..1,948.040 rows=81,283 loops=1)

152. 162.604 162.604 ↑ 1.0 2 6,254

Index Scan using ix_psamplerequestworkflow_samplerequesttradeid on psamplerequestworkflow srw (cost=0.42..0.80 rows=2 width=32) (actual time=0.014..0.026 rows=2 loops=6,254)

  • Index Cond: (samplerequesttradeid = srt.samplerequesttradeid)
153. 241.587 241.587 ↑ 1.0 1 14,211

Index Scan using ix_psamplerequestsubmit_samplerequestworkflowid_submit on psamplerequestsubmit srs (cost=0.42..0.97 rows=1 width=277) (actual time=0.011..0.017 rows=1 loops=14,211)

  • Index Cond: (samplerequestworkflowid = srw.samplerequestworkflowid)
154. 206,186.865 299,278.254 ↓ 52.9 41,763 16,698

Hash Join (cost=1.41..217.58 rows=789 width=32) (actual time=0.027..17.923 rows=41,763 loops=16,698)

  • Hash Cond: (mintbasubmit.statusid = ss.statusid)
155. 93,091.350 93,091.350 ↓ 4.8 41,763 16,698

CTE Scan on mintbasubmit (cost=0.00..175.40 rows=8,770 width=28) (actual time=0.027..5.575 rows=41,763 loops=16,698)

156. 0.016 0.039 ↑ 1.0 18 1

Hash (cost=1.18..1.18 rows=18 width=4) (actual time=0.039..0.039 rows=18 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
157. 0.023 0.023 ↑ 1.0 18 1

Seq Scan on psamplerequestsubmitstatus ss (cost=0.00..1.18 rows=18 width=4) (actual time=0.020..0.023 rows=18 loops=1)

158. 49.179 49.179 ↑ 1.0 1 3,783

Index Scan using pk_dlookup on dlookup brand (cost=0.54..0.56 rows=1 width=28) (actual time=0.013..0.013 rows=1 loops=3,783)

  • Index Cond: (fnx_try_cast_to_uuid((sh.eav ->> 'eavbrand'::text)) = lookupid)