explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OuO2

Settings
# exclusive inclusive rows x rows loops node
1. 2,785.939 3,722.255 ↓ 37.7 113 1

Nested Loop Left Join (cost=42,917.01..44,010.07 rows=3 width=1,727) (actual time=2,979.561..3,722.255 rows=113 loops=1)

  • Join Filter: (c_av.orderid = c_o.orderid)
  • Rows Removed by Join Filter: 12,432
  • loops=1)
  • (actual time=0.002..0.011 rows=22 loops=113)
2.          

CTE cte_orders

3. 0.510 58.700 ↓ 1.0 113 1

GroupAggregate (cost=1,825.94..1,829.79 rows=110 width=63) (actual time=58.037..58.700 rows=113 loops=1)

  • Group Key: o_3.orderid
4. 1.010 58.190 ↓ 19.3 2,118 1

Sort (cost=1,825.94..1,826.21 rows=110 width=39) (actual time=58.010..58.190 rows=2,118 loops=1)

  • Sort Key: o_3.orderid DESC
  • Sort Method: quicksort Memory: 260kB
5. 0.868 57.180 ↓ 19.3 2,118 1

Nested Loop Left Join (cost=1.97..1,822.21 rows=110 width=39) (actual time=1.509..57.180 rows=2,118 loops=1)

6. 0.477 54.194 ↓ 19.3 2,118 1

Nested Loop Left Join (cost=1.69..1,787.64 rows=110 width=39) (actual time=1.492..54.194 rows=2,118 loops=1)

7. 0.000 49.481 ↓ 19.3 2,118 1

Nested Loop (cost=1.41..1,752.27 rows=110 width=51) (actual time=1.458..49.481 rows=2,118 loops=1)

8. 0.075 45.354 ↓ 19.3 2,118 1

Nested Loop (cost=0.98..1,684.81 rows=110 width=51) (actual time=1.439..45.354 rows=2,118 loops=1)

9. 0.370 41.043 ↓ 19.3 2,118 1

Nested Loop (cost=0.70..1,652.18 rows=110 width=51) (actual time=1.408..41.043 rows=2,118 loops=1)

10. 1.897 1.897 ↑ 1.0 1,048 1

Index Scan using ix_orders_businessid on orders o_3 (cost=0.28..70.00 rows=1,049 width=22) (actual time=0.058..1.897 rows=1,048 loops=1)

  • Index Cond: (businessid = ANY ('{0,941}'::integer[]))
  • Filter: (sourceid = ANY ('{0,1,2,3,4}'::integer[]))
11. 38.776 38.776 ↓ 2.0 2 1,048

Index Scan using ix_orderdetails_orderid on orderdetails od_6 (cost=0.42..1.50 rows=1 width=41) (actual time=0.033..0.037 rows=2 loops=1,048)

  • Index Cond: (orderid = o_3.orderid)
  • Filter: ((salesmanagerid = 785) AND (statusid = ANY ('{0,1,3}'::integer[])) AND ((785 = salesmanagerid) OR (o_3.createdbyemployeeid = 785)))
  • Rows Removed by Filter: 20
12. 4.236 4.236 ↑ 1.0 1 2,118

Index Only Scan using product_pkey on product pr (cost=0.28..0.30 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=2,118)

  • Index Cond: (productid = od_6.productid)
  • Heap Fetches: 2,118
13. 4.236 4.236 ↑ 1.0 1 2,118

Index Only Scan using ix_person_personidtype on person p_7 (cost=0.42..0.61 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=2,118)

  • Index Cond: (personid = o_3.clientid)
  • Heap Fetches: 2,118
14. 4.236 4.236 ↑ 1.0 1 2,118

Index Only Scan using stockproduct_unique on stockproduct sp (cost=0.28..0.31 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=2,118)

  • Index Cond: ((productid = od_6.productid) AND (businessid = o_3.businessid) AND (stockid = od_6.stockid))
  • Heap Fetches: 1,508
15. 2.118 2.118 ↓ 0.0 0 2,118

Index Scan using ix_ordertask_orderdetailsid on ordertask ot_3 (cost=0.29..0.30 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=2,118)

  • Index Cond: (orderdetailsid = od_6.orderdetailsid)
16.          

CTE cte_partialavailability

17. 1.752 169.590 ↑ 2.8 248 1

Subquery Scan on t_2 (cost=4,848.35..6,928.07 rows=686 width=76) (actual time=161.920..169.590 rows=248 loops=1)

  • Filter: (t_2.required > (SubPlan 3))
  • Rows Removed by Filter: 1,517
18. 27.781 162.295 ↑ 1.2 1,765 1

HashAggregate (cost=4,848.35..4,874.07 rows=2,058 width=44) (actual time=161.709..162.295 rows=1,765 loops=1)

  • Group Key: od_7.productid, COALESCE(od_7.batchid, 0), o_4.businessid
19. 19.845 134.514 ↓ 2.7 107,919 1

Hash Join (cost=934.65..4,444.07 rows=40,428 width=17) (actual time=11.696..134.514 rows=107,919 loops=1)

  • Hash Cond: (od_7.productid = p_8.productid)
20. 20.629 113.967 ↓ 2.7 107,919 1

Hash Left Join (cost=884.12..4,287.16 rows=40,428 width=17) (actual time=10.973..113.967 rows=107,919 loops=1)

  • Hash Cond: (o_4.businessid = c_1.companyid)
  • Filter: ((od_7.statusid = 1) OR (od_7.statusid = 3) OR (COALESCE(c_1.offer_in_availability, false) AND (od_7.statusid = 0)))
21. 20.333 92.985 ↓ 2.0 107,919 1

Hash Join (cost=867.56..4,127.20 rows=54,065 width=21) (actual time=10.612..92.985 rows=107,919 loops=1)

  • Hash Cond: (od_7.orderid = o_4.orderid)
22. 62.077 62.077 ↑ 1.0 107,919 1

Seq Scan on orderdetails od_7 (cost=0.00..2,975.65 rows=108,113 width=21) (actual time=0.010..62.077 rows=107,919 loops=1)

  • Filter: ((statusid = 1) OR (statusid = 3) OR (statusid = 0))
  • Rows Removed by Filter: 35,897
23. 1.071 10.575 ↓ 2.0 6,459 1

Hash (cost=827.19..827.19 rows=3,230 width=8) (actual time=10.575..10.575 rows=6,459 loops=1)

  • Buckets: 8,192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 317kB
24. 3.045 9.504 ↓ 2.0 6,459 1

Seq Scan on orders o_4 (cost=0.00..827.19 rows=3,230 width=8) (actual time=0.021..9.504 rows=6,459 loops=1)

  • Filter: (SubPlan 4)
25.          

SubPlan (for Seq Scan)

26. 6.459 6.459 ↑ 2.0 1 6,459

Seq Scan on stockbusiness stockbusiness_2 (cost=0.00..0.20 rows=2 width=4) (actual time=0.001..0.001 rows=1 loops=6,459)

  • Filter: (businessid = o_4.businessid)
  • Rows Removed by Filter: 3
27. 0.093 0.353 ↑ 1.0 678 1

Hash (cost=8.08..8.08 rows=678 width=5) (actual time=0.353..0.353 rows=678 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 33kB
28. 0.260 0.260 ↑ 1.0 678 1

Seq Scan on company c_1 (cost=0.00..8.08 rows=678 width=5) (actual time=0.011..0.260 rows=678 loops=1)

29. 0.215 0.702 ↓ 1.0 1,816 1

Hash (cost=27.85..27.85 rows=1,815 width=4) (actual time=0.702..0.702 rows=1,816 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 80kB
30. 0.487 0.487 ↓ 1.0 1,816 1

Seq Scan on product p_8 (cost=0.00..27.85 rows=1,815 width=4) (actual time=0.008..0.487 rows=1,816 loops=1)

31.          

SubPlan (for Subquery Scan)

32. 0.000 0.248 ↑ 1.0 1 248

Aggregate (cost=0.73..0.74 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=248)

33. 0.000 0.248 ↓ 0.0 0 248

Nested Loop (cost=0.28..0.72 rows=1 width=5) (actual time=0.001..0.001 rows=0 loops=248)

  • Join Filter: (stt.stockid = stockbusiness.stockid)
34. 0.248 0.248 ↓ 0.0 0 248

Index Scan using stockproduct_unique on stockproduct stt (cost=0.28..0.51 rows=1 width=9) (actual time=0.001..0.001 rows=0 loops=248)

  • Index Cond: ((productid = t_2.productid) AND (businessid = t_2.businessid))
  • Filter: CASE WHEN (t_2.batchid = 0) THEN true ELSE (COALESCE(batchid, 0) = t_2.batchid) END
35. 0.000 0.000 ↓ 0.0 0

Seq Scan on stockbusiness (cost=0.00..0.20 rows=2 width=4) (never executed)

  • Filter: (businessid = t_2.businessid)
36. 0.000 5.295 ↑ 1.0 1 1,765

Aggregate (cost=0.73..0.74 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=1,765)

37. 0.254 5.295 ↑ 1.0 1 1,765

Nested Loop (cost=0.28..0.72 rows=1 width=5) (actual time=0.003..0.003 rows=1 loops=1,765)

  • Join Filter: (stt_1.stockid = stockbusiness_1.stockid)
38. 3.530 3.530 ↑ 1.0 1 1,765

Index Scan using stockproduct_unique on stockproduct stt_1 (cost=0.28..0.51 rows=1 width=9) (actual time=0.002..0.002 rows=1 loops=1,765)

  • Index Cond: ((productid = t_2.productid) AND (businessid = t_2.businessid))
  • Filter: CASE WHEN (t_2.batchid = 0) THEN true ELSE (COALESCE(batchid, 0) = t_2.batchid) END
39. 1.511 1.511 ↑ 2.0 1 1,511

Seq Scan on stockbusiness stockbusiness_1 (cost=0.00..0.20 rows=2 width=4) (actual time=0.001..0.001 rows=1 loops=1,511)

  • Filter: (businessid = t_2.businessid)
  • Rows Removed by Filter: 4
40.          

CTE cte_relativeavailability

41. 0.783 2,769.198 ↑ 5.0 111 1

GroupAggregate (cost=325.04..25,474.78 rows=554 width=36) (actual time=201.117..2,769.198 rows=111 loops=1)

  • Group Key: o_5.orderid
42. 0.140 173.290 ↑ 4.4 125 1

Sort (cost=325.04..326.42 rows=554 width=65) (actual time=173.190..173.290 rows=125 loops=1)

  • Sort Key: o_5.orderid
  • Sort Method: quicksort Memory: 34kB
43. 0.127 173.150 ↑ 4.4 125 1

Merge Join (cost=226.92..299.79 rows=554 width=65) (actual time=172.432..173.150 rows=125 loops=1)

  • Merge Cond: (od_8.productid = p_9.productid)
44. 0.195 171.976 ↓ 1.0 589 1

Merge Left Join (cost=226.64..239.19 rows=568 width=61) (actual time=171.435..171.976 rows=589 loops=1)

  • Merge Cond: ((od_8.productid = part.productid) AND ((COALESCE(od_8.batchid, 0)) = part.batchid) AND (o_5.businessid = part.businessid))
45. 0.538 1.951 ↓ 1.0 589 1

Sort (cost=180.60..182.02 rows=568 width=29) (actual time=1.626..1.951 rows=589 loops=1)

  • Sort Key: od_8.productid, (COALESCE(od_8.batchid, 0)), o_5.businessid
  • Sort Method: quicksort Memory: 71kB
46. 0.104 1.413 ↓ 1.0 589 1

Nested Loop (cost=0.70..154.62 rows=568 width=29) (actual time=0.027..1.413 rows=589 loops=1)

47. 0.050 0.405 ↓ 1.0 113 1

Nested Loop (cost=0.28..47.60 rows=110 width=12) (actual time=0.013..0.405 rows=113 loops=1)

48. 0.016 0.016 ↓ 1.0 113 1

CTE Scan on cte_orders c_o_9 (cost=0.00..2.20 rows=110 width=4) (actual time=0.001..0.016 rows=113 loops=1)

49. 0.339 0.339 ↑ 1.0 1 113

Index Scan using order_pkey on orders o_5 (cost=0.28..0.41 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=113)

  • Index Cond: (orderid = c_o_9.orderid)
50. 0.904 0.904 ↑ 1.2 5 113

Index Scan using ix_orderdetails_orderid on orderdetails od_8 (cost=0.42..0.91 rows=6 width=25) (actual time=0.003..0.008 rows=5 loops=113)

  • Index Cond: (orderid = o_5.orderid)
  • Filter: (parentid IS NULL)
  • Rows Removed by Filter: 17
51. 0.131 169.830 ↑ 1.1 638 1

Sort (cost=46.04..47.75 rows=686 width=44) (actual time=169.802..169.830 rows=638 loops=1)

  • Sort Key: part.productid, part.batchid, part.businessid
  • Sort Method: quicksort Memory: 36kB
52. 169.699 169.699 ↑ 2.8 248 1

CTE Scan on cte_partialavailability part (cost=0.00..13.72 rows=686 width=44) (actual time=161.923..169.699 rows=248 loops=1)

53. 1.047 1.047 ↑ 1.1 1,555 1

Index Scan using product_pkey on product p_9 (cost=0.28..49.25 rows=1,771 width=8) (actual time=0.020..1.047 rows=1,555 loops=1)

  • Filter: (producttypeid = ANY ('{0,7,12,5,6}'::integer[]))
  • Rows Removed by Filter: 47
54.          

SubPlan (for GroupAggregate)

55. 2,574.875 2,595.125 ↑ 1.0 1 125

Aggregate (cost=45.10..45.11 rows=1 width=32) (actual time=20.761..20.761 rows=1 loops=125)

56. 1.362 20.250 ↑ 1.0 16 125

Nested Loop (cost=10.26..40.92 rows=16 width=57) (actual time=0.089..0.162 rows=16 loops=125)

57. 5.375 15.000 ↑ 1.0 16 125

Hash Right Join (cost=9.98..34.00 rows=16 width=53) (actual time=0.085..0.120 rows=16 loops=125)

  • Hash Cond: ((ccpart.productid = ccod.productid) AND (ccpart.batchid = COALESCE(ccod.batchid, 0)) AND (ccpart.businessid = cco.businessid))
58. 2.625 2.625 ↑ 2.8 248 125

CTE Scan on cte_partialavailability ccpart (cost=0.00..13.72 rows=686 width=44) (actual time=0.000..0.021 rows=248 loops=125)

59. 0.750 7.000 ↑ 1.0 16 125

Hash (cost=9.70..9.70 rows=16 width=25) (actual time=0.056..0.056 rows=16 loops=125)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
60. 0.931 6.250 ↑ 1.0 16 125

Nested Loop (cost=0.70..9.70 rows=16 width=25) (actual time=0.020..0.050 rows=16 loops=125)

61. 3.375 3.375 ↑ 1.0 16 125

Index Scan using ix_orderdetails_parentid on orderdetails ccod (cost=0.42..2.10 rows=16 width=25) (actual time=0.016..0.027 rows=16 loops=125)

  • Index Cond: (parentid = od_8.orderdetailsid)
62. 1.944 1.944 ↑ 1.0 1 1,944

Index Scan using order_pkey on orders cco (cost=0.28..0.47 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=1,944)

  • Index Cond: (orderid = ccod.orderid)
63. 3.888 3.888 ↑ 1.0 1 1,944

Index Scan using product_pkey on product ccp (cost=0.28..0.43 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1,944)

  • Index Cond: (productid = ccod.productid)
64. 34.624 935.412 ↓ 113.0 113 1

Nested Loop Left Join (cost=8,684.38..9,625.66 rows=1 width=1,418) (actual time=209.573..935.412 rows=113 loops=1)

  • Join Filter: (ord_3.orderid = c_o.orderid)
  • Rows Removed by Join Filter: 6,328
65. 11.560 900.788 ↓ 113.0 113 1

Nested Loop Left Join (cost=7,038.21..7,955.45 rows=1 width=1,386) (actual time=198.526..900.788 rows=113 loops=1)

  • Join Filter: (ord_2.orderid = c_o.orderid)
66. 421.287 889.228 ↓ 113.0 113 1

Nested Loop Left Join (cost=6,738.99..7,655.50 rows=1 width=1,354) (actual time=187.112..889.228 rows=113 loops=1)

  • Join Filter: (c_o.orderid = ot_2.orderid)
  • Rows Removed by Join Filter: 331,132
67. 0.132 467.941 ↓ 113.0 113 1

Nested Loop Left Join (cost=6,580.40..7,496.44 rows=1 width=1,322) (actual time=132.484..467.941 rows=113 loops=1)

68. 0.129 467.809 ↓ 113.0 113 1

Nested Loop Left Join (cost=6,580.13..7,496.15 rows=1 width=1,308) (actual time=132.470..467.809 rows=113 loops=1)

69. 0.154 467.680 ↓ 113.0 113 1

Nested Loop Left Join (cost=6,579.70..7,495.60 rows=1 width=1,294) (actual time=132.458..467.680 rows=113 loops=1)

70. 0.524 467.526 ↓ 113.0 113 1

Nested Loop Left Join (cost=6,579.43..7,495.31 rows=1 width=1,276) (actual time=132.440..467.526 rows=113 loops=1)

71. 5.674 467.002 ↓ 113.0 113 1

Nested Loop Left Join (cost=6,579.00..7,494.76 rows=1 width=1,262) (actual time=132.419..467.002 rows=113 loops=1)

  • Join Filter: (c_o_8.orderid = o.orderid)
  • Rows Removed by Join Filter: 4,843
72. 2.958 461.328 ↓ 113.0 113 1

Nested Loop Left Join (cost=6,488.83..7,398.76 rows=1 width=1,230) (actual time=131.562..461.328 rows=113 loops=1)

  • Join Filter: (c_o_7.orderid = o.orderid)
73. 8.494 458.370 ↓ 113.0 113 1

Nested Loop Left Join (cost=6,397.57..7,306.33 rows=1 width=1,198) (actual time=128.734..458.370 rows=113 loops=1)

  • Join Filter: (c_o_6.orderid = o.orderid)
  • Rows Removed by Join Filter: 6,325
74. 10.508 449.876 ↓ 113.0 113 1

Nested Loop Left Join (cost=6,324.70..7,231.93 rows=1 width=1,166) (actual time=127.052..449.876 rows=113 loops=1)

  • Join Filter: (od_2.orderid = o.orderid)
  • Rows Removed by Join Filter: 6,328
75. 0.238 439.368 ↓ 113.0 113 1

Nested Loop Left Join (cost=6,024.39..6,903.54 rows=1 width=1,030) (actual time=124.178..439.368 rows=113 loops=1)

76. 0.330 439.130 ↓ 113.0 113 1

Nested Loop Left Join (cost=6,024.25..6,903.37 rows=1 width=1,019) (actual time=124.164..439.130 rows=113 loops=1)

77. 0.644 438.800 ↓ 113.0 113 1

Nested Loop Left Join (cost=6,024.10..6,903.21 rows=1 width=1,008) (actual time=124.147..438.800 rows=113 loops=1)

78. 1.434 438.156 ↓ 113.0 113 1

Nested Loop Left Join (cost=6,023.67..6,902.76 rows=1 width=976) (actual time=124.130..438.156 rows=113 loops=1)

79. 1.168 436.722 ↓ 113.0 113 1

Nested Loop Left Join (cost=6,023.25..6,902.31 rows=1 width=944) (actual time=124.102..436.722 rows=113 loops=1)

  • Join Filter: (pp_1.personid = p.personid)
  • Rows Removed by Join Filter: 3,189
80. 13.393 435.554 ↓ 113.0 113 1

Nested Loop Left Join (cost=5,913.82..6,789.93 rows=1 width=916) (actual time=123.714..435.554 rows=113 loops=1)

  • Join Filter: (p_3.personid = p.personid)
  • Rows Removed by Join Filter: 5,251
81. 166.799 422.161 ↓ 113.0 113 1

Nested Loop Left Join (cost=5,788.35..6,663.91 rows=1 width=884) (actual time=121.164..422.161 rows=113 loops=1)

  • Join Filter: (ord_1.orderid = c_o.orderid)
  • Rows Removed by Join Filter: 6,328
82. 184.795 255.362 ↓ 113.0 113 1

Nested Loop (cost=3,617.39..4,381.49 rows=1 width=732) (actual time=89.342..255.362 rows=113 loops=1)

  • Join Filter: (o.orderid = od_1.orderid)
  • Rows Removed by Join Filter: 6,328
83. 0.711 70.567 ↓ 113.0 113 1

Nested Loop (cost=1,162.42..1,163.29 rows=1 width=367) (actual time=68.729..70.567 rows=113 loops=1)

84. 0.343 69.856 ↓ 113.0 113 1

Nested Loop (cost=1,161.99..1,162.75 rows=1 width=304) (actual time=68.712..69.856 rows=113 loops=1)

  • Join Filter: (o.stockid = stk.stockid)
85. 0.498 69.513 ↓ 113.0 113 1

Nested Loop (cost=1,161.99..1,162.60 rows=1 width=296) (actual time=68.705..69.513 rows=113 loops=1)

86. 0.227 69.015 ↓ 113.0 113 1

Merge Join (cost=1,161.72..1,162.31 rows=1 width=274) (actual time=68.685..69.015 rows=113 loops=1)

  • Merge Cond: (o.orderid = ddl.orderid)
87. 0.146 64.577 ↓ 18.8 113 1

Sort (cost=531.94..531.96 rows=6 width=238) (actual time=64.521..64.577 rows=113 loops=1)

  • Sort Key: c_o.orderid DESC
  • Sort Method: quicksort Memory: 57kB
88. 0.075 64.431 ↓ 18.8 113 1

Merge Right Join (cost=425.76..531.87 rows=6 width=238) (actual time=63.731..64.431 rows=113 loops=1)

  • Merge Cond: (od.orderid = o.orderid)
  • Filter: ((785 = ANY ((array_remove(array_agg(DISTINCT od.salesmanagerid), NULL::integer)))) OR (o.createdbyemployeeid = 785))
89. 0.530 64.033 ↑ 21.8 113 1

GroupAggregate (cost=374.43..448.20 rows=2,459 width=156) (actual time=63.407..64.033 rows=113 loops=1)

  • Group Key: od.orderid
90. 0.782 63.503 ↑ 1.0 2,408 1

Sort (cost=374.43..380.58 rows=2,459 width=32) (actual time=63.386..63.503 rows=2,408 loops=1)

  • Sort Key: od.orderid
  • Sort Method: quicksort Memory: 285kB
91. 0.457 62.721 ↑ 1.0 2,408 1

Hash Join (cost=55.75..235.94 rows=2,459 width=32) (actual time=58.938..62.721 rows=2,408 loops=1)

  • Hash Cond: (od.productid = p_1.productid)
92. 0.221 61.429 ↓ 1.0 2,533 1

Nested Loop (cost=0.70..174.42 rows=2,462 width=36) (actual time=58.078..61.429 rows=2,533 loops=1)

93. 0.078 59.174 ↓ 1.0 113 1

Nested Loop (cost=0.28..47.60 rows=110 width=8) (actual time=58.066..59.174 rows=113 loops=1)

94. 58.757 58.757 ↓ 1.0 113 1

CTE Scan on cte_orders c_o_1 (cost=0.00..2.20 rows=110 width=4) (actual time=58.041..58.757 rows=113 loops=1)

95. 0.339 0.339 ↑ 1.0 1 113

Index Only Scan using order_pkey on orders o_1 (cost=0.28..0.41 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=113)

  • Index Cond: (orderid = c_o_1.orderid)
  • Heap Fetches: 113
96. 2.034 2.034 ↑ 1.1 22 113

Index Scan using ix_orderdetails_orderid on orderdetails od (cost=0.42..0.91 rows=24 width=36) (actual time=0.004..0.018 rows=22 loops=113)

  • Index Cond: (orderid = o_1.orderid)
97. 0.274 0.835 ↓ 1.0 1,815 1

Hash (cost=32.39..32.39 rows=1,813 width=4) (actual time=0.835..0.835 rows=1,815 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 80kB
98. 0.561 0.561 ↓ 1.0 1,815 1

Seq Scan on product p_1 (cost=0.00..32.39 rows=1,813 width=4) (actual time=0.010..0.561 rows=1,815 loops=1)

  • Filter: (producttypeid <> ALL ('{5,6}'::integer[]))
  • Rows Removed by Filter: 1
99. 0.084 0.323 ↓ 1.0 113 1

Sort (cost=51.33..51.60 rows=110 width=182) (actual time=0.317..0.323 rows=113 loops=1)

  • Sort Key: c_o.orderid
  • Sort Method: quicksort Memory: 46kB
  • -> Nested Loop (cost=0.28..47.60 rows=110 width=182) (actual time=0.009..0.255 rows=113loops=1)
100. 0.013 0.013 ↓ 1.0 113 1

CTE Scan on cte_orders c_o (cost=0.00..2.20 rows=110 width=36) (actual time=0.001..0.013 rows=113 loops=1)

101. 0.226 0.226 ↑ 1.0 1 113

Index Scan using order_pkey on orders o (cost=0.28..0.41 rows=1 width=146) (actual time=0.002..0.002 rows=1 loops=113)

  • Index Cond: (orderid = c_o.orderid)
102. 0.080 4.211 ↓ 1.0 113 1

Sort (cost=629.78..630.05 rows=110 width=36) (actual time=4.159..4.211 rows=113 loops=1)

  • Sort Key: ddl.orderid DESC
  • Sort Method: quicksort Memory: 30kB
103. 0.009 4.131 ↓ 1.0 113 1

Subquery Scan on ddl (cost=623.57..626.05 rows=110 width=36) (actual time=4.101..4.131 rows=113 loops=1)

104. 0.182 4.122 ↓ 1.0 113 1

HashAggregate (cost=623.57..624.95 rows=110 width=36) (actual time=4.100..4.122 rows=113 loops=1)

  • Group Key: ord.orderid
105. 0.186 3.940 ↑ 1.0 589 1

Nested Loop Left Join (cost=302.32..609.80 rows=612 width=29) (actual time=1.342..3.940 rows=589 loops=1)

106. 0.141 3.754 ↑ 1.0 589 1

Hash Left Join (cost=302.03..415.45 rows=612 width=29) (actual time=1.330..3.754 rows=589 loops=1)

  • Hash Cond: (odd.orderdetailsid = dod.orderdetailsid)
107. 0.534 3.613 ↑ 1.0 589 1

Merge Right Join (cost=299.77..410.89 rows=612 width=24) (actual time=1.266..3.613 rows=589 loops=1)

  • Merge Cond: (ot.orderdetailsid = odd.orderdetailsid)
108. 1.873 1.873 ↑ 1.9 5,795 1

Index Scan using ix_ordertask_orderdetailsid on ordertask ot (cost=0.29..176.72 rows=11,002 width=8) (actual time=0.013..1.873 rows=5,795 loops=1)

109. 0.231 1.206 ↑ 1.0 589 1

Sort (cost=299.49..301.02 rows=612 width=20) (actual time=1.175..1.206 rows=589 loops=1)

  • Sort Key: odd.orderdetailsid
  • Sort Method: quicksort Memory: 70kB
110. 0.961 0.975 ↑ 1.0 589 1

Nested Loop (cost=0.42..271.16 rows=612 width=20) (actual time=0.013..0.975 rows=589 loops=1)

  • -> Index Scan using ix_orderdetails_orderid on orderdetails
111. 0.014 0.014 ↓ 1.0 113 1

CTE Scan on cte_orders ord (cost=0.00..2.20 rows=110 width=4) (actual time=0.000..0.014 rows=113 loops=1)

112. 0.000 0.904 ↑ 1.2 5 113

odd (cost=0.42..2.39 rows=6 width=20) (actual time=0.003..0.008 rows=5 loops=113)

  • Index Cond: (orderid = ord.orderid)
  • Filter: (parentid IS NULL)
  • Rows Removed by Filter: 17
  • -> Hash (cost=1.06..1.06 rows=96 width=13) (actual time=0.044..0.044 rows=96
  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
113. 0.026 0.026 ↑ 1.0 96 1

Seq Scan on deliveryorderdetails dod (cost=0.00..1.06 rows=96 width=13) (actual time=0.015..0.026 rows=96 loops=1)

114. 0.000 0.000 ↓ 0.0 0 589

Index Scan using pk_task on task t (cost=0.29..0.32 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=589)

  • Index Cond: (taskid = ot.taskid)
115. 0.339 0.339 ↑ 1.0 1 113

Index Scan using ix_company_companyidtypeid on company b (cost=0.28..0.29 rows=1 width=26) (actual time=0.003..0.003 rows=1 loops=113)

  • Index Cond: (companyid = o.businessid)
116. 0.113 0.113 ↑ 2.0 1 113

Seq Scan on stock stk (cost=0.00..0.12 rows=2 width=12) (actual time=0.001..0.001 rows=1 loops=113)

117. 0.565 0.565 ↑ 1.0 1 113

Index Scan using ix_person_personidtype on person p (cost=0.42..0.55 rows=1 width=63) (actual time=0.005..0.005 rows=1 loops=113)

  • Index Cond: (personid = o.clientid)
118. 177.905 183.964 ↑ 43.2 57 113

HashAggregate (cost=2,454.97..3,162.80 rows=2,462 width=445) (actual time=0.195..1.628 rows=57 loops=113)

  • Group Key: od_1.orderid
119. 2.105 6.059 ↓ 1.0 2,533 1

Nested Loop Left Join (cost=51.67..1,359.38 rows=2,462 width=91) (actual time=0.621..6.059 rows=2,533 loops=1)

120. 0.721 3.954 ↓ 1.0 2,533 1

Hash Join (cost=51.24..231.43 rows=2,462 width=91) (actual time=0.588..3.954 rows=2,533 loops=1)

  • Hash Cond: (od_1.productid = p_2.productid)
121. 0.419 2.706 ↓ 1.0 2,533 1

Nested Loop (cost=0.70..174.42 rows=2,462 width=87) (actual time=0.040..2.706 rows=2,533 loops=1)

122. 0.114 0.366 ↓ 1.0 113 1

Nested Loop (cost=0.28..47.60 rows=110 width=8) (actual time=0.022..0.366 rows=113 loops=1)

123. 0.026 0.026 ↓ 1.0 113 1

CTE Scan on cte_orders c_o_2 (cost=0.00..2.20 rows=110 width=4) (actual time=0.000..0.026 rows=113 loops=1)

124. 0.226 0.226 ↑ 1.0 1 113

Index Only Scan using order_pkey on orders o_2 (cost=0.28..0.41 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=113)

  • Index Cond: (orderid = c_o_2.orderid)
  • Heap Fetches: 113
125. 1.921 1.921 ↑ 1.1 22 113

Index Scan using ix_orderdetails_orderid on orderdetails od_1 (cost=0.42..0.91 rows=24 width=87) (actual time=0.003..0.017 rows=22 loops=113)

  • Index Cond: (orderid = o_2.orderid)
126. 0.258 0.527 ↓ 1.0 1,816 1

Hash (cost=27.85..27.85 rows=1,815 width=8) (actual time=0.527..0.527 rows=1,816 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 87kB
127. 0.269 0.269 ↓ 1.0 1,816 1

Seq Scan on product p_2 (cost=0.00..27.85 rows=1,815 width=8) (actual time=0.003..0.269 rows=1,816 loops=1)

128. 0.000 0.000 ↓ 0.0 0 2,533

Index Scan using pk_address on address odda (cost=0.43..0.46 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=2,533)

  • Index Cond: (addressid = od_1.deliveryaddressid)
129. 129.950 166.223 ↑ 1.9 57 113

GroupAggregate (cost=2,170.96..2,279.95 rows=110 width=156) (actual time=0.286..1.471 rows=57 loops=113)

  • Group Key: ord_1.orderid
130. 8.073 36.273 ↑ 2.1 1,250 113

Sort (cost=2,170.96..2,177.60 rows=2,656 width=53) (actual time=0.258..0.321 rows=1,250 loops=113)

  • Sort Key: ord_1.orderid
  • Sort Method: quicksort Memory: 405kB
131. 0.592 28.200 ↑ 1.0 2,593 1

Hash Left Join (cost=576.90..2,019.90 rows=2,656 width=53) (actual time=7.805..28.200 rows=2,593 loops=1)

  • Hash Cond: (iod.invoiceid = invv.invoiceid)
132. 9.545 21.964 ↑ 1.0 2,593 1

Hash Right Join (cost=324.16..1,760.19 rows=2,656 width=29) (actual time=2.083..21.964 rows=2,593 loops=1)

  • Hash Cond: (iod.orderdetailsid = odd_1.orderdetailsid)
133. 10.411 10.411 ↑ 1.0 98,468 1

Seq Scan on invoiceorderdetails iod (cost=0.00..1,048.35 rows=98,555 width=13) (actual time=0.019..10.411 rows=98,468 loops=1)

134. 0.549 2.008 ↑ 1.0 2,533 1

Hash (cost=290.96..290.96 rows=2,656 width=24) (actual time=2.008..2.008 rows=2,533 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 177kB
135. 1.443 1.459 ↑ 1.0 2,533 1

Nested Loop (cost=0.42..290.96 rows=2,656 width=24) (actual time=0.014..1.459 rows=2,533 loops=1)

136. 0.016 0.016 ↓ 1.0 113 1

CTE Scan on cte_orders ord_1 (cost=0.00..2.20 rows=110 width=4) (actual time=0.000..0.016 rows=113 loops=1)

137. 0.000 0.000 ↓ 0.0

Index Scan using ix_orderdetails_orderid on orderdetails odd_1 (cost=0.42..2.39 rows=24 width=24) (actual rows= loops=)

  • Index Cond: (orderid = ord_1.orderid)
138. 1.888 5.644 ↑ 1.0 10,200 1

Hash (cost=124.94..124.94 rows=10,224 width=24) (actual time=5.644..5.644 rows=10,200 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 766kB
139. 3.756 3.756 ↑ 1.0 10,200 1

Seq Scan on invoice invv (cost=0.00..124.94 rows=10,224 width=24) (actual time=0.013..3.756 rows=10,200 loops=1)

140. 10.283 12.995 ↓ 47.0 47 113

GroupAggregate (cost=125.47..125.99 rows=1 width=36) (actual time=0.035..0.115 rows=47 loops=113)

  • Group Key: p_3.personid
141. 0.508 2.712 ↓ 71.0 71 113

Sort (cost=125.47..125.47 rows=1 width=22) (actual time=0.020..0.024 rows=71 loops=113)

  • Sort Key: p_3.personid
  • Sort Method: quicksort Memory: 34kB
142. 0.118 2.204 ↓ 122.0 122 1

Nested Loop (cost=0.85..125.46 rows=1 width=22) (actual time=0.061..2.204 rows=122 loops=1)

  • Join Filter: (pp.phonetypeid = pt.phonetypeid)
  • Rows Removed by Join Filter: 141
143. 0.039 2.086 ↓ 122.0 122 1

Nested Loop (cost=0.85..125.27 rows=1 width=18) (actual time=0.053..2.086 rows=122 loops=1)

  • Join Filter: (c_o_3.clientid = pp.personid)
144. 0.087 0.578 ↓ 1.0 113 1

Nested Loop (cost=0.42..72.58 rows=110 width=18) (actual time=0.023..0.578 rows=113 loops=1)

145. 0.039 0.039 ↓ 1.0 113 1

CTE Scan on cte_orders c_o_3 (cost=0.00..2.20 rows=110 width=4) (actual time=0.002..0.039 rows=113 loops=1)

146. 0.452 0.452 ↑ 1.0 1 113

Index Scan using ix_person_personidtype on person p_3 (cost=0.42..0.64 rows=1 width=14) (actual time=0.004..0.004 rows=1 loops=113)

  • Index Cond: (personid = c_o_3.clientid)
147. 1.469 1.469 ↑ 1.0 1 113

Index Scan using ix_personphone_personid on personphone pp (cost=0.42..0.47 rows=1 width=18) (actual time=0.011..0.013 rows=1 loops=113)

  • Index Cond: (personid = p_3.personid)
  • Filter: ((p_3.phonebusiness)::text = (personphone)::text)
  • Rows Removed by Filter: 0
148. 0.000 0.000 ↑ 2.0 2 122

Seq Scan on phonetypes pt (cost=0.00..0.14 rows=4 width=10) (actual time=0.000..0.000 rows=2 loops=122)

149. 0.633 0.904 ↑ 3.1 29 113

HashAggregate (cost=109.43..110.34 rows=91 width=36) (actual time=0.004..0.008 rows=29 loops=113)

  • Group Key: pp_1.personid
150. 0.032 0.271 ↑ 1.2 75 1

Nested Loop (cost=0.42..86.45 rows=91 width=16) (actual time=0.024..0.271 rows=75 loops=1)

151. 0.013 0.013 ↓ 1.0 113 1

CTE Scan on cte_orders c_o_4 (cost=0.00..2.20 rows=110 width=4) (actual time=0.000..0.013 rows=113 loops=1)

152. 0.226 0.226 ↑ 1.0 1 113

Index Scan using ix_personphone_personid on personphone pp_1 (cost=0.42..0.76 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=113)

  • Index Cond: (personid = c_o_4.clientid)
  • Filter: (phonetypeid = 1)
  • Rows Removed by Filter: 1
153. 1.243 1.243 ↑ 1.0 1 113

Index Scan using pk_address on address cbad (cost=0.43..0.45 rows=1 width=40) (actual time=0.011..0.011 rows=1 loops=113)

  • Index Cond: (addressid = p.billingaddressid)
154. 0.452 0.452 ↑ 1.0 1 113

Index Scan using pk_address on address cvad (cost=0.43..0.45 rows=1 width=40) (actual time=0.004..0.004 rows=1 loops=113)

  • Index Cond: (addressid = p.visitingaddressid)
155. 0.226 0.226 ↑ 1.0 1 113

Index Scan using country_pk_contries on country cbcn (cost=0.14..0.16 rows=1 width=15) (actual time=0.002..0.002 rows=1 loops=113)

  • Index Cond: (countryid = cbad.countryid)
156. 0.113 0.113 ↑ 1.0 1 113

Index Scan using country_pk_contries on country cvcn (cost=0.14..0.16 rows=1 width=15) (actual time=0.001..0.001 rows=1 loops=113)

  • Index Cond: (countryid = cvad.countryid)
157. 7.119 10.057 ↓ 1.6 57 113

GroupAggregate (cost=300.31..327.61 rows=35 width=140) (actual time=0.027..0.089 rows=57 loops=113)

  • Group Key: od_2.orderid
158. 0.333 2.938 ↓ 1.7 61 113

Sort (cost=300.31..300.40 rows=35 width=49) (actual time=0.024..0.026 rows=61 loops=113)

  • Sort Key: od_2.orderid
  • Sort Method: quicksort Memory: 34kB
159. 0.213 2.605 ↓ 3.5 124 1

Hash Join (cost=1.89..299.41 rows=35 width=49) (actual time=0.089..2.605 rows=124 loops=1)

  • Hash Cond: (od_2.productid = p_4.productid)
160. 0.292 2.340 ↑ 1.0 2,533 1

Nested Loop (cost=0.42..290.96 rows=2,656 width=45) (actual time=0.019..2.340 rows=2,533 loops=1)

161. 0.014 0.014 ↓ 1.0 113 1

CTE Scan on cte_orders c_o_5 (cost=0.00..2.20 rows=110 width=4) (actual time=0.000..0.014 rows=113 loops=1)

162. 2.034 2.034 ↑ 1.1 22 113

Index Scan using ix_orderdetails_orderid on orderdetails od_2 (cost=0.42..2.39 rows=24 width=45) (actual time=0.004..0.018 rows=22 loops=113)

  • Index Cond: (orderid = c_o_5.orderid)
163. 0.008 0.052 ↑ 1.0 24 1

Hash (cost=1.17..1.17 rows=24 width=8) (actual time=0.052..0.052 rows=24 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
164. 0.044 0.044 ↑ 1.0 24 1

Index Scan using ix_product_producttype on product p_4 (cost=0.28..1.17 rows=24 width=8) (actual time=0.031..0.044 rows=24 loops=1)

  • Index Cond: (producttypeid = 8)
165. 5.989 8.023 ↓ 1.7 57 113

GroupAggregate (cost=72.86..73.63 rows=34 width=36) (actual time=0.016..0.071 rows=57 loops=113)

  • Group Key: c_o_6.orderid
166. 0.557 2.034 ↓ 2.7 92 113

Sort (cost=72.86..72.95 rows=34 width=8) (actual time=0.014..0.018 rows=92 loops=113)

  • Sort Key: c_o_6.orderid
  • Sort Method: quicksort Memory: 34kB
167. 0.104 1.477 ↓ 5.9 201 1

Nested Loop (cost=0.42..72.00 rows=34 width=8) (actual time=0.045..1.477 rows=201 loops=1)

168. 0.017 0.017 ↓ 1.0 113 1

CTE Scan on cte_orders c_o_6 (cost=0.00..2.20 rows=110 width=4) (actual time=0.001..0.017 rows=113 loops=1)

169. 1.356 1.356 ↓ 2.0 2 113

Index Scan using idx_filelink_refereceid on filelink fl (cost=0.42..0.62 rows=1 width=8) (actual time=0.010..0.012 rows=2 loops=113)

  • Index Cond: ((filetypeid = 19) AND (referenceid = c_o_6.orderid))
170. 0.000 2.825 ↓ 0.0 0 113

GroupAggregate (cost=91.26..91.84 rows=26 width=36) (actual time=0.025..0.025 rows=0 loops=113)

  • Group Key: c_o_7.orderid
171. 0.006 2.825 ↓ 0.0 0 113

Sort (cost=91.26..91.32 rows=26 width=8) (actual time=0.025..0.025 rows=0 loops=113)

  • Sort Key: c_o_7.orderid
  • Sort Method: quicksort Memory: 25kB
172. 0.355 2.819 ↓ 0.0 0 1

Merge Join (cost=58.64..90.65 rows=26 width=8) (actual time=2.819..2.819 rows=0 loops=1)

  • Merge Cond: (ot_1.taskid = fl_1.referenceid)
173. 0.075 0.649 ↑ 1.0 208 1

Sort (cost=57.53..58.05 rows=208 width=8) (actual time=0.637..0.649 rows=208 loops=1)

  • Sort Key: ot_1.taskid
  • Sort Method: quicksort Memory: 34kB
174. 0.108 0.574 ↑ 1.0 208 1

Nested Loop (cost=0.29..49.52 rows=208 width=8) (actual time=0.030..0.574 rows=208 loops=1)

175. 0.014 0.014 ↓ 1.0 113 1

CTE Scan on cte_orders c_o_7 (cost=0.00..2.20 rows=110 width=4) (actual time=0.001..0.014 rows=113 loops=1)

176. 0.452 0.452 ↑ 1.0 2 113

Index Scan using ix_ordertask_orderid on ordertask ot_1 (cost=0.29..0.41 rows=2 width=8) (actual time=0.004..0.004 rows=2 loops=113)

  • Index Cond: (orderid = c_o_7.orderid)
177. 1.815 1.815 ↓ 1.0 4,627 1

Index Scan using idx_filelink_refereceid on filelink fl_1 (cost=0.42..126.31 rows=4,594 width=8) (actual time=0.049..1.815 rows=4,627 loops=1)

  • Index Cond: (filetypeid = 13)
178. 4.294 5.311 ↑ 2.6 43 113

GroupAggregate (cost=90.18..93.53 rows=110 width=36) (actual time=0.008..0.047 rows=43 loops=113)

  • Group Key: c_o_8.orderid
179. 0.303 1.017 ↑ 3.9 58 113

Sort (cost=90.18..90.75 rows=227 width=8) (actual time=0.007..0.009 rows=58 loops=113)

  • Sort Key: c_o_8.orderid
  • Sort Method: quicksort Memory: 28kB
180. 0.006 0.714 ↑ 2.9 78 1

Nested Loop (cost=0.42..81.29 rows=227 width=8) (actual time=0.040..0.714 rows=78 loops=1)

181. 0.030 0.030 ↓ 1.0 113 1

CTE Scan on cte_orders c_o_8 (cost=0.00..2.20 rows=110 width=4) (actual time=0.001..0.030 rows=113 loops=1)

182. 0.678 0.678 ↑ 2.0 1 113

Index Scan using ix_contactmomentlink_orderid on contactmomentlink cml (cost=0.42..0.70 rows=2 width=8) (actual time=0.005..0.006 rows=1 loops=113)

  • Index Cond: (orderid = c_o_8.orderid)
183. 0.339 0.339 ↑ 1.0 1 113

Index Scan using ix_person_personidtype on person e (cost=0.42..0.55 rows=1 width=18) (actual time=0.003..0.003 rows=1 loops=113)

  • Index Cond: (personid = o.createdbyemployeeid)
184. 0.000 0.000 ↓ 0.0 0 113

Index Scan using ix_company_companyidtypeid on company c (cost=0.28..0.29 rows=1 width=22) (actual time=0.000..0.000 rows=0 loops=113)

  • Index Cond: (companyid = p.companyid)
185. 0.000 0.000 ↓ 0.0 0 113

Index Scan using ix_person_personidtype on person lnk (cost=0.42..0.55 rows=1 width=22) (actual time=0.000..0.000 rows=0 loops=113)

  • Index Cond: (personid = o.linktoclientid)
186. 0.000 0.000 ↓ 0.0 0 113

Index Scan using ix_company_companyidtypeid on company lnkc (cost=0.28..0.29 rows=1 width=22) (actual time=0.000..0.000 rows=0 loops=113)

  • Index Cond: (companyid = lnk.companyid)
187. 337.079 403.975 ↓ 293.1 2,931 113

GroupAggregate (cost=158.58..158.83 rows=10 width=36) (actual time=0.427..3.575 rows=2,931 loops=113)

  • Group Key: ot_2.orderid
188. 20.917 66.896 ↓ 317.3 3,173 113

Sort (cost=158.58..158.61 rows=10 width=13) (actual time=0.424..0.592 rows=3,173 loops=113)

  • Sort Key: ot_2.orderid
  • Sort Method: quicksort Memory: 464kB
189. 2.422 45.979 ↓ 579.6 5,796 1

Nested Loop (cost=1.27..158.42 rows=10 width=13) (actual time=0.133..45.979 rows=5,796 loops=1)

  • Join Filter: (t_1.taskstatusid = ts.taskstatusid)
  • Rows Removed by Join Filter: 8,196
190. 1.414 43.557 ↓ 579.6 5,796 1

Nested Loop (cost=1.27..157.26 rows=10 width=8) (actual time=0.118..43.557 rows=5,796 loops=1)

191. 5.031 30.551 ↓ 579.6 5,796 1

Nested Loop (cost=0.98..153.97 rows=10 width=8) (actual time=0.092..30.551 rows=5,796 loops=1)

192. 0.651 17.830 ↓ 32.2 7,690 1

Nested Loop (cost=0.70..78.66 rows=239 width=4) (actual time=0.059..17.830 rows=7,690 loops=1)

193. 0.016 0.016 ↑ 1.0 3 1

Index Scan using ix_product_producttype on product p_5 (cost=0.28..0.55 rows=3 width=4) (actual time=0.011..0.016 rows=3 loops=1)

  • Index Cond: (producttypeid = 3)
194. 17.163 17.163 ↓ 12.1 2,563 3

Index Scan using ix_orderdetails_productid on orderdetails od_3 (cost=0.42..23.93 rows=211 width=8) (actual time=0.026..5.721 rows=2,563 loops=3)

  • Index Cond: (productid = p_5.productid)
195. 7.690 7.690 ↑ 1.0 1 7,690

Index Scan using ix_ordertask_orderdetailsid on ordertask ot_2 (cost=0.29..0.31 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=7,690)

  • Index Cond: ((orderdetailsid = od_3.orderdetailsid) AND (orderdetailsid IS NOT NULL))
196. 11.592 11.592 ↑ 1.0 1 5,796

Index Scan using pk_task on task t_1 (cost=0.29..0.33 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=5,796)

  • Index Cond: (taskid = ot_2.taskid)
  • Filter: (taskstatusid > 0)
197. 0.000 0.000 ↑ 3.5 2 5,796

Materialize (cost=0.00..0.21 rows=7 width=13) (actual time=0.000..0.000 rows=2 loops=5,796)

198. 0.006 0.006 ↑ 1.0 7 1

Seq Scan on taskstatus ts (cost=0.00..0.17 rows=7 width=13) (actual time=0.005..0.006 rows=7 loops=1)

199. 0.000 11.413 ↓ 0.0 0 113

GroupAggregate (cost=299.23..299.59 rows=16 width=36) (actual time=0.101..0.101 rows=0 loops=113)

  • Group Key: ord_2.orderid
200. 0.010 11.413 ↓ 0.0 0 113

Sort (cost=299.23..299.27 rows=16 width=8) (actual time=0.101..0.101 rows=0 loops=113)

  • Sort Key: ord_2.orderid
  • Sort Method: quicksort Memory: 25kB
201. 0.114 11.403 ↓ 0.0 0 1

Nested Loop (cost=4.69..298.91 rows=16 width=8) (actual time=11.403..11.403 rows=0 loops=1)

  • Join Filter: (lod.orderid <> ord_2.orderid)
  • Rows Removed by Join Filter: 2,370
202. 0.563 8.971 ↓ 6.8 122 1

Hash Join (cost=4.27..281.03 rows=18 width=8) (actual time=6.903..8.971 rows=122 loops=1)

  • Hash Cond: (od_4.orderid = ord_2.orderid)
203. 0.543 8.367 ↓ 7.5 7,197 1

Nested Loop (cost=0.70..273.69 rows=956 width=8) (actual time=0.055..8.367 rows=7,197 loops=1)

204. 0.024 0.024 ↑ 1.0 12 1

Index Scan using ix_product_producttype on product p_6 (cost=0.28..0.78 rows=12 width=4) (actual time=0.012..0.024 rows=12 loops=1)

  • Index Cond: (producttypeid = 1)
205. 7.800 7.800 ↓ 2.8 600 12

Index Scan using ix_orderdetails_productid on orderdetails od_4 (cost=0.42..20.63 rows=211 width=12) (actual time=0.008..0.650 rows=600 loops=12)

  • Index Cond: (productid = p_6.productid)
206. 0.014 0.041 ↓ 1.0 113 1

Hash (cost=2.20..2.20 rows=110 width=4) (actual time=0.041..0.041 rows=113 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
207. 0.027 0.027 ↓ 1.0 113 1

CTE Scan on cte_orders ord_2 (cost=0.00..2.20 rows=110 width=4) (actual time=0.002..0.027 rows=113 loops=1)

208. 2.318 2.318 ↑ 1.0 19 122

Index Scan using ix_orderdetails_deliveryproductid on orderdetails lod (cost=0.42..0.76 rows=19 width=8) (actual time=0.007..0.019 rows=19 loops=122)

  • Index Cond: (deliveryproductid = od_4.orderdetailsid)
209. 17.741 34.239 ↑ 1.9 57 113

GroupAggregate (cost=1,646.16..1,667.73 rows=110 width=36) (actual time=0.098..0.303 rows=57 loops=113)

  • Group Key: ord_3.orderid
210. 6.569 16.498 ↑ 2.2 1,234 113

Sort (cost=1,646.16..1,652.80 rows=2,656 width=8) (actual time=0.093..0.146 rows=1,234 loops=113)

  • Sort Key: ord_3.orderid
  • Sort Method: quicksort Memory: 215kB
211. 0.396 9.929 ↑ 1.0 2,533 1

Nested Loop Left Join (cost=0.84..1,495.10 rows=2,656 width=8) (actual time=0.031..9.929 rows=2,533 loops=1)

  • Join Filter: (mod.orderid <> ord_3.orderid)
  • Rows Removed by Join Filter: 2,370
212. 0.210 1.934 ↑ 1.0 2,533 1

Nested Loop (cost=0.42..290.96 rows=2,656 width=8) (actual time=0.021..1.934 rows=2,533 loops=1)

213. 0.029 0.029 ↓ 1.0 113 1

CTE Scan on cte_orders ord_3 (cost=0.00..2.20 rows=110 width=4) (actual time=0.001..0.029 rows=113 loops=1)

214. 1.695 1.695 ↑ 1.1 22 113

Index Scan using ix_orderdetails_orderid on orderdetails od_5 (cost=0.42..2.39 rows=24 width=8) (actual time=0.005..0.015 rows=22 loops=113)

  • Index Cond: (orderid = ord_3.orderid)
215. 7.599 7.599 ↑ 1.0 1 2,533

Index Scan using pk_orderdetails on orderdetails mod (cost=0.42..0.44 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=2,533)

  • Index Cond: (orderdetailsid = od_5.deliveryproductid)
216. 2,770.421 2,770.421 ↑ 5.0 111 113

CTE Scan on cte_relativeavailability c_av (cost=0.00..11.08 rows=554 width=36) (actual time=1.780..24.517 rows=111 loops=113)

217.          

SubPlan (for odd)

218. 0.339 1.469 ↑ 1.0 1 113

Result (cost=8.81..8.82 rows=1 width=32) (actual time=0.012..0.013 rows=1 loops=113)

219.          

Initplan (for Result)

220. 1.130 1.130 ↑ 10.0 1 113

Index Scan using person_idx on person pmm (cost=0.42..8.81 rows=10 width=32) (actual time=0.009..0.010 rows=1 loops=113)

  • Index Cond: (personid = ANY ((array_remove(array_agg(DISTINCT od.salesmanagerid), NULL::integer))))
221. 0.452 0.904 ↑ 5.0 2 113

HashAggregate (cost=0.13..0.23 rows=10 width=32) (actual time=0.008..0.008 rows=2 loops=113)

  • Group Key: a.a
222. 0.452 0.452 ↓ 1.5 15 113

Function Scan on unnest a (cost=0.00..0.10 rows=10 width=32) (actual time=0.003..0.004 rows=15 loops=113)

223. 0.000 7.119 ↑ 1.0 1 113

Limit (cost=18.13..18.14 rows=1 width=8) (actual time=0.063..0.063 rows=1 loops=113)

224. 0.113 7.119 ↑ 1.0 1 113

Unique (cost=18.13..18.14 rows=1 width=8) (actual time=0.063..0.063 rows=1 loops=113)

225. 0.452 7.006 ↑ 1.0 1 113

Sort (cost=18.13..18.14 rows=1 width=8) (actual time=0.062..0.062 rows=1 loops=113)

  • Sort Key: rod.shipmentdate DESC
  • Sort Method: quicksort Memory: 25kB
226. 1.761 6.554 ↓ 19.0 19 113

Nested Loop (cost=0.84..18.12 rows=1 width=8) (actual time=0.013..0.058 rows=19 loops=113)

227. 2.260 2.260 ↑ 1.1 22 113

Index Scan using ix_orderdetails_orderid on orderdetails od_9 (cost=0.42..2.82 rows=24 width=4) (actual time=0.006..0.020 rows=22 loops=113)

  • Index Cond: (orderid = o.orderid)
228. 2.533 2.533 ↑ 1.0 1 2,533

Index Scan using pk_orderdetails on orderdetails rod (cost=0.42..0.64 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=2,533)

  • Index Cond: (orderdetailsid = od_9.deliveryproductid)
  • Filter: (shipmentdate IS NOT NULL)
  • Rows Removed by Filter: 0
229. 0.232 1.243 ↑ 1.0 1 113

Nested Loop (cost=2.05..15.80 rows=1 width=0) (actual time=0.011..0.011 rows=1 loops=113)

230. 0.087 0.791 ↑ 1.0 1 113

Hash Join (cost=1.77..15.36 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=113)

  • Hash Cond: (d_odd.productid = d_p.productid)
231. 0.203 0.678 ↑ 12.0 1 113

Nested Loop (cost=0.84..14.40 rows=12 width=8) (actual time=0.006..0.006 rows=1 loops=113)

232. 0.339 0.339 ↑ 18.0 1 113

Index Scan using ix_orderdetails_orderid on orderdetails odd_2 (cost=0.42..2.88 rows=18 width=8) (actual time=0.003..0.003 rows=1 loops=113)

  • Index Cond: (orderid = c_o.orderid)
  • Filter: (statusid = ANY ('{0,1}'::integer[]))
  • Rows Removed by Filter: 1
233. 0.136 0.136 ↑ 1.0 1 136

Index Scan using pk_orderdetails on orderdetails d_odd (cost=0.42..0.64 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=136)

  • Index Cond: (orderdetailsid = odd_2.deliveryproductid)
  • Filter: (statusid = ANY ('{0,1}'::integer[]))
  • Rows Removed by Filter: 0
234. 0.005 0.026 ↑ 1.0 12 1

Hash (cost=0.78..0.78 rows=12 width=4) (actual time=0.026..0.026 rows=12 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
235. 0.021 0.021 ↑ 1.0 12 1

Index Scan using ix_product_producttype on product d_p (cost=0.28..0.78 rows=12 width=4) (actual time=0.011..0.021 rows=12 loops=1)

  • Index Cond: (producttypeid = 1)
236. 0.220 0.220 ↑ 1.0 1 110

Index Scan using product_pkey on product p_10 (cost=0.28..0.43 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=110)

  • Index Cond: (productid = odd_2.productid)
  • Filter: (producttypeid <> 1)
237. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=56.04..965.12 rows=472 width=4) (never executed)

  • Hash Cond: (odd_3.productid = p_11.productid)
238. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.12..908.95 rows=475 width=8) (never executed)

239. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.70..226.47 rows=715 width=4) (never executed)

240. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_product_producttype on product d_p_1 (cost=0.28..0.78 rows=12 width=4) (never executed)

  • Index Cond: (producttypeid = 1)
241. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_orderdetails_statusid_productid on orderdetails d_odd_1 (cost=0.42..17.23 rows=158 width=8) (never executed)

  • Index Cond: ((productid = d_p_1.productid) AND (statusid = ANY ('{0,1}'::integer[])))
242. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_orderdetails_deliveryproductid on orderdetails odd_3 (cost=0.42..0.80 rows=15 width=12) (never executed)

  • Index Cond: (deliveryproductid = d_odd_1.orderdetailsid)
  • Filter: (statusid = ANY ('{0,1}'::integer[]))
243. 0.000 0.000 ↓ 0.0 0

Hash (cost=32.39..32.39 rows=1,803 width=4) (never executed)

244. 0.000 0.000 ↓ 0.0 0

Seq Scan on product p_11 (cost=0.00..32.39 rows=1,803 width=4) (never executed)

  • Filter: (producttypeid <> 1)
245. 0.113 0.339 ↑ 1.0 1 113

Aggregate (cost=0.51..0.52 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=113)

246. 0.226 0.226 ↓ 0.0 0 113

Index Scan using invoice_idx on invoice (cost=0.29..0.50 rows=1 width=3) (actual time=0.002..0.002 rows=0 loops=113)

  • Index Cond: (orderid = c_o.orderid)
Planning time : 34.235 ms
Execution time : 3,725.159 ms