explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4tl6

Settings
# exclusive inclusive rows x rows loops node
1. 37.217 10,312.815 ↓ 37,669.0 37,669 1

Unique (cost=168,679.93..168,679.97 rows=1 width=280) (actual time=10,258.901..10,312.815 rows=37,669 loops=1)

2.          

CTE af_devtimedeptgroupings

3. 0.004 0.014 ↑ 1.0 11 1

Append (cost=0.00..0.22 rows=11 width=128) (actual time=0.001..0.014 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.000..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.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)

15.          

CTE af_devtimedeptgroupings_depts

16. 0.007 0.056 ↑ 1.0 49 1

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

17. 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)

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.001..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.001..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.000..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.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)

32. 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)

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

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.001..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.000..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.001..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.001..0.001 rows=1 loops=1)

53. 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)

54. 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)

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

60. 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)

61. 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)

62. 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)

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. 114.611 457.143 ↓ 4.8 41,763 1

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

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

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

  • Hash Cond: (srt_mintba.samplerequesttradeid = srw_mintba.samplerequesttradeid)
69. 15.830 15.830 ↑ 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.023..15.830 rows=87,379 loops=1)

70. 29.124 279.610 ↓ 4.8 42,005 1

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

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

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

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

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

  • Hash Cond: (srw_mintba.samplerequestworkflowid = srs_mintba.samplerequestworkflowid)
73. 35.005 35.005 ↑ 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.006..35.005 rows=177,437 loops=1)

74. 27.019 125.870 ↓ 1.9 42,486 1

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

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

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

  • Hash Cond: (srs_mintba.status = ss_mintba.statusid)
76. 49.889 49.889 ↑ 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.007..49.889 rows=202,960 loops=1)

77. 0.003 0.010 ↑ 1.0 2 1

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

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

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

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

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

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

Seq Scan on psampleworkflow sw_mintba (cost=0.00..1.99 rows=14 width=24) (actual time=0.020..0.029 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. 153.010 655.927 ↑ 1.0 84,083 1

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

  • Group Key: srt_ff2.samplerequesttradeid
83. 53.902 502.917 ↑ 1.2 166,155 1

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

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

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

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

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

  • Hash Cond: (srs_ff2.samplerequestworkflowid = srw_ff2.samplerequestworkflowid)
86. 104.289 104.289 ↑ 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.022..104.289 rows=166,155 loops=1)

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

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

  • Buckets: 262144 Batches: 1 Memory Usage: 14178kB
88. 48.993 48.993 ↑ 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.013..48.993 rows=177,437 loops=1)

89. 22.411 40.501 ↑ 1.0 87,379 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 5120kB
90. 18.090 18.090 ↑ 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.031..18.090 rows=87,379 loops=1)

91. 0.027 0.068 ↑ 1.0 36 1

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

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

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

93.          

CTE firstfit

94. 36.561 1,875.048 ↓ 192.6 81,283 1

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

  • Group Key: srt_ff.samplerequesttradeid
95. 61.992 1,838.487 ↓ 192.6 81,283 1

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

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

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

97. 24.766 1,502.113 ↓ 192.6 81,283 1

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

98. 106.961 1,152.215 ↓ 192.2 81,283 1

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

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

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

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

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

101. 0.022 0.065 ↑ 1.0 14 1

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

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

Seq Scan on psampleworkflow sw_ff (cost=0.00..1.99 rows=14 width=48) (actual time=0.034..0.043 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. 325.132 325.132 ↑ 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.004 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.003..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. 107.227 528.324 ↓ 1.7 136,070 1

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

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

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

  • Hash Cond: (srs_sc.samplerequestworkflowid = srw_sc.samplerequestworkflowid)
109. 111.893 111.893 ↑ 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.024..111.893 rows=166,155 loops=1)

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

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

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

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

  • Hash Cond: ((srw_sc.sampleworkflowid)::text = (sw_mintba_1.sampleworkflowid)::text)
112. 37.607 37.607 ↑ 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.010..37.607 rows=177,437 loops=1)

113. 0.010 0.032 ↑ 1.0 14 1

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

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

Seq Scan on psampleworkflow sw_mintba_1 (cost=0.00..1.99 rows=14 width=24) (actual time=0.013..0.022 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.681 38.203 ↑ 1.0 87,379 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 5120kB
116. 17.522 17.522 ↑ 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.028..17.522 rows=87,379 loops=1)

117. 126.771 10,275.598 ↓ 78,689.0 78,689 1

Sort (cost=26,975.26..26,975.26 rows=1 width=280) (actual time=10,258.837..10,275.598 rows=78,689 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: 23974kB
118. 79.176 10,148.827 ↓ 78,689.0 78,689 1

WindowAgg (cost=26,975.21..26,975.25 rows=1 width=280) (actual time=10,049.205..10,148.827 rows=78,689 loops=1)

119. 95.873 10,069.651 ↓ 78,689.0 78,689 1

Sort (cost=26,975.21..26,975.21 rows=1 width=225) (actual time=10,049.157..10,069.651 rows=78,689 loops=1)

  • Sort Key: srw.samplerequestworkflowid, srt.samplerequesttradeid
  • Sort Method: quicksort Memory: 21768kB
120. 395.920 9,973.778 ↓ 78,689.0 78,689 1

Nested Loop Left Join (cost=25,108.40..26,975.20 rows=1 width=225) (actual time=5,548.673..9,973.778 rows=78,689 loops=1)

121. 896.155 9,420.480 ↓ 78,689.0 78,689 1

Hash Join (cost=25,107.86..26,974.63 rows=1 width=573) (actual time=5,548.615..9,420.480 rows=78,689 loops=1)

  • Hash Cond: ((srt.samplerequesttradeid = mintbasubmit.samplerequesttradeid) AND (((srs.eav ->> 'eavfitcommentdate'::text))::timestamp without time zone = mintbasubmit.techbulkapproved) AND (srs.status = mintbasubmit.statusid))
  • Join Filter: (samplecount.cdate <= mintbasubmit.techbulkapproved)
  • Rows Removed by Join Filter: 6551
122. 210.790 8,010.344 ↓ 393,831.0 393,831 1

Nested Loop (cost=24,876.47..26,742.63 rows=1 width=866) (actual time=5,034.560..8,010.344 rows=393,831 loops=1)

123. 83.785 6,518.910 ↓ 320,161.0 320,161 1

Nested Loop (cost=24,876.05..26,741.65 rows=1 width=605) (actual time=5,034.533..6,518.910 rows=320,161 loops=1)

124. 157.016 5,619.323 ↓ 135,967.0 135,967 1

Hash Join (cost=24,875.63..26,740.82 rows=1 width=573) (actual time=5,034.472..5,619.323 rows=135,967 loops=1)

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

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

126. 79.377 4,858.803 ↓ 81,265.0 81,265 1

Hash (cost=24,875.62..24,875.62 rows=1 width=533) (actual time=4,858.803..4,858.803 rows=81,265 loops=1)

  • Buckets: 131072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 38480kB
127. 82.970 4,779.426 ↓ 81,265.0 81,265 1

Hash Join (cost=446.91..24,875.62 rows=1 width=533) (actual time=1,930.711..4,779.426 rows=81,265 loops=1)

  • Hash Cond: (srt.samplerequesttradeid = firstfit.samplerequesttradeid)
128. 33.113 2,771.886 ↓ 856.3 87,345 1

Nested Loop (cost=433.19..24,856.28 rows=102 width=509) (actual time=5.511..2,771.886 rows=87,345 loops=1)

129. 14.222 2,426.397 ↓ 858.2 78,094 1

Nested Loop (cost=432.78..24,803.45 rows=91 width=509) (actual time=5.499..2,426.397 rows=78,094 loops=1)

130. 36.518 2,177.893 ↓ 858.2 78,094 1

Hash Join (cost=432.36..24,754.02 rows=91 width=541) (actual time=5.487..2,177.893 rows=78,094 loops=1)

  • Hash Cond: (ssy.seasonyearid = sy.seasonyearid)
131. 60.889 2,141.315 ↓ 858.2 78,094 1

Nested Loop (cost=429.25..24,749.78 rows=91 width=557) (actual time=5.407..2,141.315 rows=78,094 loops=1)

132. 40.345 1,687.609 ↓ 855.8 130,939 1

Nested Loop (cost=428.84..24,674.02 rows=153 width=557) (actual time=5.249..1,687.609 rows=130,939 loops=1)

133. 67.515 1,254.342 ↓ 856.0 130,974 1

Hash Join (cost=428.42..24,590.90 rows=153 width=509) (actual time=5.221..1,254.342 rows=130,974 loops=1)

  • Hash Cond: ((dept.itemkey)::text = dgd.deptcustomkey)
134. 1,136.226 1,186.666 ↑ 1.0 132,174 1

Hash Join (cost=426.65..24,091.77 rows=132,223 width=381) (actual time=5.024..1,186.666 rows=132,174 loops=1)

  • Hash Cond: (fnx_try_cast_to_uuid((sh.eav ->> 'eavdepartment'::text)) = dept.lookupid)
135. 45.552 45.552 ↑ 1.0 132,209 1

Seq Scan on pstyleheader sh (cost=0.00..20,325.23 rows=132,223 width=376) (actual time=0.006..45.552 rows=132,209 loops=1)

136. 2.281 4.888 ↑ 1.0 10,651 1

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

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

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

138. 0.017 0.161 ↓ 4.5 49 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
139. 0.024 0.144 ↓ 4.5 49 1

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

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

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

141. 0.011 0.033 ↑ 1.0 11 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
142. 0.022 0.022 ↑ 1.0 11 1

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

143. 392.922 392.922 ↑ 1.0 1 130,974

Index Scan using ix_pstyleseasonyear_styleid_seasonyearid on pstyleseasonyear ssy (cost=0.42..0.53 rows=1 width=48) (actual time=0.002..0.003 rows=1 loops=130,974)

  • Index Cond: (styleid = sh.styleid)
144. 392.817 392.817 ↑ 1.0 1 130,939

Index Scan using ix_pstylesamplerequesttrade_styleseasonyearid on pstylesamplerequesttrade ssrt (cost=0.42..0.49 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=130,939)

  • Index Cond: (styleseasonyearid = ssy.styleseasonyearid)
145. 0.024 0.060 ↑ 1.0 49 1

Hash (cost=2.49..2.49 rows=49 width=16) (actual time=0.060..0.060 rows=49 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
146. 0.036 0.036 ↑ 1.0 49 1

Seq Scan on pseasonyear sy (cost=0.00..2.49 rows=49 width=16) (actual time=0.026..0.036 rows=49 loops=1)

147. 234.282 234.282 ↑ 1.0 1 78,094

Index Only Scan using ix_pstyledevelopmentitem_styleid on pstyledevelopmentitem var (cost=0.42..0.53 rows=1 width=16) (actual time=0.002..0.003 rows=1 loops=78,094)

  • Index Cond: (styleid = ssy.styleid)
  • Heap Fetches: 78094
148. 312.376 312.376 ↑ 1.0 1 78,094

Index Scan using ix_psamplerequesttrade_stylesamplerequesttradeid on psamplerequesttrade srt (cost=0.42..0.57 rows=1 width=32) (actual time=0.003..0.004 rows=1 loops=78,094)

  • Index Cond: (stylesamplerequesttradeid = ssrt.stylesamplerequesttradeid)
149. 18.546 1,924.570 ↓ 192.6 81,283 1

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

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

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

151. 815.802 815.802 ↑ 1.0 2 135,967

Index Scan using ix_psamplerequestworkflow_samplerequesttradeid on psamplerequestworkflow srw (cost=0.42..0.80 rows=2 width=32) (actual time=0.004..0.006 rows=2 loops=135,967)

  • Index Cond: (samplerequesttradeid = srt.samplerequesttradeid)
152. 1,280.644 1,280.644 ↑ 1.0 1 320,161

Index Scan using ix_psamplerequestsubmit_samplerequestworkflowid_submit on psamplerequestsubmit srs (cost=0.42..0.97 rows=1 width=277) (actual time=0.003..0.004 rows=1 loops=320,161)

  • Index Cond: (samplerequestworkflowid = srw.samplerequestworkflowid)
153. 18.840 513.981 ↓ 52.4 41,343 1

Hash (cost=217.58..217.58 rows=789 width=32) (actual time=513.981..513.981 rows=41,343 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3096kB
154. 22.790 495.141 ↓ 52.9 41,763 1

Hash Join (cost=1.41..217.58 rows=789 width=32) (actual time=441.898..495.141 rows=41,763 loops=1)

  • Hash Cond: (mintbasubmit.statusid = ss.statusid)
155. 472.316 472.316 ↓ 4.8 41,763 1

CTE Scan on mintbasubmit (cost=0.00..175.40 rows=8,770 width=28) (actual time=441.847..472.316 rows=41,763 loops=1)

156. 0.012 0.035 ↑ 1.0 18 1

Hash (cost=1.18..1.18 rows=18 width=4) (actual time=0.035..0.035 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. 157.378 157.378 ↑ 1.0 1 78,689

Index Scan using pk_dlookup on dlookup brand (cost=0.54..0.56 rows=1 width=28) (actual time=0.002..0.002 rows=1 loops=78,689)

  • Index Cond: (fnx_try_cast_to_uuid((sh.eav ->> 'eavbrand'::text)) = lookupid)
Planning time : 13.377 ms
Execution time : 10,353.103 ms